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;
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
.NET ProgrammingVisual Basic.NET
Last Comment
anthonytr
The database where the Stored procedure was compiled needs to be passed in the Connection String. Check for the cases too.
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.
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?
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
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
Replace the INSERT with this.
"INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES(" + customerID + "," + txtBatchNumber + "," + txtBatchReference + ")"
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?
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.
ASKER
"INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference]) VALUES(" + customerID + "," + txtBatchNumber + "," + txtBatchReference + ")"
This does not work as the '+' if not defined for type String.
use this before you assign the INSERT query.
cmd.Parameters.Clear();
cmd.CommandType = CommandType.Text;
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 + ")
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
ASKER
Now on my INSERT query I get the error:
An unhandled exception of type 'System.Data.SqlClient.Sql
Additional information: Invalid column name 'customerID'.
Invalid column name 'returnbatch'.
Invalid column name 'batchref'.
An unhandled exception of type 'System.Data.SqlClient.Sql
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])
INSERT INTO tbl_batches ([customer_id], [batch_number], [batch_reference])
ASKER CERTIFIED SOLUTION
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
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