Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Database > SQL Language
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
SQL Language SQL Language discussions not specific to a particular RDBMS program or vendor.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the SQL Language section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 22nd, 2015, 02:47 PM
Registered User
Points: 11, Level: 1
Points: 11, Level: 1 Points: 11, Level: 1 Points: 11, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT Statement Issue

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
Reply With Quote
  #2 (permalink)  
Old September 24th, 2015, 05:52 PM
Authorized User
Points: 203, Level: 4
Points: 203, Level: 4 Points: 203, Level: 4 Points: 203, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Location: Mesquite Texas
Posts: 45
Thanks: 11
Thanked 0 Times in 0 Posts
Default

well Astrogirl,

First: test your SQL Select statement Interactively or a database workspace.
Select * from degreez where Const = "XX" and Decan = "YY"

see if that works; ? are you supposed to return multiple rows or just one ?
anyway...

then:
db = open("yourDatabase");
sql = "Select * from degreez where Const = @0 and Decan = @1";
result=db.Query(sql,Var1,Var2);

*** use these as needed; if one row returned; else loop through them..
result.degree
result.const
result.Decan
Reply With Quote
  #3 (permalink)  
Old September 24th, 2015, 07:39 PM
Registered User
Points: 11, Level: 1
Points: 11, Level: 1 Points: 11, Level: 1 Points: 11, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default SELECT Statement Issue

Edgy, thank you very much for your reply! :)

Ok, I will try this as you suggested! My goal is to return a single record.

The data base has three fields, the first one CONST contains strings
the other two fields ; DECAN, DEGREE contain numbers altho I have tried
a db version where all the fields including number fields are stored as string values. Ideally Id like to use my db version where field one is string, and the other two fields are numeric. I want to search fields CONST & DECAN where
the values entered to the form text fields are the search parameters and then
the last field DEGREE is the value found and then returned to the 3rd and last text field displayed on the form. At some point Id like to be able to loop parts of code to find multiple entries stored in a text file, but at this point I
just want to get the SELECT statement working for a single search.

Quote:
Originally Posted by EDGY View Post
well Astrogirl,

First: test your SQL Select statement Interactively or a database workspace.
Select * from degreez where Const = "XX" and Decan = "YY"

see if that works; ? are you supposed to return multiple rows or just one ?
anyway...

then:
db = open("yourDatabase");
sql = "Select * from degreez where Const = @0 and Decan = @1";
result=db.Query(sql,Var1,Var2);

*** use these as needed; if one row returned; else loop through them..
result.degree
result.const
result.Decan
Reply With Quote
  #4 (permalink)  
Old September 24th, 2015, 11:09 PM
Registered User
Points: 11, Level: 1
Points: 11, Level: 1 Points: 11, Level: 1 Points: 11, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2015
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Just Need to Clarify though

My current statement is structured like this syntactically ;

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

You said use the form ; 'Select * from degreez where Const = "XX" and Decan = "YY"

When I do this I get error message of unexpected end of statement, Im still
a bit of a newbie to VB can you explain?
Reply With Quote
  #5 (permalink)  
Old September 25th, 2015, 10:23 AM
Authorized User
Points: 203, Level: 4
Points: 203, Level: 4 Points: 203, Level: 4 Points: 203, Level: 4
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2012
Location: Mesquite Texas
Posts: 45
Thanks: 11
Thanked 0 Times in 0 Posts
Default

well, I m a C# user myself,

However do a goggle search on VB SQL and you should see some wonderful and simple examples...

Your question is not too complex... any decent VB SQL site should
show you exactly what you want...

[URL="http://www.w3schools.com/sql/default.asp"]

http://www.homeandlearn.co.uk/NET/nets12p5.html


good luck..
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Select Statement jmss66 VB How-To 7 August 20th, 2008 04:02 PM
Select In Statement mr_pc1963 SQL Server 2005 2 April 19th, 2008 09:59 AM
select Statement gregalb SQL Server 2000 3 January 15th, 2008 12:00 AM
Select from another select statement to a repeater simsen ASP.NET 2.0 Professional 0 May 2nd, 2007 04:34 PM
Select Into statement ashu_from_india Oracle 8 October 9th, 2005 11:30 PM



All times are GMT -4. The time now is 03:15 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.