CODEWITHSUNDEEP
sql-servervb6
suvi
suvi

Reputation: 37

Run time error '91' when using MSSql

I'm making a program in which I have to check some column values in another table before trying to save values in a different table.. both tables are in SQL.

I tried my best to do it myself but I get the error near the highlighted line.

rs.open(insert into testreport_tb1...

Private Sub Command1_Click()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim BrdSrNo As String
Dim Result As Boolean
Dim machineName As String

machineName = Environ("computername")
' Ready objects for use.
Set cn = New ADODB.Connection
Set rs = New ADODB.Recordset
BrdSrNo = BoardSrNo.Text
Result = False
' Connect.
cn.Open "{Here I give the connection string}"
' Fetch a recordset.
rs.Open "select * from testreport_tb1 where board_SrNo = '" & BrdSrNo & "' order by test_DateTime desc", cn, adOpenStatic, adLockReadOnly
' Display value, and total recordcount.
MsgBox rs.Fields(3)
MsgBox rs.Fields(8)
'MsgBox rs.RecordCount
stage_Status = rs.Fields(3)
stage_Id = rs.Fields(8)
rs.Close
cn.Close

If stage_Status = "C" Then
If stage_Id = "True" Then
rs.Open "insert into testreport_tb1 values('" & BrdSrNo & "',3,GETDATE(),'" & Result & "',NULL,'" & machineName & "',' KO ','A','D')", cn, adOpenDynamic, adLockBatchOptimistic
MsgBox "saved"
End If
End If
' Close and release objects.
rs.Close
cn.Close
Set rs = Nothing
Set cn = Nothing

End Sub

Upvotes: 0

Views: 85

Answers (1)

Zohar Peled
Zohar Peled

Reputation: 82474

As far as I remember, you can't use rs.Open when executing DML statements, (insert, update or delete), but only when you are executing select statements.

Also, you need to use ADODB.Command and set parameters instead of concatenating strings to create your insert statement, otherwise it's an open door for sql injection attacks.

It's been a very long time since the last time I've worked with ADODB, but your insert code should look something like this:

If stage_Status = "C" And  stage_Id = "True" Then
     Dim cmd as new ADODB.Command
     cmd.CommandText = "insert into testreport_tb1 values(?, 3, GETDATE(), ?, NULL, ?, ' KO ', 'A', 'D')"

     cmd.ActiveConnection = cn

     Set param = cmd.CreateParameter(, adVarChar, adParamInput)
     param.Value = BrdSrNo
     cmd.Parameters.Append param

     Set param = cmd.CreateParameter(, adVarChar, adParamInput)
     param.Value = Result
     cmd.Parameters.Append param

     Set param = cmd.CreateParameter(, adVarChar, adParamInput)
     param.Value = machineName
     cmd.Parameters.Append param

     cmd.Execute

  MsgBox "saved"
End If

Note: Code was written directly here, and as I wrote, it's been a long time since I've used ADODB, so there might be mistakes in the code. However, this is the proper way of executing an insert statement with ADODB.

Upvotes: 2

Related Questions