trying to run this code but i keep getting an error saying "incorrect syntax near keyword 'where'" please assist

        If _id <> String.Empty Then
            cn.Open()
            cm = New SqlCommand("update tblBill_Items set description = @description,qty = @qty,unitprice=@unitiprice,discount=@discount WHERE id=@id AND invoiceno=@invoiceno", cn)
            With cm.Parameters
                .AddWithValue("description", TextBox9.Text)
                .AddWithValue("qty", CDbl(TextBox8.Text))
                .AddWithValue("unitprice", CDbl(TextBox7.Text))
                .AddWithValue("discount", CDbl(TextBox6.Text))
                .AddWithValue("id", _id)
                .AddWithValue("invoiceno", TextBox4.Text)
            End With
            cm.ExecuteNonQuery()
            cn.Close()

        Else

            cn.Open()
            cm = New SqlCommand("insert into tblBill_Items (invoiceno,description,qty,unitprice,discount) values(@invoiceno,@description,@qty,@unitprice,@discount)", cn)
            With cm.Parameters
                .AddWithValue("invoiceno", TextBox4.Text)
                .AddWithValue("description", TextBox9.Text)
                .AddWithValue("qty", CDbl(TextBox8.Text))
                .AddWithValue("unitprice", CDbl(TextBox7.Text))
                .AddWithValue("discount", CDbl(TextBox6.Text))
            End With
            cm.ExecuteNonQuery()
            cn.Close()
        End If

Solution

In the OP, the data types for the database columns weren't provided which would have been rather useful. I'll go over some potential issues with your code. I say "potential" because without knowing the data types for the database columns and without having some sample input for the Form, it's not possible to know for sure where some of the issues may lie.

It's possible that an issue is occurring due to AddWithValue. You may consider using Add instead and explicitly specifying the data type-which is used in the code below. If the value in one of the TextBoxes is null or empty this will be an issue. In this case, it's necessary to set the value as DBNull.Value.

Both _id <> String.Empty and .AddWithValue("id", _id) seem to indicate that the id column is a string data type such as varchar or nvarchar. However, in your insert statement, id wasn't specified:

cm = New SqlCommand("insert into tblBill_Items (invoiceno,description,qty,unitprice,discount) values(@invoiceno,@description,@qty,@unitprice,@discount)", cn)

When a value is autoincremented upon insertion, it isn't necessary to specify the column during the insertion so it's possible that the data type is a decimal that autoincrements which makes it a good candidate to be the primary key. However, invoiceno should also be unique, which makes it a good candidate to be the primary key. Personally, I would eliminate the id column and use invoiceno as the primary key. However, in the code below, I've made id the primary key and made invoiceno unique, as this is another viable option.

There doesn't seem to be any checking to see if any of the values from the Textboxes are null or empty, although it's possible that this was done in code that wasn't included in the OP. There doesn't seem to be any checking to ensure that a value is numeric prior to converting it to a numeric value. What happens if TextBox8.Text contains abc?

CDbl(TextBox8.Text)

You may consider using Double.TryParse or Decimal.TryParse instead.

In the update SQL statement, WHERE id=@id AND invoiceno=@invoiceno it doesn't seem necessary to include both id and invoiceno since they are both likely unique values.

The following shows how to create a table in SQL Server using VB.NET. It also shows how to both insert data and update data in the table. The code has been tested using SQL Express.

Database name: Order
Table name: tblBill_items

Note: The connection string needs to be modified for your environment. See Connection Strings for more information. The table name was taken from the OP.

In the code below, the id column (in the database table) is decimal and autoincrements. Additionally, invoiceno is unique.

Create a new Windows Forms App (.NET Framework) project

Add a module (name: Helper.vb)

Helper.vb

