how can i avoid data duplicate in the database?
i mean if i add in the fields that has been already save it will still save.
i don't know how to query or filter not to add the same info.

i have the code below but it still save if i input same as already in the database.

myqry = "INSERT INTO tblInformation(Firstname,LastName,Address) "
myqry = myqry + "VALUES('" & txtFirst.Text & "','" & txtLast.Text & "','" & txtAddress.Text & "')"

mycmd = New OleDbCommand

With mycmd
     .CommandText = myqry
     .Connection = conn
     .ExecuteNonQuery()
End With

i want is to query first before it add the data to avoid same info.

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

U can also use a datareader instead of datatable....

if the reader returns true means the data is present so do not insert....else insert

Try
                Dim myCommand As OleDbCommand
                myCommand = New OleDbCommand("your sql query", Connection)
                Dim reader As OleDbDataReader = myCommand.ExecuteReader
                if reader.read=True
                 MsgBox("Data already exists")
                else
                 'insert the data
                reader.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
commented: thanks i got it. +0

i think datareader is slower then dataadapter , but yes we can use datareader also for this purpose.but speed is something which is imp for any application.

Another possibility (fewer layers) is to use ADO as in

Dim con As New ADODB.Connection
Dim rec As New ADODB.Recordset

con.Open("Driver={SQL Server};Server=.\SQLEXPRESS;Database=mydb;Trusted_Connection=yes;")
rec.Open("select count(*) from admin where username='John'", con, CursorTypeEnum.adOpenForwardOnly)

If rec(0).Value = 1 Then
    'record exists
Else
    'record does not exist
End If

rec.Close()
con.Close()

A table should have a primary key defined. In the above example, the primary key is "username". Any attempt to add a duplicate username will result in an error. Because the primary key is unique, you only need to check that field to determine whether or not you can add a new record. You could easily encapsulate the duplicate check in a function as in

user = "John")

If KeyExists(user) Then
    MsgBox("User " & user & " already exists in database")
Else
    'do ADD code here
End If

hello !
try this may be this will solve your prob

dim con as new sqlconnection("connectionstring")
con.open()
dim da as new sqldataadapter("select firstname,lastname,address from table where firstname='" +txtfirstname.text+"' and lastname='"+txtlastname.text +"' and address='"+ txtaddress.text+"'",con)
dim dt as new datatable
da.fill(dt)
if dt.rows.count = 0 then
'means record is not in db , you can save
else
'record is already in db , so dont save
end if

this is not the ideal solution as i think this thing make your program slow ,but it will solve your prob .
if your prob is solved then please mark this thread solved .

Regards

but can you help me how to to do it with in OLEDB?
sorry i'm still fresh in VB 2010

U can also use a datareader instead of datatable....

if the reader returns true means the data is present so do not insert....else insert

Try
                Dim myCommand As OleDbCommand
                myCommand = New OleDbCommand("your sql query", Connection)
                Dim reader As OleDbDataReader = myCommand.ExecuteReader
                if reader.read=True
                 MsgBox("Data already exists")
                else
                 'insert the data
                reader.Close()
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

it's ok not slow. :)

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.