I am trying to update a single record in sql using a recordset I'm Pulling data from one recordset and trying to save it to another table in the database This is the code I have the insert statement runs fine in SQL.
' Opening the connection
cn.ConnectionString = "Provider=SQLOLEDB; Data Source=" & dbLocation & "; Initial Catalog=Posbdat; User Id=sa"
cn.Open
rh.ConnectionString = "Provider=SQLOLEDB; Data Source=" & dbLocation & "; Initial Catalog=Postrans; User Id=sa"
rh.Open
rs.Open "Select Top 1 * from CustomerPoints order by RedemptionDate desc", cn, adModeReadWrite
x.Open " Select Top 1 * from Register_Header order by datetime desc", rh, adModeReadWrite
rt.Open " SELECT top 1 upc FROM Register_Trans INNER JOIN Register_Header ON Register_Trans.trans_no = Register_Header.trans_no Where trans_subtype = 'AP' Order by Register_Trans.datetime desc ", rh, adOpenDynamic
Debug.Print x!emp_no
Debug.Print x!till_no
Debug.Print x.Fields(10)
Debug.Print itemupc
itemupc = rt.Fields(0)
Dim cmd As New ADODB.Recordset
cmd.Open "UPDATE CustomerPoints set emp_no = " & x!emp_no & _
", till_no = " & x!till_no & " purch_amt = " & x!Total & _
", item_redeem = ' " & itemupc & " ' Where RedemptionDate = (Select top 1 * from CustomerPoints order by " & _
"RedemptionDate Desc)", cn, adOpenDynamic
cmd.update
I haven't closed any of the connections or cleaned it up because it won't run without crashing.
It has been a long time but this is how I used to execute update statements:
Dim conTemp As New ADODB.Connection
conTemp.CommandTimeout = mvarconConnection.CommandTimeout
conTemp.ConnectionTimeout = mvarconConnection.ConnectionTimeout
conTemp.CursorLocation = mvarconConnection.CursorLocation
conTemp.Mode = mvarconConnection.Mode
conTemp.ConnectionString = mvarconConnection.ConnectionString
conTemp.Open mvarconConnection.ConnectionString
conTemp.Execute "SET CONCAT_NULL_YIELDS_NULL OFF"
conTemp.Execute "UPDATE CustomerPoints set emp_no = " & x!emp_no & _
", till_no = " & x!till_no & " purch_amt = " & x!Total & _
", item_redeem = ' " & itemupc & " ' Where RedemptionDate = (Select top 1 * from
CustomerPoints order by " & _
"RedemptionDate Desc)"
The mvarconConnection is just an object that stored all of my DB settings, just replace my settings with your own.