Data Report 3

[MuraliKrishnaK]

Hai,

Is it possible to send a parameter to the datareport's query?

The situation is..
1. MS Access Database of addresses
2. Query to retrieve address
3. Parameter criteria for the query(the city part when the user wants to print the address of a particular city)

Now how to solve this situation? How to pass parameters to the reports?

Murali Krishna

 

[iwebslinger]

What type of connection are you using? Are you using data enviroment or are you coding the sql statement to retrieve you data?

spideysense

 

[MuraliKrishnaK]

Hi,

Using data environment. The situation is something like this
. Connection is established to the database
. command should be
select name,street,area,city from address where city='xyz'

Now I want to take the user input for the city and replace the 'xyz' part at runtime, so that adress list for that city will be on the report.

I think now you can help me.

Murali Krishna

 

[rat7980]

This is a problem I am having also, except I am using Oracle 8i rather than Access. I need to be able to use a parameter that the user selects for my queries used to generate the reports.

Thanking you for your help in advance.

 

[ariel35]

In the command properties - SQL statement, u should type:
"select name,street,area,city from address where city=?"
and then click the Parameters tab, u will see "param1" and u can change the name, data type.

 

[rat7980]

Thankyou for you post ariel35, I have tried what you suggested but I am getting the following error;

"Provider cannot derive parameter information and SetParameterInfo has not been called"

Hopefully you can help me work out what is wrong.
Again, thanking you in advance.

 

[iwebslinger]

Unfortunately - I don't use the data enviroment when I'm doing data report. I usually get the input and place into a sql statement and then open up the report. If you want to do this I can try to help you with that. Hope it works out for you.

spidey

 

[stnkyminky]

deFile is what I'm calling my data environment. I used the ? in my SQL statement and gave the name ID_JOB. Below you will see how to execute the parameter.


With deFile.Commands("cmdOne_Grouping&quot
.Parameters("ID_JOB&quot.Value = modGlobal.strIDJOB
.Execute
End With

Then show the datareport that is bound to the command.

 

[rat7980]

Thanks for the tips, since I made my posting I also tried to use ado to create the reports, but I couldn't figure out how to place fields that I wanted to group other fields by into the group header. Hope someone could help.
Again thanking those who respond in advance.

 

[soumyakutsya]

I hope i could help u in this.
Firstly in the data environment, in the sql builder, where You select the column names set the criteria of the column to which you want to pass the parameter as ?.
SO here you can see the parameters as param1.
Then in the group by option group the table on your choice.
In the datareport first select the data source and data member on the properties, and then right click on the data report form and tell "Retrieve structure" where the group header is displayed and now you can just drag and drop the group header.

 

[RACaluste]

if you are going to use ADO, you need to make a shaped-recordset if you are going to put data in a group header

as an example, i have a program that does the following (queries a specific data from a specific date range). as you can see, i am doing specific queries from two different tables.

Dim sqlstr As String

'The following recordset goes to the group header
sqlstr = "SHAPE {Select * from tblHR WHERE ID = ?} as rshr"

'The following recordset goes to the details section
sqlstr = sqlstr & &quot; APPEND ({Select * from tblTR where Date >= ? AND Date <= ? ORDER BY Date} as rstr&quot;
sqlstr = sqlstr & &quot; RELATE ID to ID)&quot;

conn.Open &quot;Provider=MSDATASHAPE;Data Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=myCatalog;Data Source=MyServer&quot;

Set comm.ActiveConnection = conn

comm.CommandType = adCmdText
comm.CommandText = sqlstr

comm.Parameters.Refresh

'these are my variables that i am going to pass to the data report
comm.Parameters(0) = mvarid
comm.Parameters(1) = mvardatefrom
comm.Parameters(2) = mvardateto

rs.CursorLocation = adUseClient
rs.Open comm, , adOpenStatic, adLockReadOnly, adCmdText

set datareport1.datasource = rs
datareport1.show, 1

Hope this helps. It is a bit more complicated without the use of a dataenvironment, but in my opinion, using it gives the developer more flexibility.

arcanist

 

[geeker21]

arcanist

I tried to use your code format, but somehow i couldn't get to pass the parameter values on the child. the code always gives an error message &quot;Item cannot be found in the collection corresponding to the requested name or ordinal.&quot; Would you be able to tell me what I am missing here? this one also relates to another thread (

http://www.tek-tips.com/gviewthread.cfm/lev2/4/lev3/32/pid/222/qid/5644)

where I posted my first question as it gives me another type of error. Hope you can assist me on this.

 

[geeker21]

anyone out there have an answer to my question???? I'm really stuck and tried everything I know.... I need some assistance please..... :-(

 

[phinoppix]

geeker21

&quot;Item cannot be found in the collection corresponding to the requested name or ordinal.&quot;

You misspelled a field on your SQL statement.

 

[rajeshkhatri]

Hi!
Please try this:
In Data Report Command's properties
SQL statement :
&quot;select desired fields from table_name where parameter = ?&quot;
and then click the Parameters tab, you will see &quot;param1&quot;
Please Change the datatype accordingly.

In VB Code
Load DataEnvironment1
If DataEnvironment1.rsCommand1.State = adStateOpen Then
DataEnvironment1.rsCommand1.Close
End If
DataEnvironment1.Command1 (parameter)
If DataReport1.Visible = False Then DataReport1.Show
Please test and reply.
Bye

 

[geeker21]

hi rajeshkatri

I tried it too, I can't seem to work it out....


Any more suggestions?

 

[geeker21]

hi rajeshkatri and to whom who may assist

I have posted my code below for a better understanding. I have tried and change my query several times and below is the most successful one at the moment. but there is one problem, I have to populate the fields on the different section of the Data Report, W/c gives me another error which saids &quot;Report Section does not match data source&quot;.

Dim q As Integer
Dim intCtrl As Integer
Dim x As Integer
Dim z As Integer


cn.Open &quot;Provider=MSDATASHAPE; Data Provider=Microsoft.JET.OLEDB.4.0; Data Source=d:\BILLADJ\BillingDB.mdb;&quot;

t = InputBox(&quot;test&quot
With cmd
.ActiveConnection = cn
.CommandType = adCmdText
.CommandText = &quot;SELECT StaffInfo.ID, StaffInfo.name, StaffInfo.team, Details.area, Details.billtrak, Details.disputeref, Details.enduser, Details.custref, Details.acct, Details.servnum, Details.product, Details.billsys, Details.adjdate, Details.auth, Details.moa, Details.typadj, Details.attributed, Details.reason, Details.comment, TBL_Details.tref, TBL_Details.amt_gst_inc, TBL_Details.amt_gst_excl FROM StaffInfo INNER JOIN (TBL_Details INNER JOIN Details ON TBL_Details.billtrak = Details.billtrak) ON StaffInfo.ID = Details.ID WHERE (((Details.billtrak)=[t]));&quot;
.Parameters(0).Value = t
.Execute
End With

With rs
.ActiveConnection = cn
.CursorLocation = adUseClient
.Open cmd
End With

'(Below is just part of the code section to populate the report)

With DataReport2
.Hide
Set .DataSource = rs
.DataMember = &quot;&quot;
With .Sections(&quot;Section4&quot.Controls
For intCtrl = 1 To .Count
If TypeOf .Item(intCtrl) Is RptTextBox Then
.Item(intCtrl).DataMember = &quot;&quot;
.Item(intCtrl).DataField = rs(z).Name
z = z + 1
End If
Next intCtrl
End With
.Refresh
.Show
End With


Anyone for suggestions? I really need to finish this project and I'm just stuck in here..... help urgent...

 

[boatBuny]

Reference another post regarding a simular instance:
thread222-417529 have tried everything to pass param's with the grouping feature on reports...to no avail. All works wonderfully without grouping. Has anyone figured this problem out yet?

Thanks

bB

 

[geeker21]

hheelp guys, anybody? please? :-(

 

[MangroBongacello]

why just not use variable in sql string

dim MyVar
&quot;select ... where Field = &quot; & MyVar

Mangro