Problem - Stored Procedure

[lkmoody]

I am trying to use a stored procedure from SQL in VB.Net to add a row to the Product table. Here is the stored procedure:

CREATE PROCEDURE add_product
(
  @name char,
  @wholesaler int,
  @price money,
  @number_in_stock int,
  @type char
)
AS
   INSERT INTO Product ([name], wholesalerID, price, number_in_stock, type)
VALUES (@name, @wholesaler, @price, @number_in_stock, @type)

I am creating the command in VB.Net with this code:

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand
command.Connection = obj_conn
command.CommandType = CommandType.StoredProcedure
command.CommandText = "dbo.add_product"
[/SIZE] 
Dim sql_param_name As New SqlParameter
sql_param_name.ParameterName = "@name"
sql_param_name.SqlDbType = SqlDbType.Char
sql_param_name.Direction = ParameterDirection.Input
sql_param_name.Value = txt_product_name.Text
Dim sql_param_wholesalerID As New SqlParameter
sql_param_wholesalerID.ParameterName = "@wholesaler"
sql_param_wholesalerID.SqlDbType = SqlDbType.Int
sql_param_wholesalerID.Direction = ParameterDirection.Input
sql_param_wholesalerID.Value = id
Dim sql_param_price As New SqlParameter
sql_param_wholesalerID.ParameterName = "@price"
sql_param_wholesalerID.SqlDbType = SqlDbType.Money
sql_param_wholesalerID.Direction = ParameterDirection.Input
sql_param_wholesalerID.Value = CDbl(txt_product_price.Text)
Dim sql_param_stock As New SqlParameter
sql_param_wholesalerID.ParameterName = "@number_in_stock"
sql_param_wholesalerID.SqlDbType = SqlDbType.Int
sql_param_wholesalerID.Direction = ParameterDirection.Input
sql_param_wholesalerID.Value = CInt(txt_product_price.Text)
Dim sql_param_type As New SqlParameter
sql_param_type.ParameterName = "@type"
sql_param_type.SqlDbType = SqlDbType.Char
sql_param_type.Direction = ParameterDirection.Input
sql_param_type.Value = cbo_product_type.Text
command.Parameters.Add(sql_param_name)
command.Parameters.Add(sql_param_wholesalerID)
command.Parameters.Add(sql_param_price)
command.Parameters.Add(sql_param_stock)
command.Parameters.Add(sql_param_type

I keep getting an error saying:

Parameter1 is not a parameter for procedure add_product

I am new to stored procedures in VB.Net so Im sure it is some silly mistake. If anyone could help that would be great.

 

[CygNuS]

Where exactly does the error occur?

I don't see anything wrong at all at first sight (except for the missing closing bracket in the last line, but that's prolly copy paste problem)
What comes after your code; how do you execute the command?

 

[lkmoody]

I call this sub routine that executes the sql command:

Public Sub db_change(ByVal sql_command As SqlCommand)
 
Dim transaction As SqlTransaction
transaction = obj_conn.BeginTransaction(IsolationLevel.ReadCommitted)
sql_command.Transaction = transaction
Try
sql_command.ExecuteNonQuery()
transaction.Commit()


Catch ex As Exception

MessageBox.Show(ex.Message)
Try
transaction.Rollback()
Catch ex2 As Exception
MessageBox.Show(ex2.Message)
End Try
End Try
End Sub

The sub routine is contained inside of the db_conn class.

 

[cjard]

Parameter1 is not a parameter for procedure add_product

Immediately BEFORE the interpreter runs the "ExecuteNonQuery()" line, you should break the code and use the debugger to look at the contents of the Parameters collection. I think you might find that Parameter1 is a default in there for some reason

 

[lkmoody]

I think that you are right. I'm not sure how to change the default value to the correct one though.

 

[cjard]

Remove it?

command.Parameters.Clear() 'Do this BEFORE you add all your params!!

or
command.Parameters.Remove("Parameter1")

or
command.Parameters.Delete("Parameter1")


I cant remember - have a play