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
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.
What would you suggest for MS SQL and ODBC coz I use both
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.
Thanks your very good