x

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
rcdQuotes.Update
Thank you
more ▼

asked Jul 12 '10 at 08:50 AM in Default

dendic gravatar image

dendic
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.

Oleg

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
Oleg
more ▼

answered Jul 12 '10 at 09:42 AM

Oleg gravatar image

Oleg
15.9k 2 4 24

Epic, really good answer. I would +2 this if it were possible...
Jul 13 '10 at 12:23 PM Matt Whitfield ♦♦
@Matt Whitfield Thank you very much, and congratulations on 13k!
Jul 13 '10 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 '10 at 09:50 AM

dendic gravatar image

dendic
11 3 3 3

@dendic I added the sample snippet to my answer.
Jul 13 '10 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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x8

asked: Jul 12 '10 at 08:50 AM

Seen: 740 times

Last Updated: Jul 12 '10 at 09:09 AM