SQL SELECT PROB IN VB 5

[astrogirl77]

Hi,

Im using VB 5, not 6, but am just trying to solve a simple SELECT statement
issue Im having.

I have a simple database, dbase 3 file, only has 360 records

The field and record structure looks like the following, note there are only
3 columns (fields) ;

CONST DECAN DEGREE
Taurus 8 38

All Im trying to do is lookup the first and second column through all the rows
and then return the the third value to a text box (DEGREE), so in my form
I enter a text string of "Taurus" into text1, and then the value of "8" (DECAN)
in the second text box, text2, and then I want the code to return the number "38" (DECAN) into the 3rd text box, text3.

I keep trying to get my SELECT statement to allow my use of the AND operator so I can search on both CONST and DECAN but it just keeps returning error messages... cant even get to the point of returning the DEGREE value Im after. Am posting the code below, can anyone help?

See Code ;

Private Sub Command1_Click()


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

'Dim MySQL As Variant, I As Integer

Dim degrees_value_ref 'var that represents the number in the multiplier cell of the sq grid cell that shows prime or fib in base and or mult, this value becomes a look up value on the 9 by 9 (81) value grid
Dim degrees_value_ref2

degrees_value_ref = Text1.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
degrees_value_ref2 = Text2.Text 'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number


Set MyDatabase = OpenDatabase("C:\% DEGREES", 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 from Accounts table.
Set MyRecordset = MyDatabase.OpenRecordset("degreez")
'here, the actual name of the dbase 3 file is indicate, 'test2', but NOT its file extention

MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = " & degrees_value_ref 'And [DECAN] = "& degrees_value_ref2"
'here the sql string finds the data


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

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

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

Debug.Print "var1;"; var1
Debug.Print "var2;"; var2


'Text2.Text = var1
Text3.Text = var2

End Sub

 

[astrogirl77]

@dilettante

Thank you very much for your reply! I tried to adapt your code
to see if it would work but got the following error message ;

Run Time error "424" Object Required

The debug locked on this line ;

With Cn.Execute(SQL, , adCmdText)

Below is the code I adapted from what you provided ;

Private Sub Command3_Click()
    Dim MyDatabase As Database
    Dim MyRecordset As Recordset, MyField As Field
    Dim MySQL As String, I As Integer
    
    Set MyDatabase = OpenDatabase("C:\degrees", False, False, "dBASE III;")

    Set MyRecordset = MyDatabase.OpenRecordset("degreez")
    '................

    Dim SQL As String
    
    SQL = "SELECT [DEGREE] FROM [degreez] WHERE " _
        & "UCase$([CONST]) = UCase$('" & Trim$(txtCONST.Text) & "') " _
        & "AND [DECAN] = '" & Trim$(txtDECAN.Text) & "';"
    With Cn.Execute(SQL, , adCmdText)
        If .BOF And .EOF Then
            txtDEGREE.Text = "no hits"
        Else
            txtDEGREE.Text = .Fields("DEGREE").Value
        End If
        .Close
    End With
    txtCONST.SetFocus

End Sub

 

[dilettante]

Perhaps this will help, but it is VB6 and uses ADO instead of DAO:

Option Explicit

Private Const JET_CONN1 As String = _
        "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='"
Private Const JET_CONN2 As String = _
        "';" _
      & "Extended Properties='dBase III';" _
      & "Mode=Share Exclusive"

Private Cn As ADODB.Connection

Private Sub DumpTable(ByVal TableName As String)
    Dim Rs As ADODB.Recordset
    Dim FieldIndex As Long
    
    lblGrid.Caption = "Dump of [" & TableName & "]"
    Set Rs = New ADODB.Recordset
    With Rs
        .CursorLocation = adUseClient
        .Open TableName, Cn, adOpenStatic, adLockReadOnly, adCmdTable
        Set Grid.DataSource = Rs
        'Add data type names to column headings:
        For FieldIndex = 0 To .Fields.Count - 1
            Grid.TextMatrix(0, FieldIndex) = _
                    Grid.TextMatrix(0, FieldIndex) _
                  & " (" & TypeName(.Fields(FieldIndex).Value) & ")"
            Grid.ColWidth(FieldIndex) = 2400
            With .Fields(FieldIndex)
                Debug.Print .Name, .DefinedSize, .ActualSize
            End With
        Next
        .Close
    End With
End Sub

Private Sub cmdQuery_Click()
    Dim SQL As String
    
    SQL = "SELECT [DEGREE] FROM [degreez] WHERE " _
        & "UCase$([CONST]) = UCase$('" & Trim$(txtCONST.Text) & "') " _
        & "AND [DECAN] = '" & Trim$(txtDECAN.Text) & "';"
    With Cn.Execute(SQL, , adCmdText)
        If .BOF And .EOF Then
            txtDEGREE.Text = "no hits"
        Else
            txtDEGREE.Text = .Fields("DEGREE").Value
        End If
        .Close
    End With
    txtCONST.SetFocus
End Sub

Private Sub Form_Load()
    Set Cn = New ADODB.Connection
    'degreez.dbf, ninegrid.dbf are in App.Path here:
    Cn.Open JET_CONN1 & App.Path & JET_CONN2
    DumpTable "degreez"
End Sub

Private Sub Form_Resize()
    Dim Top As Single
    
    If WindowState <> vbMinimized Then
        With lblGrid
            Top = .Top + .Height
            Grid.Move 0, Top, ScaleWidth, lblQuery.Top - Top - ScaleY(10, vbPixels, ScaleMode)
        End With
    End If
End Sub

Private Sub Form_Unload(Cancel As Integer)
    Cn.Close
End Sub

Private Sub mnuDumpDegreez_Click()
    DumpTable "degreez"
End Sub

Private Sub mnuDumpNinegrid_Click()
    DumpTable "ninegrid"
End Sub

The only reference required is one for Microsoft ActiveX Data Objects 2.5 Library (or later) unless you count the MSHFlexGrid.

 

[astrogirl77]

@gmmastros Thanks for your reply!

Both your and dilettante ideas make sense of course, I seem to be missing some of the code from dilettante so the solution dilettante provided I cant get to work, I also tried your recommendation but I
got two forms of errors. When I change the code string you suggested
I still get this ; Run Time Error '3011' Jet cant find object

If I rem out this code line ;

     Set MyRecordset = MyDatabase.OpenRecordset("MySQL")   ' Generate recordset.

Then the debug.window and the DEGREE text box in the small app form still pump out the result of 1, where it should be 22.

Current code with your suggested change is shown below ;

Private Sub Command1_Click()

    Dim MyDatabase As Database
    Dim MyRecordset As Recordset, MyField As Field
    Dim MySQL As String, I As Integer
    
'Dim MySQL As Variant, I As Integer
    
    Dim degrees_value_ref 
    Dim degrees_value_ref2 'As String
    
degrees_value_ref = Text1.Text   'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number

degrees_value_ref2 = Text2.Text   'this line used as a test, grabs value from text box, 

        Debug.Print ; "degrees_value_ref2"; degrees_value_ref2
    
    Set MyDatabase = OpenDatabase("C:\degrees", 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

    Set MyRecordset = MyDatabase.OpenRecordset("degreez")
   
'MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = " & degrees_value_ref & " AND [DECAN] = " & degrees_value_ref2 & ";"

MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = '" & degrees_value_ref & "' AND [DECAN] = '" & degrees_value_ref2 & "';"

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

Debug.Print "qDEGREES = " & MyRecordset("DEGREE")
    
Var1 = MyRecordset("CONST")
'here the first part of the actual data from the record i want is stored to a variable
    
    Var2 = MyRecordset("DECAN")
    Var3 = MyRecordset("DEGREE")
    
    Text3.Text = Var3
    
        Debug.Print "var1;"; Var1
        Debug.Print "var2;"; Var2
        Debug.Print "var3;"; Var3

End Sub

 

[dilettante]

VB5 only has the old MSFlexGrid, which can't use an ADO Recordset as a DataSource... so yep you'd have to work out the equivalent DAO logic I suppose.

Perhaps just use your original code only substituting the SQL string building code I had above? Or forget the Grid and related code and just focus on the query since VB5 should be able to use ADO as long as data binding isn't attempted. Even VBScript has no problem with ADO.

 

[astrogirl77]

@Dilettante I do not have VB6 Only VB 5 and am not able to upgrade
at this time, the code you provided above, was it in a module or in the
form declarations section? Can I use this code in VB5 ?

 

[dilettante]

I've edited the previous reply. Yes, chop out the Grid and related code and just use the other code.

 

[astrogirl77]

@dilettante, Im a newbie somewhat to VB programming but Im not lazy and am willing to put in effort to tinker and research, however I do not think I have the experience to easily identify which parts of your code I should chop out, would you feel comfortable if I asked you to edit out the parts you think should go? I will experiment with that and test it!

 

[astrogirl77]

@ dilettante, I tried to insert the code you sent into the GEneral declarations section, wasnt sure where else if should go... except the form load code, I put in in the form .... I got a bunch of errors back ;
................
Compile Errors ;
Ambiguous Name detected;
form_resize
form_unload
mnuDumpDegreez_Click
mnuDumpNineGrid_Click
Variable not defined : lblGrid.Caption
................

 

[astrogirl77]

@ ALL

I am still not understanding why on the same computer, in the same environment, with essentially the same code, I CAN access and query the ninegrid dbf file (see attached above) just fine, but when I try to access the degreez dbf file I never can, its the SAME code basically, and I have tried all kinds of varieties of the other dbf files that also wont work......

So in my code that is working, I am accessing the ninegrid dbf via DAO and not ADO is that right?

I have tried to use versions of the same data as different variations
of variable types, and none of this seems to matter either, I have also
tested all of the suggested changes to the SELECT statement everyone
has provided so far..... the SELECT statement type that seems closest
seems to be one of ;

 MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = '" & degrees_value_ref & "' AND [DECAN] = '" & degrees_value_ref2 & "';"

But I get the 3011 error when I use degreez.dbf, but I do NOT get that error ever when I use the ninegrid.dbf file.....

HELP!!! THIS IS DRIVING ME CRAZY!!!!

I know that what I want to do MUST be possible with the tools and environment I have, cos I CAN do it with the same essential code
and dbf 3 files, b ut WHY CAnt I do it with any other ones? This makes no sense!

 

[gmmastros]

You need to remove the quotes from this line of code:

[tt]Set MyRecordset = MyDatabase.OpenRecordset("MySQL")[/tt]

so that it appears like this:

Set MyRecordset = MyDatabase.OpenRecordset(MySQL)

I have downloaded your dBase III file and have used the following code successfully.

Private Sub Command1_Click()


    Dim MyDatabase As Database
    Dim MyRecordset As Recordset, MyField As Field
    Dim MySQL As String, I As Integer
    Dim Var1
    Dim Var2
    Dim Var3
    
'Dim MySQL As Variant, I As Integer
    
    Dim degrees_value_ref 'var that represents the number in the multiplier cell of the sq grid cell that shows prime or fib in base and or mult, this value becomes a look up value on the 9 by 9 (81) value grid
    Dim degrees_value_ref2 'As String
    
degrees_value_ref = Text1.Text   'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
degrees_value_ref2 = Text2.Text   'this line used as a test, grabs value from text box, value is simulation of sq grid mult cell value number
        Debug.Print ; "degrees_value_ref2"; degrees_value_ref2
    
    Set MyDatabase = OpenDatabase("C:\degrees", 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

    Set MyRecordset = MyDatabase.OpenRecordset("degreez")
   
MySQL = "SELECT degreez.* FROM degreez WHERE [CONST] = '" & degrees_value_ref & "' AND [DECAN] = '" & degrees_value_ref2 & "';"

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

Debug.Print "qDEGREES = " & MyRecordset("DEGREE")
    
    Var1 = MyRecordset("CONST")
'here the first part of the actual data from the record i want is stored to a variable
    
    Var2 = MyRecordset("DECAN")
    Var3 = MyRecordset("DEGREE")
    
    Text3.Text = Var3
    
        Debug.Print "var1;"; Var1
        Debug.Print "var2;"; Var2
        Debug.Print "var3;"; Var3



End Sub

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[astrogirl77]

@ gmmastros - FINALLY! THAT DID IT!!!! THANK YOU SO MUCH, ITS WORKING NOW!!!!

 

[astrogirl77]

@gmmastros, Could you tell me, if I wanted to use a dbf file that was not all string fields, but instead field 1 was a string type, but the two other fields were numeric, float, etc.... how would
I have to modify the code? Ive been tinkering with this also but ran into a few things that for
a newbie like me I had to wrestle with. For example, if the MySQL starts off as a string defined
variable, then how if the values in the other two fields are numeric, would they get stored
in MySQL if its only a string? Wouldnt that error out? Am I supposed to define MySQL as a variant
to store either string or numeric so that I can access string and number fields in the dbase 3 file?

 

[gmmastros]

Modifying your code won't affect the way that the data is stored. You mentioned earlier that you use a third party something-or-other that exports from excel to dBase. To store the data as integers instead of strings, you would need to modify that process. I am not familiar with that process.

I can say... Your "big" file is 270 KB. I do not consider this big. I generally work with sql server databases that are measured in gigabytes. That being said, I'm not a fan of wasting space either. In fact, if this were my project, I would not use dBase files. Instead, I would access the excel files directly, or use csv files instead.

When I mentioned the data type stuff earlier, it was because you need to wrap strings in single quotes when working with strings. The MySQL variable is, of course, a string. You cannot change that.

When your code was like this:

[tt]Set MyRecordset = MyDatabase.OpenRecordset("MySQL")[/tt]

It failed because you had quotes around MySQL, so instead if opening a recordset using the contents of the MySQL variable, you were actually trying to open the value MySQL. With the OpenRecordset function, you can pass a table name in to the parameter of the function and it will open that table name and retrieve all the data from it. Basically, VB thought you were trying to open a dBase file named MySQL which did not exist.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

>MSQuery ... dont work

Works fine here, using your sample files.

You might want to check the msquery help file (just hit F1 when running msquery), which has an entire chapter entitled 'Setting Up Data Sources' which explains how to properly open a new data source, including a section specifically about dBase files.

 

[dilettante]

@ dilettante, I tried to insert the code you sent into the GEneral declarations section, wasnt sure where else if should go... except the form load code, I put in in the form .... I got a bunch of errors back ;

I'm not sure what might be confusing there. Surely you aren't working with the IDE's "Default to Full Module View" option turned off? That certainly won't aid in understanding because it forces you to view code with blinders on.


The [tt]DumpTable[/tt] subroutine is most of the grid-related code, so all of that can be removed.

The standard naming convention for menu controls is to start them with an [tt]mnu[/tt] prefix, so it should be clear enough that [tt]mnuDumpDegreez_Click[/tt] and [tt]mnuDumpNinegrid_Click[/tt] event handlers can go. And those merely call [tt]DumpTable[/tt] anyway.

Then the [tt]Form_Resize[/tt] handler just positions and sizes the grid among the other controls on the Form, so that can go or you can modify it to manage the controls you have on the Form.

So then we're back to what I posted the first time before more code was requested. About all that was missing was the connection string I was using.

Once you've done that you merely need to rename a few things in the small amount of remaining code to reflect the names you used for the other controls such as Command buttons and TextBoxes.