SQL Update

Does this code to the same as an insert command. Where the server does the work as opposed to the client. Is there any difference between the two?

 strSQL = "Select * from quotes WHERE quoteno = " & Me.QuoteNo
 rcdQuotes.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
 rcdQuotes.Fields("consigneekey").Value = Me.Consignee

Thank you

more ▼

asked Jul 12, 2010 at 08:50 AM in Default

avatar image

11 3 3 3

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Before going any further, I would like to point out one thing: please schema qualify your tables, it makes it so much easier for the engine to parse your queries.

I know that many people like the approach of making the tool to figure out the details about update and insert statements, but the feature should be used with utmost caution. In your code, you select (possibly) bunch of records from quotes table and then try to update the underlying table by setting value of one of the fields. I am not sure whether this is what your code is trying to achieve (as written, it will only update the first record). The downside of this approach is that you do not have any control over the update statement that ADODB generates, and from my experience, such the statement can be rather horrendous. Here is the reason: the first record in the possible bunch of quote records where quoteno is equal to the value you specify needs to be uniquely identified by the SQL Server engine to ensure that the update is applied to the correct record. Without any information about the structure of the table, the client code will generate a very unattractive update statement with the predicate including every column in the table. Turn on the profiler and check it out. Usually, such check makes a very loud wake-up call. When I saw the update SQL generated by ADODB for the first time, it was enough to turn me off from ever considering using the feature, much less to to actually using it.

To summarize, if the number of quotes records is just one then your code will update that record (using the aforementioned dreaded update statement generated by ADODB). If the number of records is greater than one then you need to loop through the records in the recordset setting the value of the field you wish to update and issuing the Update command against the recordset (making it even worse). Instead, I would definitely opt to:

instantiate a command object

Add 2 parameters to it (quoteno @q and consigneekey @c) and set their respective values

Issue the parameterized command (ExecuteNonquery) to it like so: "update quotes set consigneekey = @c where quoteno = @q;"

This will update your (one or more) records in one statement without the necessity to create needless traffic by returning original recordset and bunch of needless call to the data store to update your records.

Hope this helps.


OK, here is the sample code as requested (again, I cannot guarantee that it is bulletproof because I have not worked with ADODB for a long time):

 Dim objCommand As ADODB.Command
 Set objCommand = New ADODB.Command
 objCommand.ActiveConnection = Conn
 objCommand.CommandType = adCmdText
 objCommand.CommandText = "update quotes set consigneekey = @c where quoteno = @q;"
 objCommand.Parameters.Append objCommand.CreateParameter( _
     "@q", adInteger, adParamInput, 4, CInt(Me.QuoteNo))
 objCommand.Parameters.Append objCommand.CreateParameter( _
     "@c", adInteger, adParamInput, 4, CInt(Me.Consignee))
 objCommand.Execute , , adExecuteNoRecords
 Set objCommand = Nothing


more ▼

answered Jul 12, 2010 at 09:42 AM

avatar image

20.2k 3 7 29

Epic, really good answer. I would +2 this if it were possible...

Jul 13, 2010 at 12:23 PM Matt Whitfield ♦♦

@Matt Whitfield Thank you very much, and congratulations on 13k!

Jul 13, 2010 at 01:15 PM Oleg
(comments are locked)
10|1200 characters needed characters left

Thank you for the quick response. The code I asked about is only updating one field on one record. Could you please send me a sample of using a parameterized command that would take the place of my code? Thanks again

more ▼

answered Jul 12, 2010 at 09:50 AM

avatar image

11 3 3 3

@dendic I added the sample snippet to my answer.

Jul 13, 2010 at 08:35 AM Oleg
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Jul 12, 2010 at 08:50 AM

Seen: 1017 times

Last Updated: Jul 12, 2010 at 09:09 AM

Copyright 2018 Redgate Software. Privacy Policy