Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > SQL Language
|
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 software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old September 22nd, 2015, 02:47 PM
Registered User
 
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
 
Old September 24th, 2015, 05:52 PM
Authorized User
 
Join Date: Jun 2012
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
 
Old September 24th, 2015, 07:39 PM
Registered User
 
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
 
Old September 24th, 2015, 11:09 PM
Registered User
 
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?
 
Old September 25th, 2015, 10:23 AM
Authorized User
 
Join Date: Jun 2012
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..





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





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.