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.
juststeveAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mmarinovCommented:
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
0
juststeveAuthor Commented:
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'.
0
mmarinovCommented:
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
0
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

juststeveAuthor Commented:
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)
0
mmarinovCommented:
juststeve,

sorry but i've made a typo ( please exuse me - i don't have a compiler working on this machine )
correct to this

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


Regards!
B..M
mmarinov
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
juststeveAuthor Commented:
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!
0
mmarinovCommented:
You are welcome juststeve
Glad to be in help

B..M
mmarinov
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.