Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    6

    Unhappy Unanswered: SQL Server update problem

    Hi, I have problem sometimes when updating fields in a table in SQLServer 2K. The fields are updated in a table from VBScripts using ODBC connection. The problem is happening intermittently, once in a blue moon some of the values gets updated out of 5 and sometimes none and many times it will be working fine. I am lost where to find the problem ? Is it in connection or Database or ??? So is there any possibilities to check whether the ResultSet Update statement in script has successfully performed updation or not ? Can I catch any exception of resultset of the querydone or any indicator of rows affected by the query ?

    Thanks...

  2. #2
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    ...some of the values gets updated out of 5...

    To avoid confusion : when you say VALUES, are you talking about COLUMNS in a single row, or are you talking about several ROWS in a table ?

    If you are talking about COLUMNS, then your problem is most probably NOT Mssql. I never heard of the following problem :

    Consider the following statement :
    UPDATE TABLE1 SET COL1 = 'A', COL2 = 'B' WHERE ID = 15,
    where column COL1 gets updated and COL2 does not get updated.

    I cannot imagine this is possible. If it where, MSSQL would be a very BAD database, which it isn't.

    To answer one of your questions :
    You can use @@ROWCOUNT to fetch the number of rows affected by a statement. But be very carefull when you are using this on statements performed on tables that fire triggers.

  3. #3
    Join Date
    Oct 2003
    Posts
    6

    Arrow

    Hi, Thanks for the reply. This is in SQL-Server 2000, not in MSSQL. Yeah only row gets updated, but several columns in a row. Funny thing is sometimes one column gets updated, sometimes none and sometimes all gets updated. What could be the problem. The values are not hardcoded in the SQL query, its all the variable..

    example :

    set rs = comdatabaseconnection.execute("update table_name set name='" & sName & "', Bank='" & sBank & '" where id='" & sID & "'")

    rs is Resultset
    comdatabaseconnection = is a ODBCconnection DSN checking (function CheckComDatabaseConnection below ):


    Sub CheckcomDatabaseConnection()
    If ComDatabaseConnection Is Nothing Then
    Set ComDatabaseConnection = CreateObject("ADODB.Connection")
    ComDatabaseConnection.Open = "DSN=Admin"
    ElseIf ComDatabaseConnection.state=0 Then
    ComDatabaseConnection.Open = "DSN=Admin"
    End If
    End Sub

    How to check whether the row has been affected or not ? Is there any property to check for the resultset object ?

    Thanks.

  4. #4
    Join Date
    Oct 2003
    Location
    Switzerland
    Posts
    140
    Well, well.

    1. The letters MS in MSSQL stand for Microsoft. In other words MSSQL = Microsoft SQL server, which is exactly the one you are running. Good.

    2. What you are telling us is that when executing the following statement :

    update table_name
    set name='" & sName & "', Bank='" & sBank & '"
    where id='" & sID & "'"

    the column NAME gets updated, while the column BANK doesn't get updated. As a standalone instruction against your database, this is - to my opinion - simply NOT possible. You might think it is, but the problem has most certainly another origin. Perhaps the value you are assigning to column BANK is equivalent to its original value, perhaps you have an AFTER UPDATE trigger on your table that is "undoing" the effect, ...

    Moreover, I don't know why you want to check whether the row has been affected, as this does not seem to be your problem. You describe your problem as one where some of the COLUMNS in an update statement get updated, while others are not. Checking whether a row has been affected does not give you any usefull information to solve your problem.

  5. #5
    Join Date
    Oct 2003
    Location
    Ireland
    Posts
    54
    First of all, you're update query is wrong, but i suspect its just a typo

    "update table_name set name='" & sName & "', Bank='" & sBank & '" where id='" & sID & "'"

    should be

    "update table_name set name='" & sName & "', Bank='" & sBank & "' where id='" & sID & "'"

    single quote after sBank.

    Anyhow. To know whether the update worked, you need to know how many rows were affected.

    Dim nRowsAffected as Integer

    set rs = comdatabaseconnection.execute("update table_name set name='" & sName & "', Bank='" & sBank & "' where id='" & sID & "'", nRowsAffected)

    I don't use it exactly like this, but you get the idea, nRowAffected should contain the row count updated.

    Mark

Posting Permissions

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