[RESOLVED] Comparing variable with rows in database?

[kaushiks]

Hi,


I have an application in which i have to collect a value in variable and compare with a field in database which has many rows or entries. For example variable is a string called 'name'. Now this variable will be compared with a field in database which has many rows of names in it. If value of any row matches with the value of 'name' it will give a message name already exists. If not then a new row will be added.

Basically i have an idea to do it, i will get the number of rows in a variable, run a loop with counter in it. In that i will put the if else codition. But my deliemma is how do i count the number of rows and how do i introduce a loop.

Please need help on this ASAP.

Regards,
Kaushik

[marktheman]

Hi,
Start looking at using "ADODB.Connection" connection object to connect to database.
Start looking at using "ADODB.Recordset" to retrieve records from database and in your case to compare with strings.
Look at the following link and it should help.
http://www.timesheetsmts.com/adotutorial.htm
Hope I have helped.

[VBFnewcomer]

I suspect you would even require a loop. I suppose you might be aware of ADO recordsets. Simply create one. Open it. then use

adoRs.find "databaseFieldNameToCompare = '" & yourValue & "'"
if not (adoRs.EOF) then
message name already exists
else
code to add a new row
endif

[kaushiks]

Hi,

The code is working perfecly well. Thanks guys it was really of great help.

[leinad31]

I suspect you would even require a loop. I suppose you might be aware of ADO recordsets. Simply create one. Open it. then use

Bear in mind this is only good for school assignments and in most cases will not be efficient for real world data (rows numbering thousands)... this is because your loading an entire table just to get values in one row or worse in one column... Worst is when you end up loading almost the entire database in memory.

[VBFnewcomer]

Bear in mind this is only good for school assignments

Is there anyother efficient way. Taking into considertaion the nature of search.

Now this variable will be compared with a field in database which has many rows of names in it

[leinad31]

Is there anyother efficient way. Taking into considertaion the nature of search.

Use a SELECT query with a WHERE clause and an aggregate COUNT(). Do a search, there are hundreds of samples.

[VBFnewcomer]

Use a SELECT query with a WHERE clause and an aggregate COUNT().

I am not sure if these work with rs.find. I presume these would be taken care of by the adoRs.open <sql statement> which would be coded before reaching rs.find lines correct me if Iam wrong.

[leinad31]

Did you try to do a search? There's no need for rs.find and there's no need to retrieve entire list of users just to verify if name already exists.

[VBFnewcomer]

Did you try to do a search?

NO rs.find was something a friend of mine introduced me

there's no need to retrieve entire list of users just to verify if name already exists

can u point me to certain examples. I would be thankful.

[leinad31]

Learn SQL first:

sSQL = "SELECT * FROM sampletable", returns all columns and all records in the table
sSQL = "SELECT * FROM sampletable WHERE username = 'bob'", returns all columns for records having username of 'bob'
sSQL = "SELECT COUNT(username) AS cnt FROM sampletable WHERE username = 'bob' ", returns the number of records having username of 'bob'

Dim sSQL As String
Dim rs As ADODB.Recordset  'assume ADODB.Connection named cn already setup

sSQL = "SELECT COUNT(username) AS cnt FROM sampletable WHERE username = 'bob' "
Set rs = cn.Execute(sSQL)
If rs.Fields("cnt") > 0 Then
   Msgbox "already exists"
Else
   sSQL = "INSERT INTO sampletable (username) VALUES ('bob') "
   cn.Execute sSQL  'add new name
End if

[Al42]

To simplify leinad31's code

sSQL = "SELECT username FROM sampletable WHERE username = 'bob' "
Set rs = New ADODB Recordset 'so we can reclaim the memory later
rs.Open sSQL, cn
If rs.EOF Then 'if we're at EOF, there are no records where username = 'bob'
   sSQL = "INSERT INTO sampletable (username) VALUES ('bob') "
   cn.Execute sSQL  'add new name
Else
   Msgbox "already exists"
End if
Set rs = Nothing 'reclaim the memory