Imports System.Data.SqlClient
Module Helper
 
    'ToDo: modify the connection string for your environment
    Private connectionStr As String = String.Format("Data Source='.\SQLExpress'; Initial Catalog='Order'; Integrated Security=True")

    Public Sub CreateTblBillItems()
        Dim sqlText As String = "CREATE TABLE tblBill_Items (id decimal NOT NULL IDENTITY(1,1)
                                          CONSTRAINT PK_tblBill_Items_id PRIMARY KEY,
                                          invoiceno nvarchar(50) NOT NULL
                                          CONSTRAINT UQ_tblBill_Items_invoiceno UNIQUE,
                                          description nvarchar(50),
                                          qty decimal NOT NULL,
                                          unitprice decimal(18,2) NOT NULL,
                                          discount decimal(18,2))"

        ExecuteNonQuery(sqlText)

    End Sub

    Private Sub ExecuteNonQuery(sqlText As String)

        Try
            Using cn As SqlConnection = New SqlConnection(connectionStr)
                'open
                cn.Open()

                Using cmd As SqlCommand = New SqlCommand(sqlText, cn)
                    'execute
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As SqlException
            Debug.WriteLine("Error (SqlException): " & ex.Message)
            Throw ex
        Catch ex As Exception
            Debug.WriteLine("Error: " & ex.Message)
            Throw ex
        End Try
    End Sub

    Private Sub ExecuteNonQueryTblBillItems(sqlText As String, invoiceNo As String, description As String, qty As Double, unitPrice As Decimal, discount As Decimal, Optional id As Decimal = 0)

        Try
            Using cn As SqlConnection = New SqlConnection(connectionStr)
                'open
                cn.Open()

                Using cmd As SqlCommand = New SqlCommand(sqlText, cn)

                    With cmd.Parameters
                        .Add("@id", SqlDbType.Decimal).Value = id
                        .Add("@invoiceno", SqlDbType.NVarChar).Value = If(String.IsNullOrEmpty(invoiceNo), DBNull.Value, invoiceNo)
                        .Add("@description", SqlDbType.NVarChar).Value = If(String.IsNullOrEmpty(description), DBNull.Value, description)
                        .Add("@qty", SqlDbType.Decimal).Value = qty
                        .Add("@unitprice", SqlDbType.Decimal).Value = unitPrice
                        .Add("@discount", SqlDbType.Decimal).Value = discount
                    End With

                    'ToDo: remove the following code that is for debugging
                    'For Each p As SqlParameter In cmd.Parameters
                    'Debug.WriteLine(p.ParameterName & ": " & p.Value.ToString())
                    'Next

                    'execute
                    cmd.ExecuteNonQuery()
                End Using
            End Using
        Catch ex As SqlException
            Debug.WriteLine("Error (SqlException): " & ex.Message)
            Throw ex
        Catch ex As Exception
            Debug.WriteLine("Error: " & ex.Message)
            Throw ex
        End Try
    End Sub

    Public Sub InsertTblBillItems(invoiceNo As String, description As String, qty As Double, unitPrice As Decimal, discount As Decimal)
        Dim sqlText As String = "INSERT INTO tblBill_Items(invoiceno, description, qty, unitprice, discount) VALUES(@invoiceno, @description, @qty, @unitprice, @discount)"
        ExecuteNonQueryTblBillItems(sqlText, invoiceNo, description, qty, unitPrice, discount)
    End Sub

    Public Sub UpdateTblBillItems(invoiceNo As String, description As String, qty As Double, unitPrice As Decimal, discount As Decimal, id As Decimal)
        Dim sqlText As String = "UPDATE tblBill_Items set invoiceno = @invoiceno, description = @description, qty = @qty, unitprice = @unitprice, discount = @discount WHERE invoiceno = @invoiceno"
        ExecuteNonQueryTblBillItems(sqlText, invoiceNo, description, qty, unitPrice, discount, id)
    End Sub
End Module

On Form1, add the Button, Label, and TextBox controls as shown below:

Double-click each of the buttons to add the "Click" event handler.

Form1.vb

Public Class Form1

    Private id As Decimal = 0
    Private invoiceNo As String = String.Empty
    Private description As String = String.Empty
    Private qty As Double = 0
    Private unitPrice As Decimal = 0
    Private discount As Decimal = 0

    Private Sub ButtonCreateTable_Click(sender As Object, e As EventArgs) Handles ButtonCreateTable.Click
        Helper.CreateTblBillItems()

        Debug.WriteLine("Info: Table 'tblBill_Items' created")
    End Sub

    Private Function IsInputAvailable() As Boolean

        If String.IsNullOrEmpty(TextBoxInvoiceNo.Text) Then
            MessageBox.Show("InvoiceNo not specified", "Error - InvoiceNo", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End If

        If String.IsNullOrEmpty(TextBoxDescription.Text) Then
            MessageBox.Show("Description not specified", "Error - Description", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End If

        If String.IsNullOrEmpty(TextBoxQty.Text) Then
            MessageBox.Show("Qty not specified", "Error - Qty", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End If

        If String.IsNullOrEmpty(TextBoxUnitPrice.Text) Then
            MessageBox.Show("UnitPrice not specified", "Error - UnitPrice", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return False
        End If

        Return True
    End Function

    Private Sub GetData()
        'get/set values

        'try to convert
        Decimal.TryParse(TextBoxId.Text, id)

        invoiceNo = TextBoxInvoiceNo.Text.Trim()
        description = TextBoxDescription.Text.Trim()
        qty = 0
        unitPrice = 0
        discount = 0

        'convert to Double
        Double.TryParse(TextBoxQty.Text, qty)

        'convert to Decimal
        Decimal.TryParse(TextBoxUnitPrice.Text, unitPrice)

        If Not String.IsNullOrEmpty(TextBoxDiscount.Text) Then
            'convert to Decimal
            Decimal.TryParse(TextBoxDiscount.Text, discount)
        End If
    End Sub

    Private Sub BtnInsert_Click(sender As Object, e As EventArgs) Handles BtnInsert.Click

        If IsInputAvailable() Then
            GetData()
            Helper.InsertTblBillItems(invoiceNo, description, qty, unitPrice, discount)

            Debug.WriteLine("Info: Data inserted into 'tblBill_Items'")
        End If

    End Sub

    Private Sub ButtonUpdate_Click(sender As Object, e As EventArgs) Handles ButtonUpdate.Click
        If IsInputAvailable() Then
            GetData()
            Helper.UpdateTblBillItems(invoiceNo, description, qty, unitPrice, discount, id)

            Debug.WriteLine("Info: Table 'tblBill_Items' updated")
        End If
    End Sub
End Class

Resources: