locked
Update without Primary Key RRS feed

  • Question

  • I have 5 column in database and datagridivew

    Date,  InvoiceNo,  Description,  Rate, Total

    I wanna update record and i dont have any primary key. My coding is below for update record.

      Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
       
            Dim conn As New SqlServerCe.SqlCeConnection("Data Source=|DataDirectory|\DatabaseInvoice.sdf")
            Dim cmd As New SqlServerCe.SqlCeCommand
            Try
                cmd.CommandText = "UPDATE tbl_Invoice" &
                                " SET (Date = @Date, " &
                                " InvoiceNo = @InvoiceNo," &
                                " Description = @Description," &
                                " Rate = @Rate," &
                                " Total = @Total" &
                                " WHERE InvoiceNo = @InvoiceNo"
                cmd.CommandType = CommandType.Text
                cmd.Connection = conn

                With cmd.Parameters
                    .AddWithValue("@Date", DateDataGridViewTextBoxColumn).DbType = DbType.DateTime
                    .AddWithValue("@InvoiceNo", InvoiceNoDataGridViewTextBoxColumn)
                    .AddWithValue("@Description", DescriptionDataGridViewTextBoxColumn)
                    .AddWithValue("@Rate", RateDataGridViewTextBoxColumn)
                    .AddWithValue("@Total", TotalDataGridViewTextBoxColumn)
                End With

                conn.Open()
                cmd.ExecuteNonQuery()
                Me.Tbl_InvoiceTableAdapter.Update(Me.Ds_Invoice.tbl_Invoice)

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
                cmd.Parameters.Clear()
            End Try

        End Sub

    Kindly help me, if u know another procedure for update let me know.

    Monday, May 27, 2013 11:26 AM

Answers

  • Primary keys are always unique. If you don't have a primary key and only wants to update a single row, specify the minimum number of columns that uniquely identifies a row in the WHERE clause of the UPDATE statement:

    UPDATE tbl_Invoice"
    SET Date = @Date, InvoiceNo = @InvoiceNo
    ....
    WHERE InvoiceNo = @InvoiceNo AND Date = @Date ...

    • Proposed as answer by Mike Feng Tuesday, May 28, 2013 2:37 AM
    • Marked as answer by Mike Feng Tuesday, June 4, 2013 3:06 AM
    Monday, May 27, 2013 3:14 PM
  • First of all, SET arguments don't require brackets, so your query should be:

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total" & _
        " WHERE Date = @Date AND InvoiceNo = @InvoiceNo AND Description = @Description AND Rate = @Rate AND Total = @Total"

    But this query is still meaningless, because you update columns to the very same values.

    You should either remove anything except InvoiceNo from WHERE clause

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, Description = @Description, Rate = @Rate, Total = @Total" & _ 
        " WHERE InvoiceNo = @InvoiceNo"

    , or (if you expect concurrent users updating the same record) provide another parameters with original values:

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total" & _ 
        " WHERE Date = @DateOrig AND InvoiceNo = @InvoiceNoOrig AND Description = @DescriptionOrig AND Rate = @RateOrig AND Total = @TotalOrig"
    • Marked as answer by Mike Feng Tuesday, June 4, 2013 3:05 AM
    Tuesday, May 28, 2013 11:03 PM

