New Posts New Posts RSS Feed: Parameterized Stored Procedure call in PassthruRdbQuery
  FAQ FAQ  Forum Search   Calendar   Register Register  Login Login

Parameterized Stored Procedure call in PassthruRdbQuery
 Post Reply Post Reply
Author
  Share Topic Share Topic  Topic Search Topic Search  Topic Options Topic Options
toddb View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Feb-2009
Location: Australia
Posts: 8 		Post Options Post Options   Quote toddb Quote  Post ReplyReply Direct Link To This Post Topic: Parameterized Stored Procedure call in PassthruRdbQuery
    Posted: 16-Sep-2010 at 12:11am
Hi,

Is it possible to call a Stored Procedure with multiple parameters via a PassthruRdbQuery?

A few lines extracted from my code are below:

            Dim Parameters(2) As RdbParameter
            Parameters(0) = New RdbParameter("@ModelName", DbType.AnsiString)
            Parameters(1) = New RdbParameter("@IDField", DbType.AnsiString)
            Parameters(2) = New RdbParameter("@Method", DbType.Byte)

            Parameters(0).Value = pModelName
            Parameters(1).Value = pIdField
            Parameters(2).Value = 1

            Dim sql As New ParameterizedSql("EXEC [Security].[GetNextAvailableId] @ModelName, @IDField, @Method", Parameters)
            Dim query As New PassthruRdbQuery(PS.GetDynamicType("NextAvailableId"), sql)

            Dim el = PM.GetEntities(query)

When it makes the GetEntities call it fails with the following exception:

"Must declare the scalar variable "@ModelName"

Any help would be greatly appreciated.

Thanks,
Todd
Back to Top
davidklitzke View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 14-Jun-2007
Posts: 715 		Post Options Post Options   Quote davidklitzke Quote  Post ReplyReply Direct Link To This Post Posted: 16-Sep-2010 at 2:51pm
See this post:

http://www.ideablade.com/forum/forum_posts.asp?TID=394&title=parameterizedsql-in-passthrurdbquery
Back to Top
toddb View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Feb-2009
Location: Australia
Posts: 8 		Post Options Post Options   Quote toddb Quote  Post ReplyReply Direct Link To This Post Posted: 16-Sep-2010 at 3:40pm
Hi David,

I had seen that post which appears to be the same as the Help documentation. Unfortunately it does not have an example of multiple parameters being passed through to a Stored Procedure which is where I think I am having problems.

Using SQL Server Profiler I can see it make the call to the database but I don't think it is actually passing the parameters. Should I be able to use a PassThruQuery in this way??

Kind regards,
Todd
Back to Top
kimj View Drop Down
IdeaBlade
IdeaBlade
Avatar

Joined: 09-May-2007
Posts: 1391 		Post Options Post Options   Quote kimj Quote  Post ReplyReply Direct Link To This Post Posted: 17-Sep-2010 at 12:23pm
You can use the PassthruRdbQuery in this way, although the parameter placeholder names in the ParameterizedSql need to be correct for the database provider.  If you're using the OleDb provider then "?" is the correct placeholder.  You can obtain the provider-specific name by calling rdbKey.AdoHelper.GetParameterPlaceHolderName(parm).
 
You can instead use a StoredProcRdbQuery for this, which might be a bit simpler.  You can create the query like this:
   Dim query as New StoredProcRdbQuery("security", "GetNextAvailableId", yourdynamicType)
 
and then add the parameters to the query.
Back to Top
toddb View Drop Down
Newbie
Newbie
Avatar

Joined: 04-Feb-2009
Location: Australia
Posts: 8 		Post Options Post Options   Quote toddb Quote  Post ReplyReply Direct Link To This Post Posted: 18-Sep-2010 at 4:47am
Hi Kim,

The placeholder was exactly where I was going wrong...all starting to make a little more sense now. Thank you!

Kind regards,
Todd
Back to Top
 Post Reply Post Reply

Forum Jump Forum Permissions View Drop Down