Solved

VB6 Data Report Designer

Posted on 2000-04-17
8
317 Views
Last Modified: 2008-02-26
OK... the question that keeps coming up.  HOW do I get the Data Report Designer to work with SQL statements?

I've read it all... In the DataEnvironment:

In Dataenvironment write a command

1)this is in General tab of dataenvironment

SELECT * from customer where custcode = ?

2) In the Paramentrs tab write dataype of ur table field(here datatype of
Custcode) And Host Data Type.

Well, I get that far and I can't even ACCESS the Parameters section under the Parameters tab.

How about this:

No dataenviroment is needed.

....
Set DataReport.DataSource = rs
Datareport.show

Place Unbound TextBoxes on your Datareport and type in the name of the field in the DataField Property.  That is it.

That hasn't worked either.  I either get a "Invalid DataSource" or "Type Mismatch (13)" error.  

So what is it that REALLY needs to be done?  What I need is to do is an SQL statement, which means going to a RecordSet.  Then once I have my RecordSet, I want to be able to use this as the source for my report.  Please tell me how and please be detailed.

Thanks.

KDSayers
0
Comment
Question by:KDSayers
8 Comments
 
LVL 69

Expert Comment

by:Éric Moreau
ID: 2724247
0
 
LVL 2

Expert Comment

by:Sage020999
ID: 2724560
Instead of placing the *, specify the field names.  You can also you the data connection to drag the tables you need into the data enviroment.
0
 
LVL 1

Author Comment

by:KDSayers
ID: 2724571
This does not answer the specified question.  Particularly that I have paid some serious points out looking at previously asked questions related to this topic that the help did not work, and was also vague.  So I am looking for detail and the person with the best help will get the points.

Thanks.

KDS
0
 

Expert Comment

by:Billy06
ID: 2724814
hi,
well, I did a VB project using Data Environment. It worked fine.

1. Give the command name
2. Connection name
3. SQL statement

Parameter:
name - give it a name
Direction -  input
Data_type   - adVarChar
size   -   xx
Host Data Type  -  String (VT_BSTR)
Required   - true

For Integer:
Data Type  - adInteger
no size, scale, precision  (all 0)
Host Data_Type - integer(VT_I2)
Required  - True

it should work.
Inside Advanced Tab,
Cursor location is 3.


Good Luck
Bill
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 12

Expert Comment

by:roverm
ID: 2726106
You can use the DataEnvironment (DE):

make a command using your sql-statement, or make a stored procedure in sql server and point to it.

Then set the DataSource property of the DataReport (DR) to the DE and the DataMember to the command (let's say cmdMine).

Before invoking the DR close the DE connection:

If DE.Connection1.State = adStateOpen Then DE.Connection1.Close

Then re-open it and invoke the command including the parameter (parm1)

DE.connection1.Open
DE.cmdMine parm1

Then invoke the report:

DE.Show vbModal
Unload DE 'don't forget to unload it else it will remain in memory!!

After this close you can close the connection again to prevent memory loss:

DE.Connection1.Close

Good luck!

D'Mzzl!
RoverM
0
 
LVL 1

Accepted Solution

by:
garrenb earned 100 total points
ID: 2818271
Hi,
I had the same frustration as you a while back.
This is a tried, tested and frequently used
piece of code I use to solve that very problem.

Basicially setup the report the way you would like
it to appear, showing all the records.


Initially put a break on the line after the 'DEBUG' line (F9)
When you run this piece of code your Debug Screen will
show you what your 'Select String' should look like e.g.  "SHAPE {select * from te....."
Copy the line in the Debug Screen into your own code, (see the STRSQL line)
extend it with your own search parameters and
code it in like mine in the 'STRSQL' line below.

Hey presto, you have your "Select String" just like the older Data Control

For Each myCmd In DataEnvironment1.Commands
   If myCmd.Name = "Testing" Then  ' Put your own Command Name here
     Debug.Print myCmd.CommandText
     strsql = "SHAPE {select * from testing WHERE (((Testing.REF)=" & theref.text & "));} AS Testing APPEND (( SHAPE {SELECT Testing_Fails.Ref, Testing_Fails.VT5_ItemNo, VT5_Items.Item, Testing_Fails.Fail_ItemNo, Fail_Items.Fail FROM (Testing_Fails INNER JOIN VT5_Items ON Testing_Fails.VT5_ItemNo = VT5_Items.ItemNo) INNER JOIN Fail_Items ON Testing_Fails.Fail_ItemNo = Fail_Items.FailNo GROUP BY Testing_Fails.Ref, Testing_Fails.VT5_ItemNo, VT5_Items.Item, Testing_Fails.Fail_ItemNo, Fail_Items.Fail;}  AS Testing_Fails COMPUTE Testing_Fails BY 'Ref','VT5_ItemNo','Item') AS Testing_Fails_Grouping RELATE 'ref' TO 'Ref') AS Testing_Fails_Grouping"

      myCmd.CommandText = strsql
      myCmd.Execute
     DataReport1.Refresh
            Exit For
   End If
Next myCmd

DataReport1.Show
0
 
LVL 1

Author Comment

by:KDSayers
ID: 2898344
Comment accepted as answer
0
 
LVL 1

Author Comment

by:KDSayers
ID: 2898345
This was the detail needed to get the job done.  Thanks for your help.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
z = x + y – 1 6 67
Window placement 17 75
Paint/Redraw window while dragging 16 68
Sorting multiple rows and columns, and count duplicates in Excel 2013 4 68
There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now