All replies

  • The primary key in this case looks like the InvoiceNo.

    jdweng

    Monday, May 27, 2013 12:08 PM
  • Yes invoice is primary key but it not Unique key
    Monday, May 27, 2013 12:36 PM
  • Hi,

    it is strongly discouraged to not have a Primary Key in a Table. Surely Db will not notify you in case incorrect row gets updated. Otherwise any method to update table without any primary key can give you unexpected result.


    One good question is equivalent to ten best answers.

    Monday, May 27, 2013 1:03 PM
  • Primary keys are always unique. If you don't have a primary key and only wants to update a single row, specify the minimum number of columns that uniquely identifies a row in the WHERE clause of the UPDATE statement:

    UPDATE tbl_Invoice"
    SET Date = @Date, InvoiceNo = @InvoiceNo
    ....
    WHERE InvoiceNo = @InvoiceNo AND Date = @Date ...

    • Proposed as answer by Mike Feng Tuesday, May 28, 2013 2:37 AM
    • Marked as answer by Mike Feng Tuesday, June 4, 2013 3:06 AM
    Monday, May 27, 2013 3:14 PM
  • Thanks for your reply Magnus

    I am trying following coding but hving error.

    Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click
       
            Dim conn As New SqlServerCe.SqlCeConnection("Data Source=|DataDirectory|\DatabaseInvoice.sdf")
            Dim cmd As New SqlServerCe.SqlCeCommand
            Try
                cmd.CommandText = "UPDATE tbl_Invoice" &
                                " SET (Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total)" &
                                " WHERE (Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total"

                cmd.CommandType = CommandType.Text
                cmd.Connection = conn

                With cmd.Parameters
                    .AddWithValue("@Date", "Date")
                    .AddWithValue("@InvoiceNo", "InvoiceNo")
                    .AddWithValue("@Description", "Description")
                    .AddWithValue("@Rate", "Rate")
                    .AddWithValue("@Total", "Total")
                End With

                conn.Open()
                cmd.ExecuteNonQuery()
                Me.Tbl_InvoiceTableAdapter.Update(Me.Ds_Invoice.tbl_Invoice)

            Catch ex As Exception
                MsgBox(ex.Message)
            Finally
                conn.Close()
                cmd.Parameters.Clear()
            End Try

        End Sub

    Plz reply me.



    • Edited by iffi_nice Tuesday, May 28, 2013 11:05 AM mistake
    Tuesday, May 28, 2013 11:04 AM
  • What is the error?  The code looks good.  You may have a credential issue.  the database may be setup not to allow duplicate entries and you are adding a duplicate.  There are too many reasons this can fail and without an error message it is difficult to determine the problem.

    jdweng

    Tuesday, May 28, 2013 12:06 PM
  • When i change in datagridview column and click update button i hv error.

    There was an error parsing the query. [Token line number = 1, token line offset = 24, token in error = (]

    Tuesday, May 28, 2013 12:33 PM
  • Use "AND" in the WHERE clause instead of commas:

    WHERE (Date = @Date AND InvoiceNo = @InvoiceNo AND Description = @Description AND Rate = @Rate AND Total = @Total)

    Tuesday, May 28, 2013 12:36 PM
  • Thanks for your reply but i hv still same error.


    cmd.CommandText = "UPDATE tbl_Invoice" & _
                    "SET (Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total) WHERE (Date = @Date AND InvoiceNo = @InvoiceNo AND Description = @Description AND Rate = @Rate AND Total = @Total)"

    Tuesday, May 28, 2013 1:18 PM
  • Try to add a space between "tbl_Invoice" and SET:

    cmd.CommandText = "UPDATE tbl_Invoice" & _
                    " SET (Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total) WHERE (Date = @Date AND InvoiceNo = @InvoiceNo AND Description = @Description AND Rate = @Rate AND Total = @Total)"

    Tuesday, May 28, 2013 1:40 PM
  • Start simple by doing a SELECT query to make sure everything is correct.

    cmd.CommandText = "SELECT * FROM TABLE"

    conn.Open()
    cmd.ExecuteQuery()

    Then build up to to the command below.  Make sure you get results that aren't null.

    cmd.CommandText = "Select * From TABLE" & _
    " WHERE (Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total"

    After the Select works then you can try to do a INSERT.  I think you are missing the FROM in your command.

             
                               


    jdweng

    Tuesday, May 28, 2013 1:41 PM
  • Insert and delete working well.
    Tuesday, May 28, 2013 4:19 PM
  • Update not working well
    Tuesday, May 28, 2013 4:20 PM
  • already space between "tbl_Invoice" and SET:
    Tuesday, May 28, 2013 4:22 PM
  • First of all, SET arguments don't require brackets, so your query should be:

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total" & _
        " WHERE Date = @Date AND InvoiceNo = @InvoiceNo AND Description = @Description AND Rate = @Rate AND Total = @Total"

    But this query is still meaningless, because you update columns to the very same values.

    You should either remove anything except InvoiceNo from WHERE clause

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, Description = @Description, Rate = @Rate, Total = @Total" & _ 
        " WHERE InvoiceNo = @InvoiceNo"

    , or (if you expect concurrent users updating the same record) provide another parameters with original values:

    cmd.CommandText = "UPDATE tbl_Invoice" & _
        " SET Date = @Date, InvoiceNo = @InvoiceNo, Description = @Description, Rate = @Rate, Total = @Total" & _ 
        " WHERE Date = @DateOrig AND InvoiceNo = @InvoiceNoOrig AND Description = @DescriptionOrig AND Rate = @RateOrig AND Total = @TotalOrig"
    • Marked as answer by Mike Feng Tuesday, June 4, 2013 3:05 AM
    Tuesday, May 28, 2013 11:03 PM