passing recordset - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

passing recordset

Author

Topic

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-12 : 05:20:51

can i pass recordset object(which have 10 records) from vb6 to stored procedures, if yes what is the datatype i have to declare in stored procedure

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 05:26:05

Now you cant do like this..

Create the Temp Table and insert all the values from the Records into that table and then in the procedure, do calculation based on that
temp table.

Chirag

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-09-12 : 05:26:21

Hi,

Yes is the answer. You have to declare your datatypes at the server itself and just pass the params from your vb6 app.


Dim cmd As ADODB.Command
Set cmd = New ADODB.Command

cmd.ActiveConnection = "YourConnectionStringHere"
cmd.CommandType = adCmdText
cmd.CommandText = "exec stp_YourProcName " & "'" & rst!YourVal & "'"  & ", " & rst!YourVal2 & ", " & rst!YourVal3 & "

"Impossible is Nothing"

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-12 : 05:53:24

hi
thanks. if i have more than 10 records, should i use a loop. that means i am making a call to the server that many times. in a single call i want to complete this transaction.

regards

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 06:27:04

quote:
Originally posted by sujeethbala2110

hi
thanks. if i have more than 10 records, should i use a loop. that means i am making a call to the server that many times. in a single call i want to complete this transaction.

regards

Yes, you need to loop then for each record.... or why not directly use the query which in your procedure, through which you had open your record set.

Chirag

sujeethbala2110
Starting Member

29 Posts

Posted - 2006-09-12 : 07:21:44

i have 10 records in a grid
all the records i want to insert into a table
this is my present code

---start
recordset.movefirst
while not recordset.eof
conn.execute "INSERT INTO Dlv_Note_Details Values('DN06000469',31)"
recordset.movenext
wend
---end

this will make 10 calls to the server. this will be slow. what is the best methad to do this.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-09-12 : 07:36:10


Dim strQry As String
strQry = "Insert Dlv_Note_Details"
recordset.movefirst
while not recordset.eof
  strQry += "Select 'DN06000469', 31 Union All" 
  recordset.movenext
wend
strQry = Left(strQry, Len(strQry) - (Len("Union All")) - 1)
conn.execute strQry 

--- Or another way post the query through which you are loading the th values your grid.

Chirag

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-09-12 : 07:46:39

Why not add a parameter to the SP to accept the number of iterations?

conn.execute "EXEC USP_Dlv_Note_Details 'DN06000469', 31, 10"

And let the SP handle the iterations?

Peter Larsson
Helsingborg, Sweden

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-09-13 : 09:26:32

I think the questioner wants to send data from grid to table

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources