Member Avatar for sing1006

here is my code for delete

Private Sub Button4_Click(sender As Object, e As EventArgs) Handles Button4.Click
        If MsgBox("Are you sure you want to delete this item?", vbYesNo + vbQuestion, "Delete") = vbNo Then
        Else
            If Me.DataGridView1.Rows.Count > 0 Then
                If Me.DataGridView1.SelectedRows.Count > 0 Then
                    Dim x As Integer = Me.DataGridView1.SelectedRows(0).Cells("FNo").Value


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

                    'delete data
                    Dim cmb As New OleDb.OleDbCommand
                    cmb.Connection = cnn
                    cmb.CommandText = "DELETE FROM Food WHERE FNo=" & x

                    cmb.ExecuteNonQuery()

                    'refresh data
                    Me.RefreshData()

                    'close connection
                    cnn.Close()
                End If
            End If
        End If
    End Sub

it keep showing me error that Column named FNo cannot be found. in my access database, i have Fno as primary key, foodname and food price.

  • 2 Contributors
  • 3 Replies
  • 249 Views
  • 13 Hours Discussion Span
  • Latest Post Latest Post by sing1006

Dani AI

Generated

Brief diagnosis and checklist (what actually caused the error and quick fixes)

The runtime error "Column named FNo cannot be found." most often comes from the DataGridView lookup (calling a cell by column name that doesn’t exist) — not always from SQL. It can also be caused by a database field name that conflicts with Access reserved words or contains special characters. As discovered, renaming the field fixed the symptom; ’s SQL suggestion pointed at the SQL side but the safer fix is to confirm both the grid column name and the DB identifier before deleting. (learn.microsoft.com)

What to check and quick debugging (do this first)

  • Dump the grid’s columns to see the column Name, HeaderText and DataPropertyName (Name is what Cells("...") uses).
  • If the grid is data-bound, read the primary key from the bound row (BindingSource / DataRowView) instead of relying on SelectedRows, which avoids name/selection mismatches.

Example VB.NET snippets to inspect and read values:

For Each c As DataGridViewColumn In DataGridView1.Columns
    Debug.WriteLine(String.Format("Idx={0} Name='{1}' Header='{2}' DataProperty='{3}'", _
        c.Index, c.Name, c.HeaderText, c.DataPropertyName))
Next

Dim drv As DataRowView = TryCast(BindingSource1.Current, DataRowView)
If drv IsNot Nothing Then
    Dim id As Integer = Convert.ToInt32(drv("FoodID"))
End If

(Use these to confirm the exact column identifier you must use in code.) (learn.microsoft.com)

Safe deletion: parameterized + bracketed names

Avoid string concatenation for SQL and escape any problematic identifiers with brackets. With OleDb use positional parameters (?) and add parameters in the same order. Example pattern:

Using cmd As New OleDb.OleDbCommand("DELETE FROM Food WHERE [FoodID]=?", cnn)
    cmd.Parameters.AddWithValue("?", id)
    cmd.ExecuteNonQuery()
End Using

This prevents type/quoting problems and SQL injection; OleDb requires ? placeholders (parameters must be added in order). Also bracket names like [FieldName] if they might be reserved or contain spaces. (learn.microsoft.com)

Best-practice notes

  • Prefer nonreserved, descriptive PK names (e.g., FoodID or ID).
  • Avoid spaces/special characters in field names; use brackets if renaming is not possible.
  • Always test by dumping grid columns and the bound row to locate the exact identifier before changing SQL. Back up data before running destructive statements. (support.microsoft.com)

This addresses both the grid-side cause (missing DataGridView column Name) and the DB-side cause (reserved/ill-formed field names) that appear in this thread.

Recommended Answers

All 3 Replies

Member Avatar for AleMonteiro

I don't know much about access but try

cmb.CommandText = "DELETE * FROM Food WHERE FNo=" & x
Member Avatar for sing1006

i dont think that line of code have problem. i think the problem come from this line of coding.

 Dim x As Integer = Me.DataGridView1.SelectedRows(0).Cells("FNo").Value

it looks like x cannot get the value from the selected row.

Member Avatar for sing1006

i found the the solution already. FNo is a reserve word in microsoft access database.thats why i cant get the value. just change the FNo to others name then problem solve.

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.