Stored Procedure Help 1

[dvannoy]

VB6, SQL 2005

I'm trying to use the below code to populate a form once I double click a data grid. "ID" is a key in sql 2005. getting the following error.

Run time error: -2147467259
The precision is invalid

Below is the form code.

Dim cmd As ADODB.Command
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command

With cmd
.ActiveConnection = strCn
.CommandType = adCmdStoredProc
.CommandText = "SP_NAME"
.Parameters.Append .CreateParameter("ID", adNumeric, adParamInputOutput, 8)
.Parameters("ID").Value = (form.dg.Columns(0))
End With

With rs
rs.CursorType = adOpenDynamic
rs.CursorLocation = adUseClient
rs.LockType = adLockPessimistic
rs.Open cmd
End With

any help would be appreciated

thanks

 

[Bluejay07]

I'm not sure if you want the parameter to be input or output or both. Try modifying your code to be similar to:

Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
   
On Error GoTo ERR_Handler
   
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
   g_udtQuote.ShipToID = cmd.Parameters("Auto").Value
End With
Set cmd = Nothing

Where the stored procedure starts like:

CREATE PROCEDURE cpyBillToBillTo @BTID int, @Auto int OUTPUT AS

If you are not looking for an output, then you can use something similar to:

Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = strCn
   .CommandText = "SP_NAME"
   .CommandType = adCmdStoredProc
   .Parameters.Append .CreateParameter("ID", adInteger, adParamInput)
   .Parameters("ID").Value = (form.dg.Columns(0))
End With
rs.Open cmd, , adOpenStatic, adLockReadOnly

I hope this helps.

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

 

[dvannoy]

Worked!

("ID", adInteger, adParamInput)

I didn't change my SP at it's still @ID NUMERIC (8)
do you think that I will have any issues with that?

Thank you...

 

[Bluejay07]

I would probably change it to be @ID int. That works for me and that way you don't have worry about it (however I've never userd numeric so I'm not really sure). Try it your way. If it works great. Keep it that way. If it doesn't, you now have an alternate solution.

Glad I could help.

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