Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 746
  • Last Modified:

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.
0
juststeve
Asked:
juststeve
  • 4
  • 3
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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
 
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now