We help IT Professionals succeed at work.
Get Started

updating table via VB.net and stored procedure

47 Views
Last Modified: 2016-06-21
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

Comment
Watch Question
This problem has been solved!
Unlock 1 Answer and 12 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE