SQL Server Performance

Trouble with comm.execute

Discussion in 'General DBA Questions' started by rhindp, Feb 11, 2003.

  1. rhindp New Member

    I have experienced a problem using VB6 with SQL 6.5 over NT4.
    Using a stored procedure to update data the .execute fails every second update but when I trap the error and repeat it seems to go OK
    The records are retrieved using selected search criteria
    & held in memory in an array (size depends on search criteria selected). I Only had ten test records.
    I have cleared out the records from the database and started again and now cannot generate the error!Only two records now
    Any ideas
    All other updates from different forms and recordsets work OK using
    .ActiveConnection = conn

    Code snippet
    .....

    With comm
    .ActiveConnection = conn
    .CommandText = strUpdateSQL
    .Execute 'FAILS EVERY SECOND TIME
    Set .ActiveConnection = Nothing
    End With
    End If

    Exit Sub

    Errlabel:
    Select Case Err.Number
    Case 94 ' replaces null value with zero length string in controls
    ctl = ""
    Resume Next
    Case -2147217885, -2147217887, -2147217900
    Resume Next
    Case -2147467259 'updates second time through
    With comm
    .ActiveConnection = conn
    .CommandText = strUpdateSQL
    .Execute
    Set .ActiveConnection = Nothing
    End With
    Exit Sub
    Case Else
    MsgBox Err.Number & " " & Err.Description & Chr(13) & Err.Source
    End Select

    End Sub
  2. Argyle New Member

    What is the error message when the update fails?

    /Argyle
  3. rhindp New Member

    quote:Originally posted by Argyle

    What is the error message when the update fails?

    /Argyle
    Since I cleared out data cannot recreate error as yet.
    Error number is -2147467259
    However source was ODBC Driver
    problem was something to do with TCP/IP on server connection or something like that. I cannot remember exact text.
    I have disabled error handler so when it occurs I will get a screen grab of the message.
    Sorry I cannot be of more help

    Peter R

Share This Page