How to use SQL in VB

[renemejilla]

Help me on this, I'd like to know how to use SQL in VB?

[dilip_singh3]

Clearfy urself
Do u want to know how to query database using SQL or
do u want to connect SQL Server.

[colonelkuz]

Hi there,

just a sample:

Declare the following:

Public cmdQuery As ADODB.Command
Public rstObject As ADODB.Recordset
Public ADOConnect As ADODB.Connection
Public ActiveConnect As Variant

(of course don't' forget to add Ref: ADO, ActiveX etc.)
........................................................................
create a connection string:

sConnect = "DRIVER={Microsoft ODBC for Oracle}; SERVER=<server_name>; UID=usr; PWD=pwd"

(some prefer to create DSN but I like "server" way - faster)

open a new db-connection:

Set ActiveConnect = New ADODB.Connection
ActiveConnect.Open sConnect
If ActiveConnect.State = 0 Then
    Call MyMsgBox("Can't open " + ActiveConnect + " Database Connection.", 48)
    MousePointer = 0
    Exit Sub
End If

........................................................................
now you create sql-statement:

db_query="select/update/delete ...."
carefully with the "delete" ;o)
and don't forget about the quotes for varchar2

........................................................................

...and finally shoot it! ;o)

Set cmdQuery = New ADODB.Command
Set cmdQuery.ActiveConnection = ActiveConnect
cmdQuery.CommandText = db_query
cmdQuery.CommandType = adCmdUnknown
Set rstObject = New ADODB.Recordset

Set rstObject = cmdQuery.Execute()

........................................................................
and small sample how to fill out your combo box with the results from db:

iObject = 0
Do While Not rstObject.EOF
    YourCombo.List(iObject) = rstObject!<column_name1> string values
    YourCombo.ItemData(iObject) = rstObject!<column_name2> integer
    rstObject.MoveNext
    iObject = iObject + 1
Loop

every time you send a request to db you should keep in mind that your VB data types
should match Oracle ones, e.g. String <=> varchar2, Integer<=> number etc.

Good luck!

[debasisdas]

Nice explanation.