Link to home
Start Free TrialLog in
Avatar of anthonytr
anthonytrFlag for United Kingdom of Great Britain and Northern Ireland

asked on

updating table via and stored procedure


Can anyone see where I'm going wrong here?  I get an error saying it can't find the stored procedure.  The stored procedure is working correctly and returns the right @NextBatch number and it's returned to the form.  However it's the next bit when attempting to update the tbl_batches table where it's all going wrong.

Here is my code;

    Private Sub cmdCreateBactch_Click(sender As Object, e As EventArgs) Handles cmdCreateBactch.Click
        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        'Dim reader As SqlDataReader

            Dim returnbatch As Integer = 0
            Dim customerID As String
            Dim batchref As String
            Dim todaysdate As String = String.Format("{0:dd/MM/yyyy}", DateTime.Now)

            con.ConnectionString = My.Settings.TestValue()
            cmd.Connection = con
            cmd.CommandText = "p_GetNextBatchNumber"
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.Add("@CustomerID", SqlDbType.Char)
            cmd.Parameters("@CustomerID").Value = txtCustomerNumber.Text
            cmd.Parameters.Add("@NextBatch", SqlDbType.Int).Direction = ParameterDirection.Output

            Dim sqlReader As SqlDataReader = cmd.ExecuteReader()

            returnbatch = Convert.ToInt32(cmd.Parameters("@NextBatch").Value)
            batchref = txtCustomerNumber.Text & "-" & returnbatch
            txtBatchNumber.Text = returnbatch
            txtBatchReference.Text = batchref
            txtCreateDate.Text = todaysdate
            customerID = txtCustomerNumber.Text
            cmd.CommandText = "INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES([@customerID], [@returnbatch], [@batchref])"

        Catch ex As Exception
            MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
        End Try

    End Sub

Open in new window

Avatar of Ganapathi
Flag of India image

The database  where the Stored procedure was compiled needs to be passed in the Connection String. Check for the cases too.
Avatar of anthonytr


The stored procedure works fine it's the 'INSERT INTO' which doesn't work.  I'm assuming it's because I have:

cmd.CommandText = "p_GetNextBatchNumber"

at the beginning of the code on line 15 and the have

cmd.CommandText = "INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES([@customerID], [@returnbatch], [@batchref])"

on line 30

Don't know how to rectify it.
Can you post your stored procedure?

CREATE PROCEDURE dbo.p_GetNextBatchNumber
 @CustomerID Varchar(50),
 @Nextbatch INT OUT

    SELECT  @Nextbatch = COALESCE(MAX(batch_number), 0) + 1
    FROM    tbl_batches
    WHERE   customer_id = @CustomerID;

	return @NextBatch

Open in new window

Replace the INSERT with this.

"INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES(" + customerID + "," + txtBatchNumber + "," + txtBatchReference + ")"

Open in new window

The error message says that it can't find my Stored Procedure when trying to add the records with the insert query.

Do I have to close the first cmd.CommandText before I try and set it with the Insert query?
"INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES(" + customerID + "," + txtBatchNumber + "," + txtBatchReference + ")"

Open in new window

This does not work as the '+' if not defined for type String.
use this before you assign the INSERT query.

cmd.CommandType = CommandType.Text;

Open in new window

I thought these customerID, txtBatchNumber and txtBatchReference are variables. They are TextBoxes. Try the below.

"INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES(" + customerID.Text + "," + txtBatchNumber.Text + "," + txtBatchReference.Text + ")

Open in new window

Now on my INSERT query I get the error:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll

Additional information: Invalid column name 'customerID'.

Invalid column name 'returnbatch'.

Invalid column name 'batchref'.
It looks like the Columns in the VALUES clause are not same available in the Database Table. Please check your table and provide the correct colmn names.

INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference])
Avatar of anthonytr
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial