Ok, I've created a page that pulls info from a data base and inserts it into a form. Now how do I go about updating the data base when they click update? Code can be viewed here..
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!
-
Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
updating a database
- Thread starter help120
- Start date
- Thread starter
- #2
an update sql statement is as such:
myTable = the tables name in the database
fPK = primary key field in the database
fOne = first fields name in the database
fTwo = second fields name
fThree = third fields name
sql = "UPDATE myTable SET myTable.fOne="John", myTable.fTwo="Deer", myTable.fThree="Farming Provider" WHERE ((myTable.fPK)=1);"
Hope this helps. -Ovatvvon :-Q
myTable = the tables name in the database
fPK = primary key field in the database
fOne = first fields name in the database
fTwo = second fields name
fThree = third fields name
sql = "UPDATE myTable SET myTable.fOne="John", myTable.fTwo="Deer", myTable.fThree="Farming Provider" WHERE ((myTable.fPK)=1);"
Hope this helps. -Ovatvvon :-Q
- Thread starter
- #4
Page1.asp
------------------------------------
<form action='page2.asp'>
First Name: <input type='text' name='firstName'><BR>
Last Name: <input type='text' name='lastName'><BR>
Career Type: <input type='text' name='careerType'><BR>
<BR>
<input type='submit' value='Submit'><input type='reset' value='Reset'><BR>
</form>
Page2.asp
------------------------------------
<%
Dim fName, lName, cType, sql
fName = Request.QueryString("firstName"
lName = Request.QueryString("lastName"
cType = Request.QueryString("careerType"
sql = "UPDATE myTable SET myTable.fName='" & fname & "', myTable.lName='" & lName & "', myTable.cType='" & cType & "' WHERE ((myTable.userPK)=1);"
' Make Database connection here and then close it after executeing the sql statement.
%>
<BR>
Update Successful!
You are working with a table named myTable in your database. You also have 3 fields in it, fName, lName, and cType. You already have a record that you inserted into the database from another page...that is the first record in the table. You are now going to update it from the sample I gave you. First page has the form that the user is going to upgrade their first and last names, and their career type. The second page will process it and enter it into the database.
Hope This Helps.
-Ovatvvon :-Q
- Thread starter
- #6
ok this is what I've got so fare..
----------First Page---------------page1.asp
<%@ Language=VBScript %>
<html>
<head>
<title>Name</title>
</head>
<body>
<!--#INCLUDE FILE="inc_top.asp" -->
<%
dim MyConn, sql, Name, csdod, aim, icq, email
%>
<!--#INCLUDE FILE="config.asp" -->
<%
Set RS=Server.CreateObject("ADODB.RecordSet"
Set RS2=Server.CreateObject("ADODB.RecordSet"
RS.Open "Select * From members", MyConn %>
<% Do While Not RS.eof %>
<form action='page2.asp'>
Name: <input type='text' name='Name' size="20" value="<% = RS("Name"%>"><BR>
aim: <input type='text' name='aim' size="20" value="<% = RS("aim"%>"><BR>
icq: <input type='text' name='icq' size="20" value="<% = RS("icq"%>"><BR>
<BR>
<input type='submit' value='Submit'><input type='reset' value='Reset'><BR>
</form>
<%
RS.movenext
Loop %>
<%
RS.close
MyConn.close
%>
</body>
</html>
----------2nd page---------------------page2.asp
<html>
<head>
<title>page2</title>
</head>
<body>
<%
Dim mName, mAIM, mICQ, sql
mName = Request.QueryString("Name"
mAIM = Request.QueryString("aim"
mICQ = Request.QueryString("icq"
sql = "UPDATE members SET members.mName='" & mname & "', members.mAIM='" & mAIM & "', members .mICQ='" & mICQ & "' WHERE ((members.userPK)=1);"
' Make Database connection here and then close it after executeing the sql statement.
Set MyConn = Server.CreateObject("ADODB.Connection"
MyConn.ConnectionString = "DBQ=C:\inetpub\finalphase\databases\nps\roster.mdb;DRIVER={Microsoft Access Driver (*.mdb)}"
MyConn.Open
sql = "UPDATE members (name,aim,icq) values("
sql = sql & "'" & mName & "',"
sql = sql & "'" & maim & "',"
sql = sql & "'" & micq & "',"
'execute first statement
myConn.execute sql
set myConn = nothing
%>
<BR>
Update Successful!
</body>
</html>
--------------------------end-----------------------
This is the error I'm getting...
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/nps/forum/roster/page2.asp, line 27
----------First Page---------------page1.asp
<%@ Language=VBScript %>
<html>
<head>
<title>Name</title>
</head>
<body>
<!--#INCLUDE FILE="inc_top.asp" -->
<%
dim MyConn, sql, Name, csdod, aim, icq, email
%>
<!--#INCLUDE FILE="config.asp" -->
<%
Set RS=Server.CreateObject("ADODB.RecordSet"
Set RS2=Server.CreateObject("ADODB.RecordSet"
RS.Open "Select * From members", MyConn %>
<% Do While Not RS.eof %>
<form action='page2.asp'>
Name: <input type='text' name='Name' size="20" value="<% = RS("Name"
%>"><BR>aim: <input type='text' name='aim' size="20" value="<% = RS("aim"
%>"><BR>icq: <input type='text' name='icq' size="20" value="<% = RS("icq"
%>"><BR><BR>
<input type='submit' value='Submit'><input type='reset' value='Reset'><BR>
</form>
<%
RS.movenext
Loop %>
<%
RS.close
MyConn.close
%>
</body>
</html>
----------2nd page---------------------page2.asp
<html>
<head>
<title>page2</title>
</head>
<body>
<%
Dim mName, mAIM, mICQ, sql
mName = Request.QueryString("Name"
mAIM = Request.QueryString("aim"
mICQ = Request.QueryString("icq"
sql = "UPDATE members SET members.mName='" & mname & "', members.mAIM='" & mAIM & "', members .mICQ='" & mICQ & "' WHERE ((members.userPK)=1);"
' Make Database connection here and then close it after executeing the sql statement.
Set MyConn = Server.CreateObject("ADODB.Connection"
MyConn.ConnectionString = "DBQ=C:\inetpub\finalphase\databases\nps\roster.mdb;DRIVER={Microsoft Access Driver (*.mdb)}"
MyConn.Open
sql = "UPDATE members (name,aim,icq) values("
sql = sql & "'" & mName & "',"
sql = sql & "'" & maim & "',"
sql = sql & "'" & micq & "',"
'execute first statement
myConn.execute sql
set myConn = nothing
%>
<BR>
Update Successful!
</body>
</html>
--------------------------end-----------------------
This is the error I'm getting...
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in UPDATE statement.
/nps/forum/roster/page2.asp, line 27
Right after the update statement...right below it, write this:
response.write(sql)
response.end
This will print out the sql statement right after it's been built, and then stop the page from loading any further. Then you can see on the page What the actual sql statement looks like to the server. It's possible (and I would think this is the most likely situation) that one of the variables is not passing a value...I'll bet you're trying to set a value for a field, but within the sql statement it's blank.
Try this and let me know. -Ovatvvon :-Q
response.write(sql)
response.end
This will print out the sql statement right after it's been built, and then stop the page from loading any further. Then you can see on the page What the actual sql statement looks like to the server. It's possible (and I would think this is the most likely situation) that one of the variables is not passing a value...I'll bet you're trying to set a value for a field, but within the sql statement it's blank.
Try this and let me know. -Ovatvvon :-Q
krappleby025
Programmer
ok once you have opened a table for update you do not need to mention the table name again...
ie
sql = "UPDATE members SET mName='" & mname & "', mAIM='" & mAIM & "', ICQ='" & mICQ & "' WHERE ((members.userPK)=1);"
and also shouldnt icq be only a number which if it is so then you shouldnt need the ' before and after
ie
sql = "UPDATE members SET mName='" & mname & "', mAIM='" & mAIM & "', ICQ='" & mICQ & "' WHERE ((members.userPK)=1);"
and also shouldnt icq be only a number which if it is so then you shouldnt need the ' before and after
It is true you do not NEED the table named over and over again in the sql statement, however, that means the server will have to figure out which table it is in if you are useing (in this...which your not...or in any other sql statement) multiple tables within the same select statement, or whatever. If the server has to figure it out, it moves that much slower. The more specific you get, the better performance you'll have.
I don't use ICQ, don't know how they handle stuff. If all it is, is a number, then you won't need the appostrophes, however that may only be in the case if the field is declared as a numeral field within the database...don't recall. If all you have is a number though, and it's not a string, then yes, you don't need the appostrophe's.
-Ovatvvon :-Q
I don't use ICQ, don't know how they handle stuff. If all it is, is a number, then you won't need the appostrophes, however that may only be in the case if the field is declared as a numeral field within the database...don't recall. If all you have is a number though, and it's not a string, then yes, you don't need the appostrophe's.
-Ovatvvon :-Q
- Status
Similar threads
- Locked
- Question
- Locked
- Question