Recordset not editable 1

[llmclaughlin]

Changing vb6 inline sql to stored procedures, having problem with query where the recordset needs to be editable.
Below is current code:

adoProbeVials.CursorLocation = adUseClient
adoProbeVials.Open strSQL, adoConnection, adOpenDynamic, adLockBatchOptimistic, adCmdText

New code
adoCmd.ActiveConnection = adoConnection
adoCmd.CommandText = "{CALL LIMS.ALIQUOT_WORKLIST.get_PROBEVIALS(?,?)}"
adoCmd.CommandType = adCmdText


adoCmd.Parameters.Append adoCmd.CreateParameter("inNAME", adVarChar, adParamInput, 2000, adoRS(2))
adoCmd.Parameters.Append adoCmd.CreateParameter("inVOLUME", adVarChar, adParamInput, 2000, strVolumeMin)
adoProbeVials.CursorLocation = adUseClient
adoProbeVials.CursorType = adOpenDynamic
adoProbeVials.LockType = adLockBatchOptimistic
Set adoProbeVials = adoCmd.Execute()

The new code does not work when it gets to this.
adoProbeVials(3).Value = dblVolume
Get error that table is not editable.

Any suggestions

Thanks

Louie

 

[Bluejay07]

Here is some code that I use. Also, notice that my command type references a stored procedure, not text since you are calling a stored procedure and not passing a text query.

Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = qmCONN
   .CommandType = adCmdStoredProc
   .CommandText = "cpyBillToShipTo"
   .Parameters.Append .CreateParameter("BTID", adInteger, adParamInput)
   .Parameters("BTID").Value = g_udtQuote.BillToID
   .Parameters.Append .CreateParameter("Auto", adInteger, adParamOutput)
   .Execute , , ADODB.adExecuteNoRecords
End With
Set cmd = Nothing

I hope this may be helpful to you.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[Bluejay07]

Now if you want to create a recordset and edit it while referencing a stored procedure, then this code might help.

Set cmd = New ADODB.Command
Set rs = New ADODB.Recordset
With cmd
   .ActiveConnection = qmCONN
   .CommandType = adCmdStoredProc
   .CommandText = "sprCustomerOneID"
   .Parameters.Append .CreateParameter("CID", adInteger, adParamInput)
   .Parameters("CID").Value = pCustomerID
End With

rs.Open cmd, , adOpenStatic, adLockOptimistic

If Not (rs.BOF And rs.EOF) Then
   rs.MoveFirst
   
   'Insert or update information here.
End If

If at first you don't succeed, then sky diving wasn't meant for you!

 

[llmclaughlin]

What I'm doing is calling a cursor in an oracle stored procedure.
when I try this


With adoCmd
.ActiveConnection = adoConnection
.CommandType = adCmdText
.CommandText = "{CALL LIMS.ALIQUOT_WORKLIST.get_PROBEVIALS(?,?)}"
.Parameters.Append .CreateParameter("inNAME", adVarChar, adParamInput, 2000)
.Parameters("inNAME").Value = adoRS(2)
.Parameters.Append .CreateParameter("inVOLUME", adVarChar, adParamInput, 200)
.Parameters("inVOLUME").Value = strVolumeMin
End With
adoProbeVials.Open adoCmd, , adOpenStatic, adLockOptimistic

I've used the adCmdText and adCmdStoredProc neither works.
The error I get is that all parameters did not get passed to sp.
There are two in's and one out parameter which is the sys_refcursor.

Louie

 

[Bluejay07]

Sorry, I do not use Oracle. I use Microsoft SQL Server.

If at first you don't succeed, then sky diving wasn't meant for you!

 

[llmclaughlin]

This how to make a oracle recordset from cursor updateable.


Set rsWorkflow = New adodb.Recordset
Set adoCmd.ActiveConnection = adoConnection
adoCmd.CommandText = "LIMS.ALIQUOT_WORKLIST.PlateWorkFlow"
With rsWorkflow
.CursorLocation = adUseClient
.Open Source:=adoCmd, _
CursorType:=adOpenDynamic, _
LockType:=adLockOptimistic, _
Options:=adCmdStoredProc
End With


Louie