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

 

[guitarzan]

In your last sample code, you are assigning to Text3.text the value of var2, and var2 is coming from the value of the "DECAN" field.

var2 = MyRecordset("DECAN")
Text3.Text = var2

If you wanted the value of the DEGREE field, wouldn't it be:

var2 = MyRecordset("DEGREE")
Text3.Text = var2

 

[astrogirl77]

Hi guitarzan

I actually caught and changed this before I read your reply;

 Var3 = MyRecordset("DEGREE")
Text3.Text = Var3

but I get the same exact return values. I am wondering, is it possible the dbf file I am using has something
wrong with it? It just has three string fields currently, there is no dedicated index field that I am aware of?

Am attaching the dbf file in case this helps at all.

 

[guitarzan]

I don't know, tell us what the value of DECAN is? If it's 1, your result will be 1. Show the Debug.Print of the sql statement.

 

[astrogirl77]

Hi guitarzan, the attached database sho0ws all field values, but for my sample query the values are

CONST DECAN DEGREES
ARIES 22 22

So in this case DECANS and DEGREES have the same exact value, so DEGREE should be returned
as 22, not 1

 

[astrogirl77]

Also, the dug.print shows the same error ;

CONST = ARIES, DECAN = 22, DEGREE = 1 ( but should be 22 )

 

[Andrzejek]

astrogirl77, do you have any tool to create, run, and see the outcome of your SQLs?

If you’ve ever worked with Access, you’ve seen the ‘visual way’ of creating the SQL. Pretty much - select table(s), drag them into the window. You can do the same with VB 6 – Data Environment. I use it for many years now and it is my favorite tool of creating Select, Update, Insert, and Delete SQLs. It is not perfect (no Unions, and it is pre-1999 syntax for joining the tables), but it will do.

You can Google “vb6 data environment tutorial”, one of the link is this one.

Just mu opinion ...


Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[guitarzan]

First, all your fields are text fields so I would think you would be required to surround them in single quotes.

Second, keep it simple. Something like this. Don't complicate things. Get the code working at it's simplest level, then you can move on to retreiving parameters from a text box.

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("degreesn")

MySQL = "SELECT degreez.* FROM degreez WHERE CONST = 'Aries' AND DECAN = '22'"

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

debug.print "DEGREES = " & MyRecordset("DEGREE")

Get that working so the result is "DEGREES = 22"

 

[astrogirl77]

@guitarzan, tried this and a few other things, but am getting an error of 3011 saying
MS Jet could not find object "MySQL", this is really driving me crazy cos I have another similiar small vb program thats almost identical to this one and it works fine, but Im in a different vb project, same computer, very similiar code and it wont work! How is that possible?

 

[astrogirl77]

Also I tried using with and without single quotes and no go, here is the code from a similiar small program that is working, and will attach the data base here ;

 Private Sub Command1_Click()


    Dim MyDatabase As Database
    Dim MyRecordset As Recordset, MyField As Field
    Dim MySQL As String, I As Integer
    Dim nine_base_sqgrid_mult_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
    
    
