by 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.