Can execute a query with multiple parameters

30 Mar 2006 6:16 PM

mrmagoo

Dim cn As SqlConnection = New SqlConnection("<ConnStr>")
        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand("scsp_Stored_Proc", cn)
        cmd.CommandType = CommandType.StoredProcedure
        Dim pc As SqlParameterCollection = cmd.Parameters

        pc.Add("@Param1", Param1Val)
        pc.Add("@Param2", Param2Val)
        If Param3Val <> 0 Then
            pc.Add("@Param3", Param3Val)
        Else
            pc.Add("@Param4", Param4Val)
        End If
        cmd.ExecuteNonQuery()

what am I doing wrong?

It's erroring out on the last line:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred
in system.data.dll

Additional information: System error.

30 Mar 2006 6:35 PM

mrmagoo

I got it. It was expecting nulls. I used System.DBNull.Value for the other
params in the If..then condition.

Show quoteHide quote

"mrmagoo" <-> wrote in message
news:%2320tXYCVGHA.5592@TK2MSFTNGP09.phx.gbl...
>         Dim cn As SqlConnection = New SqlConnection("<ConnStr>")
>         cn.Open()
>         Dim cmd As SqlCommand = New SqlCommand("scsp_Stored_Proc", cn)
>         cmd.CommandType = CommandType.StoredProcedure
>         Dim pc As SqlParameterCollection = cmd.Parameters
>
>         pc.Add("@Param1", Param1Val)
>         pc.Add("@Param2", Param2Val)
>         If Param3Val <> 0 Then
>             pc.Add("@Param3", Param3Val)
>         Else
>             pc.Add("@Param4", Param4Val)
>         End If
>         cmd.ExecuteNonQuery()
>
> what am I doing wrong?
>
> It's erroring out on the last line:
>
> An unhandled exception of type 'System.Data.SqlClient.SqlException'
occurred
> in system.data.dll
>
> Additional information: System error.
>
>

30 Mar 2006 8:04 PM

parez

I think you should have looked at the inner exception.. that would
have told you the exact problem.