SQL queries with variables--How do I do this?

SQL queries with variables--How do I do this?

Post by David Ha » Thu, 09 May 1996 04:00:00



 Set dbDIRS = DBEngine.Workspaces(0).OpenDatabase("c:\My
Documents\db4.mdb")                    
Set rsetDIRS = dbDIRS.OpenRecordset("SELECT * FROM [Directory List]
WHERE [Directory State] = 'WI' ORDER BY [Directory Number]")

    Set databaseDIRS.Recordset = rsetDIRS
    databaseDIRS.Refresh

I am not very versed in SQL.  I need to do some very basic things in
VB 4.0--simple Querying from an Access database.  The above SQL expr
gets me all Wisconsin books---how do I search using a variable or
other object (such as a text box)?  I couldn't find ANYTHING in a
Oracle book nor MS Access or VB books  This MUST be possible.
Substituting strDATA (as variable) or searchINPUT.text (as text box)
doesn't work.

Thanks!

                Dave

 
 
 

SQL queries with variables--How do I do this?

Post by Harry Stryb » Thu, 09 May 1996 04:00:00


snip    

Quote:>I am not very versed in SQL.  I need to do some very basic things in
>VB 4.0--simple Querying from an Access database.  The above SQL expr
>gets me all Wisconsin books---how do I search using a variable or
>other object (such as a text box)?  I couldn't find ANYTHING in a
>Oracle book nor MS Access or VB books  This MUST be possible.
>Substituting strDATA (as variable) or searchINPUT.text (as text box)
>doesn't work.

snip
Try something like:  "Select * From MyTable Where ((MyField='" & Text.Text &
"'))"
OR "Select * From MyTable Where ((MyField=" & myvar & "))" (for numbers)

Harry Strybos   :-{>
___________________________________________________________
"I prefer to die peacefully in my sleep like my grandfather
not screaming in terror like his passengers"
___________________________________________________________

 
 
 

SQL queries with variables--How do I do this?

Post by ThGruen » Thu, 09 May 1996 04:00:00



> Set dbDIRS = DBEngine.Workspaces(0).OpenDatabase("c:\My
>Documents\db4.mdb")                    
>Set rsetDIRS = dbDIRS.OpenRecordset("SELECT * FROM [Directory List]
>WHERE [Directory State] = 'WI' ORDER BY [Directory Number]")

>    Set databaseDIRS.Recordset = rsetDIRS
>    databaseDIRS.Refresh

>I am not very versed in SQL.  I need to do some very basic things in
>VB 4.0--simple Querying from an Access database.  The above SQL expr
>gets me all Wisconsin books---how do I search using a variable or
>other object (such as a text box)?  I couldn't find ANYTHING in a
>Oracle book nor MS Access or VB books  This MUST be possible.
>Substituting strDATA (as variable) or searchINPUT.text (as text box)
>doesn't work.

How about this:

Assuming that Text1 is a textbox where the user can enter the desired
state:

Dim MySql as String
Dim Quo as String

.....   'other declarations etc.

Quo = Chr$(34)  'quotation character

MySql = "SELECT * FROM [Directory List] WHERE [Directory State] = "
MySql = MySql & Quo & Text1.Text & Quo
MySql = MySql & " ORDER BY [Directory Number]"

...  'Use MySql

Hope it help

Thomas

*****
If you are well, don't worry ... it will pass over.
*****

 
 
 

SQL queries with variables--How do I do this?

Post by Jeremy Markm » Thu, 09 May 1996 04:00:00


:  Set dbDIRS = DBEngine.Workspaces(0).OpenDatabase("c:\My
: Documents\db4.mdb")                    
: Set rsetDIRS = dbDIRS.OpenRecordset("SELECT * FROM [Directory List]
: WHERE [Directory State] = 'WI' ORDER BY [Directory Number]")
:    
:     Set databaseDIRS.Recordset = rsetDIRS
:     databaseDIRS.Refresh
:    

Just concatenate a string with the variables you want to include: i.e.
SQLStr = "Select * FROM [Directory List] Where [Directory State] = '"
SQLStr = SQLStr & Text1.Text & "' ORDER By [Directory Number];"

 
 
 

SQL queries with variables--How do I do this?

Post by Joe Garri » Fri, 10 May 1996 04:00:00



> Set dbDIRS = DBEngine.Workspaces(0).OpenDatabase("c:\My
>Documents\db4.mdb")                    
>Set rsetDIRS = dbDIRS.OpenRecordset("SELECT * FROM [Directory List]
>WHERE [Directory State] = 'WI' ORDER BY [Directory Number]")

>    Set databaseDIRS.Recordset = rsetDIRS
>    databaseDIRS.Refresh

>I am not very versed in SQL.  I need to do some very basic things in
>VB 4.0--simple Querying from an Access database.  The above SQL expr
>gets me all Wisconsin books---how do I search using a variable or
>other object (such as a text box)?  I couldn't find ANYTHING in a
>Oracle book nor MS Access or VB books  This MUST be possible.
>Substituting strDATA (as variable) or searchINPUT.text (as text box)
>doesn't work.

You have to insert the quotes manually if you're building the SQL
based on a variable or control.

SQL = "<...> WHERE [Directory State] = " & Chr$(34) &
searchINPUT.Text & Chr$(34) & <...>"

-----

Joe

Never underestimate the power of a WAG.

 
 
 

SQL queries with variables--How do I do this?

Post by Sue Turn » Wed, 15 May 1996 04:00:00



> Set dbDIRS = DBEngine.Workspaces(0).OpenDatabase("c:\My
>Documents\db4.mdb")                    
>Set rsetDIRS = dbDIRS.OpenRecordset("SELECT * FROM [Directory List]
>WHERE [Directory State] = 'WI' ORDER BY [Directory Number]")

>    Set databaseDIRS.Recordset = rsetDIRS
>    databaseDIRS.Refresh

>I am not very versed in SQL.  I need to do some very basic things in
>VB 4.0--simple Querying from an Access database.  The above SQL expr
>gets me all Wisconsin books---how do I search using a variable or
>other object (such as a text box)?  I couldn't find ANYTHING in a
>Oracle book nor MS Access or VB books  This MUST be possible.
>Substituting strDATA (as variable) or searchINPUT.text (as text box)
>doesn't work.

>Thanks!

>                Dave

>-------------------------------------------------------------------

>'Here is a couple of Sqls pulling a text field from another form

strSQL = "SELECT * FROM Table1 WHERE field1='" &
trim(frmName.txtfield(0)) & "' ORDER BY fieldname"

'and another example

strsql = "SELECT * FROM Table1 WHERE [field1]=" & """"
strsql = strsql &
frmname.datcontrol.Recordset.Fields("field1").Value & """"

datcontrol2.RecordSource = strSQL

Hope that helps...

SUE

 
 
 

1. ADO thinks DBMS is done, but DBMS ain't done

I have 2 Recordsets on 2 different applications communicating with the same
database

-=The first Recordset is opened with=-
m_pGlobal_RS->Open(adCmd, m_pConnection.GetInterfacePtr(), adOpenKeyset,
adLockPessimistic, adCmdText);

-=The second Recordset is opened with=-
local_RS->Open(adCmd, m_pConnection.GetInterfacePtr(), adOpenKeyset,
adLockReadOnly, adCmdText);

* adCmd is a simple SELECT statement that is the SAME for both recordsets.

The code order is roughly this

Open m_pGlobal_RS
...
...
Change fields on m_pGlobal_RS
...
...
MoveNext on all rows of m_pGlobal_RS until EOF then MoveFirst  // since
pessimistic every row op sends update
..
..
I then fire an event to another application which opens local_RS.

It is random whether the data opened by the second RS is the pre-update data
or the post-update data. How can this be? I opened the connection
with the default  synchronous option, isn't this asynchronous behavior?

Thanks for the help,
-=Adog

2. "before delete" in ms sql?

3. Caché from InterSystems - doing wh at other MV vendors should be doing

4. Replication Jobs have automatically increasing suffix

5. Trigger doing UNLOAD to file OR calling Store Procedure doing the UNLOAD to file

6. HELP! Informix SDK C++ and thread environment

7. Help - Simple SQL query doing full table scans

8. MS-Access: Advantages and limitations?

9. Doing a crosstab query in SQL 6.5

10. psql Segfaults shortly after doing variable substitution

11. Help Doing a Query.

12. Doing a find on a query - Delphi 1