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?
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):
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
answered Jul 12, 2010 at 09:50 AM