Work with recordsets

kko24
10-19-2004, 07:40 AM
Hello,
I try to get more than one set of recordsets with an adodb.recordset object from sqlserver/sybase query.
When the data return to vb, I already get more than one set of recordset, and I can show in datagrid the results combined, for that I create a new recordset and append fields to the max count of fields of the recordsets obtained before, so I fill the new recordset one by one record, it works, but slowly.
I try to obtain better performance with :
Private Declare Sub CopyMem Lib "kernel32" Alias "RtlMoveMemory" (pDest As Any, pSource As Any, ByVal ByteLen As Long)
for moving the data, but is not working.
Maybe if I can combine the getrows of the recordsets and use the final array to show the data in the grid, but how I can Combine those arrays?
Some one have a better way for make that I want?

Shurik12
10-19-2004, 03:25 PM
Hi,

Just a question, is there any chance you can return one recordset (using UNION or so..)
instead of trying to do what you're doing now?

Shurik.

kko24
10-22-2004, 10:05 AM
No. Cause the thing is have the posibility of write any query(s), that replies different fields from different tables. So union don't work.
Example:

select campo1 from tabla1
select campo2, campo3 from tabla2

union donīt work int this case for SQL

Maybe :
select A1 = campo1, A2 = "" from tabla1
union
select A1 = campo1, A2 = campo2 from tabl2
but the problem is recognize the number of fields we want to receive, thinking in the posibility of make querys with subquerys, and the problem goes deep, cause the different types of fields that can exists in the tables

Dennis DVR
10-22-2004, 10:45 AM
can you tell us why do you need to pass the value of your recordset to another line by line? and if you are just copying all the data in the first recordset to the second recordset why don't you just pass the first recordset object to the second recordset using the set statement i.e. ser rs2 = rs1 or use the clone method of the first recordset?

kko24
10-22-2004, 11:05 AM
If I do this:
set rs2 = rs1
the rs2 will be equal to rs1, but the idea is have a "rst = rst1 + rst2"
example:
select 1 from tabla1
select 2 from tabla2
select 3,4 from tabla3

the recordset (rst) will have three (number 3, sorry I'm chilean) "recordset"
the first have one column and one row
the second have one column and one row
the third have 2 column and one row

when i assign the object's .datasource to rst, then the grid show me
------
1
------
if I make a .nextrecordset and the "set..." the grid show:
------
2
------
if I make a .nextrecordset and the "set..." the grid show:
-------------
3 |4
-------------
the idea is show:
-------
1 |
-------
2 |
-------
3 | 4
-------
I mean, combine the results of the three(3) results that receive from the query

Dennis DVR
10-22-2004, 11:11 AM
is left join not an option to you? perhaps posting the select statement would help us understand your current situation.

kko24
10-22-2004, 11:21 AM
the query could be just to simply like:
consulta = " select 1
select 2
select 3,4"

sample how do I make the thing:

Dim Com As New Command
Dim Arr()

Dim Rst As New Recordset
Com.ActiveConnection = Con
Com.CommandTimeout = 15000

On Error GoTo salir
Com.CommandText = Consulta
'MsgBox com.CommandText
Set Rst = Com.Execute

Grilla.DataSource = rst will show the first result

It will receive 3 set of results
with the code "set rst = rst.nextrecordset" I can navigate thru the results.
so, :
set rst = rst.nextrecordset
Grilla.DataSource = rst will show the second result

set rst = rst.nextrecordset
Grilla.DataSource = rst will show the third result

and I want to show the three result at one time.

Dennis DVR
10-22-2004, 11:32 AM
if these three tables have field in common you can use the LEFT JOIN

the SQL statement would look like:

SELECT Table1.Field1,Table2.Field1, Table3.Field1,Table3.Field2 FROM Table1
LEFT JOIN Table2 ON Table2.KeyField = Table1.KeyField
LEFTJOIN Table3 ON Table3.KeyField = Table1.KeyField


where Keyfield is the common field for the 3 tables usually the ID field

kko24
10-22-2004, 11:37 AM
Yes, but the idea is make the query the most simply and fast, and is oriented to users that not have advanced knowledge of SQL.

Is a form that have a textbox where the users write the query and then press a command to execute this query, and receive the data in a grid.

loquin
10-22-2004, 12:07 PM
Yes, BUT, what duane mentioned is not advanced SQL. It is simple SQL.

Take a look at this link to an SQL Tutorial (http://www.xtremevbtalk.com/showthread.php?t=4339).

kko24
10-22-2004, 12:16 PM
a conceptual question not make the thing better,
well if you like,
the idea is make the more simply the query, forget the advanced, but try to make a LEFT JOIN in sybase... first read the post.
the sintax in sqlserver v/s sybase can change. Go to
www.sybase.com and check some very,very basic tutorials...

loquin
10-22-2004, 12:41 PM
I looked at their site; I didn't able to find a tutorial.

Sybase is similar to SQL Server, as you indicated. (They had a common version, long ago)

Now, the basic concept here is that you should let the database server do the work of joining data from multiple, related tables, rather than loading the tables and then joining them in your application. The server is optimized for, and effecient in processing joins.

Dennis DVR
10-22-2004, 12:57 PM
Yes, but the idea is make the query the most simply and fast, and is oriented to users that not have advanced knowledge of SQL.

Is a form that have a textbox where the users write the query and then press a command to execute this query, and receive the data in a grid.

Additionally, you should not allow the user to type the queries, what you could do is, let them select the fields that they want to appear in the grid and let your application create the queries based on the user selected fields, in this manner you are not limeted to the users' level of thinking, and it would make your application user friendly.

loquin
10-22-2004, 01:25 PM
I agree. Here's a screen-shot of one I'm working on.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum