Checking if entry is already in database 1

[Catrina]

I have an entry form that the user fills out(using textboxes), then clicks save and the entries are inserted into the database (using Insert Into). I can not figure out how to check the primary key field to see it is a duplicate, so I can pop up a message box to inform the user. I'm just learning to use vb with databases, so a detailed explaination would be greatly appreciated.

I'm not sure if it matters, but I have connected to the database via code and using ADO.

Here is my code for inserting the data, I want to do the check right before.

Public Sub Add()
Dim stSQL As String
Dim stPassword As String
Dim stCommPass As String
Dim stTransactionDate As String
Dim stClient As String
Dim stFound As String

With frmAdd

stPassword = .txtPassword
stCommPass = .txtCommPass
stClient = .txtClient

stSQL = "insert into cust"
stSQL = stSQL & "("
stSQL = stSQL & "Client"
stSQL = stSQL & ",Pass"
stSQL = stSQL & ",CommPass"
stSQL = stSQL & &quot"
stSQL = stSQL & " values"
stSQL = stSQL & "("
stSQL = stSQL & IsNVLString(stClient)
stSQL = stSQL & "," & IsNVLString(stPassword)
stSQL = stSQL & "," & IsNVLString(stCommPass)

stSQL = stSQL & &quot"

objAccessConnection.Execute stSQL



End With

MsgBox "Worked"
End Sub

Thanks for any help

Catrina

 

[listener22]

Do a
select from cust where
client = stClient (fix up a sql string like you did in your insert)
(and save the results in an ado recordset rs)

(Then)

if rs.bof = rs.eof then

(do the insert)
else
msgbox("Client already exists.&quot
(you could also display the record)

end if

 

[Catrina]

Thank you, that helped me in more than one way. It finally sunk in that the recordset is what I define it as. I've been using books to learn, and not quite getting it, so I just started a tiny program. Actually doing something that makes sense to me helps me learn. Thanks again

Catrina

 

[mohmehran]

You may use a T-SQL SELECT statement first to chech if a duplicate exists.
For example:

strSQL = "SELECT Client FROM Cust WHERE Client='" & txtClient.Text & "'"

But you should have in mind that the primary key is not a filed that is specified by the User. It's much better to have another primary key (like an AutoNumber or Identity) field as primary key. Still you can have a contraint on Client to be unique. Generally the primary key is specified by the system not the User. What if a Client name is entered incorrectly? Will you change the primary key?

Good luck.