How to update a database in IIS 5.0?

NightWatcher · Jul 8, 2001

Hello everybody..

I have been using for the past year or so, Xitami web server with iASP on it, and all ASP scripts worked fine. But, I have now choose to get IIS 5.0, for stability reasons, and the ASP scripts that update the databases no longer work.. They show the records, but the update causes an error. I have been looking for examples of the correct expression to use and have seen/tested many but still dont work. Can someone please drop here the correct SQL expression to update a database in IIS 5.0? And also can someone tell me the main differences in SQLing there are btw IIS and other web servers?

Thank you in advance.

Ovatvvon · Jul 8, 2001

are you useing Microsoft Access, or SQL Server for your databases? I believe that's what plays the major roll in the miniscule differences in the sql statements.

Believe the correct UPDATE statement for sql should be:

sql = "UPDATE myTable SET myTable.firstFieldName='" & stringValue & "',myTable.secondFieldName=" & integerValue & ",myTable.thirdFieldValue='" & anotherStringValue & "' WHERE ((myTable.firstFieldName)='George');"

string variables need to have appostrophies on both sides of it, integers do not need this. Need to tell it where to update your update in the database. That, to the best of my knowledge, is a correct sql statement. Also, you need to look at the error you're receiveing. What does it say? Work with it. If you can't figure it out, try printing the sql statment to the page...

<%
Dim sql
sql = "SELECT * FROM myTable WHERE ((myTable.field1)='" & someVariable & "');"
response.write(sql)
response.end
%>

IF this doesn't work, try printing the sql statment to the page, let me know what that says, and what your error is.
-Ovatvvon :-Q

jasonsalas · Jul 8, 2001

Here's another way...not exactly SQL-specific, but relies on ASP 3.0 code to carry out the database UPDATE command in the same manner, using the "Find" method to locate the specific record to be modified, and then the "Update" method of the ADODB.Recordset object to carry out the request. So functionally, it's completely the same. It just swaps out old values with new ones.

This is a script which modifies existing records in ASP 3.0...can be applied to your situation for IIS 5.0.

================== MODIFY.ASP ========================
<html>
<head>
<title>Modify a record in the database</title>
<form action=process.asp method=post>
<p>
<input type="text" name="RecordID" size="20"><br>
Enter your record's ID</p>
<p><input type="text" name="Title" size="20"><br>
Enter your record's new TITLE</p>
<p><input type="text" name="Author" size="20"><br>
Enter your record's new AUTHOR</p>
<p><input type="text" name="Description" size="20"><br>
Enter your record's new description</p>
<p> </p>
<p><input type="submit" value="Submit"><input type="reset" value="Reset"></p>
</form>
</body>
</html>

