Newbie Question: Data from Access directly into variables

[stefanRusterholz]

Hi. Sorry if I ask a stupid question, but I'm a Newbie to VB so please don't beat me ;-)

What I want to do:
open a specific database
run the sql "SELECT field1,field2 FROM mytable WHERE condition='true'" (just an example)
fetch the result directly into a variable (NOT into a datagrid or another visual component and fetch it then into a variable!)

For those who are familiar with PHP and mySQL, the corresponding code for what I want to do with VB was in PHP the following:

mysql_pconnect(...);
$result = mysql_db_query($db_name, "SELECT field1,field2 FROM mytable where condition='true'");
while ($row = mysql_fetch_array($result)){
   #do something with what I got in $row
}

My primary problem is, that the book i bought always talks about putting all the data it got from the db into a datagrid or something like this but because there won't be a GUI (it's for a CGI) this is impossible.

I was very glad about every help you can provide
Stefan Rusterholz

 

[sunaj]

Hi,

What you do is that you open a recordset with the results:
----------------------------------------------------------
Dim Rst as ADODB.recordset

Set Rst = new ADOBD.recordset
Rst.open SELECT field1,field2 FROM mytable WHERE Condition='true'"

if not (Rst.eof and rst.bof) then
Rst.movefirst
'do you code here, loop through the recordset
else
msgbox("nothing found&quot
end if
----------------------------------------------------------

Sunaj

 

[jtl6713]

The code needed to get the data directly into the variable is as follows

Dim MyDatabase As ADODB.Database
Dim MyRecordset As ADODB.Recordset

Set MyDatabase = New ADODB.Recordset
Set MyRecordset = New ADODB.Recordset

MyDatabase.Open COnnectionString
Set MyRecordset = MyDatabase


dim MySqlString
MySqlString = "SELECT field1,field2 FROM mytable where condition='true'"

MyRecordset.Open MySqlString

MyVariable = MyRecordset("field1&quot.value

MyRecordset.Close
Set MyRecordset = Nothing
Set MyDatabase = Nothing Joseph Logan
jlogan@softsource.net

 

[sunaj]

Hi agian
I forgot the connection string, the code should read:
--------------------------------------------------------------
Dim Rst as ADODB.recordset
Dim StrCon as string

Set Rst = new ADOBD.recordset
ConStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = c:\tmp\test.mdb"

Rst.open SELECT field1,field2 FROM mytable WHERE Condition='true'", StrCon
if not (Rst.eof and rst.bof) then
Rst.movefirst
'do you code here, loop through the recordset
else
msgbox("nothing found&quot
end if
--------------------------------------------------------------

If you do like Joseph suggest, you have to be absolutely sure that the query returns extactly one value (e.g. SELECT count(*) FROM...)

Sunaj

 

[stefanRusterholz]

Thank you very mutch. Unfortunately I'm not at work yet so I can't test it, but I'll try it Monday
Again: thank you!

Stefan