Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories

SIMPLE SQL PROBLEM - HELP


Hey folks,

Simple SQL prob.

USing VB 4.0, dbase 3 file and SQL

All I want to do is check two numeric values in two separate fields
and if they match the values of stored variables in the VB app then
they return records.

Sample code below, but doesnt work, the problem is SQL syntax, Im making a mistake somewhere.

Two internal variables in Vb app;
Value1
Value2

Two fields to check in database;
Field1
Field2

name of database file;
dbfile

so for example if

Value1 = 20
Value2 = 12

Then find the the records in the database where both fields
match both values and then return records.

I thought I should use two "where" statements in the SQL query but that doesnt seem to work.

Also, if there are more than one record that qualifies and has both values
then Id like all the records listed that qualify and dumped into a text box, I twidled with sime fudges that didnt quite work but it would only find the first qualifying record and not the others....?




Private Sub Command2_Click()
On Error Resume Next

'=============
'=============
'=============
Dim MyDatabase As Database
Dim MyRecordset As Recordset, MyField As Field
Dim MySQL As String, I As Integer


Set MyDatabase = OpenDatabase("C:", False, False, "dBASE III;")
'here in the above line, for dbase 3 files, only indicate the path where the file is to be found, but not the acutal file itself

' Create Recordset object

Set MyRecordset = MyDatabase.OpenRecordset("dbfile")
'here, the actual name of the dbase 3 file is indicate, 'test2', but NOT its file extention

' MySQL = "SELECT [FIELD1],[FIELD2] FROM dbfile WHERE = " & value1 & value2


Set MyRecordset = MyDatabase.OpenRecordset(MySQL) ' Generate recordset.
'here the located record matching the criteria i wanted is targeted

house_var = MyRecordset("HOUSE")
'here the first part of the actual data from the record i want is stored to a variable

term_var = MyRecordset("TERM")
'here the first part of the actual data from the record i want is stored to a variable

rotation_var = MyRecordset("ROTATION")
'here the first part of the actual data from the record i want is stored to a variable


RichTextBox2.Text = house_var & Chr(9) & term_var & Chr(9) & rotation_var & Chr(13)
MyRecordset.Close ' Close table.
MyDatabase.Close


Comments

  • JonathanJonathan Member Posts: 2,914
    You want something like:

    MySQL = "SELECT [FIELD1],[FIELD2] FROM dbfile WHERE = [FIELD1] = " & value1 & " AND [FIELD2] = " & value2

    Jonathan
    ###
    for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");
  • cyndy_northrupcyndy_northrup Member Posts: 11
    Hooray! that works! :)

    I didnt know how to place the syntax correctly! Thanks Jonathan!

    Only one problem though, :(

    It only returns the first record meeting the criteria?

    The way the table is set up, is such that there are two separate fields both of which contain
    numerica data, there are other fields that contain text data. The numeric fields can contain
    the same numbers but the text data in the fields is always unique.

    So for instance

    FIELD1 FIELD2 FIELD3 FIELD4
    2334 124 Part xyq Bob Smith
    2334 124 Part xyz Jane Brown
    2334 124 Part xyl Sarah Jose

    And the table has say hundreds of entries, here we want to go through the table and return
    all records that have fields 1,2 that have 2334, 124, and return their associated FIELD3,4
    text data. Thats pretty much it. Then dump that in a text box.




    : You want something like:
    :
    : MySQL = "SELECT [FIELD1],[FIELD2] FROM dbfile WHERE = [FIELD1] = " &
    : value1 & " AND [FIELD2] = " & value2
    :
    : Jonathan
    : ###
    : for(74,117,115,116){$::a.=chr};(($_.='qwertyui')&&
    : (tr/yuiqwert/her anot/))for($::b);for($::c){$_.=$^X;
    : /(p.{2}l)/;$_=$1}$::b=~/(..)$/;print("$::a$::b $::c hack$1.");

  • BitByBit_ThorBitByBit_Thor Member Posts: 2,444
    : Hooray! that works! :)
    :
    : I didnt know how to place the syntax correctly! Thanks Jonathan!
    :
    : Only one problem though, :(
    :
    : It only returns the first record meeting the criteria?

    Actually, when an SQL statement is evaluated it returns a table with the results. So if there are more, it returns more.

    What you want to do is modify the statement to this:
    MySQL = "SELECT [FIELD3],[FIELD4] FROM dbfile WHERE [FIELD1] = " & value1 & " AND [FIELD2] = " & value2

    This willl, when evaluated, return a table with the columns: the third and fourth. For each of these entries, field 1 and 2 will be as specified.

    Best Regards,
    Richard

    The way I see it... Well, it's all pretty blurry
  • cyndy_northrupcyndy_northrup Member Posts: 11

    Thanks for the tips guys, it really helps me a lot!

    : : Hooray! that works! :)
    : :
    : : I didnt know how to place the syntax correctly! Thanks Jonathan!
    : :
    : : Only one problem though, :(
    : :
    : : It only returns the first record meeting the criteria?
    :
    : Actually, when an SQL statement is evaluated it returns a table with
    : the results. So if there are more, it returns more.
    :
    : What you want to do is modify the statement to this:
    : MySQL = "SELECT [FIELD3],[FIELD4] FROM dbfile WHERE [FIELD1] = " &
    : value1 & " AND [FIELD2] = " & value2
    :
    : This willl, when evaluated, return a table with the columns: the
    : third and fourth. For each of these entries, field 1 and 2 will be
    : as specified.
    :
    : Best Regards,
    : Richard
    :
    : The way I see it... Well, it's all pretty blurry

Sign In or Register to comment.