Help pls - Xtreme Visual Basic Talk

02-08-2006, 01:48 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

Help pls

i am using ADO and how do i find out how many records is in the table?

let's say a table name fruits and i was searching using SQL

SELECT * FROM Fruits WHERE FruitName = "Apple"

there was 10 apples inside the fruits table, how do i know from the coding?

02-08-2006, 02:03 AM

anthony_n

Senior Contributor

Join Date: May 2005

Location: Manchester,England

Posts: 1,293

just run a query
Select count(*) from fruits where FruitName = "Apple"

what type of database is this?

__________________
ADO TUTOR

File IO Tutor

02-08-2006, 02:25 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

i am using access. how to store the count(*) into a variable?

hmm... i heard that can use recordset to store the number of record affected and i had go through msdn but couldn't understand it... can show me some code??

02-08-2006, 03:11 AM

MilanJ

Junior Contributor

Join Date: Sep 2005

Posts: 370

Such:
"select count(*) as Numb .... "
And then you can Numb store to a variable.

02-08-2006, 03:29 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

hmm... i get 0 instead of 10

here is my example of my code:

Code:

'general
Dim db As ADODB.Connection
Dim em, si As ADODB.Recordset 'em for employee, si for system info

'formload
Set db = New ADODB.Connection
db.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=test"
db.Open

'search function
Dim searchfruit As ADODB.Recordset
set searchfruit = new adodb.recordset

searchfruit.open "SELECT COUNT(*)  as total FROM Fruits Where FruitsName = '" & txtfruit.text & "'"
msgbox total,,"test"

how?

02-08-2006, 03:37 AM

MilanJ

Junior Contributor

Join Date: Sep 2005

Posts: 370

If it selected wrong number, then your condition may be wrong - check the content of your txtfruit.text, if it is adequate.
You can also launch such select statement from Access, what would be the result.
MilanJ

02-08-2006, 03:39 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

no, even i change the txtfruits.text to apple it still returns me 0
and i don't know how to launch statement from access

02-08-2006, 03:53 AM

MilanJ

Junior Contributor

Join Date: Sep 2005

Posts: 370

Well, try select without any selecting criteria, it will return numer of all records in the table:
"SELECT COUNT(*) as total FROM Fruits"
EDIT: Now I noticed your MsgBox in the code. You need there make reference between selected row and recordset - fro example such:
msgbox searchfruit!total,,"test"

Last edited by MilanJ; 02-08-2006 at 04:03 AM.

02-08-2006, 03:56 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

return as nothing... not even 0 and how to import query from access?

#10

02-08-2006, 04:00 AM

MilanJ

Junior Contributor

Join Date: Sep 2005

Posts: 370

Quote:

Originally Posted by thevision

return as nothing... not even 0 and how to import query from access?

I replied in EDIT in my previous post - you need make reference between recordset and selected row - msgbox searchfruit!total

#11

02-08-2006, 05:40 AM

thevision

Freshman

Join Date: Jan 2006

Posts: 44

Yeah ! it works ! thanks ^^

#12

02-08-2006, 08:41 AM

loquin

Google Hound

Retired Moderator
* Guru *

Join Date: Nov 2001

Location: Arizona, USA

Posts: 12,378

Also, note that with VB6, your statement

Code:

Dim em, si As ADODB.Recordset 'em for employee, si for system info

defines em as a variant, and only si is defined as an ado recordset... You need to EXPLICITLY dim them both if you wish to have TWO ado recordsets. Like this:

Code:

Dim em As ADODB.Recordset, si As ADODB.Recordset 'em for employee, si for system info

(or, place the declarations on separate lines...)

__________________
Lou
"I have my standards. They may be low, but I have them!" ~ Bette Middler
"It's a book about a Spanish guy called Manual. You should read it." ~ Dilbert
"To understand recursion, you must first understand recursion." ~ unknown