June 21, 2006 at 10:36 am
Is possible to Send a recordset from vb6.0 to a sp in SQL Server 2000?
If so, how can I do that?
June 21, 2006 at 10:39 am
You can loop the recordset and execute the stored procedure for each record.
June 21, 2006 at 10:45 am
What I need to do is apply the next queries to the recordset, so seding one record will not help
Query1:
select noEmple,count(noEmple) N
from tboclineas
where nocliente=150 and noOrden=4
group by nocliente,noOrden, noemple
having count(noEmple)>1
Query2:
select noTarjeta, N
from
(
select count(noTarjeta) N, noTarjeta
from tboclineas
where nocliente=150 and noOrden=4
group by nocliente,noOrden, noTarjeta
)tbej
where N>1
Query3:
select noEmple,count(noEmple) N
from tboclineas
where nocliente=150 and noOrden=4 and (MOnto<=0 or monto>=10000)
group by nocliente,noOrden, noemple
In vb I am not allow to make a select of a recorset result, so I was thinking that maybe using the sp I will be able to do that.
June 21, 2006 at 1:32 pm
The primary reason to send a recordset from a client to the server is if the client has modified the recordset.
In your case, it appears you're performing additional queries against the same data.
Can you build Queries #2 and #3 to just take the parameters? Why do they need the recordset?
On a side note, you can change Query #2 to
select noTarjeta, N
from
(
select count(noTarjeta) N, noTarjeta
from tboclineas
where nocliente=150 and noOrden=4
group by nocliente,noOrden, noTarjeta
HAVING count(noTarjeta) > 1
)tbej
where N>1
-Eddie
Eddie Wuerch
MCM: SQL
June 21, 2006 at 2:50 pm
To send a recordset to the DB, you would preferably use an update method. The recordset (even if empty) should have come from the DB to start with, so you are just writing your changes back. Once written back, the data needs to reside somewhere - and that place is the base table the data is conformed to, i.e. where its metadata, and possibly some data, came from to start with. Actually I guess there are more elegant ways of connecting a recordset to a DB storage structure than the old where 1=2, but the basic principle won't have changed. If you need to do joins, do them on the server before fetching the data.
If you need to do some sort of extensive what-if analysis, you might be looking at a completely different kind of solution, e.g. off the top of my head, OLAP (usually a kind of optimised client-side setwise processing).
edit: i didn't see your second post. Most of what I said above isn't relevant to you. Just get the recordets you need separately from each other. You could do it in an sp - just have three SELECT statements, and use rst.NextRecordset to access them on the client. Basically I don't have much to add to the previous post. I'll go back to sleep.
Tim Wilkinson
"If it doesn't work in practice, you're using the wrong theory"
- Immanuel Kant
June 23, 2006 at 1:32 am
The simple answr is no - but you could write it to a temporary table which the sp then could see.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply