Need help on this code

[twistercool]

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

[BitBlt]

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.

[twistercool]

What would you suggest for MS SQL and ODBC coz I use both

[BitBlt]

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)
    begin
        insert into myTable (myKey, myOtherStuff) values (myKeyValue, myOtherStuffValue)
    end
else
    begin
        raiserror ('Error in here', 16, 1)
    end

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

ExecuteSQL_Resume:
myConn.Close
Set myConn = Nothing
Exit Sub

ExecuteSQL_Err:
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"
Else
    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!

[twistercool]

Thanks your very good