INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

SQL SELECT PROB IN VB 5

SQL SELECT PROB IN VB 5

(OP)
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

RE: SQL SELECT PROB IN VB 5

Try:

CODE

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = " & degrees_value_ref _
  & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL 

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.

RE: SQL SELECT PROB IN VB 5

Your sql string is not being built correctly. This may work:

CODE

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

Or, if CONST is a Text value in the database:

CODE

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

>it just keeps returning error messages.
Like?

RE: SQL SELECT PROB IN VB 5

(OP)
Problems still occurring but thanks to both Andrzejek & guitarzan for your
replies! I tried all your suggestions and in each case I got the same
error message ;

"Run-Time Error '3075'
Syntax Error (missing operator) in query expression '[CONST] = 'ARIES' And [DECAN] = ' "

RE: SQL SELECT PROB IN VB 5

OK, but now you need to show your new MySQL = line, since it doesn't seem to match what Andrzejek or I posted.

RE: SQL SELECT PROB IN VB 5

Since CONST is Text and DECAN and DEGREE as Numbers, your code should be:

CODE

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = '" & degrees_value_ref & "'" _
  & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL 

Try this and show us what you get in the Immediate Window from the Debug line.

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.

RE: SQL SELECT PROB IN VB 5

(OP)
Still getting the same error message, but here is what shows in the DEBUG window ;

SELECT degreez.* FROM degreez WHERE CONST = 'ARIES' And DECAN =

The value for DECAN is in the text2 box and should be getting picked up from there...

RE: SQL SELECT PROB IN VB 5

Then try this:

CODE

MySQL = "SELECT degreez.* FROM degreez " _
  & " WHERE CONST = '" & Text1.Text & "'" _
  & " And DECAN = " & Text2.Text  

I would use better names for my text boxes, like txtCONST and txtDECAN

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.

RE: SQL SELECT PROB IN VB 5

(OP)
Thanks Andrzejek, tried this and am getting the exact same error message!

RE: SQL SELECT PROB IN VB 5

(OP)
Hi Andrzejek, I tried this but get the exact same error message....! :(

RE: SQL SELECT PROB IN VB 5

>The value for DECAN is in the text2 box and should be getting picked up from there...

It should be, yes. If it isn't, there should be a simple reason... Add the following

CODE

Debug.Print MySQL 
Debug.Print "Text2.Text = " & Text2.Text 

Does that come up with a blank value? Maybe the textbox control with the value for DECAN is named something besides "Text2"?

RE: SQL SELECT PROB IN VB 5

(OP)
thanks so much! tried that now I get ;

run time error 3464 data type mismatch in criteria expression

I have different versions of this database I can work with, one version
has first column as text file the other two as numeric

another version has all values as strings, it doesnt matter to me the data tyopes
I just want to be able to search the first two fields that match the text entries
and then return the third field value into text3 ....

what do you suggest good sir? :)

RE: SQL SELECT PROB IN VB 5

You tried what? You mean you added my Debug.Print "Text2.Text = " & Text2.Text and got back and error 3464 data type mismatch???

How about posting your code as it is now, and explaining which line gives and error, and posting exactly what that error is?

RE: SQL SELECT PROB IN VB 5

If you have different versions of your DB ("one version has first column as text file the other two as numeric" and "another version has all values as strings") - it is a nightmare to work with.

You ask for Numbers differently that for Text, or Dates. It matters big time.

If you want to deal with the 2 versions of your DB, you would have to add some checks of what type of the field you deal with and act appropriately.

It may not matter to YOU, but it does matter to your VB 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.

RE: SQL SELECT PROB IN VB 5

(OP)
@guitarzan

>You tried what? You mean you added my Debug.Print "Text2.Text = " & Text2.Text and got >back and error 3464 data type mismatch???

Sorry, I should have explained in greater detail! Your advice helped me see that when I ran the extra debug statement the DECAN value wasnt even being picked up at all, it was blank! The data type mismatch return gave me the idea that the problem Im having with this is possibly related elsewhere in the code, Im declaring MySql as a string, but the value may be getting interpreted as a number. Ive been trying to hunt this down since we talked last, an error now occurs at this line ;

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

here is the current version of the 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 '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


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


'MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = '" & Text1.Text & "'" & " And DECAN = " & Text2.Text


MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = " & degrees_value_ref & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL


Debug.Print MySQL



' 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

RE: SQL SELECT PROB IN VB 5

(OP)
@ Andrzejek (Programmer)

>If you have different versions of your DB ("one version has first column as text file >the other two as numeric" and "another version has all values as strings") - it is a >nightmare to work with.

The DB is very small - only 360 records, and I only reference it three times in code.

I have named each version of the DB differently, so I know which DBs have all string fields and which ones have a string field and two number fields.

>You ask for Numbers differently that for Text, or Dates. It matters big time.

