Data Environment Designer 
Author Message
 Data Environment Designer

I'm using the Data Environment Designer to build a form and for one of the
command's, am using a SQL query. For simplicity sake I'll make up an
example: select * from tbl1 where fld1 = 2", the problem is that I want to
make "2" a variable that is passed from an input box, etc... from the user.
Is there a way to pass form level variables to a SQL query? I'm new to VB so
I may be overlooking something very simple. Thanks for your help in advance.
The ideal SQL type query I want would be :

select * from tbl1 where fld1 = intVariable

etc...

Thanks again.



Sun, 26 Sep 2004 12:55:25 GMT  
 Data Environment Designer
Yes this is possible! Create your command (MyCommand) object within the
DataEnvironment and paste in your SQL text like so:


Click in the "Parameters" tab and then specify the data type (2 boxes) that
is being passed in. Thats it!

Now to call your command from code, do this:

DataEnvironment1.Connection1.MyCommand("test")
debug.print DataEnvironment1.Connection1.rsMyCommand("FieldName")
DataEnvironment1.Connection1.rsMyCommand.close

The first line opens a recordset for MyCommand using the variable you
provided ("test" in this case). Then you can read in values from the
recordset, use MoveNext, etc. Don't forget to call the close method when you
are done or you will get an error next time the code is called (it will say
it was already open).

Actually, this doesn't get you all the way. I am not sure how you can bind
controls to a parameterized command at run time. What I have done is bind a
form to a command with no parameters (such as a table) and then use the
FindFirst method to make the form navigate to the correct record. Like so:

Public Sub GoToClient(ClientID As Integer)

   deData.rstbl_client.MoveFirst
   deData.rstbl_client.Find "[client_id] = " & ClientID
   Me.Caption = "Client Information - " & txtFName.Text & " " &
txtLName.Text

End Sub

Hope this helps!

Bill Geake


Quote:
> I'm using the Data Environment Designer to build a form and for one of the
> command's, am using a SQL query. For simplicity sake I'll make up an
> example: select * from tbl1 where fld1 = 2", the problem is that I want to
> make "2" a variable that is passed from an input box, etc... from the
user.
> Is there a way to pass form level variables to a SQL query? I'm new to VB
so
> I may be overlooking something very simple. Thanks for your help in
advance.
> The ideal SQL type query I want would be :

> select * from tbl1 where fld1 = intVariable

> etc...

> Thanks again.



Sun, 26 Sep 2004 22:24:40 GMT  
 Data Environment Designer

Quote:
> I'm using the Data Environment Designer to build a form and for one of the
> command's, am using a SQL query. For simplicity sake I'll make up an
> example: select * from tbl1 where fld1 = 2", the problem is that I want to
> make "2" a variable that is passed from an input box, etc... from the user.
> Is there a way to pass form level variables to a SQL query? I'm new to VB so
> I may be overlooking something very simple. Thanks for your help in advance.
> The ideal SQL type query I want would be :

Try:

"select * from tbl1 where fld1='" & txtFld1.text & "'"

--
Hp Lorenzana MCP
Philippines
2002



Wed, 29 Sep 2004 00:19:27 GMT  
 
 [ 3 post ] 

 Relevant Pages 

1. data report / data environment designers

2. data report / data environment designers

3. Data Environment Designer

4. Data Environment Designer

5. Error when using Data Environment Designer

6. Any problems with the Data Environment Designer?

7. Data environment designer

8. Data Environment Designer - User Controls

9. Data Environment Designer

10. Help...Data Environment Designer

11. Connecting to Oracle with Data Environment Designer/ADO

12. data environment designer

 

 
Powered by phpBB® Forum Software