I am trying to port over a classic ASP application to .Net and it has been pretty smooth so far. (I know ideally this should be rewritten in ADO.Net but we do not have the time for that at the moment.)
One method requires a bunch of queries to be executed within a single transaction. The original code as is works just fine but I also need to address a SQL injection issue with it and implement parameterized queries.
Original code: (Works fine)
Sub UpdateSortOrder()
Dim sqlstr As String, x As Integer
Dim cn = Server.CreateObject("ADODB.Connection")
cn.Open(Application("Conn_ConnectionString"))
Dim rs = Server.CreateObject("ADODB.RecordSet")
rs.CursorType = 3
rs.ActiveConnection = cn
cn.BeginTrans()
For x = 1 To UBound(Session("DivisionsArray"))
sqlstr = "EXEC SPName" & _
"@Param1 = 'value1'," & _
"@Param2 = " & Value2 & "")
cn.Execute(sqlstr)
Next
cn.CommitTrans()
cn.Close()
End Sub
Updated Code: Gives error: COUNT field inccorrect or syntax error. It however seems to me that once cannot pass parameters to Connection object.
Sub UpdateSortOrder()
Dim sqlstr As String, x As Integer, cn As New ADODB.Connection()
cn.Open(Application("Conn_ConnectionString"))
cn.BeginTrans()
For x = 1 To UBound(Session("DivisionsArray"))
Dim rs As New ADODB.RecordSet(), cmd As New ADODB.Command()
cmd.CommandText = "SPName"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.Parameters.Append(cmd.CreateParameter("@Param1", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 10, value1))
cmd.Parameters.Append(cmd.CreateParameter("@Param2", ADODB.DataTypeEnum.adUnsignedTinyInt, ADODB.ParameterDirectionEnum.adParamInput, 1, value2))
cn.execute(cmd.CommandText, 1, cmd.CommandType)
cmd = Nothing
Next
cn.CommitTrans()
cn.Close()
End Sub
If I try this instead, I get the below error:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
Sub UpdateSortOrder()
Dim sqlstr As String, x As Integer, cn As New ADODB.Connection()
cn.Open(Application("Conn_ConnectionString"))
cn.BeginTrans()
For x = 1 To UBound(Session("DivisionsArray"))
Dim rs As New ADODB.RecordSet(), cmd As New ADODB.Command()
rs.CursorType = 3
rs.ActiveConnection = cn
cmd.CommandText = "SPName"
cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc
cmd.Parameters.Append(Param1)
cmd.Parameters.Append(Param2)
rs = cmd.Execute()
cmd = Nothing
rs = Nothing
Next
cn.CommitTrans()
cn.Close()
End Sub
From the sample code it is not clear where you are getting the different procedures and their parameters to execute in the loop. From what I understand, you are trying to execute more than one procedure with parameters in the context of a single transaction.
The code should be organised as follows:
1. Create and open connection outside the loop (as you are doing)
2. Create command object and set active connection outside the loop. Recordset object not required since you are not interested in the returned values
3. Inside the loop, clear parameters, set commandtext to procedure name and parameters for the procedure and execute.
Does Session("DivisionArray") have the procedure names and its parameters?
Thanks all..My issue was with classic ASP code, not .Net code. I decided it would be worth my while to rewrite the database calls using the ODBC .Net provider. Thanks for your time
adodbConnection.executeCommandParameters
Marked as answer by pkrishnap on May 07, 2013 07:15 PM
Unmarked as answer by pkrishnap on May 07, 2013 07:15 PM
We are. I was hoping to keep the old classic ASP way of database calls using ADODB without having to rewrite those pieces in ADO.Net's way of doing things. But figured it would probably be the worth the effort to use the ODBC .Net provider.
pkrishnap
Member
7 Points
24 Posts
How to execute multiple parameterized stored procedures within a single transaction using ADODB
Apr 29, 2013 05:26 PM|LINK
Hi There,
I am trying to port over a classic ASP application to .Net and it has been pretty smooth so far. (I know ideally this should be rewritten in ADO.Net but we do not have the time for that at the moment.)
One method requires a bunch of queries to be executed within a single transaction. The original code as is works just fine but I also need to address a SQL injection issue with it and implement parameterized queries.
Original code: (Works fine)
Sub UpdateSortOrder() Dim sqlstr As String, x As Integer Dim cn = Server.CreateObject("ADODB.Connection") cn.Open(Application("Conn_ConnectionString")) Dim rs = Server.CreateObject("ADODB.RecordSet") rs.CursorType = 3 rs.ActiveConnection = cn cn.BeginTrans() For x = 1 To UBound(Session("DivisionsArray")) sqlstr = "EXEC SPName" & _ "@Param1 = 'value1'," & _ "@Param2 = " & Value2 & "") cn.Execute(sqlstr) Next cn.CommitTrans() cn.Close() End SubUpdated Code: Gives error: COUNT field inccorrect or syntax error. It however seems to me that once cannot pass parameters to Connection object.
Sub UpdateSortOrder() Dim sqlstr As String, x As Integer, cn As New ADODB.Connection() cn.Open(Application("Conn_ConnectionString")) cn.BeginTrans() For x = 1 To UBound(Session("DivisionsArray")) Dim rs As New ADODB.RecordSet(), cmd As New ADODB.Command() cmd.CommandText = "SPName" cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc cmd.Parameters.Append(cmd.CreateParameter("@Param1", ADODB.DataTypeEnum.adVarChar, ADODB.ParameterDirectionEnum.adParamInput, 10, value1)) cmd.Parameters.Append(cmd.CreateParameter("@Param2", ADODB.DataTypeEnum.adUnsignedTinyInt, ADODB.ParameterDirectionEnum.adParamInput, 1, value2)) cn.execute(cmd.CommandText, 1, cmd.CommandType) cmd = Nothing Next cn.CommitTrans() cn.Close() End SubIf I try this instead, I get the below error:
The connection cannot be used to perform this operation. It is either closed or invalid in this context.
Sub UpdateSortOrder() Dim sqlstr As String, x As Integer, cn As New ADODB.Connection() cn.Open(Application("Conn_ConnectionString")) cn.BeginTrans() For x = 1 To UBound(Session("DivisionsArray")) Dim rs As New ADODB.RecordSet(), cmd As New ADODB.Command() rs.CursorType = 3 rs.ActiveConnection = cn cmd.CommandText = "SPName" cmd.CommandType = ADODB.CommandTypeEnum.adCmdStoredProc cmd.Parameters.Append(Param1) cmd.Parameters.Append(Param2) rs = cmd.Execute() cmd = Nothing rs = Nothing Next cn.CommitTrans() cn.Close() End Subadodb Connection.execute CommandParameters
Prashant Kum...
Star
12334 Points
1992 Posts
Re: How to execute multiple parameterized stored procedures within a single transaction using ADO...
May 01, 2013 12:01 AM|LINK
From the sample code it is not clear where you are getting the different procedures and their parameters to execute in the loop. From what I understand, you are trying to execute more than one procedure with parameters in the context of a single transaction.
The code should be organised as follows:
1. Create and open connection outside the loop (as you are doing)
2. Create command object and set active connection outside the loop. Recordset object not required since you are not interested in the returned values
3. Inside the loop, clear parameters, set commandtext to procedure name and parameters for the procedure and execute.
Does Session("DivisionArray") have the procedure names and its parameters?
adodb Connection.execute CommandParameters
ryanbesko
Contributor
3561 Points
619 Posts
Re: How to execute multiple parameterized stored procedures within a single transaction using ADO...
May 05, 2013 06:21 AM|LINK
To execute any SQL in one transaction use TransactionScope:
Using scope = New TransactionScope() ... your sql statements End Usingadodb Connection.execute CommandParameters
pkrishnap
Member
7 Points
24 Posts
Re: How to execute multiple parameterized stored procedures within a single transaction using ADO...
May 06, 2013 07:57 PM|LINK
Thanks all..My issue was with classic ASP code, not .Net code. I decided it would be worth my while to rewrite the database calls using the ODBC .Net provider. Thanks for your time
adodb Connection.execute CommandParameters
ryanbesko
Contributor
3561 Points
619 Posts
Re: How to execute multiple parameterized stored procedures within a single transaction using ADO...
May 07, 2013 02:06 AM|LINK
So you aren't migrating?
adodb Connection.execute CommandParameters
pkrishnap
Member
7 Points
24 Posts
Re: How to execute multiple parameterized stored procedures within a single transaction using ADO...
May 07, 2013 04:02 PM|LINK
We are. I was hoping to keep the old classic ASP way of database calls using ADODB without having to rewrite those pieces in ADO.Net's way of doing things. But figured it would probably be the worth the effort to use the ODBC .Net provider.
adodb Connection.execute CommandParameters