Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2003
    Posts
    3

    Unanswered: MSSQL: Incorrect Syntax on Recordset.Update

    ADO/MSSQL throws an error "Incorrect syntax near the keyword 'Order'." on the oRS.Update line in the code below. Been working with MS SQL for quite some time but i've never come across anything like this.

    Set oRS = Server.CreateObject("ADODB.Recordset")
    oRS.Open "SELECT * FROM [PropertyTab]", oDB, 3, 2
    oRS.AddNew
    oRS("ID") = "{02AFC06C-99C2-4FF2-9569-4410E84F645C}"
    oRS("Created") = Now
    oRS("EntityTypeID") = "{DC9ED96D-E73F-4ADE-BB66-5FB5DF8AC240}"
    oRS("FriendlyName") = "Testing"
    oRS("Order") = 0
    oRS("Modified") = Now
    oRS.Update
    oRS.Close
    Set oRS = Nothing

    oDB is a std ADODB.Connection object, no special parameters or options set. Everything else in the application is working perfectly. Similar updates are working perfectly.

    Table design is as follows:

    [ID] uniqueidentifier
    [EntityTypeID] uniqueidentifier
    [FriendlyName] varchar(1024)
    [Order] int
    [Created] datetime
    [Modified] datetime

    None of the fields allow NULL. [EntityTypeID] is indexed from [EntityType] and the specified Guid does exist in that table. ID indexes [PropertyTabID] in [PropertyTab]. The [PropertyTab] table is empty. Both indexes cascade. No triggers or check constraints in any of the tables. All tables and database (MS SQL 200) are using default settings. Logged in as 'sa'.

    I know Order is a reserved keyword, but this has worked fine before either by bracing the field name or, as in the code above, updating the record as an object.

    Suggestions please?

  2. #2
    Join Date
    Jul 2003
    Location
    PA
    Posts
    7
    I suggest just executing an insert query instead of going through all that recordset nonsense.

    sSQL = "INSERT INTO [PropertyTab] ([ID],[Created],[EnityTypeID],[FriendlyName],[Order],[Modified]) VALUES ('02AFC06C-99C2-4FF2-9569-4410E84F645C}','" & Now() & "',{DC9ED96D-E73F-4ADE-BB66-5FB5DF8AC240}','Testing',0,'" & Now() & "')"

    oDB.Open YourConnectionString
    oDB.Execute sSQL
    oDB.Close
    Set oDB = Nothing

  3. #3
    Join Date
    Jul 2003
    Posts
    3

    Not a solution

    Naturally I would if there was'nt a good reason for doing it this way. The code above is NOT the code that is used in the program I simplified it significantly to make it more readable and easier to see what the problem is. The code above has been tried in place of the actual code and does reproduce the error.

    Even if the problem can indeed be solved by using queries (alot slower in this particular case, and speed is of the essence) I am not the kind of person that runs into a problem and decides to take an alternate route to avoid it without trying to understand the problem first. That is according to me the best way to *NOT* better yourself.

    The problem can also be solved by renaming the Order field, and this is what I have done, but I still have a strong urge to understand why the code behaves in this unpredicted manned.

  4. #4
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75
    "That is according to me the best way to *NOT* better yourself."

    Ray is only trying to help, please try and be curteous as he's just trying to provide help and being polite wouldn't hurt.

    The reason you did not get this before is either you were correctly using them before or may be partially due to your SQL server version / upgrade from. Order as you know is a reserved keyword which is part of the grammar of the Transact-SQL language used to parse and understand Transact-SQL statements and batches. Although it is syntactically possible to use SQL Server reserved keywords as identifiers and object names in Transact-SQL scripts, this can be done only by using delimited identifiers.

    http://msdn.microsoft.com/library/de...on_03_89rn.asp

    i hope this helps.

  5. #5
    Join Date
    Jul 2003
    Location
    PA
    Posts
    7
    He he. Thanks unatratnag. I'm not offended or put out. I agree with you, SenseiTG, that finding a different way of doing something is not the correct way to solve a problem, sometimes. But it depends on what it is that you're doing. 99 times out of 100, there is extra overhead in creating recordsets and using the update method of a recordset. That is the reason I suggested skipping the recordset method - not as a way of solving the problem. I'd be curious to know how it is that the recordset method you're using is more efficient than executing an INSERT.

  6. #6
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75
    haha, start reading ray, there's some in there pertaining to sensei's method but most are just general speed improvement for SQL.

    http://www.sql-server-performance.co...erformance.asp

  7. #7
    Join Date
    Jul 2003
    Location
    PA
    Posts
    7
    I don't know what you mean by your post, unatratnag. Are you saying the RS would be more efficient? That is NOT what I am saying, and that link you provided says the same thing in paragraph 2.

    avoid using the ADO recordset object to modify (INSERT, UPDATE, DELETE) data in your code

  8. #8
    Join Date
    Jul 2003
    Location
    Ohio/Chicago
    Posts
    75
    oh no no, you are absolutely correct with this situation. sorry, didn't mean to confuse you.

    I was attempting to convey that disconnected record sets can be used to actually save overhead as opposed to querying the database for updating records.

    Consider using ADO's ability to create disconnected recordsets to help reduce the load on SQL Server... Small DB's such as Access can increase network or server load. Although fully realized databases certainly have a very vital role in today's application development, there are many cases when an external database isn't necessary. Any application where the data is relatively flat or where you cannot assume network connectivity may be a candidate for this approach..

    But in your case you're looking for advantages over opening a connection each time (make sure you keep using the same connection too each time you connect). Frequently a database connection is open the whole time the client is accessing data. Typically, every client holds an open connection. Web applications are not well suited to this two-tier client-server application architecture, however. A very popular web site can get a couple hundred million hits a day. Since most web sites are data driven to some degree, it quickly becomes apparent that the database for that site would have to support tens of millions of concurrent open connections. This is currently impractical

    Disconnected Recordsets are the most commonly used mechanism to retrieve a Recordset and open a connection for only the necessary amount of time, thus increasing scalability.

    I can't say I am the master of efficiency or DB's for that matter but these are some of my understandings for disconnected record sets.

  9. #9
    Join Date
    Jul 2003
    Posts
    3
    Thank you very much for your replies.

    After reading my post i see your point but I must stress that it was not my intention toindicate that Ray is a person whos problem finding policy is incorrect. Neither was it my intention to imply that he is a person who would not better himself by working around instead of solving problems. In many cases, when time is short, finding an alternate method of doing the same thing, thus avoiding the origninal problem, is the way to go.
    I am glad you were not offended.

    I am very aware that ORDER is a reserved keyword and I am very familiar with bracketing as well. However, bracketing cannot be used when updating a recordset in this manner. Whatever query syntax may be generated and executed in the background by the above code bracketing should be done by the ADO provider where needed. Similar code using the field name [Order] can still be run from the same client on other databases on the same SQL Server, hence ruling out the possibility of a version problem. My suggestion is that this is a bug with the ADO provider for SQL Server.

    Under normal circumstances you would be right when it comes to speed. But there are other issues i have not mentioned. Consider the following code:


    RS.Open "SELECT * FROM [" + Table + "]", DB, adOpenStatic, adLockOptimistic
    For Rows = 1 To RowCount
    ID = RowID(Rows)
    Values = RowValues(Rows)

    RS.Find "[ID] = '" + ID + "'"
    If RS.EOF Then
    RS.AddNew
    RS("ID") = ID
    End If

    For Count = 0 To UBound(Fields)
    If RS(Fields(Count)) <> Values(Count) Then
    'LOG CHANGES
    End If
    RS(Fields(Count)) = Values(Count)
    Next

    RS.Update
    Next
    RS.Close


    It is mainly because of the need for logging that this method is the fastest way of updating. The first code sample was merely to illustrate the actual work done to produce the error.

    The main reason for posting to this thread was, as previously mentioned, to find an explanation to why the code behaves in this manner and hopefully a way to solve it.
    The code is running fine since changing [Order] to [Sorting] so there is no need for help with alternate solutions but any information regarding this problem would be useful so that I and others can avoid running into it again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •