Classic ASP Discussion

Print Page | Close Window

SQL Syntax

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=5980
Printed Date: 31 March 2026 at 8:41am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Syntax

Posted By: twooly
Subject: SQL Syntax
Date Posted: 25 September 2003 at 7:59pm

Having some troubles and wandering if someone could point me in the right direction. I can trying to update a row with values from a form

The problem is I want the value of strXXXX and not the string "strXXX"

strFirstName = Request.Form("txtFirstName")
strLastName = Request.Form("txtLastName")
strHomepage = Request.Form("txtHomepage")
strAim = Request.Form("txtAim")
strLocation = Request.Form("txtLocation")
strInterests = Request.Form("txInterests")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "UPDATE firstname, lastname VALUES ('strFirstName', 'strLastName') WHERE ID =" & userID

Replies:

Posted By: KCWebMonkey
Date Posted: 25 September 2003 at 9:55pm

You aren't using the UPDATE statement right, check here: http://www.w3schools.com/sql/sql_update.asp - http://www.w3schools.com/sql/sql_update.asp

Posted By: 3BEPb
Date Posted: 26 September 2003 at 12:00am

strSQL = "UPDATE table_name set table_name.firstname='strFirstName', table_name.lastname'strLastName') WHERE ID =" & userID

Posted By: twooly
Date Posted: 26 September 2003 at 9:12am

I am still having troubles. When I hit enter on my form this is what I get

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC Microsoft Access Driver] Operation must use an updateable query.

/updateprofile.asp, line 37

Code: - Line 37 is marked below

<%
Dim strUserName 'Holds the name of the user
Dim userID
Dim adoCon         'Holds the Database Connection Object
Dim rsViewProfile   'Holds the recordset for the records in the database
Dim strSQL          'Holds the SQL query to query the database

'Get the users name passed from the previous page
strUserName = Session("userName")

userID = Session("userID")

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("toddwoolums_site.mdb")

'Initialise the strSQL variable with an SQL statement to query the database
strSQL = "UPDATE tblUsers SET tblUsers.firstname='strFirstName', tblUsers.lastname='strLastName', tblUsers.homepage='strHomepage', tblUsers.aim='strAim', tblUsers.location='strLocation', tblUsers.interests='strInterests' WHERE ID =" & userID

'Open the recordset with the SQL query
Set rsEditProfile = adoCon.Execute(strSQL) --Line 37

'Reset server objects
rsEditProfile.Close
Set rsEditProfile = Nothing
Set adoCon = Nothing
%>

Posted By: twooly
Date Posted: 26 September 2003 at 9:58am

OK I got the other error fixed. I didn't have the correct permissions on the database. Now I am getting this

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'ID ='.

/updateprofile.asp, line 37

Posted By: twooly
Date Posted: 26 September 2003 at 10:02am

Yet again I found out what that error was. My session variable needed to me passed to the next page. It almost works the way I want it too. The only problem now is that it passes "strFirstName" and not the actual value of the variable. How can I get it to update with the variable value.

Code

'Get the users name passed from the previous page
strUserName = Session("userName")

userID = Session("userID")

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("toddwoolums_site.mdb")

'Create an ADO recordset object
'Set rsEditProfile = Server.CreateObject("ADODB.Recordset")

'Open the recordset with the SQL query
'rsEditProfile.Open strSQL, adoCon
Set rsEditProfile = adoCon.Execute(strSQL)

'Reset server objects

Set rsEditProfile = Nothing
Set adoCon = Nothing
%>

Posted By: KCWebMonkey
Date Posted: 26 September 2003 at 10:21am

strSQL = "UPDATE tblUsers SET tblUsers.firstname='" & strFirstName & "', tblUsers.lastname='" & strLastName & "', tblUsers.homepage='" & strHomepage & "', tblUsers.aim='" & strAim & "', tblUsers.location='" & strLocation & "', tblUsers.interests='" & strInterests & "' WHERE ID =" & userID