connecting and passing query in VB6 and CR

[kehar]

Sir, I have prepared programme using VB6 and Access2003. The programme is working very fine so far as adding,deleting,modifying the record through VB is concerned. I have installed Crystal Report 8.5 and have prepared a report and save as (bill.rpt) but I dont know how to connect the report with VB. The path of report is C:\ks\bill.rpt and path of database in Access is C:\ks\exp.mdb
I shall be grateful if you kindly help me
1. to connect the report with VB and
2. to pass a query in which if I enter America in Text1.text in VB and Click the command button VIEW the report should generate only the name and age of all the person who live in America within the field (address).
The Data base fieldname are Name, age and address

I am very new to CR so pl write all the code to write within the click event procedure of command button VIEW so that I may not add anything else.

thanking you in anticipation

Keshar

[choudhuryshouvi]

you have to create an ODBC connection with the access database to connect your program to the crystal report. then create an query and make the report based on that query. after that you will call the query in your program and the rest will be handled by ur apps.

first of all u have to create an query in ur access db. then...

if u r not familiar with how to create an access odbc connection plz follow the steps:-

1.goto control panel->administrative tools->database(odbc connection)
2.in user dsn tab select add
3.select microsoft access driver .mdb from list and click finish
4.enter ur dsn name(this can be anything)
5.click select and locate ur access db
6.click ok
7.then ok again to create the dsn

after creating it goto crystal report and select the dsn under the odbc section from the connection tree while u select to create a new blank report.

use the query what u created in ur db to fetch the fields from it into the report. create the report based on that query.

after this use the following code to create the query dynamically to run it from ur vb6 apps:-

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.CreateQueryDef("MyQuery", "SELECT * FROM MyTable")
Set db = Nothing

then use a crystal report control and pass ur dbname,query and report name as parameters. ur program now should call the report.

hope it'll work.
regards
Shouvik

[kehar]

Sir,
thanks for your reply
I am very new to CR so I could not understand second part of your reply starting from
create query ....
I know how to prepare DSN in database
for your information I am giving you some hints
my dsn is exp
my database file path is C:\home\exp.mdb
database field are billno, chequeno, chequedt,month
In vb form I've placed crystal report control , one text box (text1.text) and one command window.

my problem is - when I run the VB and type "January" in text1.text and click the command botton the report should contain all billno,chequeno,chequedt pertaining to the month of January
similarly if I write February the report should contain all billno,chequeno,chequedt pertaining to the month of February.

I know the query statement to retrieve data from database to grid in VB that is written as :-
Select * from exp where month=' " & text1.text & " '
but I dont know the statement to retrieve the data from the Report

So I shall be grateful if you write the step by step procedure. I am dummy so far as CR is concerned.

[kehar]

Sir,
thanks for your reply
I am very new to CR so I could not understand second part of your reply starting from
create query ....
I know how to prepare DSN in database
for your information I am giving you some hints
my dsn is exp
my database file path is C:\home\exp.mdb
database field are billno, chequeno, chequedt,month
In vb form I've placed crystal report control , one text box (text1.text) and one command window.

my problem is - when I run the VB and type "January" in text1.text and click the command botton the report should contain all billno,chequeno,chequedt pertaining to the month of January
similarly if I write February the report should contain all billno,chequeno,chequedt pertaining to the month of February.

I know the query statement to retrieve data from database to grid in VB that is written as :-
Select * from exp where month=' " & text1.text & " '
but I dont know the statement to retrieve the data from the Report

So I shall be grateful if you write the step by step procedure. I am dummy so far as CR is concerned.

[choudhuryshouvi]

use the code in the zip file.
hope it'll help you.
but before run the code create the dsn with name "exp" and mention the database path of the database "exp". the path should be where u installed the db from the zip file.

regards
Shouvik

[kehar]

The Zip file is not opening even after log in with daniweb as such I once again request your goodself to send the zip file at my email id - [e-mail] as attach file ple.

Thanks

K.S. Chhetri

[choudhuryshouvi]

are u using internet explorer?
might IE is not supporting this download

download mozilla firefox browser from http://www.mozilla.com and try the download from that browser.

i'm telling you because i've tried downloading the file using firefox and it was successfully downloaded.

[choudhuryshouvi]

ok, for ur convenience i've sent it to ur mail.
check it and for further references if u wish can mail me at [e-mail]

regards
Shouvik

[kehar]

yes, download over and extracted the file and I have to try with my programme. I will see it tell you later.
thanks
k.s

[kehar]

Sir,
When I run the programme prepared by you the following error is shown

Run time error 20599
Cannot open SQL Server

So what is this error how is this rectified.

secondly when I tried to open your report only and tried to see the preview it ask for number of things how is this prepared and why ?

K.s.

[choudhuryshouvi]

can u approach to send a copy of ur program?
if yes then send it to [e-mail]
i'll check and fix that.
the error that u r getting is very strenge b'coz i did the whole thing using access dsn,there is no question about sql server. when i run the code in my comp it ran smoothly.

[kehar]

Sir,
Your programme is working fine but I think I am missing something while preparing report.
I am writing the procedure that I am following to create the report pl see it and pl inform me where I am wrong and what is still left.
1. After creating the DSN of the database I close the access and switch over to CR
a. open the CR and select the as a blank radio botton >click OK.
b. Data explorer window open > expand the ODBC >further expand the DSN name>select the database file >click Add botton and Close it.
c. In field explorer window >expand the database field >select file name>select the field one by one and place it under details section of report and close the field explorer window.
d. Save the report as exp.rpt.

So it the above procedure true ? If not then pl mention the other steps in detail. this is because when I open your report alone and try to see preview I could not see. Whereas when I open my report alone I can see it in preview mode. So some thing is wrong in my report.

Waiting for your valuable suggestion.

thanks

[choudhuryshouvi]

have u benefited from the sample code that i sent you?
do you have any more questions?
if u need any more help post it under the same thread or u can also mail me at [e-mail]