[RESOLVED]retrieving data from the database using SQL query

I have a form that retrieves data from the database and shows the record fields in text boxes...

I have retrieved data using the properties window.... I called the datasource the reuiqred database and have assiged a datafield...this works expect it returns the first record in the table...I want it to return the data for a certain ID no (which is in textbox 'IDtxt' on the form)

So I created the following query with in the command:

SELECT * FROM customer WHERE (customerID = 'frmUpdate.IDtxt.Text')

this does not work however if I change 'frmUpdate.IDtxt.Text ' to 2 it does work and returns the 2 record in the table...


what am I doing wrong in the above query?????

Code:

---

SQL = "SELECT * FROM customer WHERE (customerID = " & frmUpdate.IDtxt.Text & ")"

---

TG

the query doesnt wrk... i tried but nothing comes up on the form!!

so i tried this:

Code:

---

WHERE (customerID = ' " & frmUpdate.IDtxt.Text & " ')

---

and it still does not work.....can u actually call a text box in a query, i have tried calling ID = 2 that worked....

PH
Cheers

Is the ID field numeric or alpha numeric?

Can you post the spot of code where you are defining your sql statement and then calling it?

Quote:

---

Originally posted by pame1a
the query doesnt wrk... i tried but nothing comes up on the form!!

so i tried this:

Code:

---

WHERE (customerID = ' " & frmUpdate.IDtxt.Text & " ')

---

and it still does not work.....can u actually call a text box in a query, i have tried calling ID = 2 that worked....

PH
Cheers

---

No you can' call a text box in a query.... that's why I took it OUT, and appended it to the string that becomes the query.
So, after executing the line I supplied, if yo udid a MsgBox SQL, you should have gotten a message that read
SELECT * FROM customer WHERE (customerID = 2)
Assuming 2 was in the text box.

TG

maybe thats the problem....

What I've done is used a data environment using a command to create a query.....

so the query appears in the datamember section of properties window!!
Is this wrong? is this the only way to retrieve data from the database???


Ive

Quote:

---

Originally posted by pame1a
maybe thats the problem....

What I've done is used a data environment using a command to create a query.....

so the query appears in the datamember section of properties window!!
Is this wrong? is this the only way to retrieve data from the database???


Ive

---

Now it all makes sense. That is an importaint piece of information.
Data environment is (in my opinion) an evil entity for just this reason.
Suggestion - try reading the articles on ADO. They'll take you through the steps of establishign a conenction, retrieving data, and even updating data. Will provide more flexibility than the Data Environment.

TG

im using da ADO con now...

i'm getting a timeout error.... wats going wrong??/

Post the code in question to avoid the guess work :)




Bruce.

In form load i make a connection....

then I have a sub fillfields()

FName.Text = rs.Fields("FirstName")
(trying it with one field first!!!)


Private Sub Fill_Click ()
Dim sql As string
sql = "SELECT * FROM customer WHERE (customerID = '" & IDtxt.Text & "')"
rs.Open sql, cn
fillfields
end sub


problem is IDtxt is filled by a value from another form....I first had the fill_click sub within the form load sub ....however I think the IDtxt was not filled by then so it gave an error.

The page works if i fill the text box instead of calling the value from another form.....

PH

try this (as I had do do it in aprevious app); set the Focaus back to IDtxt just prior to the execution of the SQL statement.

My guess is that the other Forms Control (TextBox?) may have the focus, and as such when the SQL execute IDtxt.Text it raises an error.




Bruce.

what database engine you uses?

Quote:

---

what database engine you uses?

---

Does it really matters or r u just incrementing ur posts :p