nine_base_sqgrid_mult_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
    
    Set MyDatabase = OpenDatabase("C:\SOFTWARE TOOLS", 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("ninegrid")
'here, the actual name of the dbase 3 file is indicate, 'test2', but NOT its file extention
    
    MySQL = "SELECT ninegrid.* FROM ninegrid WHERE [LOOKUPCODE] = " & nine_base_sqgrid_mult_value_ref
'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("VERT")
'here the first part of the actual data from the record i want is stored to a variable
    
    var2 = MyRecordset("HORIZ")
'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]

Current code that is NOT working and is giving the 3011 error, note how very similiar it is
to the above code that IS working...... very frustrating!

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 '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

 

[astrogirl77]

@ Andrzejek ,

Andrzejek - thanks for the suggestion, unfortunately I do not have an SQL query builder but hope to get one!

 

[strongm]

> I do not have an SQL query builder

Actually, if you've got MS Office installed, then you probably do. It is a little known fact that MS Query, normally used for building queries for use in Excel (and other Office applications) can actually be run standalone. Look for msquery32.exe in your Office folder, and run it ...

 

[Andrzejek]

I just checked my computer with Office 2010 on Win7 and cannot find msquery32.exe :-(

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[strongm]

Have you tried "Add or Remove Programs" to change the installed features for Office 2010, and adding Microsoft Query (under the Office Tools section)?

 

[astrogirl77]

@ strongm - Thank you for telling me that! I just checked and I do have this, so this means I can use this to experiment more easily with building SQL statements right!?

 

[astrogirl77]

To all - Ive been searching the net for any info on error 3011 with VB 5 and similiar and none
of the very small number of articles online cover whats happening to me. My path isnt
wrong, my filename isnt spelled incorrectly. Jet must be installed properly because I can
open the other dbf file ninegrid as shown above. I am wondering if some how the dbf files I have been using in my new code might be corrupted? I noticed that the file size of the dbf I am using that doesnt work is much larger in bytes than the dbf file that does work and yet the working dbf has more records in it! I was using something called "Exportizer Pro" to export data from Excel to dbf file format since Excel no longer lets you export data to database file format..... could someone see if they can actually access the dbf file that I posted above?
Not the ninegrid one, I know that one works, but degreez.dbf?

 

[gmmastros]

I noticed that the file size of the dbf I am using that doesnt work is much larger in bytes than the dbf file that does work and yet the working dbf has more records in it!

The smaller (in file size) database has 3 Integer columns. Each Integer takes 4 bytes, so each row takes 12 bytes. Multiply this by the number of rows, and you get something close to the file size. There's header data and such, so this is not an exact calculation.

The larger file has string data. With dBase files, when you have a string column, there is always a length associated with it. In this case, the lengths are very long. I don't know how long they are, but for example purposes, let's say 50 characters. With the 3 columns, this would be approximately 150 bytes per row multiplied by the number of rows, and some header data. Storing numbers as strings almost always takes more space than storing them as numbers.

I think your problem is here:

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

Since you have strings in this database, you need to treat them as strings.

Let's look as some data...

The first row has: Aries, 1, 1

When you substitute the data in the query, you end up with...

SELECT degreez.* 
FROM   degreez 
WHERE  [CONST] = Aries 
       AND [DECAN] = 1;

When the query engine interprets your command, it appears as though Aries is another column in the database instead of data. To fix this, you should treat your parameters as though they are the same data type as the column definition (in this case strings). Ideally, your query would look like this:

SELECT degreez.* 
FROM   degreez 
WHERE  [CONST] = 'Aries' 
       AND [DECAN] = '1';

Therefore, try changing this line of code to:

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

-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]

@strongm

Found MSQuery where you said to look for it, then tried using it on the dbf files that work and dont work in code, and it would let me access any of them, kept getting error messages like ;

"this data source contains no visible tables"

"Couldnt read this file"

But it did this not just for the dbfs Im having trouble with but also
with the dbf file that works ok when I try to access it via VB.

Also, I can open any of the dbf files just fine in both Excel as well
as "Exportizer Pro".

 

[dilettante]

The ninegrid.dbf has 3 columns, all defined as NUMERIC (BCD) of size 8 digits which maps to ADO's adDouble and VB's Double when using the Jet 4.0 dBase III IISAM.

These are stored in the file in text form and each one takes 9 ASCII characters (perhaps 1 extra for a sign?), with another 6 characters of overhead at the front of each record. A hex dump makes that pretty clear.

But I'm not sure why these details matter here.


I had no trouble dumping either table to an MSHFlexGrid in VB6 using ADO and Jet 4.0, and no problem querying [degreez]... once I remembered that this IISAM doesn't do case-insensitive string compares. So I added a workaround to handle queries with typed-in values that might not match the stored case of the values. Some fields contain only digits even though they're string values so case-sensitivity is irrelevant there.

Hard to imagine needing a Query Builder for something this trivial.

I presume that the VB5 program is going through DAO down into RDO or an old version of Jet (3.5?) and then reaching again down into a hoary old ODBC Desktop Driver for dBase III. Even so the rules should be largely the same aside from the ancient SQL syntax available that way. The most obvious impact of that is wildcard pattern differences but we aren't using those here anyway. However perhaps string-compare case-sensitivity is still an issue?

Here's my query code:

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

 

[Andrzejek]

dilettante,
Could you show the declarations and setting of [tt]Cn[/tt]?
And any references needed for your code.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.