VB.Net and Stored Procedure

[ivan dan]

hi, my name is ivan,i am new to stored procedure and VB.Net, i am trying to insert a value to a column in a table in my database using VB.Net and Stored procedure


Here's the procedure:

1. A user will input a value to a textbox, for example lastname.
2. When the user click the save button, the button will call the stored procedure.


Here's my code in stored procedure:

DELIMITER $$

DROP PROCEDURE IF EXISTS `sp_tutorial`.`sp_addCustomer` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_addCustomer`(lastname char(255))
BEGIN
insert into table_customer(cust_lname) values(lastname);
END $$

DELIMITER ;

And here's my code in VB.Net in save button:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click



 	Dim password As String

        password = ""

        Dim conn As New Odbc.OdbcConnection
        Dim cmd As New Odbc.OdbcCommand


        conn.ConnectionString = "Driver={MySQL ODBC 3.51 Driver};" + "Server=localhost;" + "Database=sp_tutorial;" + "Uid=root;" + "Pwd=" & password & ";"

        Try
            conn.Open()

            cmd.Connection = conn
            cmd.CommandText = "call sp_addCustomer"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("lastname", Odbc.OdbcType.NVarChar, 255)
            cmd.Parameters.AddWithValue("lastname", TextBox1.Text)
            cmd.ExecuteNonQuery()


        Catch ex As Exception

            MessageBox.Show(ex.Message)

        End Try

        
End Sub

And i got this error message:

ERROR[HY000][MySQL][ODBC 3.51 Driver][mysqld-5.0.37] Incorrect number of arguments for procedure sp_tutorial.sp_ addCustomer; expected 1,got 0


Please help me, i want to learn more in VB.Net and stored procedure.

[Joseph Martell]

The reason you are getting that error is that you are adding 2 parameters to your stored procedure where you really only want 1.

These 2 lines each add a parameter to your cmd object:

           cmd.Parameters.Add("lastname", Odbc.OdbcType.NVarChar, 255)  'First parameter added
            cmd.Parameters.AddWithValue("lastname", TextBox1.Text)  'second parameter added

Your first line "cmd.Paramters. Add(..." is adding the parameter and also returning a reference to the newly added parameter. I would suggest changing your code to something like:

           Dim myParm as OdbcParameter = cmd.Parameters.Add("lastname", Odbc.OdbcType.NVarChar, 255)
           myParm.Value = TextBox1.Text

This way you are not adding a second value to your parameter collection. You are setting the value of the parameter that you have already added to your parameter collection.