asked on

Create a SqlParamArray

I'm trying to learn to use MS's Data Application Block which wraps a bunch of db connection stuff. I'm having problem calling the method below when I get to the point of passing in the ParamArray arg. I'm seeing lots of documentation about creating parameters for the DataAdapter and SqlCommand objects but not for creating a ParamArray from scratch.

ExecuteDataset(ByVal connection As SqlConnection, _
ByVal commandType As CommandType, _
ByVal commandText As String, _
ByVal ParamArray commandParameters() As SqlParameter)

I would _think I'd be able to do something like:

Dim myParams = New System.Data.SqlClient.SqlParameter

myParams.Add("@facilityID", SqlDbType.Int).Value = "11111"
myParams.Add("@Address", SqlDbType.Int).Value = "11 Elm Street"

But um....obviously not.

mmarinov

Hi juststeve,

Dim myParams as SqlParameter() = New System.Data.SqlClient.SqlParameter(2)

myParams.Add("@facilityID", SqlDbType.Int).Value = "11111"
myParams.Add("@Address", SqlDbType.Int).Value = "11 Elm Street"

Regards!
B..M
mmarinov

juststeve

ASKER

Using the syntax as above i get:
Overload resolution failed because no accessible 'New' accepts this number of arguments.

Trying an alternate form:
Dim myParams As SqlParameter = New System.Data.SqlClient.SqlParameter
throws an error on each of my 'Add' statements:
'Add' is not a member of 'System.Data.SqlClient.SqlParameter'.

mmarinov

juststeve,

sorry, i've mixed vb and c#:(
try this

Dim p1 as SqlParameter = new SqlParemeter("@facilityID", SqlDbType.Int)
p1.Value = "11111"
Dim p2 as SqlParameter = new SqlParemeter("@Address", SqlDbType.Int)
p2.Value = "11 Elm Street"

Dim myParams() as SqlParameter = { p1, p2 }

Regards!
B..M
mmarinov

juststeve

ASKER

The above will compile but when the page is served iis tosses an exception:
Exception Details: System.InvalidCastException: Specified cast is not valid.

pointing at:
Dim p1 as SqlParameter = new SqlParemeter("@facilityID", SqlDbType.Int)

ASKER CERTIFIED SOLUTION

mmarinov

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

juststeve

ASKER

Acually I'd caught that typo in my code but simply pasted from this page when I replied back. I found the problem to be in the argument list of the sproc itself. I had a mismatched number of arguments being passed in vs. what the sproc expected. Corrected that and your code worked fine.

thankx for the speedy attention to my problem!

mmarinov

You are welcome juststeve
Glad to be in help

B..M
mmarinov