result doesnt match search string - Xtreme Visual Basic Talk

04-04-2012, 07:14 AM

starmanMike starmanMike is offline

Centurion

Join Date: Oct 2005

Location: near Norwich, UK

Posts: 174

result doesnt match search string

Hi,
I'm just doing a little app for myself, just on my own PC. I have made a DB (originally using the DAO library - it's a pretty small DB, and doesn't need fancy bells and whistles). Once the DB was created, I deleted the code that created it; after all the DB is now there!
OK, the problem.
I have a simple data control hooked up to the DB (told you the app was simple!) and there is only one table "charts" with two fields "starname" and "chart". Guess what... it's a database of star charts. Both fields are strings. First one is obvious, second field is a serial number identifying the chart (say something like 'abc1234'). The type is access, dynaset.
As a test I have made one record. starname = ss cyg and chart = 1978
When I leave the star name text box (after having entered the star name) and the next box gets the focus, this code fires inside its GotFocus event:

Code:

Dim thestar As String
thestar = txtStarname.Text
Data1.RecordSource = "select * FROM charts WHERE [Starname]= 'thestar' "

Also on the form is a bound text box hooked up to the 'chart' field of the DB.
I enter ss cyg in the starname text box and yay!

1978 appears in the 'chart' text box. Unfortunately if I enter anything else in the starname text box, 1978 still appears in the 'chart' text box! Why? Any ideas?

04-04-2012, 07:27 AM

DrPunk

Senior Contributor

* Expert *

Join Date: Apr 2003

Location: Never where I want to be

Posts: 1,273

VB doesn't look inside strings for variable names and substitute those names with their values.

What your query string should really look like is...

Code:

"Select * FROM charts WHERE [Starname] = '" & thestar & "'"

Note how the variable exists outside of the strings and is appended to the strings using &.

Now the question is how it's returning anything given that you suggest there is no entry in your database with a starname of "thestar" (there must be one because you say your query is returning it). Chances are you never put "ss cyg" into the database for the very same reasons as shown above. i.e.

Code:

Dim theName as String
theName = "ss cvg"

"INSERT INTO charts (StarName) VALUES ('theName')" ' Will insert "theName" into StarName, not the value of theName

"INSERT INTO charts(StarName) VALUES ('" & theName & "')" ' This will insert "ss cvg", i.e. the value of theName, into StarName

__________________
There are no computers in heaven!