================== PROCESS.ASP ===========================
<% @ Language = VBScript %>
<html>
<head>
<title>Your record has been update!</title>
</head>
<body>
<h1>Congratulations!  Your record has been updated</h1>
<br>
<hr>
<%
Dim oRS
Set oRS=Server.CreateObject ("ADODB.Recordset&quot

oRS.Open "YOURTABLENAMEHERE", "DSN=dsn", adOpenKeyset, adLockOptimistic
oRS.Find "RecordID=" & Request.Form("RecordID&quot

If oRS.EOF then
Response.Write "<p>This record does not exist in the database.</p>"
Else
Response.Write "<p>This record was updated from:</p>" & "<p>Old Title: " _
& oRS.Fields("Title&quot

& "<br>Old Reporter: " & oRS.Fields("ReporterID&quot

& "</p>"
oRS.Fields("Title&quot

=Request.Form("newTitle&quot

oRS.Fields("Author&quot

=Request.Form("newAuthor&quot

oRS.Fields("Description&quot

=Request.Form("newDescription&quot

oRS.Update
Response.Write "<p>The record has been updated as follows:</p>" & "<p>New Title: " _
& "<B>" & oRS.Fields("Title&quot

& "</b>" & "<br>New Reporter: " & "<b>" & oRS.Fields("Author&quot

& "</b>" & "</p>"
End If
oRS.Close
%>
</body>
</html>

NightWatcher · Jul 8, 2001

Thank you guys for the prompt reply, but still doesn't work.
Here's the error generated, by my detailed error reporting:

Database Errors:
----------------
SQL Expression: UPDATE tblPASSES SET dbCOMPANY='data', dbUSERNAME='data', dbPASSWORD='data', dbURL='data', dbOBS='data' WHERE dbID=30

Error Number: -2147467259

Error Descr: Operation must use an updateable query.

------------------------- END ---------------------

And here is the code I use (VBScript):

Dim Conn, RS, fileDB, SQL
fileDB = Server.MapPath("dbPASSES.mdb&quot

Set Conn = Server.CreateObject("ADODB.Connection&quot

Conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & fileDB

SQL = "UPDATE tblPASSES SET "
SQL = SQL & "dbCOMPANY='" & Request.Form("txtCOMPANY&quot

& "', "
SQL = SQL & "dbUSERNAME='" & Request.Form("txtUSERNAME&quot

& "', "
SQL = SQL & "dbPASSWORD='" & Request.Form("txtPASSWORD&quot

& "', "
SQL = SQL & "dbURL='" & Request.Form("txtURL&quot

& "', "
SQL = SQL & "dbOBS='" & Request.Form("txtOBS&quot

& "' "
SQL = SQL & "WHERE dbID=" & Request.Form("txtID&quot

Set RS = Conn.Execute(SQL)

------------------------- END ---------------------

What's wrong with the code?
Also used the 'Ovatvvon' approach, of no avail.

I'm still evaluating 'jasonsalas' code.

Please help.

Ovatvvon · Jul 8, 2001

Operation must use an updateable query.

This is not an IIS problem. Nor is it a database problem. It is your sql and database connection problem.

First of all, are you using Microsoft Access, or SQL Server?
If you are using Microsoft Access, then try the following connection:

Set conn = Server.CreateObject("ADODB.Connection&quot

DSNtemp = "DRIVER={Microsoft Access Driver(*.mdb)};"
DSNtemp = DSNtemp & "DBQ=" & server.Mappath("db/dbPASSES.mdb&quot

& ";"
conn.Open DSNtemp

Also, try two other things...since it appears as though that is not the 'actual' print out of the error message that you put up here, then just try making the sql statement all in one line rather than several lines, to make sure you didn't make a mistake...not gonna hurt to try. And, enter the table name in front of the fields during the SET portion of the sql statement. So it is as follows:

SQL = "UPDATE tblPASSES SET tblPASSES.dbCOMPANY='" & Request.Form("txtCOMPANY&quot

& "', tblPASSES.dbUSERNAME='" & Request.Form("txtUSERNAME&quot

& "', tblPASSES.dbPASSWORD='" & Request.Form("txtPASSWORD&quot

& "', tblPASSES.dbURL='" & Request.Form("txtURL&quot

& "', tblPASSES.dbOBS='" & Request.Form("txtOBS&quot

& "' WHERE ((tblPASSES.dbID)=" & Request.Form("txtID&quot

& &quot

;"

Hope this helps!
-Ovatvvon :-Q

TechnicalAnalysis · Jul 9, 2001

I'd think the folder that contains the database hasn't been set with the write permission. It's the IIS configuration instead of the syntax of the code.
If none of the above works.

NightWatcher · Jul 9, 2001

Guys, still can't update.

Ovatvvon: I'm using MSAccess not SQLServer, and used your new connection which actually does not causes an error but doesn't update either, but we are close, I can smell it

.
I have also relooked the SQL statment very carefully and redone/use your suplied one, with no effect.
I have also noticed that you use a ; at the end od statments, does it matters? Coz ASP is by default VBScript. Either way I tryed with/without it, and nothing.

TechnicalAnalysis: I have set the 'db' folder with websharing permissions, but don't know which 'alias' to use for the share.. Please advice, on the right procedures for this operation.

Thank you.

NightWatcher · Jul 9, 2001

Hehey.. It's working now..

It was the permissions in the MDB file and not in the folder, but one thing that amazes me is that it worked with the original code without any modification..

Thank you guys, for all your help.

NightWatcher

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

How to update a database in IIS 5.0?

NightWatcher

Programmer

Ovatvvon

Programmer

jasonsalas

IS-IT--Management

NightWatcher

Programmer

Ovatvvon

Programmer

TechnicalAnalysis

Programmer

NightWatcher

Programmer

NightWatcher

Programmer

Similar threads

Part and Inventory Search

Sponsor