INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

ASP writing back to SQL database

ASP writing back to SQL database

(OP)
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

RE: ASP writing back to SQL database

1) You have a syntax error

CODE

strSQL = "INSERT INTO KattCouponCode(fieldname2) Values (SeatstoDB)" 

2) You are not writing to the DB. Add

CODE

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

RE: ASP writing back to SQL database

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 :)

RE: ASP writing back to SQL database

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












RE: ASP writing back to SQL database

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

CODE --> vbscript

StrSQL = "Update KattCouponCode set fieldname2 = " & SeatstoDB 
This assumes fieldname2 is numeric. If it is a string, I would try:

CODE --> vbscript

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close