ASP writing back to SQL database

[aapk]

I am having troubles finding the right syntax to write a variable back to my SQL database to update entries. Basically I need to put the result of "SeatstoDB" into the "fieldname2" spot on the DB. Any help would be sooo appreciated.

Here's part of the code I have written so far:

Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open connectstr

qry = "SELECT * FROM KattCouponCode"

Set oRS = oConn.Execute(qry)

if not oRS.EOF then
while not oRS.EOF
CodefromDB = oRs.Fields(fieldname)
SeatsfromDB = oRs.Fields(fieldname2)
OutMessage = oRs.Fields(fieldname3)
ToURL = oRS.Fields(fieldname4)

If Code = CodefromDB and SeatsfromDB => 26 then
strURL = ToURL
response.write "<span style=""font-family: Arial,Helvetica,Geneva,Sans-serif; font-size: 24px;"">" & "<a href=""" & "

http://www.katttraining.net/test/"

& strURL & """ target=_blank>" & "Click here to take the test" & "</a>" & "</span>" & "<br>"
SeatstoDB = SeatsfromDB - 1
strSQL = "INSERT INTO KattCouponCode(fieldname2) Value (SeatstoDB)"
response.write (SeatstoDB)

else if Code = CodefromDB and DBSeatsfromDB < 20 then
response.write "<span style=""font-family: Arial,Helvetica,Geneva,Sans-serif; font-size: 24px;"">" & (OutMessage) & "</span>"
End If

End If
oRS.movenext

wend

oRS.close

end if
Set oRs = nothing
Set oConn = nothing

 

[xwb]

1) You have a syntax error

strSQL = "INSERT INTO KattCouponCode(fieldname2) Value[COLOR=red]s[/color] (SeatstoDB)"

2) You are not writing to the DB. Add

oConn.Execute sqlstr

If you switch on debugging on IE, it will tell you where the error is.

 

[gk53]

I'm not sure what you are rtrying to achive, but your code does not make sense for me.
1. You open database and read all records from table KattCouponCode into forward only, read only recordset
2. Loop thruw recordset and retrieve and print in table 3 fields.
3. inside the loop when retieving values you trying to insert record with query
strSQL = "INSERT INTO KattCouponCode(fieldname2) Value (SeatstoDB)", table which you have open and only one value... what will be in fieldname and fieldname3?
4 and xwb already pointed you are not executing sql statement, which I think will fail,
5. looking on line SeatstoDB = SeatsfromDB - 1 i can gess you may be what to update record whti new number, but it is not insert

 

[Dashley]

XWB is correct the SQL statement is wrong. Also like gk53 says, you should be doing an update to the field.

StrSQL = "Update KattCouponCode set fieldname2 = SeatstoDB"

--this staement below sets the mycmd1 command text and connection
Dim mycmd1 As New OleDb.OleDbCommand(strSQL , oConn)

--or you can do

With mycmd1
.connection - oconn
.commandtext = strSql
end with

--An update is a nonquery which is what you want to use.

--open your connection

mycmd1.executenonquery

You could say "Dim cmdsuccess as integer = 0" and then do

cmdsuccess = mycmd1.executenonquery

if cmdsuccess = 0 then
--you have a problem
--close connection
else
--close connection
--your good to go
end if


-good luck

 

[dhookom]

I expect these statements to error out since "SeatstoDB" is a variable. I would write the statement like:

StrSQL = "Update KattCouponCode set fieldname2 = " & SeatstoDB

This assumes fieldname2 is numeric. If it is a string, I would try:

StrSQL = "Update KattCouponCode set fieldname2 = '" & SeatstoDB & "'"

This would set the value of fieldname2 in every record in KattCouponCode which seems odd to me.

Duane
Hook'D on Access
MS Access MVP