What is a good code for adding data in adodb, wherein if a particular data is already present , for example in a text-box,it would give a warning like dulpicate data

5 Years
Discussion Span
Last Post by twistercool

You have several options, but a lot depends on what the data source is behind your ADODB object. What works in SQL Server might not work with some other generic ODBC data source. More details of your scenario would help determine what advice to offer.


For MSSQL you have a couple of options. You can set an "if not exists" on the front of your statement like so (modify to fit your code, of course):

if not exists (select 1 from myTable where myKey = myKeyValue)
        insert into myTable (myKey, myOtherStuff) values (myKeyValue, myOtherStuffValue)
        raiserror ('Error in here', 16, 1)

Or, you can wrap the whole thing in a stored procedure and just pass parameters. This gives you a little more freedom as far as error checking and such.

Last, you can just execute the statement and let it error, and trap the errors in your VB code with an "On Error" paragraph. Here's a little utility routine I use all the time:

Sub ExecuteSQL(mySqlStmt As String)
On Error GoTo ExecuteSQL_Err
Dim myConn As ADODB.Connection
Dim myMsg As String
Dim i as Integer
Set myConn = New ADODB.Connection
myConn.Open strConnectString
myConn.Execute mySqlStmt

Set myConn = Nothing
Exit Sub

If myConn.Errors.Count > 0 Then
    For i = 0 To myConn.Errors.Count - 1
        myMsg = myMsg & vbCrLf & myConn.Errors(i).Number & ": " & myConn.Errors(i).Description
    Next i
    MsgBox "SQL Errors:" & vbCrLf & myMsg, vbOKOnly + vbCritical, "Execute SQL Statement"
    MsgBox "Error " & Err.Number & ": " & Err.Description, vbOKOnly + vbCritical, "Execute SQL Statement"
End If

Resume ExecuteSQL_Resume

End Sub

It assumes that you have a valid connection string somewhere called "strConnectString" to point to either an OLEDB provider or an ODBC data source.

Keep in mind though that if you're using an ODBC provider that doesn't support SQL (think "Text Provider") the technique will not work.

Good luck!

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.