the coding will works if the data type of my primary key is integer.but when i try to change the primary key in string data type i cant delete oe edit. below is my coding for that 2 button that is working.

coding for edit button
` Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
If Me.DataGridView1.Rows.Count > 0 Then
If Me.DataGridView1.SelectedRows.Count > 0 Then
Dim intISBN As String = Me.DataGridView1.SelectedRows(0).Cells("ISBN").Value

            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If

            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Book " & _
                                                 " WHERE ISBN=" & intISBN, cnn)

            Dim dt As New DataTable
            da.Fill(dt)



            Me.txtISBN.Text = intISBN
            Me.txtBookTitle.Text = dt.Rows(0).Item("BookTitle")
            Me.txtAuthor.Text = dt.Rows(0).Item("Author")
            Me.txtPublisher.Text = dt.Rows(0).Item("Publisher")
            Me.txtEdition.Text = dt.Rows(0).Item("Edition")
            Me.txtCategory.Text = dt.Rows(0).Item("Category")
            Me.txtQuantity.Text = dt.Rows(0).Item("Quantity")
            Me.txtBookPrice.Text = dt.Rows(0).Item("BookPrice")
            Me.txtLocation.Text = dt.Rows(0).Item("Location")

            Me.txtISBN.Tag = intISBN

            Me.Button1.Text = "Update"

            Me.Button4.Enabled = False

            cnn.Close()
        End If
    End If
End Sub`

coding for delete button
`Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click
If Me.DataGridView1.Rows.Count > 0 Then
If Me.DataGridView1.SelectedRows.Count > 0 Then
Dim x As String = Me.DataGridView1.SelectedRows(0).Cells("ISBN").Value

            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If

            Dim cmb As New OleDb.OleDbCommand
            cmb.Connection = cnn
            cmb.CommandText = "DELETE FROM Book WHERE ISBN=" & x
            cmb.ExecuteNonQuery()


            Me.RefreshData()


            cnn.Close()
        End If
    End If
End Sub`

if i declare intISBN and x as Interger, the coding will works. but the problem is the book's ISBN contain interger and string, so i have to use string data type for book's ISBN in my access database.

Recommended Answers

All 3 Replies

so.. don't set it to be primary.. that's it..

preferably primary key should be integer only. It should not be varchar datatype.

You will have to change your command strings.

Any STRING that is being entered into a command should be wrapped in `'s.

Example:

SELECT * FROM book WHERE isbn='" & Cstr(intISBN) & "'"

You just need to fix your queries.

commented: Agree +8
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.