ADO: Stored Procedures & Parameters Insert Problem
- From: incubusaurus@xxxxxxxxx
- Date: 13 Mar 2007 02:44:29 -0700
Hi,
I am using ADO, in a fairly simple VB 6.0 client based database
application, to manipulate an Access database. When I use a stored
procedure with parametised inputs to insert multiple records into a
table, I end up with the same values repeated for each record, despite
changing the value of the parameter. I suspect that somewhere along
the line, the parameter value is being cached. To simplify this post,
I have written a small procedure (see below) which produces the same
results as the application.
The test database contains one table, TestCount, in which there are
two fields: an AutoNumber primary key field, and lngTestCount. Using
the stored procedure, twenty records are created, all with
lngTestCount set to 1. The AutoNumber field increments as expected,
and the debug print of the parameter value shows the parameter value
to be incrementing also.
When I run exactly the same SQL INSERT with the CommandType set to
adCmdText instead of adCmdStoredProc, it works correctly.
Can anyone help? I am sure I must be missing something very obvious.
Public Sub TestInsert()
Dim adoDBConn As ADODB.Connection
Dim adoCommand As ADODB.Command
Dim prmADOParam As ADODB.Parameter
Dim strConn As String
Dim strDBPath As String
Dim strCmd As String
Dim lngTestCount As Long
Set adoDBConn = New ADODB.Connection
Set adoCommand = New ADODB.Command
adoDBConn.Open "Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & App.Path & "\Test.mdb"
adoCommand.ActiveConnection = adoDBConn
On Error Resume Next
adoDBConn.Execute "DROP PROCEDURE sp_InsertCount"
On Error GoTo 0
strCmd = "CREATE PROCEDURE sp_InsertCount AS "
strCmd = strCmd & "INSERT INTO TestCount (lngTestCount) "
strCmd = strCmd & "VALUES (@lngTestCount)"
adoDBConn.Execute strCmd
adoCommand.CommandText = "sp_InsertCount"
adoCommand.CommandType = adCmdStoredProc
Set prmADOParam = adoCommand.CreateParameter _
("@lngTestCount", adInteger, adParamInput, , Null)
adoCommand.Parameters.Append prmADOParam
' ** This produces 20 rows all with the same value **
For lngTestCount = 1 To 20
adoCommand.Parameters("@lngTestCount").Value = lngTestCount
Debug.Print adoCommand.Parameters("@lngTestCount").Value
'adoDBConn.BeginTrans
adoCommand.Execute
'adoDBConn.CommitTrans
Next
strCmd = "INSERT INTO TestCount (lngTestCount) "
strCmd = strCmd & "VALUES (@lngTestCount)"
adoCommand.CommandText = strCmd
adoCommand.CommandType = adCmdText
' ** This produces 20 rows all with incrementing values **
For lngTestCount = 1 To 20
adoCommand.Parameters("@lngTestCount").Value = lngTestCount
Debug.Print adoCommand.Parameters("@lngTestCount").Value
'adoDBConn.BeginTrans
adoCommand.Execute
'adoDBConn.CommitTrans
Next
adoDBConn.Close
Set prmADOParam = Nothing
Set adoCommand = Nothing
Set adoDBConn = Nothing
End Sub
.
- Follow-Ups:
- Re: Stored Procedures & Parameters Insert Problem
- From: Bob Barrows [MVP]
- Re: Stored Procedures & Parameters Insert Problem
- Prev by Date: Re: C++ support for ADO
- Next by Date: ADODB.Connection for OID (Oracle Internet Directory) from ASP classic
- Previous by thread: CCommand Open freezing.
- Next by thread: Re: Stored Procedures & Parameters Insert Problem
- Index(es):
Relevant Pages
|
|
