Result of SQL query in a textbox ?

VBKid04
04-14-2004, 02:36 PM
I think I'm going daft here or something but I can't remember how to do this:

I need to run a SQL query, then return the result of that SQL query to a textbox...

What I've got so far is:

Dim sqlQuery as String

sqlQuery = ("SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'")

db.Execute sqlQuery

txtDetails.Text = sqlQuery


But its not putting the result into the textbox!


I have also tried:

Dim sqlQuery As New ADODB.Recordset
Set sqlQuery = db.Execute("SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'")


txtDetails.Text = sqlQuery("TaskDetails")


But all I'm getting is the error:
Either BOF or EOF is true or current record has been deleted.


Can someone please help?

DaddyHarris
04-14-2004, 04:03 PM
Have you tried executing the query in query analyzer or ?? It could be also...

txtDetails.text = sqlquery.fields("TaskDetails")

msmeth
04-14-2004, 04:11 PM
I think I'm going daft here or something but I can't remember how to do this:

I need to run a SQL query, then return the result of that SQL query to a textbox...

What I've got so far is:

Dim sqlQuery as String

sqlQuery = ("SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'")

db.Execute sqlQuery

txtDetails.Text = sqlQuery


But its not putting the result into the textbox!


I have also tried:

Dim sqlQuery As New ADODB.Recordset
Set sqlQuery = db.Execute("SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'")


txtDetails.Text = sqlQuery("TaskDetails")


But all I'm getting is the error:
Either BOF or EOF is true or current record has been deleted.


Can someone please help?

It seems to me you're opening your recordset wrong.


Dim rs As New ADODB.Recordset
Dim sqlQuery as String

sqlQuery = "SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'"

rs.Open sqlQuery, cn, adOpenStatic 'where cn is your active connection to your db

txtDetails.Text = rs!TaskDetails

MKoslof
04-14-2004, 04:29 PM
Well, if this query returns more than one record, your text box will only show the last record. So if you are expecting to see 5 or 6 records, that won't happen. For that you need to use a control that allows multiple values. However, if this only returns one record, you are OK. And since you are using a commandText string you want something like this:




rs.Open sqlQuery, cn, adOpenStatic, adLockOptimistic, adCmdText

txtDetails.text = rs.Fields("TaskDetails").value

VBKid04
04-14-2004, 06:42 PM
This is absolutely baffling me...

My code is now like this:

Dim sqlQuery As New ADODB.Recordset
Set sqlQuery = db.Execute("SELECT TaskDetails From TASK WHERE TASK.CustomerID='" & txtCustomerID & "'")

txtDetails.Text = sqlQuery!TaskDetails


which would appear to be correct, but there seems to be a flaw somewhere.


When I use the above code, I get the error:
Either BOF or EOF is true, or the current record has been deleted.
when I open my form.

I have tried the code both in the txtTaskID_Change and Form_Load.


I have checked the database and what its saying is nonsense, as the first record in the table clearly has a match and should display when the form opens...


So as a test:
On my form I have put a listbox called lstDetails (I dont need this listbox, but I'm just testing).
Instead of passing the result to the textbox, I have then passed it to the listbox (and commented out the line to do with the textbox).

Strangely enough, the form opens, with no error.


But what else I notice, is that when the form opens at the 1st record, the listbox is empty (there should be a value in it due to the query because it has a match in the other table).

If I browse forward 1 record (to the 2nd), a matching value appears in the listbox.

Then if I browse back 1 record (now at the 1st record), the matching value appears in the listbox! (The value that should have appeared on opening the form).

So clearly, when the form is opening, the value cannot be instantiated, because it is seeing the TaskDetails field as empty for the 1st record - but why, when it clearly is not?

I have tried using the code both on the txtTaskID_Change properties and the Form_Load properties, but both have exactly the same results.


Back to the textbox method... I have setup an error handler to ignore the error, as the problem is only whenever the form opens for the first time.
But this is no good as the whole recordset then disappears completely!


Can someone please help me here?
I am on the point of insanity!

MKoslof
04-14-2004, 06:56 PM
You should be using the rs.open method like I showed if you will be doing any sort of recordset modifications. Doing the db.Execute method automatically provides a forward only cursor. You should use the rs.Open method with the cursor and lock type parameters I provided.

VBKid04
04-14-2004, 07:15 PM
MKoslof actually I did try yours before reverting to my own idea, it came up with exactly the same result :(

Any other ideas?

msmeth
04-14-2004, 08:57 PM
MKoslof actually I did try yours before reverting to my own idea, it came up with exactly the same result :(

Any other ideas?

Did you try just a simple Open without the commandText like I showed you?

MKoslof
04-15-2004, 07:29 AM
At this point I would really recommend that you read an ADO tutorial. You are still making a lot of syntax errors, and it appears the advice we give you is being ignored. What you want to do is quite common, and the advice given by everyone should lead you to resolve the issue. I have recommended this to you over and over again. But, go to the knowledge base..in the tutorial section there is an "ADO For Beginners" tutorial written by Optikal. You will be much more productive if you read this. And, there are many books out there to help as well.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum