SQL Statement Not Producing Results

[Cheyenne]

Hi!!

I'm really stuck on this and have been for awhile. I'm hoping to get some help!

I have tired all of the below and got the following error.

"SELECT * FROM Customers WHERE ProductID = " & (Text6.Text) & ";"

Error: Failed getting rowsets from current data source.

"SELECT * FROM Customers WHERE ProductID = " & CLONG(Text6.Text) & ";"
Error: Failed getting rowsets from current data source.


"SELECT * FROM Customers Where ProductID = " & Val(Text6.Text)
Error: Failed getting rowsets from current data source.

What is wrong?? How do I fix it?

I produce a datareport but the sql statement isn't working for displaying only 1 customer. I would like to display the information for one customer. The field that the one customer is based upon is a number which is entered into a text6.text box on a previous form.

Customers is a table name. ProductID is a field in the table. Do I have to say the what form it comes from???

Thanks

[Psyrus]

Have you tried"

"SELECT * FROM Customers Where ProductID = " & CInt(Text6.Text)


Chris

[beasty1711]

are you putting single quotes around your form item??
ie
"select * from MyTable where values in(" ' & MyFormItem&'")"

[Cheyenne]

I have tried the following with the errors listed below....


"select * from Customers where values in(" ' & Text6.Text&'")"
Error: Failed getting rowsets from current data source.

"SELECT * FROM Customers Where ProductID = " & CInt(Text6.Text)
Error: Failed getting rowsets from current data source.


What in the world I'm I doing wrong??? Why is this not working??

[Psyrus]

Have you tried other queries? Have you checked the data connection? The previous suggestions should have worked.


Chris

[reyghie]

You mentioned about DataReport, are you using this code in DataEnvironment's SQL Query Builder to create a report? We were focusing on the SQL statement itself, and we've shared with you all the possible solutions to your problem, but we forgot to ask you the exact purpose/location of this code in your program. Is this code located in a subprocedure(s) or is it inside DataEnvironment module?

[mdake]

In response to an earlier PM from another thread with the same question, I asked Cheyene to post the rest of his code so we could see what he is trying to do.

[Cheyenne]

I was using the SQL statement to produce the DataReport. So,
I was entering this information in the SQL Statemnet box and hitting
apply and ok buttons.

Change the Name property of Command1 to customers.
I do a right click on customers (not to be confused with the database
table).

I do a Right-click Owners and select Properties
Then Click the 'SQL Statement' radio button and entered the previous
SQL Statments. However with those SQL statements I dont see a list of
fields that the query should return.

The datareport produces when I used the SQL statement of
Select * From Customers. So I'm thinking that the database
connect is correct.

[mdake]

What did you use for the DataSource and DataMember of the DataReport, and what are the settings for them. You should be able to right click on the DataReport and choose Retrieve Structure, that will tell you if you are at least connecting to the DB.

[astauffer]

another form of quotation marks to try...

"SELECT * FROM Customers WHERE ProductID = ' " & text.text & " ' "

Assume you are searching for the number 10, if you display the final string in a message box I think it should be like this (although not completely sure)

Select * FROM Customers WHERE ProductID = '10'

Andy

[reyghie]

When you click 'SQL statement' radio button, it gives you two options;
1. to type your query directly inside the query box OR
2. click on the 'SQL Builder' command button, which will enable you to create SQL statement simply by clicking on tables and their fields.

Now, since you have a very simple query, do the first one and type,

SELECT * FROM Customers Where ProductID = ?

(The ? is used as a parameter)

Click on the Parameters Tab and set the appropriate properties of the ? parameter:
Name = ParamWhatever,
Direction must be INPUT,
Datatype = selectTheRightOne,
Host Datatype = SelectTheRightOne.

Click apply,OK.

Next, create a command button and type the ff. code behind it.

'change the object names where applicable, but you must add the 'rs' prefix as shown.
'I assumed that you have already created a DataReport.
'Val(Text6.text) will take the place of the ? parameter
------------
Private Sub cmdButtonName_Click()
On Error Resume Next
If DataEnvironment1.rsCommand1.State = adStateOpen Then
DataEnvironment1.rsCommand1.Close
End If
DataEnvironment1.Command1 Val(Text6.text)
DataReport1.Refresh
DataReport1.Show (vbModal)
Unload DataReport1
End Sub
--------------
Also, refer to mdake's last comment.

Well, I guess this is it...

[Cheyenne]

I used all of the above code expect for the line "DataEnviornment1.rs.Command1
Val(text6.text)". I had to change that line to say "DataEnviornment1.rs.Command1
Val(Form1.text6.text)".

I'm not the results that I expected to get from the database. For example, say the customer has 2 orders already in the database and is currently entering in another order and then hits the datareport button. The datareport is only showing the
customers previous 2 orders and does not display the last one just entered.

If I enter a new customer, and hit the datareport button I get a blank DataReport.

I know that the last order goes in to the database by looking at the Access 2000
file just prior to hitting the button to produce the report.

What is wrong? What needs to be changed? Ideas?

Thanks a million!!!!!!!!!!!!!!!!!!!!!!1

[reyghie]

just making sure this was not a typo error...
'your post
I used all of the above code expect for the line "DataEnviornment1.rs.Command1
Val(text6.text)". I had to change that line to say "DataEnviornment1.rs.Command1
Val(Form1.text6.text)".

Note:

You don't put a (.) period after rs, it should be one word:
---------
DataEnvironment1.rsCommand1 Val(Form1.text6.text)
---------
Of course, I hope you're changing the object names unless you named them as the above statement.

[turtle]

I have worked with the data environment and the reports. So I have a little experience. I found that the following thread was a great help with my problem. If You look over the thread and verify that you have a connection but, are still having problems just make another post or e-mail me and I will try to help.

http://www.vbforums.com/showthread.p...ta+environment