Asp Error Help

cyberwit

'First we should check if trying to insert
'Only execute the INSERT statement if it has been requested
If Request("submit") = "Insert" Then
sSQL = "INSERT INTO Leave VALUES ('" & Request("PPS") & "', '" & Request("ManagerApproval") & "','" & Request("LeaveType") & "', '" & Request("DateRequested")& "', '" & Request("StartDate")& "', '"& Request("EndDate")& "')"
oConn.Execute(sSQL)
Response.Write("<strong>The Record Added</strong><BR><BR>")
End If


Error with the following the error line 23 oConn.Execute(sSQL)

HTTP 500.100 - Internal Server Error - ASP error
Internet Information Services

---

Technical Information (for support personnel)

Error Type:
Microsoft JET Database Engine (0x80004005)
Operation must use an updateable query.
/Project UI/addleave.asp, line 23

louie

long time since I did asp but I thing you are not supposed to use ' single quotes for inserting date/time into the databse.

cyberwit

Thanks i think it also might have something to do with the IIS installed on my PC i will be checking the code when i go back into collage

DJB

You haven't told it what columns to insert the data into, you need to change it to...

sSQL = "INSERT INTO Leave (col1name, col2name, col3name, col4name, col5name, col6name) VALUES ('" & Request("PPS") & "', '" & Request("ManagerApproval") & "','" & Request("LeaveType") & "', '" & Request("DateRequested")& "', '" & Request("StartDate")& "', '"& Request("EndDate")& "')"

Replace col1name, etc with the actual names of the columns in your database corresponding to the order in the values group.

Also, you should do your requests into variables before sql statement is built and do some manipulation of the data, e.g.

sPPS = Request("PPS")
sManagerApproval = Request("ManagerApproval")
sLeaveType = Request("LeaveType")
sDateRequested = Request("DateRequested")
sStartDate = Request("StartDate")
sEndDate = Request("EndDate")

sSQL = "INSERT INTO Leave (col1name, col2name, col3name, col4name, col5name, col6name) VALUES ('" & sPPS & "', '" & sManagerApproval & "','" & sLeaveType & "', '" & sDateRequested & "', '" & sStartDate & "', '"& sEndDate & "')"

On the variables you have collected, you should do checks for ' and change them to '' for any text fields somebody can type into. Otherwise, anyone that types in an apostrophe will break your code and cause an error.

I'm guessing this is only a college project but... you should run it through a function for cleaning up sql injection. Read more here: http://en.wikipedia.org/wiki/SQL_injection. Not necessary here but you should know about it.

Oh, and dates caused me so many problems when I started out in asp and databases. If you are using MSSQL as your database, you've already got the correct format but to save you hours of headaches (and I mean mixing US/UK date formats up), you should send the dates into the database in this format: '20071206'. If you are using MS Access, it's '#20071206#' I think.

May as well have you running on the right path.

Best of luck,

Dave

Seth Brundle

http://databases.aspfaq.com/database/how-do-i-solve-operation-must-use-an-updateable-query-errors.html

Eoin

DJB wrote: »

You haven't told it what columns to insert the data into, you need to change it to...

sSQL = "INSERT INTO Leave (col1name, col2name, col3name, col4name, col5name, col6name) VALUES ('" & Request("PPS") & "', '" & Request("ManagerApproval") & "','" & Request("LeaveType") & "', '" & Request("DateRequested")& "', '" & Request("StartDate")& "', '"& Request("EndDate")& "')"

Replace col1name, etc with the actual names of the columns in your database corresponding to the order in the values group.

You don't have to specify the column names if you're inserting a value into every column. Not sure if that's standard syntax though.