Print Page | Close Window

Parameterized Stored Procedure call in PassthruRdbQuery

Printed From: IdeaBlade
Category: DevForce
Forum Name: DevForce Classic
Forum Discription: For .NET 2.0
URL: http://www.ideablade.com/forum/forum_posts.asp?TID=2162
Printed Date: 13-Sep-2025 at 7:42pm


Topic: Parameterized Stored Procedure call in PassthruRdbQuery
Posted By: toddb
Subject: Parameterized Stored Procedure call in PassthruRdbQuery
Date 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


Replies:
Posted By: davidklitzke
Date Posted: 16-Sep-2010 at 2:51pm
See this post:

http://www.ideablade.com/forum/forum_posts.asp?TID=394&title=parameterizedsql-in-passthrurdbquery

Posted By: toddb
Date 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

Posted By: kimj
Date 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.

Posted By: toddb
Date 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


Print Page | Close Window