Discussion Locked

VB-6 Rpt Designer in Data Environment

0 helpful
4 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

I am running VB6 with SP5. How can you include a variable in the SELECT statement (SQL) which appears on the CommandText property line of the DE Command? The objective is to select records for printing based on criteria the user inputs on a form. Normally, you could code it in a procedure using the ASCII CHR(34) for string variables or CHR(35) for dates within the SELECT statement command string.

Also, in the properties of the DE Command, what is the function of the Parameters tab? The documentation on this is not very useful.

4 replies

by steved

You write your SQL with question marks in place of the parameters. The DE automatically creates parameter objects corresponding to your question marks. Then you assign values when you execute the query at run time. From the Help file:

Creating a Parameterized Query

Create a new project and add a DE designer.

On the DE designer, create a Connection object and configure it to connect to the SQL Server Pubs database.

Right-click the Connection object and click Add Command.

Rename the Command object CommandQuery.

Right-click the Command object and click Properties.

On the General tab, click SQL Statement and type the following:
SELECT * FROM Authors Where Au_ID = ?

Click the Parameters tab. (A message will appear informing you that not enough information has been supplied. Click OK.)
A new Input parameter will have been created for you. You can create further parameters, if needed, or set properties of the parameter.

Click OK to close the dialog box.

On the default form, draw two TextBox controls and a CommandButton control.

Add the code below to the Form object's code window:

Option Explicit
Private Sub Command1_Click()
' You must close the recordset before changing the parameter.
If DataEnvironment1.rsCommandQuery.State = adStateOpen Then
DataEnvironment1.rsCommandQuery.Close
End If
' Reopen the recordset with the input parameter supplied by
' the TextBox control.
DataEnvironment1.CommandQuery Text1.Text
With Text2
.DataField = "AU_LName"
.DataMember = "CommandQuery"
Set .DataSource = DataEnvironment1
End With
End Sub

Private Sub Form_Load()
' Supply a default value.
Text1.Text = "172-32-1172"
' Change the CommandButton caption.
Command1.Caption = "Run Query"
End Sub

Run the project and click the button. Change the text in Text1 to run another query.

This discussion is closed to new replies.