Avatar of anthonytr
anthonytr
Flag for United Kingdom of Great Britain and Northern Ireland 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

.NET ProgrammingVisual Basic.NET

Avatar of undefined
Last Comment
anthonytr

8/22/2022 - Mon
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
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?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
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

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question