Passing a parameter to a stored procedure

[Jimmy211]

Hi,
I'm having difficulty passing a varchar parameter to a stored procedure in Access. Using VB, I pass the values of a record to a stored procedure in MS SQL Server 2k, which then adds or updates the record in the database. I can get date and integer values to pass to the recordset, but not varchar data types. I'd appreciate it if someone could tell me what I'm doing wrong.

VB Code:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4
cmd1.CommandText = "spSampleEdit1"
Dim prm2 As ADODB.Parameter
Set prm2 = cmd1.CreateParameter(Note, adVarChar, adParamInput, 255, Me.LBSAM_NOTES)
cmd1.Parameters.Append prm2
cmd1.Execute

VB gets down to the last line to execute and returns the error "Error converting data type varchar to int"
In my proc @Note is defined as VarChar. I don't see why this is trying to convert the varchar data to integer.

Heres my stored procedure:

ALTER PROC spSampleEdit1
/* This Proc updates a record in the sample table
*/
@SamId as int,
@Date1 as datetime,
@TsId1 as int,
@OrigId1 as int,
@CId1 as int,
@LbogId1 as int,
@Note as varchar
AS
BEGIN
/* Step 1: Add the sample information from the edit table into the main table
*/
UPDATE tblSample
SET LBSAM_DATE=@Date1, LBSAM_TS_ID=@TsId1, LBSAM_ORIG_ID=@OrigId1, LBSAM_C_ID=@CId1, LBSAM_LBOG_ID=@LbogId1, LBSAM_NOTES=@Note
WHERE LBSAM_ID=@SamId
END
GO

Any Ideas? Thanks in advance.
Jimmy211

 

[mrdenny]

If memory serves you have to add all the paramaters in order in the VB code. You'll need to add all the paramaters before you won't get this error.

Also in your stored procedure code you need to tell SQL how large to make the @Note variable. As is it will be created with the default number of characters which I believe is 1.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[Jimmy211]

mrdenny,

Thanks for responding so fast.

In my Access code I do add all the parameters in order. Heres the full access code I'm using:

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4
If IsNull(Me.LBSAM_ID) Then
'If no id then add a new record
cmd1.CommandText = "spSampleAdd1"
Else
' Update the existing record
cmd1.CommandText = "spSampleEdit1"

End If

Dim prm1, prm2 As ADODB.Parameter
Set prm1 = CreateObject("ADODB.Parameter")
Set prm2 = CreateObject("ADODB.Parameter")
Set prm1 = cmd1.CreateParameter(SamId1, 3, 1, , Me.LBSAM_ID)
cmd1.Parameters.Append prm1
Set prm1 = cmd1.CreateParameter(date1, 7, 1, , Me.LBSAM_DATE)
cmd1.Parameters.Append prm1
Set prm1 = cmd1.CreateParameter(TsId1, 3, 1, , Me.LBSAM_TS_ID)
cmd1.Parameters.Append prm1
Set prm1 = cmd1.CreateParameter(OrigId1, 3, 1, , Me.LBSAM_ORIG_ID)
cmd1.Parameters.Append prm1
Set prm1 = cmd1.CreateParameter(CId1, 3, 1, , Me.LBSAM_C_ID)
cmd1.Parameters.Append prm1
Set prm1 = cmd1.CreateParameter(LbogId1, 3, 1, , Me.LBSAM_LBOG_ID)
Debug.Print Me.LBSAM_NOTES
Set prm2 = cmd1.CreateParameter(Note, adVarChar, adParamInput, 255, Me.LBSAM_NOTES)
cmd1.Parameters.Append prm2
cmd1.Execute

I tried the second part of your suggestion and changed my line in the stored procedure from:
@Note as varchar
To:
@Note as varchar(255)

I still get the same error message when I run it.

Thanks,

Jimmy211

 

[Jimmy211]

For anyone who's curious I figured out the answer to this. It turned out to be a dumb mistake. I forgot to append the second to the last prm1 parameter to the proc, the one called LbogId1. The 'cmd1.Parameters.Append prm1' line is missing.

Thanks for your help anyway Mr Denny.

Jimmy211

 

[mrdenny]

no problem.

Denny
MCSA (2003) / MCDBA (SQL 2000)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[dwight1]

Jimmy211,

I want to append the record based on the combo selection id. I am using the following code that you have used. Can you please correct me where i am going wrong.
When i run the following code, it gives error "Object Required".


Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4 'This is the datatype field size
cmd1.CommandText = "z2" 'Name of the stored procedure
Dim prm1 As ADODB.Parameter
Set prm1 = cmd1.CreateParameter(id, adint, adParamInput, 4, Me.Combo8)
cmd1.Parameters.Append prm2
cmd1.Execute



Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub

ID is the field name/bound column selection for the combo with data type Int(4).

Any suggestion

Dwight

 

[dwight1]

Cont....Well i have now set the object..even then it complains about "parameter object improperly defined. Incomplete or inconsistant information was provided" Can you correct me.

Private Sub Command30_Click()
On Error GoTo Err_Command30_Click

Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim cmd1 As ADODB.Command
Set cmd1 = CreateObject("ADODB.Command")
cmd1.ActiveConnection = cnn
cmd1.CommandType = 4
cmd1.CommandText = "z2"
Dim prm1 As ADODB.Parameter
Set prm1 = CreateObject("ADODB.Parameter")
Set prm1 = cmd1.CreateParameter(idofcust, adint, adParamInput, 4, Me.Combo8)
cmd1.Parameters.Append prm1
cmd1.Execute

Exit_Command30_Click:
Exit Sub

Err_Command30_Click:
MsgBox Err.Description
Resume Exit_Command30_Click

End Sub

 

[dwight1]

Please ignore Got the solution for the previous problem...


I woudl be glad if some one can suggest how to create a message that says the above step executed successfully.

Thanks

 

[Jimmy211]

dwight1,

You probably have it figured out by now but I was wondering about this line where you assign the parameter:

Set prm1 = cmd1.CreateParameter(idofcust, adint, adParamInput, 4, Me.Combo8)

Is it necessary to set the size of the parameter=4? Parameters I've gotten to work do fine if that part is left blank. Also, don't you have to spell out adInteger, and put quotes around the name of the stored procedure parameter?

Example:

Set prm1 = cmd1.CreateParameter("idofcust", adInteger, adParamInput, , Me.Combo8)

Just curious. Thanks,

Jimmy211

 

[dwight1]

Jimmy211,

I used the follwing code and it worked.

Set prm1 = cmd1.CreateParameter(AppID, 4, 1, , Me.Combo8)

Well as you know i had copied your code can you tell me what is 4 and 1 here?

THank you

Dwigth

 

[Jimmy211]

Dwight1,

The 1 is the same thing as saying adParamInput and the 4 is the same as saying the type of variable, adSingle in this case. Take a look at the following link.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstdatatypeenum.asp

Jimmy211