How to user multiple Params with stored procedure..?

[melegant]

Notice my big thumbs down.

 

So, why the HECK won't this work@@!@#E

 

the two Arrays are private fields (arraylists actually) in the class (I use a method to fill them, the values are correct...argh!)

 

Here is the stored proc

CREATE PROC sp_AddCustomer @cname varchar(100),@addr varchar(150),@city varchar(100),@st varchar(4),@zip varchar(12) as INSERT INTO custinf (cname,addr,city,st,zip) VALUES (@cname , @addr, @city, @st, @zip)

 

Public Overloads Sub InitCmd()
       Dim hold As String
       Dim num As Integer = MarrParmVal.Count - 1

       MSQLCmd = New SqlClient.SqlCommand(MSp, m_SQLcn)
       m_SQLcn.Open()
       MSQLCmd.CommandType = CommandType.StoredProcedure
       Dim i As Int32
       For i = 0 To num
           hold = MarrParmVal(i).GetType.ToString
           Select Case hold
               Case "System.Int32"
                   MSQLCmd.Parameters.Add(MarrParm(i), SqlDbType.Int)
               Case "System.String"
                   MSQLCmd.Parameters.Add(MarrParm(i), SqlDbType.VarChar)
               Case Else
           End Select
           MSQLCmd.Parameters(MarrParm(i)).Value = MarrParmVal(i)
       Next

       Try
          MSQLCmd.ExecuteNonQuery()
       Catch eos As System.Data.SqlClient.SqlException
            ErrHandle(eos.Number)
       End Try
       m_SQLcn.Close()
   End Sub

Edited April 7, 2003 by melegant

[Nerseus]

Have you walked through this code? I don't see how your loop is working, or at least not how you expect it to. You have "num" used as the loop and as the Max value. Try using something like:

 

Dim i as Int32
For i = 0 To num
...
Next

 

Then inside your loop, change all the references to "num" with "i" since it is now your loop counter.

 

-Nerseus

[melegant]

The Num actually worked as I stepped through this bastage piece of code many , many times,

however I changed it to what you suggested.

 

and i still have the same problem. i get a non specific error..that is what is killing me softly.

 

I recieve the error when I try to execute the non query.

[melegant]

For the love of all that is holy...

I figured it out.

 

DTS = :eek:

 

I copied the custinf table from another sql server i am running...I selected enable identity..however it did not take.

SO, when I tried inserting the row, i would get an error that SQL could not insert the row because the custid field could not be null.

 

HAHAHAHA. ow my brain.

 

thanks though.

mel

haha..enable identity insert..not make field identity and work

Edited April 7, 2003 by melegant