A parameter is not a parameter, but it is

[Enyi]

Just like my last post I cannot solve this problem with a stored procedure in MSSQL Server 2000 Developer Edition.

I am trying to use the parameter in VB.NET 2003.

When I run the VB code and execute the stored procedure, the exact error message is: "Parameter1 is not a parameter for procedure EditCustomer."

The only thing is it is a parameter for the procedure. I would appreciate any help with this one. Most probably an obvious, simple mistake which I can't see.

Here is the code for the stored procedure.

CREATE PROCEDURE EditCustomer ( @ID int, @FullName nvarchar, @Street nvarchar, @Suburb nvarchar, @Postcode int, @State nvarchar, @Fines money, @Phone int, @JoinDate datetime )
AS
UPDATE Customers SET Full_Name=@FullName, Street=@Street, Suburb=@Suburb, Postcode=@Postcode, State=@State, Fines_Oweing=@Fines, Phone=@Phone, Join_Date=@JoinDate  WHERE Member_ID=@ID
GO

Also, here is the VB code which calls the stored procedure.

Dim cmdEditCustomer As New SqlCommand
cmdEditCustomer.CommandType = CommandType.StoredProcedure
cmdEditCustomer.CommandText = "EditCustomer"
cmdEditCustomer.Connection = sqlConn

Dim paramID As New SqlParameter
paramID.DbType = DbType.Int32
paramID.Direction = ParameterDirection.Input
paramID.ParameterName = "@ID"
paramID.Value = intID

Dim paramFullName As New SqlParameter
paramFullName.DbType = DbType.AnsiString
paramFullName.Direction = ParameterDirection.Input
paramFullName.ParameterName = "@FullName"
paramFullName.Value = strFullName

' Also same sort of code for 7 other parameters you can see in stored procedure

' This function is also called for 7 other parameters
cmdEditCustomer.Parameters.Add(paramID)
cmdEditCustomer.Parameters.Add(paramFullName)

sqlConn.Open()
cmdEditCustomer.ExecuteNonQuery()
sqlConn.Close()

From what I can see it should work right there, but it doesn't :(.


On another note, I have a similar stored procedure only not for a customer. It works nearly perfectly, but it's only saving the first character of each string I pass to it. The parameters types are of 'nvarchar', and the I've tried using the DbTypes of 'AnsiString' and 'String'.

It's like the string gets trimmed when the data is passed into the stored procedure, or when the stored procedure saves the data into the table.

Any advice on this one?

Thank you.

[vijayat23]

i think in vb code u r declaring datatype as DBType, DBType.String and all , but in storedprocedure u r using sqlDbtype, so better u use SqlDbType instead of dbtype in vb, i hope u r proble will be solved

[Enyi]

Thank you vijayat,

That solved the problem perfectly. I didn't realise there was DbType and SqlDbType.

I got another problem now, i'm getting an exception, "Invalid cast from type datetime to decimal".

Happens on the line:
cmdEditCustomer .ExecuteNonQuer y()

All the data types match fine with table, stored procedure and vb code.

Anyone know any solution to figure out what's happening?

[Enyi]

Never mind about my second post, just a stupid error again. Thanks.

[youyuanfun]

I have the same problem in my windows service program.
But it has no problem for WinApp program. and my code in both program
almost the same.
What's wrong in the windows service program?

[youyuanfun]

I have the same problem in my windows service program.
But it has no problem for WinApp program. and my code in both program
almost the same. And I use sqlDbType.
What's wrong in the windows service program?