Member Avatar for Estella 
SearchString = "SELECT * FROM Breakdown WHERE BFrom = " & txtFrom.Text ' & " AND BTo = " & txtTo.Text
Dim Con As New OleDbConnection(BCalc_ConnectionString)
Dim Search As New OleDbCommand(SelectSearchString, Con)

I want to stop users from being able to save a duplicate record in my Access database. I thought of using RecordsAffected, but I can't figure out how to get it to work.

That's how I've started. I want to be able to use the result of the RecordsAffected as a parameter in an IF statement so that

IF Search.records affected = 0 THEN Insert records
 ELSE Display msgbox telling user that duplicate records aren't allowed.

Please Help...

  • 2 Contributors
  • 2 Replies
  • 202 Views
  • 6 Hours Discussion Span
  • Latest Post Latest Post by Estella

Dani AI

Generated

A concise, practical alternative to the RecordAffected idea and the reader approach shown by : test for an existing row with a single-value query (COUNT) and use a parameterized command. That avoids loading rows, prevents SQL injection, and is efficient for a presence check. For Access/OleDb use positional placeholders (?) and add parameters in the same order the placeholders appear.

Example (VB.NET, uses ExecuteScalar to return the match count):

Dim sql As String = "SELECT COUNT(*) FROM Breakdown WHERE BFrom = ? AND BTo = ?"
Using cn As New OleDbConnection(BCalc_ConnectionString)
    Using cmd As New OleDbCommand(sql, cn)
        cmd.Parameters.AddWithValue("?", txtFrom.Text)
        cmd.Parameters.AddWithValue("?", txtTo.Text)
        cn.Open()
        Dim existing As Integer = Convert.ToInt32(cmd.ExecuteScalar())
        If existing = 0 Then
            ' proceed with insert
        Else
            MessageBox.Show("Duplicate record not allowed")
        End If
    End Using
End Using

A more robust solution is to enforce uniqueness at the database level (composite unique index on BFrom and BTo). That prevents race conditions that can occur with a check-then-insert pattern in multi-user scenarios; an insert that violates the index will raise an exception that can be caught and handled with a friendly message.

Additional notes: avoid building SQL with string concatenation (syntax errors for text/date fields and injection risk), prefer explicit parameter types over AddWithValue when possible, and use Using blocks so connections/commands are disposed. RecordsAffected/ExecuteNonQuery report affected rows for non-SELECT statements; they are not the simplest way to test whether a SELECT found matches.

Recommended Answers

All 2 Replies

Member Avatar for Jx_Man

see this code :

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\MyDB.mdb;")
cn.Open()
cmd = New OleDbCommand("SELECT * FROM Breakdown WHERE BFrom = '" & txtFrom.Text & "'", cn)
dr = cmd.ExecuteReader
If dr.Read()
  MsgBox "Matching Records Found"
  'write whatevr code u want here
Else
  MsgBox "Data not found"
End If
dr.Close()
cn.Close()
commented: helping suggestion +1
Member Avatar for Estella

thank for great suggestion
it help me much... :)

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.