how to check if record exists..

[mur3x]

Hi i'd like to use a methodology that prevents new data being added to an existing record with the same field data.

ex: if record with mno=9 and bno=4 exists in the table, The system shud prevent any new entry with mno=9 and bno=4..

i used the following dummy code but it doesn't prompt me "yes".
(rs-recordset, mno from listview, bno from variable strBno, strExists-string variable.
strMno-long variable)

private sub cmdCheck_click()

rs7.Open "SELECT count(mno) as TotalMno FROM ModuleDetails md where md.mno=" & lstvModules.SelectedItem & " and md.bno=" & strBno

strMno = rs7.Fields("totalmno")

If strMno > 1 Then
   	strExists = "yes! Mno + Bno exists"   ' already record is there
End If

If strMno < 1 Then
	strExists = &quot;No! Mno + Bno doesn't exist&quot;  ' no record is there
End If

MsgBox &quot;message:&quot; & strExists


End Sub

1. is it something wrong with my sql?.

2. can u suggest an alternative method to check whether a record exists

All i want to do is,
if it prompts "yes" i dont add the record.
if it propmts "no" i want to add the record.

input really appreciated.

thnx!!!!
Mur.

 

[zemp]

Three things that I see,

1. strBno is a string and will need to be surrounded by sibgle quotes if the field is of type string (text, varchar, etc).

" and md.bno='" & strBno & "'"

However this method leaves you open to an SQL injection attack. See faq709-1526 for the best method.

2. You conditions don't look for a count equal to 1, only greater than or less than 1. What happens if 'totalmno'=1?

3. Your if statements compare a string value to a number. It will never by true.


Thanks and Good Luck!

zemp

 

[jonathanmb]

From the looks of it, I don't believe he needs to enclose the variable in single quotes. I think he's comparing numerical values. It's my guess he's concatenating the strings together, but his strings contain numerical values (I base that guess on his example and the field names). Although he should probably convert them to the proper type rather than leaving them as strings.

As far as checking to see if a record exists, why use count? Just run a query with the same conditions you're using already and check for EOF/BOF when the query is done. If you're not at EOF/BOF, then you have a record that meets those conditions. If you're at EOF/BOF, then you don't have a record that meets your given conditions.

However, I think you're going about this wrong in a different way. You should design your database in a way that requires less work on your part as the programmer. If you add a key that contains the two fields in question, you can set that key to contain only unique values. If you try to insert a record with identical keys in that situation, the database will prevent that from happening. You would only need to trap that error and proceed accordingly.

--
Jonathan

 

[rtshort]

Try this: (assuming that cn is the connection, and rs is the recordset, and you already have the code set for that. Also the DBName is the name of your Database, and FName is the first thing that you are searching for SName is the second thing that you are searching for.)

sql = "Select FName, SName From DBName"
If rs.BOF = True and rs.BOF = True Then
'the data does not exist
'add the data to the DB here
Else
'the data does exist
'use the MsgBox and tell the user that
'use Exit Sub to stop the entry
End If

Hope this is what you were looking for

Rob
Just my $.02.

 

[rtshort]

Sorry, forgot the connection and recordset in the example:

sql = "Select FName, SName From DBName"
rs.Open sql, cn
If rs.BOF = True and rs.BOF = True Then
'the data does not exist
'add the data to the DB here
Else
'the data does exist
'use the MsgBox and tell the user that
'use Exit Sub to stop the entry
End If
rs.close
Set rs = Nothing
cn.close
Set cn = Nothing


Sorry bout that


Rob
Just my $.02.

 

[mur3x]

you said it man!.
i d 'nt check it whether 'totalmno'=1
thnx!!!


Mur.

 

[mur3x]

thnx others too for the suggestions!!.
great!!