Re: Stored Procedures & Parameters Insert Problem
- From: "microsoft" <nospam@xxxxxxxxxx>
- Date: Wed, 30 May 2007 12:03:12 +0200
I thought oledb uses questionmarks as placeholder for parameters.
so VALUES (@lngTestCount)
would be VALUES (?)
But besides that why are yoy createin a stored procedure in code, with
ADODB?
I would use ADOX or create paramterized queries in access.
Good Luck,
Arjen S
<incubusaurus@xxxxxxxxx> schreef in bericht
news:1173784166.315791.30060@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
Hi,
Firstly, sorry if this message ends up appearing twice. I received an
error when attempting to post it the first time, so I waited a while
to see if it appeared, and it didn't. Hopefully this will be the only
instance of it...
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 parametized 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.
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.
I have tried the code:
- With and without the BeginTrans and CommitTrans commands, both
inside and outside the loop.
- With different parameter names, with and without the @ symbol.
- With random numbers instead of incrementing numbers
- With and without the adExecuteNoRecords Execute option.
I have also spent many hours searching the web for an answer.
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
'adoCommand.Execute , , adCmdStoredProc Or adExecuteNoRecords
'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
'adoCommand.Execute , , adCmdStoredProc Or adExecuteNoRecords
'adoDBConn.CommitTrans
Next
adoDBConn.Close
Set prmADOParam = Nothing
Set adoCommand = Nothing
Set adoDBConn = Nothing
End Sub
.
- Prev by Date: invoking the DSN Administrator
- Previous by thread: invoking the DSN Administrator
- Index(es):
Relevant Pages
|
