Simple Select statement

Hi all,

Have a select statement that is in a string variable strSQLSelect, the statement bacically says "Select Count(*) from table where course_name = '" & strName & "' "
am using gcnMartConnect to execure all my statements. Since the result i am getting back is a single result, can I and how do I, specify that the result be put in the variable strTemp.

Sorry for asking such a stupid question, but vb is not my choosen language.

Mike55

Declare a recordset object to hold the result.


Dim rs AS New ADODB.Recordset

rs.Open "Select Count(*) from table where course_name = '" & strName & "' ", gcnMartConnect
msgbox rs.Fields(1)

You need to use a Recordset to get your data from the database, then copy the result into a variable.

something like this...


'Need to add a reference to ADO
Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim retValue As Integer

con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\somepath\myDb.mdb;" & _
"User Id=admin;" & _
"Password="
rs.Open "Select Count(*) as resultat from table where course_name = '" & strName & "' ", con, adOpenForwardOnly, adLockReadOnly
If Not rs.EOF And Not rs.BOF Then
retValue = Val(rs!resultat)
Else
retValue = 0
MsgBox "No record"
End If

rs.Close
con.Close
Set rs = Nothing
Set con = Nothing

http://www.visualbasicforum.com/showthread.php?t=66994

An ADO tutorial will help you as well :)