Update field in recordset from SQL 1

[Kate17]

Hi,

I have a program that generates Crystal reports. I want to keep track of the reports and when they were generated so I have a reports table and a reports history table (in SQL). My code adds a new record to the history table for every report, but what I also need is for a field in the reports table to be edited to contain the current report filename. My code for adding a record is as follows:

Dim objConn As New ADODB.Connection
Dim objRS As New ADODB.Recordset
Dim objErr As ADODB.Error

objConn.Open "Driver=SQL Server;Server=xxx;Database=xxx;uid=xxx;password=xxx"

objConn.BeginTrans

objRS.Open "Select * from ReportsHistory", objConn, adOpenStatic, adLockPessimistic

objRS.AddNew
objRS.Fields("ReportID&quot = txtTPID.Text
objRS.Fields("Gen_Date&quot = Date
objRS.Fields("Report_Name&quot = txtTPID.Text & txtMonth.Text
objRS.Update

If MsgBox("Are you sure?", vbYesNo + vbQuestion) = vbYes Then
objConn.CommitTrans
Else
objConn.RollbackTrans
End If

This works fine, but how do I edit a record in a database, not just add a new record?

Thanks very much, Kate

 

[sunaj]

Hi,

Use the .execute method of the connection object to run an UPDATE statement. E.g.

--------------------------------------------------------
ObjConn.Execute "UPDATE tblMytable SET MyField='test' WHERE Surname='sunaj'"
-------------------------------------------------------- Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Kate17]

Hi Sunaj,

I have edited your code as follows:

objConn.Execute "UPDATE Reports SET ReportNumber=fileName WHERE ReportID=ReportID"

Where fileName and ReportID are variables.

When I run the code, it says invalid column name "fileName"

Do you have any ideas? Kate

 

[sunaj]

Hi Kate,

You must create the command string that you send to the dbening from text and variables. I can't give you the exact synax unless you post you declarations and field definitions, but it should look something like:

-------------------------------------------------------
objConn.Execute "UPDATE Reports SET ReportNumber=" & FileName & " WHERE ReportID=" & Cstr(ReportID)
-------------------------------------------------------
Sunaj
'The gap between theory and practice is not as wide in theory as it is in practice'

 

[Kate17]

Sunaj,

Thanks for that - got it working (almost - just a couple of tweaks to pickup the right recordset!!)

Really appreciate your help with this! Kate