how can i save a sql query from code into my database.

[aggeliki23]

hello all.
I have created my database with Visual Data Manager -> Microsoft Access -> Version 7.0 MDB
I am using VB6.
i am using a sql query in code of my application with where clauses. My query is like this:

"select * from orders where OrderID " & _
"like '" & FuncTxt.Text & "%'"

The functxt is a textbox in a form that user typing a value and my program searching the table orders to find the proper record if exists, to display it in another form. So, as you can understand, the functxt's value changes every time the user typing something else and depens on what record the users wants to display. I have to create a data report using this query. I want it to display the results of query. I wonder if i can save the sql query from code into my database, to add it as a command in data enviroment in order to connect it with data report to display the results of query. Any help will be appreciate.

Thank you
much.

 

[SBerthold]

Dim rsClone AS ADODB.Recordset

Set rsClone = MyDataEnvironment.rsSomething.Clone

rsClone.Filter = txtBox.Text

The DataReport should use rsClone as the DataSource.


Or, create a new recordset and use the DataEnvironment's Connection object as the active connection.

As far as I am aware, you cannot add a command to the DE at runtime. So you would save the sql statement (or filter arguments) to somewhere.

 

[aggeliki23]

thank you for replying me.
My problem is that the recordset you call rsSomething is created in form code, not in dataEnviroment so i dont think i can access to this with this statement:
Set rsClone = MyDataEnvironment.rsSomething.Clone
So what can i do for this?
What do you mean to save the sql statement (or filter arguments) to somewhere?

 

[SBerthold]

>My problem is that the recordset you call rsSomething is created in form code

Set rsClone = rsMyFormCodeRecordset.Clone

>What do you mean to save the sql statement (or filter arguments) to somewhere?

I understood that the user is entering the criteria, or more, which will be used for a recordset's criteria (WHERE clause or Filter method), and it should be available again at a later date.
So you bulid the sql string dynamically.

 

[aggeliki23]

Thank you again. I understood you.
I have another question to do.
How can i define more than one filters?
For example if my sql query is like this:

"select * from orders where " & _
"OrderID like '" & FuncTxt(0).Text & "%'" & _
" and " & _
"ShipName like '" & functxt(1).text & "%'"

In this case functxt is a control array of 2 textboxes.
The functxt(0).text for OrderID and
functxt(1).text for ShipName. The user types values too and we search for the properly record to display too.
Well, i have to use the filter like this:
rsClone.Filter = functxt(0).text
So, what is going to happen if i after say:
rsClone.Filter = functxt(1).text
Maybe the rsClone.filter can't keep both the values of textboxes. Can i have to define 2 filters and how can i do this?

 

[SBerthold]

Use two Clones, or add the Filter argumets together with AND/OR, depending what you want to do.

 

[aggeliki23]

This is a good idea.
In the form i display the results of sql query i have a command button named PrintPreviewCmd to connect the data report with the query i have tried this:

Private Sub PrintPreviewCmd_Click()
Dim rsClone As ADODB.Recordset
Dim cmd As New ADODB.Command
'rsord is the recordset i use in previous and current form
'and open the sql query i told you before
Set rsClone = rsOrd.Clone
'search_InvoiceRep my datareport
Set search_InvoiceRep.DataSource = rsClone

Set cmd = New ADODB.Command
'conn is my connection with my db
Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = " SHAPE {" & sql & "} AS Command1 COMPUTE Command1 BY 'OrderID'"

cmd.Execute
end sub

in cmd.Execute
i am getting a runtime error -214721900 (80040e14) INVALID SQL DECLARATION REMAIN 'DELETE','INSERT','PROCEDURE','SELECT' OR 'UPDATE'

I don't know what's going on, and what it causes this error. My sql is:
sql="select * from orders where " & _
"OrderID like '" & FuncTxt(0).Text & "%'" & _
" and " & _
"ShipName like '" & functxt(1).text & "%'"

It is strange but in the first form where the user types values in textboxes and i search for the properly record, opening the sql query with rsOrder recordset, runs all right without problem. So, i can't find the reason giving me error. May you help me pls?

Thank you
in advance.