Update SQL Server/Table with RecordSet [VB6]

[Shaitan00]

Given that I know how to generate a Recordset from an SQL Query (for example, I am able to do a Select * FROM Table and return that information in a recordset).

Question is can we do something similar for the other way? Meaning can we take a Recordset and insert it into an SQL Table in a similar way?

See, my problem is I need to import information into the SQL Table, my only options are a) inserting each row by row into the SQL Table or b) generate a Recordset with all my rows and sending the entire thing (in one shot) to the SQL Table.

Can this be done?
If so anyone able to provide an example/sample?
Thanks,

[techgnome]

Yes it is possible.... but it comes with a price, and a pretty hefty one at that.


Get your recordset, when you get it, make sure that you use a CLIENT SIDE cursor that is BatchUpdatable.

Disconnect your recordset (.ActiveConnection = Nothing)

AddNew all you need to.

When done, reconnect (.ActiveConnection = you connection object here) and use the .BatchUpdate method. This is where the price comes. While the adding to the recordset is fast, you are now transmitting the entire data set back to the server (this is going to include any rows that were already in the table) and for any new records, server will now need to do the insert row by row. Meanwhile, if something goes wrong, there's no telling 1) What row caused the problem, and 2) What state is the data in now? What got saved, and what didn't?

TG

[Shaitan00]

Hummm....

Few things I need to mention in reponse to your idea.

a) The recordset is not build from the SQL Server/Table, it is done manually (completly disconnected at all times) and therefore all information in the recordset is NEW


As for the methods, considering the price...
Would it be better just to "INSERT" line by line ?
However this way allows me to trace exactly where issues would occur and stuff.

Problem with that is my application only "connects" to the SQL Server to do specific actions, so if I did line by line my application would 1- Connect to SQL, 2- INSERT, 3-Disconnect for EACH row.

What do you think is more effective?

[techgnome]

Where are you getting the data from? A file or something? If so, forget a recordset.

Here's how I do it:
Create a Stored Procedure to insert the data (done before hand)
Then in the code:
1) Create a command object, add all the parameters (don't set values)
2) Open the file
3) Establish the connection
4) Set the command's connection to the new connection
5) Begin loop (while there's data in the file)
6) Set each of the parameters to the data imported for the current line
7) Execute the command
8) Repeat loop until we reach toe end of the file
9) Close File
10) Close connection

Done.

TG

[Shaitan00]

Source is an EXCEL File (TEST.XLS).

Stored Procedures huh.
I have never played with those, guessing I should do some reading.

Stored Procedure to insert date.
I don't seem to see the different between that and an INSERT besides the fact that it looks like the connection will stay open for the entire Stored Procedure which could do all rows opposed to open/insert row/close loop I was thinking of.

I am assuming examples for this type of situation are not simple enough to write up, i'll look into it.
Thanks,

[brucevde]

A Command can be based on a SQL string, it does not have to be a stored procedure. There is very little speed difference between a Command executing a SQL statement vs a Stored procedure. The difference comes at the first execution only. Every sql statement needs to be "Compiled". A Stored Procedure is "Compiled" when it is saved but a SQL Statement is compiled at runtime. However, set the Command.Prepared property to True and SQL Server will only need to compile the SQL statement once. The compilation is saved in a cache and is used by every execution afterwards.

Using a Command would be more efficient than executing the sql statement directly through the Connection.Execute method. It also eliminates some of the hassles you may encounter when dynamically creating sql statements through code.

I would recommend that you open the connection before the loop begins and close it after the entire job completes.

Open Connection 
 
Set objCmd = New ADODB.Command
 
With objCmd
   Set .ActiveConnection = Some ADO Connection
   .CommandType = adCmdText
   .CommandText = "Insert Into SomeTable Values(?,?,?,?)" 'question mark for each field
   .Prepared = True 'important when executing sql statements in a loop
 
   'the database can populate the parameters collection for you
   'by calling the Parameters.Refresh method.  
   'Note: this results in an extra database hit and more network 
   'traffic but shouldn't be a huge deal
   'Also, using Refresh depends on the SQL Statement - make 
   'sure you verify that it generates the intended parameters.
   '.Parameters.Refresh   
 
   'otherwise populate the Parameters Collection manually
   .Parameters.Append .CreateParameter(.... 'do for each ?
   .Parameters.Append .CreateParameter(....
End With 
 
Do Until EOF
    '...Get File Data
    'Set Parameter Values
     objCmd.Parameters("ParamName").Value = File Data 'do for each parameter
   
     objCmd.Execute , , adExecuteNoRecords
Loop
 
Close Connection

If there are lots of rows to insert, it would be more efficient to use variables declared as ADODB.Parameters, one for each field, rather than going through the Parameters Collection everytime.

One more note - If you are inserting records and need to use the new Identity column value elsewhere in your program - then you should(actually you must) use stored procedures.