ADO Updates to SQL Server not Showing, though records affected shows true 
Author Message
 ADO Updates to SQL Server not Showing, though records affected shows true

Having what feels like a newbie problem running a simple UPDATE query
to a SQL Table. I've gone through permissions on the server, corrected
connections, and now I'm writing a totally separate ADO function to
try to write to it, and while I'm seeing the correct amount of records
affected, no errors are being reported, and the data is not getting
updated. The Test code is below, but I'm wondering if there's
something I foobarred up. I had this code running in an earlier
version of the code, and all I've done is move the code to an
ActiveEXE for portability. I'm worried that I may be pounding my head
on a wall, hours wise, so thanks in advance for any help.

Option Explicit

Sub Fubar()
     Dim strConnect As String
     Dim m_conn As New ADODB.Connection
     Dim rs As New ADODB.Recordset
     Dim strSQL As String, lngAff As Long

    'init the database
    strConnect = "driver={SQL Server};" & _
      "server=mmnt_testy;uid=dbuWriter;pwd=shakespeare;database=LWIntranet"
    With m_conn
      .ConnectionString = strConnect
    End With
    'now check the state to make sure we're looking OK
    With m_conn
        'if it's not open, open it
        If .State <> adStateOpen Then .Open
        'now check the state agin to see if we need to return an error
        If .State = adStateOpen Then
            'run the code here
            strSQL = "UPDATE TBL_PDHISTORY " & _
                        "SET tintTranStatus = 2, sdatLastMod='" &
Now() & "' " & _
                        "WHERE vstrCustId = '000-00-0001' AND
tinttranstatus=10 AND vstrStore = '19' " 'AND sdatTranMaturity =
'5/5/2003';"

      'debug code
      Clipboard.SetText strSQL
            m_conn.Execute strSQL, lngAff, adCmdText
            'This shows the expected amount of records (1) from the
test db
            Debug.Print lngAff
            'no errors are being reported here
            If m_conn.Errors.Count <> 0 Then
                Dim adErr As ADODB.Error
                For Each adErr In m_conn.Errors
                    MsgBox adErr.Description
                Next
            End If
        End If
    End With
    'clsoe the recordset
    If rs.State = adStateOpen Then rs.Close
    Set rs = Nothing
    'close the connection
    If m_conn.State = adStateOpen Then m_conn.Close
    Set m_conn = Nothing
End Sub

Now, whe n?i refresh the ASP page I'm using for test reporting, the
status is still 10 despite the code changing it to 2.



Wed, 12 Oct 2005 04:04:52 GMT  
 ADO Updates to SQL Server not Showing, though records affected shows true
Never Mind on the previous post. Had a programmer from another part of the
building locvking the table while he took off for the day. Got to get a new
job, or, at least for tonight, a beer. Thanks for anyone who was looking in
to this.

Slainte!!


Quote:
> Having what feels like a newbie problem running a simple UPDATE query
> to a SQL Table. I've gone through permissions on the server, corrected
> connections, and now I'm writing a totally separate ADO function to
> try to write to it, and while I'm seeing the correct amount of records
> affected, no errors are being reported, and the data is not getting
> updated. The Test code is below, but I'm wondering if there's
> something I foobarred up. I had this code running in an earlier
> version of the code, and all I've done is move the code to an
> ActiveEXE for portability. I'm worried that I may be pounding my head
> on a wall, hours wise, so thanks in advance for any help.

> Option Explicit

> Sub Fubar()
>      Dim strConnect As String
>      Dim m_conn As New ADODB.Connection
>      Dim rs As New ADODB.Recordset
>      Dim strSQL As String, lngAff As Long

>     'init the database
>     strConnect = "driver={SQL Server};" & _

"server=mmnt_testy;uid=dbuWriter;pwd=shakespeare;database=LWIntranet"

- Show quoted text -

Quote:
>     With m_conn
>       .ConnectionString = strConnect
>     End With
>     'now check the state to make sure we're looking OK
>     With m_conn
>         'if it's not open, open it
>         If .State <> adStateOpen Then .Open
>         'now check the state agin to see if we need to return an error
>         If .State = adStateOpen Then
>             'run the code here
>             strSQL = "UPDATE TBL_PDHISTORY " & _
>                         "SET tintTranStatus = 2, sdatLastMod='" &
> Now() & "' " & _
>                         "WHERE vstrCustId = '000-00-0001' AND
> tinttranstatus=10 AND vstrStore = '19' " 'AND sdatTranMaturity =
> '5/5/2003';"

>       'debug code
>       Clipboard.SetText strSQL
>             m_conn.Execute strSQL, lngAff, adCmdText
>             'This shows the expected amount of records (1) from the
> test db
>             Debug.Print lngAff
>             'no errors are being reported here
>             If m_conn.Errors.Count <> 0 Then
>                 Dim adErr As ADODB.Error
>                 For Each adErr In m_conn.Errors
>                     MsgBox adErr.Description
>                 Next
>             End If
>         End If
>     End With
>     'clsoe the recordset
>     If rs.State = adStateOpen Then rs.Close
>     Set rs = Nothing
>     'close the connection
>     If m_conn.State = adStateOpen Then m_conn.Close
>     Set m_conn = Nothing
> End Sub

> Now, whe n?i refresh the ASP page I'm using for test reporting, the
> status is still 10 despite the code changing it to 2.



Wed, 12 Oct 2005 07:47:45 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. ADO - NEXT RECORD does not show right picture

2. Grand total shows ALL records, not SELECTED records

3. HELP: WhatsThisButton not show in form when MDIChild is True

4. Subreport is not showing when main Report does not have records

5. HELP: WhatsThisButton not show in form when MDIChild is True

6. client display setting affect the way Controls show ?

7. last record not shown

8. Reading via ODBC, direct updates do not show up

9. Query will not show all records in a combo box

10. Record Not Showing Up in FindFirst

11. newbie - not all records show in recordset

12. Do Not Show Update Linked Information Box

 

 
Powered by phpBB® Forum Software