I understand but my problem here is that Ive been trying to do the same thing different ways and none of the ways are working! Ive tried calling the DB just as string values and also as string and number, and neither approach works! What I want to do is simple, I cant understand what Im missing! :(

RE: SQL SELECT PROB IN VB 5

It would be nice to know what you actually get in the Immediate Windwow from the Debug statement, what you actually try to execute

CODE

MySQL = "SELECT degreez.* FROM degreez " & " WHERE CONST = " & degrees_value_ref & " And DECAN = " & degrees_value_ref2

Debug.Print MySQL

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

PS I. Yes, I know what you mean with 2 different DB. What Users want we need to work on... smile
PS II. Please use TGML tags to show your code. it is a lot easier to read.

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.

RE: SQL SELECT PROB IN VB 5

(OP)
Hi Andy!

When I run the sql query statement as you have it above, I get this error message ;

Run Time Error '3075' syntax error missing operator in query expression

Here is what appears in the debug window, notice that CONST returns the correct string value
but DECAN returns nothin, a blank!? Also, I have the TGML check box on already, am I forgetting anything else, do I have to always use TGML tags?

SELECT degreez.* FROM degreez WHERE CONST = ARIES And DECAN =

RE: SQL SELECT PROB IN VB 5

Based on the code that you posted, the only way to end up with that result (where there is nothing after the "DECAN = " in the SQL string) is for Text2.Text is empty.

Regarding TGML, you have to use tags, or use the tool buttons to create the tags. For code, surround your code inside [CODE][/CODE] tags. For a full list of TGML tags, click the blue question mark to the left of the Preview button.

RE: SQL SELECT PROB IN VB 5

(OP)
Thanks guitarzan!

I've been tweaking the SELECT statement code line, and I no longer get any error messages of any kind, however
I'm also not getting the value I wanted returned to text3 box from the DEGREE field.
I think my syntax and my punctuation was off, this seems to be fixed now but how do I get the actual value
returned from the DEGREE field? When I run this code it places the #1 into textbox3 but the correct value should
be 22!

Here is my new SELECT statement ;

CODE

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

RE: SQL SELECT PROB IN VB 5

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.

CODE

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

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

CODE

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

RE: SQL SELECT PROB IN VB 5

(OP)
Hi guitarzan

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

CODE

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.

RE: SQL SELECT PROB IN VB 5

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.

RE: SQL SELECT PROB IN VB 5

(OP)
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

RE: SQL SELECT PROB IN VB 5

(OP)
Also, the dug.print shows the same error ;

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

RE: SQL SELECT PROB IN VB 5

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.

RE: SQL SELECT PROB IN VB 5

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.

CODE

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"

RE: SQL SELECT PROB IN VB 5

(OP)
@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?

RE: SQL SELECT PROB IN VB 5

(OP)
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 ;

CODE

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 

RE: SQL SELECT PROB IN VB 5

(OP)
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!

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

RE: SQL SELECT PROB IN VB 5

(OP)
@ Andrzejek ,

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

RE: SQL SELECT PROB IN VB 5

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

RE: SQL SELECT PROB IN VB 5

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

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.

RE: SQL SELECT PROB IN VB 5

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

RE: SQL SELECT PROB IN VB 5

(OP)
@ 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!?

RE: SQL SELECT PROB IN VB 5

(OP)
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?

RE: SQL SELECT PROB IN VB 5

Quote:


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:

CODE

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

CODE

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:

CODE

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

Therefore, try changing this line of code to:

CODE

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

RE: SQL SELECT PROB IN VB 5

(OP)
@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".

RE: SQL SELECT PROB IN VB 5

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:

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 

RE: SQL SELECT PROB IN VB 5

dilettante,
Could you show the declarations and setting of Cn?
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.

RE: SQL SELECT PROB IN VB 5

(OP)
@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 ;

CODE

With Cn.Execute(SQL, , adCmdText) 

Below is the code I adapted from what you provided ;

CODE

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 

RE: SQL SELECT PROB IN VB 5

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

CODE

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.

RE: SQL SELECT PROB IN VB 5

(OP)
@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 ;

CODE

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 ;

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

RE: SQL SELECT PROB IN VB 5

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.

RE: SQL SELECT PROB IN VB 5

(OP)
@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 ?

RE: SQL SELECT PROB IN VB 5

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

RE: SQL SELECT PROB IN VB 5

(OP)
@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!

RE: SQL SELECT PROB IN VB 5

(OP)
@ 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
................

RE: SQL SELECT PROB IN VB 5

(OP)
@ 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 ;

CODE

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!


RE: SQL SELECT PROB IN VB 5

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

Set MyRecordset = MyDatabase.OpenRecordset("MySQL")

so that it appears like this:

CODE

Set MyRecordset = MyDatabase.OpenRecordset(MySQL) 

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

CODE

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

RE: SQL SELECT PROB IN VB 5

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

RE: SQL SELECT PROB IN VB 5

(OP)
@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?

RE: SQL SELECT PROB IN VB 5

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:

Set MyRecordset = MyDatabase.OpenRecordset("MySQL")

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

RE: SQL SELECT PROB IN VB 5

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

RE: SQL SELECT PROB IN VB 5

Quote:

@ 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 DumpTable 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 mnu prefix, so it should be clear enough that mnuDumpDegreez_Click and mnuDumpNinegrid_Click event handlers can go. And those merely call DumpTable anyway.

Then the Form_Resize 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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close