How to user multiple Params with stored procedure..?

[melegant]

Notice my big thumbs down.

So, why the HECK wont 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
[sql]

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)

[/sql]

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

 

[Nerseus]

Have you walked through this code? I dont 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 =

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