using SQL in VB

How does one use SQL statements inside of VB? I am using VB6 and Access 2000 databases.

thanks yall

[Chris]

It all depands on what type of information you need tend to retrieve from the database. the SQl statement can be:

• SELECT ...FROM...WHERE
• INSERT INTO ... SELECT ...FROM... WHERE ...ORDER BY
• SELECT DISTINCTROW ... FROM ...
• SELECT DISTINCT ... FROM...
• and many many more

So, may be you can just describe more about your objective, before we can give you some guide. Or you can take a look on the Database tutorial on this forum Tutorial

[Edited by Chris on 06-17-2000 at 03:56 PM]

i would like to use a selcet statement but i dont know how that is my question

[Chris]

May be you can take the Biblio.mdb as an example:

Let said, I want to select all the Titles information from Table Titles with PubID = 175, then the SQl will look like:

SELECT * FROM Titles WHERE PubID='175' ORDER BY ISBN ASC;

Hope this will help or you can take a look on the DAO2.6 Help file.

ok i know how to write SQL i know how to use it what i don't know is how to apply it in VB. how do i call the select state ment? where do i get the results? thats what i need to do.

can i just type in select * from table in the code and it works?

thasnk

[Chris]

Take a look on this code & hope this will help you.

Assume you have reference your project to Microsoft DAO 3.5 Object Library

Goto Project|Reference

Option Explicit
Private Db As DAO.Database
Private Rs As DAO.Recordset
Private xSQL As String
Private Sub Form_Load()
'Open the Biblio.mdb database
Set Db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\Biblio.mdb", False, False)

'Build the SQL string
xSQL = "SELECT * FROM Titles WHERE PubID=175 ORDER BY Title ASC;"

'Open the recordset
Set Rs = Db.OpenRecordset(xSQL, dbOpenSnapshot)

DoEvents
While Not Rs.EOF
    Debug.Print Rs.Fields(0) & Space(10) & Rs.Fields(1) & Space(10) & _
                Rs.Fields(2) & Space(10) & Rs.Fields(3) & Space(10) & _
                Rs.Fields(4) & Space(10) & Rs.Fields(5) & Space(10) & _
                Rs.Fields(6) & Space(10) & Rs.Fields(7)
    Rs.MoveNext
Wend

'Close the recordset & database
Rs.Close
Db.Close

'Close the DAO Object
Set Rs = Nothing
Set Db = Nothing
End Sub

[Wen Lie]

Hi dMartin17,

I've got your point, and I think Chris has explained clearly to you. I just want to add something, hope will usefull for you Martin...

I always use SQL Statement at my VB project. If you only use SQL statement to make a view virtual table (such as select statement), you can use it everywhere at VB code to open a view table (a recordset).

Here is my example.

Public Sub Open_Table()
Dim myConn as ADODB.Connection
Dim myRec as ADODB.Recordset

myConn.Open "DSN=yourdsnname"
myRec.Open "select * from author inner join books on author.codes = books.codes where author.id = 'AR001'", myConn, adOpenForwardOnly, adLockReadOnly, adCmdText

if (myRec.BOF<>True) and (myRec.EOF<>True) then
'your code goes here
end if

myRec.Close
myConn.Close
End Sub


If you want to call a SQL Server Stored Procedure from your vb, you can use the statement below :

myConn.Execute "your_stored_procedure_name"

ok, hope that's all will help you,
Cheers,
Wen Lie

thanks guys i think this is what i needed:

Set Rs = Db.OpenRecordset(xSQL, dbOpenSnapshot)

assuming xsql=select something from sometable

and that makes the recordset a databses of what was returned in the select statement, correct?

ill have to try it soon so I can see if i do have a handle on it.


thanks again
doug

[Chris]

Yes, you're rite & hope you can get whatever you wish from the database. But you also can create some more power SQL statement that will make your life easy.

what do you mean i can creat more powerful stements? do you mean with where betweens, etc? i know those. after i do the SQL stastment, should i just use the recordset as i would before with .eof and .bof etc?

[JHausmann]

Joins, Unions, Aggregate functions are some power features of SQL...

[Chris]

Exactly like what JHausmann have said and you also can create a double SELECT statement as well.