Newbie Question on parameters passed to stored procedure

[Guest]

Hi All,

I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure

Here is the vb.net cod
----------------------------------------------------------------------------------------------
command = New SqlCommand("sp_updateProducts")
command.Connection = connection
command.CommandType = CommandType.StoredProcedure
command.Transaction = trans
command.Parameters.Add(New SqlParameter("@pMacId",
SqlDbType.Char))
command.Parameters.Add(New SqlParameter("@pProdDt",
SqlDbType.DateTime))
command.Parameters.Add(New SqlParameter("@pProdInfo",
SqlDbType.VarChar))
command.Parameters(0).Direction = ParameterDirection.Input
command.Parameters(1).Direction = ParameterDirection.Input
command.Parameters(2).Direction = ParameterDirection.Input
command.Parameters(0).Value = machineID
command.Parameters(1).Value = updateDate
command.Parameters(2).Value = joinStr
command.ExecuteNonQuery()

-----------------------------------------------------------------------------------------------

Here is the stored procedure code:

-----------------------------------------------------------------------------------------------
CREATE PROCEDURE dbo.sp_updateProducts
(
@pMachineId AS CHAR(6),
@pProdDt AS DATETIME,
@pProdinfo VARCHAR(4000)
)
AS
BEGIN
.......
.......
.......
END
G
-----------------------------------------------------------------------------------------------

The error message occurs on ExecuteNonQuery() and says that @pMacId is not a
prameter to the stored procedure sp_updateProducts

I may be missing something very naive! Could anybody suggest the cause of
the error?

Thanks
kd

 

[Larry Lard]

Hi All,

I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure

Here is the vb.net code [snips]
command.Parameters.Add(New SqlParameter("@pMacId",
SqlDbType.Char))

Here is the stored procedure code:

@pMachineId AS CHAR(6),

The error message occurs on ExecuteNonQuery() and says that @pMacId is not a
prameter to the stored procedure sp_updateProducts

I may be missing something very naive! Could anybody suggest the cause of
the error?

I have given you a hint with my snippage

 

[Cor Ligthert]

Kd,

I don't know how it affects this, however I see that you are using the OleDb
methode with your parameters (and that is the only part I know where OleDb
is different in code than SQLclient).

Maybe does this sample help you
http://msdn.microsoft.com/library/d...OfParameterizedStoredProcedureVisualBasic.asp


I hope this helps,

Cor

 

[Guest]

Hi,

I am using SQLClient.

kd

 

[Guest]

Hi Larry,

Changing the line of code you mentioned to:
command.Parameters.Add(New SqlParameter("@pMacId", SqlDbType.Char, 6))

still gives tbe same error that @pMacId is not a parameter to the stored
procedure.

kd

Hi All,

I am trying to pass parameters to a stored procedure from vb.net code and
fails with the error that the variable is not a parameter to the stored
procedure

Here is the vb.net code [snips]
command.Parameters.Add(New SqlParameter("@pMacId",
SqlDbType.Char))

Here is the stored procedure code:

@pMachineId AS CHAR(6),

The error message occurs on ExecuteNonQuery() and says that @pMacId is not a
prameter to the stored procedure sp_updateProducts

I may be missing something very naive! Could anybody suggest the cause of
the error?

I have given you a hint with my snippage

 

[Cor Ligthert]

KD,

I am using SQLClient.

Exactly however as I wrote the OleDb method, I am not sure if that is the
source of your problem. However, in your case would I at least try it.

Cor

 

[Larry Lard]

Hi Larry,

Changing the line of code you mentioned to:
command.Parameters.Add(New SqlParameter("@pMacId", SqlDbType.Char, 6))

still gives tbe same error that @pMacId is not a parameter to the stored
procedure.

Well it isn't The first parameter to the stored procedure is called
@pMachineId, not @pMacId

kd

Hi All,

I am trying to pass parameters to a stored procedure from vb.net

code
and

fails with the error that the variable is not a parameter to the stored
procedure

Here is the vb.net code [snips]
command.Parameters.Add(New SqlParameter("@pMacId",
SqlDbType.Char))

Here is the stored procedure code:

@pMachineId AS CHAR(6),

The error message occurs on ExecuteNonQuery() and says that

@pMacId
is not a

prameter to the stored procedure sp_updateProducts

I may be missing something very naive! Could anybody suggest the cause of
the error?

I have given you a hint with my snippage

 

[Cor Ligthert]

Larry,

I saw it, looked at the code and thought, no that is the valuename when I
scrolled back in the message.

Stupid me.



Cor

 

[Guest]

Hi Cor,

The example shown in the link passes values directly, instead of variables
as parameters. And in my case, the error occurs in the variable. Moreover, I
am executing the stored procedure in a transaction. If my understanding is
correct, using Fill would close the connection (when Datasets are used). I
want the transaction to be live even after executing the procedure.

kd

 

[Cor Ligthert]

KD,

Have a look at Larry's answer.

Cor