asked on

updating table via VB.net and stored procedure

Hi,

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

        Try
            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()
            con.Open()
            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()
            sqlReader.Close()

            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])"
            cmd.ExecuteNonQuery()

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

    End Sub

Open in new window

Ganapathi

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

anthonytr

ASKER

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.

Ganapathi

Can you post your stored procedure?

anthonytr

ASKER

Sure:

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

 )
AS
Begin
    SET NOCOUNT ON;
	
    SELECT  @Nextbatch = COALESCE(MAX(batch_number), 0) + 1
    FROM    tbl_batches
    WHERE   customer_id = @CustomerID;

	return @NextBatch
end

Open in new window

Ganapathi

Replace the INSERT with this.

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

Open in new window

anthonytr

ASKER

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?

anthonytr

ASKER

"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.

Ganapathi

use this before you assign the INSERT query.

cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;

Open in new window

Ganapathi

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

anthonytr

ASKER

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'.

Ganapathi

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])

ASKER CERTIFIED SOLUTION

anthonytr

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial