Topic: Dataset to recordset

Hello sibprogsystem....
I need your help...
I am retrieving data from mssql server database..
How to genrate report using fast report...
And how to convert recordset to dataset..

Re: Dataset to recordset

blackpearl8534,
I know you have been asking this question on the Forum, but I am not aware of a way to go directly from a recordset to a dataset. I have not seen anything on that and without Dimitry's support I think you may be stuck. However, as an alternative you could populate a local temporary Sqlite database with your report records. Then you could report off of that. I have attached a sample. I do not have Sql Server so I used Access, but the concept is the same. I would recommend selecting only the records and fields needed for the report for efficiency purposes. I apologize if you already thought of this and determined that it is not workable.

Post's attachments

Attachment icon Blackpearl.zip 357.42 kb, 229 downloads since 2021-08-21 

Re: Dataset to recordset

Thanks ... k245 told me that i did not found any solution in mvd to genrate report from sql server... i think this is the best way to print my selected data...
Thank you very much to guide me...

4 (edited by blackpearl8534 2021-08-22 13:16:03)

Re: Dataset to recordset

hello K245 and ehwagner
i found a solution but not exactly working like i want...
i want to print all data but its print 1 row... please check it

Post's attachments

Attachment icon access print.zip 361.46 kb, 209 downloads since 2021-08-22 

Re: Dataset to recordset

blackpearl8534,
The reason you are only getting one row is because you are using variables. Variables only hold one instance of information. With each iteration of your while loop you are overriding the previous iteration of variables. So after you finish the while loop you load the report variables with essentially the last iteration of the recordset variables. That's why your report only shows the last record of the table.

Re: Dataset to recordset

Can we load all data????

Re: Dataset to recordset

Not without loading an Sqlite DB first. Unless Dimitry can provide another solution, I'm just not aware of another way.

Re: Dataset to recordset

Where is dmitry...
Is he supporting MVD now...

Re: Dataset to recordset

I am facing some problems to retrieve data from sql server...
I mean filter by datetime...

Re: Dataset to recordset

blackpearl8534,
Is there a reason why you can't use a temporary sqlite db for reporting purposes only.

11 (edited by brian.zaballa 2021-08-24 02:44:29)

Re: Dataset to recordset

ehwagner wrote:

blackpearl8534,
Is there a reason why you can't use a temporary sqlite db for reporting purposes only.

I'm up for this. You can load it into a temporary table on your sqlite, then print it.
Here I made some modifications on the script to give you an idea.

Post's attachments

Attachment icon access print 2.zip 575.15 kb, 223 downloads since 2021-08-24 

brian

Re: Dataset to recordset

great example. thanks..
can you tell me how to load data using datetimepicker filter

Re: Dataset to recordset

blackpearl8534 wrote:

great example. thanks..
can you tell me how to load data using datetimepicker filter

Just do a filter on your sqlite(temp) database

Post's attachments

Attachment icon access print 3.zip 575.53 kb, 257 downloads since 2021-08-24 

brian

Re: Dataset to recordset

But i need filter on access db because i want to load filterd data not all...

Re: Dataset to recordset

blackpearl8534 wrote:

But i need filter on access db because i want to load filterd data not all...

Well, I'm not sure why you would want that when filtering in MVD with sqlite will give you less time to spend.
But if you insist, then try to integrate Filtering on MS SQL with script and queries.
I found some here
https://stackoverflow.com/questions/424 … access-sql

Then do something like

procedure Form1_Button1_OnClick (Sender: TObject; var Cancel: boolean);
var
    i: Integer;
    _id, _num, _txt, _date: String;
    _fdate1, _fdate2: String;
begin
    LoadStatus('Opening Database...');

    _fdate1 := FormatDateTime('yyyy,MM,dd', Form1.f_from.DateTime);
    _fdate2 := FormatDateTime('yyyy,MM,dd', Form1.f_to.DateTime);
    rs.Open('SELECT id,sampleNumber,sampleShortText,sampleDate FROM SampleTable WHERE sampleDate BETWEEN DateSerial('+_fdate1+') AND DateSerial('+_fdate2+')', Conn);
    //rs.Open('SELECT id,sampleNumber,sampleShortText,sampleDate FROM SampleTable WHERE sampleDate BETWEEN DateSerial(2021,08,04) AND DateSerial(2021,08,29)', Conn);
brian

Re: Dataset to recordset

thanks for reply...
.basically i have a software that is connected to fuel dispensers and stores data into mssql database.
i am developing an external software that print data and generate summary. that is reason to filter data directly from sql database...

Re: Dataset to recordset

hello... after struggle of many hours i found datetime format to retrieve data from sql server database...
but now how to add Datetime picker is this formate...
because when i set ''mypicker'' then sql throw error.... please check it in Uploaded pic and see carefully...

Post's attachments

Attachment icon Untitled.png 33.64 kb, 83 downloads since 2021-08-24 

Re: Dataset to recordset

Try this

hd.Completed_TS >= ' + _fromTime + ' Order By...

You may need to put quotes around it as follows

hd.Completed_TS >= "' + _fromTime + '" Order By...

Re: Dataset to recordset

thanks ehwagner working Properly

Re: Dataset to recordset

how to write sql query if filter variable is null

21 (edited by ehwagner 2021-08-25 15:32:13)

Re: Dataset to recordset

Not exactly sure what you mean. Are you using a SqlQuery button or SqlExecute script? If you are using a SqlQuery button and you want all records when the variable is empty, the easiest way I can think of is to create another SqlQuery button with the WHERE clause removed, then check  for the existence of the variable and script click on the appropriate button. You could do the same with SqlExecute only you would remove the WHERE clause in the script rather than having a separate button.  Maybe you could explain a little more if I am off base.

Re: Dataset to recordset

Thanks....ehwagner
I am using query in rs.open('my sql query')
I applied a condition if combobox value is > 0 then my query so i wrote 4 queries with different clauses. Now its working...

Re: Dataset to recordset

Awesome!