How to execute a SQL Server stored procedure from vb.net

Question

I have created a stored procedure in sql server; which I am trying to execute in vb.net but, for some reasons it is not working. Can anyone help?

This is the procedure I created; which works fine in sql server:

CREATE PROCEDURE PRC_CUS_ADD 
(@CATEGORY_NAME Varchar(30), @BRAND_NAME Varchar(30), @PRODUCT_DETAIL Varchar(30), @SALE_QUANTITY Numeric(9,2), @SALE_PRICE Numeric(9,2), @SALE_TOTAL_PRICE Numeric(9,2), @SALE_DATE datetime, @CUSTOMER_ID int, @PRODUCT_ID int, @CREDIT_PAYMENT bit)
AS
BEGIN
INSERT INTO SALE(SALE_ID, CATEGORY_NAME, BRAND_NAME, PRODUCT_DETAIL, SALE_QUANTITY, SALE_PRICE, SALE_TOTAL_PRICE, SALE_DATE, CUSTOMER_ID, PRODUCT_ID, CREDIT_PAYMENT) VALUES (NEXT VALUE FOR SALE_Sequence, @CATEGORY_NAME, @BRAND_NAME, @PRODUCT_DETAIL, @SALE_QUANTITY, @SALE_PRICE, @SALE_TOTAL_PRICE, @SALE_DATE, @CUSTOMER_ID, @PRODUCT_ID, @CREDIT_PAYMENT)
END;

This is the code I wrote to execute the procedure in VB.net:

        Dim cn As New SqlConnection("")
        Dim cmd As New SqlCommand
        Dim dr As SqlDataReader
        cmd.Connection = cn

        cn.Open()
        Dim cmd As SqlCommand = New SqlCommand("PRC_CUS_ADD", cn)
        cmd.CommandType = CommandType.StoredProcedure

        cmd.Parameters.Add("@CATEGORY_NAME", SqlDbType.VarChar).Value = ComboBox1.Text
        cmd.Parameters.Add("@BRAND_NAME", SqlDbType.VarChar).Value = ComboBox2.Text
        cmd.Parameters.Add("@PRODUCT_DETAIL", SqlDbType.VarChar).Value = ComboBox3.Text
        cmd.Parameters.Add("@SALE_QUANTITY", SqlDbType.Decimal).Value =txtQUANTITY.Text
        cmd.Parameters.Add("@SALE_PRICE", SqlDbType.Decimal).Value = txtPRICE.Text
        cmd.Parameters.Add("@SALE_TOTAL_PRICE", SqlDbType.Decimal).Value =txtTOTAL.Text
        cmd.Parameters.Add("@ALE_DATE", SqlDbType.Date).Value = dtp.Value
        cmd.Parameters.Add("@CUSTOMER_ID", SqlDbType.Int).Value = txtCustomerId.Text
        cmd.Parameters.Add("@PRODUCT_ID", SqlDbType.Int).Value = txtPRODUCT.Text
        cmd.Parameters.Add("@CREDIT_PAYMENT", SqlDbType.Bit).Value = credit.CheckState

        dr = cmd.ExecuteReader()
        cn.Close()

Please help! I just don't know what's wrong with the code. Thank you.

Solution

You have used "ExecuteReader" but in fact your procedure does not return any resultset. You should use "ExecuteNonQuery".

Comments

[Yinda Yin]

Did you get an error message? What does it say?

[Admin]

Yes I get an error message that says: A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

[Yinda Yin]

There's more to the error message than that. There should be an actual description of the error.

[Admin]

Thank you for your reply. I tried cmd.ExecuteNonQuery() instead of dr = cmd.ExecuteReader but, I am still getting the same error message. I am wondering if it is not the sqlDBType I used for the variables?

[Yinda Yin]

@answerme: Without more detail from the error message, there's no hope of answering your question. If you can't get more detail from the exception message, write some code that displays the InnerException property of the exception, and tell us what it says.

[Admin]

It worked. I used an exception catch as you suggested and found that I misspelled one of the variables. THANK YOU VERY MUCH!!! YOU SAVED MY DAY...