baxtalo
asked on
How to update records from two different tables?
I would like to update records stored in two different tables in an Access database. This is the asp page that retreives the info from the database:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
What I want to do is click one record that takes me a page where where only that record is displayed, then update it. Can anyone please help?
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
What I want to do is click one record that takes me a page where where only that record is displayed, then update it. Can anyone please help?
ASKER
Thank you for your response. Right now I'm away from my computer but I will try it in about three hours from now. My problem is that my FirstName and LastName fields are in the table named First_Table.
My Address and PhoneNumber fields are in the table named Second_Table. I would like to update all fields at the same time, with one single click. Of course this is a simplified version of huge form I'm working with which has almost 500 fields. This is why I had to store the information in two tables. Access doesn't let me add more that 255 fields in one table. Thank you again, I'll be back shortly.
My Address and PhoneNumber fields are in the table named Second_Table. I would like to update all fields at the same time, with one single click. Of course this is a simplified version of huge form I'm working with which has almost 500 fields. This is why I had to store the information in two tables. Access doesn't let me add more that 255 fields in one table. Thank you again, I'll be back shortly.
Then the extension should be obvious - build two separate UPDATE statements, one for each table.
Since the ID fields are the same, this should be easy.
Since the ID fields are the same, this should be easy.
ASKER
I'm afraid I still have problems. Up to this point it works well:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select First_Table.ID, Second_Table.ID, FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
<th>Update Entry</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
<td><a href="UpdateData.asp?key=<
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
WHEN I MOVE THE MOUSE OVER THE 'Update' LINK, MY BROWSER'S STATUS BAR SHOWS THE RIGHT ADDRESS: UpdateData.asp?key=1
WHEN I CLICK IT, I GET AN ERROR MESSAGE:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
I'M SURE THE PROBLEM IS WITH MY SQL. THIS IS HOW MY UpdateData.asp looks:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE ID = " & myId" "
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=Trim(rstData("Fi
</td><input type="TEXT" name="LastName" size="30" value="<%=Trim(rstData("La
</td><input type="TEXT" name="Address" size="30" value="<%=Trim(rstData("Ad
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=Trim(rstData("Ph
</table>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
WOULD YOU PLEASE CHECK FOR ME WHERE THE PROBLEM IS?
Thank you very much.
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select First_Table.ID, Second_Table.ID, FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
<th>Update Entry</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
<td><a href="UpdateData.asp?key=<
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
WHEN I MOVE THE MOUSE OVER THE 'Update' LINK, MY BROWSER'S STATUS BAR SHOWS THE RIGHT ADDRESS: UpdateData.asp?key=1
WHEN I CLICK IT, I GET AN ERROR MESSAGE:
Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
I'M SURE THE PROBLEM IS WITH MY SQL. THIS IS HOW MY UpdateData.asp looks:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE ID = " & myId" "
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=Trim(rstData("Fi
</td><input type="TEXT" name="LastName" size="30" value="<%=Trim(rstData("La
</td><input type="TEXT" name="Address" size="30" value="<%=Trim(rstData("Ad
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=Trim(rstData("Ph
</table>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
WOULD YOU PLEASE CHECK FOR ME WHERE THE PROBLEM IS?
Thank you very much.
Missing ampersand after myID in the select statement. Might be others.
What I do for troubleshooting is add a bunch of debug "write" lines,which usually tells me how far it gets.
ASKER
Thank you, we are getting somewhere now. My UpdateData.asp seems to be perfect now. It extracts the right info from my tables and this is how it looks:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=rs("FirstName")%
</td><input type="TEXT" name="LastName" size="30" value="<%=rs("LastName")%>
</td><input type="TEXT" name="Address" size="30" value="<%=rs("Address")%>"
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=rs("PhoneNumber"
</table>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
NOW I'M HAVING PROBLEMS WITH THE LAST STEP, THE UpdateData2.asp. I'M TRYING TO FIX IT, IF I CAN'T I'LL GET BACK TO YOU. YOU ARE VERY HELPFUL, THANK YOU VERY MUCH.
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=rs("FirstName")%
</td><input type="TEXT" name="LastName" size="30" value="<%=rs("LastName")%>
</td><input type="TEXT" name="Address" size="30" value="<%=rs("Address")%>"
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=rs("PhoneNumber"
</table>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
NOW I'M HAVING PROBLEMS WITH THE LAST STEP, THE UpdateData2.asp. I'M TRYING TO FIX IT, IF I CAN'T I'LL GET BACK TO YOU. YOU ARE VERY HELPFUL, THANK YOU VERY MUCH.
ASKER
What does the "application/x-www-form-ur
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
ASKER
I am sorry, but I'm still having problems with the UpdateData2.asp
I don't get any error message after I submit the form, but the info is not updated. This if how my UpdateData2.asp looks:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
Would you please have a look at it?
I don't get any error message after I submit the form, but the info is not updated. This if how my UpdateData2.asp looks:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
Would you please have a look at it?
ASKER
I'm playing with it but nothing seems to be working. This is my UpdateData2.asp page now:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE ID = " & myId
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
You are building the statements, but I do not see anything that causes the statement to execute.
Have you omitted this from your post, or did you forget to submit the statements?
Be sure to execute the two statements separately -- I think Access nay have a problem with submitting the two statements together.
Have you omitted this from your post, or did you forget to submit the statements?
Be sure to execute the two statements separately -- I think Access nay have a problem with submitting the two statements together.
ASKER
I did not omit anything from my post. How do I have to execute the statements? I'm an absolute beginner, would you please guide me through?
You did it in your very first post, so I thought you knew how.
AdoCon.Execute(StrSQL)
ASKER
Now I added AdoCon.Execute(StrSQL) and I get this error message after I submit:
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'First_Table.ID ='.
UpdateData2.asp, line 14
THE COMPLETE CODE OF MY UpdateData2.asp IS:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "WHERE First_Table.ID = " & myId
adoCon.Execute(sql)
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE Second_Table.ID = " & myId
adoCon.Execute(sql)
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
I'M SURE I'M ADDING SOME EXTRA STUFF TO THE SQL, BUT I DON'T KNOW WHAT. I TRIED EVERYTHING, IT'S PRETTY FRUSTRATING. DO YOU HAVE ANY CLUES WHAT'S WRONG WITH THE CODE ABOVE?
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'First_Table.ID ='.
UpdateData2.asp, line 14
THE COMPLETE CODE OF MY UpdateData2.asp IS:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "UPDATE First_Table SET "
StrSQL = StrSQL & "FirstName = '" & myFirstName & "' "
StrSQL = StrSQL & "LastName = '" & myLastName & "' "
StrSQL = StrSQL & "WHERE First_Table.ID = " & myId
adoCon.Execute(sql)
StrSQL = "UPDATE Second_Table SET "
StrSQL = StrSQL & "Address = '" & myAddress & "' "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
StrSQL = StrSQL & "WHERE Second_Table.ID = " & myId
adoCon.Execute(sql)
'Redirect to the UPDATED page
Response.Redirect "TwoTablesAllData.asp"
%>
I'M SURE I'M ADDING SOME EXTRA STUFF TO THE SQL, BUT I DON'T KNOW WHAT. I TRIED EVERYTHING, IT'S PRETTY FRUSTRATING. DO YOU HAVE ANY CLUES WHAT'S WRONG WITH THE CODE ABOVE?
ASKER
Thank you very much, I fixed it. I just had to replace myId = Request.QueryString("ID") with myId = Request.form("ID")
Now it works perfectly. Without you I couldn't have soleved my problem, I am very grateful.
All the best,
baxtalo
Now it works perfectly. Without you I couldn't have soleved my problem, I am very grateful.
All the best,
baxtalo
ASKER
This question hasn't been replied completely, but it was very helpful so far. I didn't want to close it, i just clicked the wrong button.
ASKER
I did not mean to close this question, I just clicked the wrong button by accident. The reply was helpful, but not complete. I still couldn't solve my problem, but I'm working on it.
ASKER
What do you think the problem is with this page. I'm trying to update records in two different tables.
FirstName and LastName are in First_Table
Address and PhoneNumber are in Second_Table
When I click submit, the error message I get is: Syntax error in UPDATE statement. Please have a look at the code below. What do you think the problem is?
FirstName and LastName are in First_Table
Address and PhoneNumber are in Second_Table
When I click submit, the error message I get is: Syntax error in UPDATE statement. Please have a look at the code below. What do you think the problem is?
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("database\TwoTables.mdb") & ";"
objConn.Open
myId = Request.form("ID")
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber")
StrSQL = "Update First_Table SET " & _
"FirstName= '" & myFirstName & "'," & _
"LastName= '" & myLastName & "'," & _
"Where ID="&myId&""
objConn.Execute StrSQL
StrSQL = "Update Second_Table SET " & _
"Address= '" & myAddress & "'," & _
"PhoneNumber= '" & myPhoneNumber & "'" & _
"Where ID="&myId&""
objConn.Execute StrSQL
'Redirect to the UPDATED page
Response.Redirect "AllData.asp"
%>
Add this line right before your Execute statements:
Response.Write "The SQL text is <" & StrSQL & ">"
This makes a GREAT debugging tool.
What you will see is this:
Update First_Table SET FirstName= 'FN',LastName= 'LN',Where ID=ID
There is an extra comma after the SET clause for LastName. The proper command should be:
Update First_Table SET FirstName= 'FN',LastName= 'LN' Where ID=ID
The second statement appears to be syntactically correct.
Response.Write "The SQL text is <" & StrSQL & ">"
This makes a GREAT debugging tool.
What you will see is this:
Update First_Table SET FirstName= 'FN',LastName= 'LN',Where ID=ID
There is an extra comma after the SET clause for LastName. The proper command should be:
Update First_Table SET FirstName= 'FN',LastName= 'LN' Where ID=ID
The second statement appears to be syntactically correct.
ASKER
I'm sorry, but I don't get it. Would you please send it to me like yesterday. I'm trying to do exactly the same thing. Yesterday we modified data from one table, but today the data is in two different tables.
FirstName, LastName are in First_Table
Address, PhoneNumber are in Second_Table
Any method would be great for me.
I'm not sure if this is the right way to execute:
FirstName, LastName are in First_Table
Address, PhoneNumber are in Second_Table
Any method would be great for me.
I'm not sure if this is the right way to execute:
<%
Set objConn = CreateObject("ADODB.Connection")
objConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
Server.MapPath ("database\TwoTables.mdb") & ";"
objConn.Open
myId = Request.form("ID")
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber")
StrSQL = "Update First_Table SET " & _
"FirstName= '" & myFirstName & "'," & _
"LastName= '" & myLastName & "'," & _
"Where ID="&myId&""
objConn.Execute StrSQL
StrSQL = "Update Second_Table SET " & _
"Address= '" & myAddress & "'," & _
"PhoneNumber= '" & myPhoneNumber & "'" & _
"Where ID="&myId&""
objConn.Execute StrSQL
'Redirect to the UPDATED page
Response.Redirect "AllData.asp"
%>
ASKER
THIS IS THE COMPLETE CODE OF MY THREE PAGES:
PAGE 1:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select First_Table.ID, Second_Table.ID, FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
<th>Update Entry</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
<td><a href="UpdateData.asp?key=<
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
THIS IS THE PAGE WHERE THE INFORMATION TO BE UPDATED APPEARS:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=rs("FirstName")%
</td><input type="TEXT" name="LastName" size="30" value="<%=rs("LastName")%>
</td><input type="TEXT" name="Address" size="30" value="<%=rs("Address")%>"
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=rs("PhoneNumber"
</table>
<input type="hidden" name="ID" value="<% = rs("ID") %>">
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
AND FINALLY THIS IS THE PAGE THAT DOESN'T WORK AND NEEDS TO BE FIXED:
<%
Set objConn = CreateObject("ADODB.Connec
objConn.ConnectionString = "Provider=Microsoft.Jet.OL
Server.MapPath ("database\TwoTables.mdb")
objConn.Open
myId = Request.form("ID")
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "Update First_Table SET " & _
"FirstName= '" & myFirstName & "'," & _
"LastName= '" & myLastName & "'," & _
"Where ID="&myId&""
objConn.Execute StrSQL
StrSQL = "Update Second_Table SET " & _
"Address= '" & myAddress & "'," & _
"PhoneNumber= '" & myPhoneNumber & "'" & _
"Where ID="&myId&""
objConn.Execute StrSQL
'Redirect to the UPDATED page
Response.Redirect "AllData.asp"
%>
THE PAGE ABOVE MIGHT BE COMPLETELY WRONG, I JUST USED THE INFO YOU SENT ME YESTERDAY, BUT I'M NOT SURE IT'S APPLIED CORRECTLY
PAGE 1:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
Dim rs
set rs=Server.CreateObject("AD
sql="select First_Table.ID, Second_Table.ID, FirstName, LastName, Address, PhoneNumber from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID"
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>All Data</title>
</head>
<body>
<table bgcolor="#ffffff" border="1" align="center" bordercolor="000080" cellpadding="2" cellspacing="0" style="border-collapse:col
<tr bgcolor="#eeeeee">
<th>First Name</th>
<th>Last Name</th>
<th>Address</th>
<th>Phone Number</th>
<th>Update Entry</th>
</tr>
<%
if not (rs.EOF or rs.BOF) then rs.movefirst
do until rs.EOF
%>
<tr>
<td><%= rs("FirstName")%></td>
<td><%= rs("LastName")%></td>
<td><%= rs("Address")%></td>
<td><%= rs("PhoneNumber")%></td>
<td><a href="UpdateData.asp?key=<
</tr>
<%
rs.MoveNext
loop
rs.Close
adoCon.Close
%>
</table>
</body>
</html>
THIS IS THE PAGE WHERE THE INFORMATION TO BE UPDATED APPEARS:
<%
'Create an ADO connection odject
Set adoCon = Server.CreateObject("ADODB
'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("database\T
myId = Request.QueryString("key")
Dim rs
set rs=Server.CreateObject("AD
sql="select * from First_Table INNER JOIN Second_Table ON First_Table.ID = Second_Table.ID WHERE First_Table.ID = " & myId &""
Set rs = adoCon.Execute(sql)
%>
<html>
<head>
<title>Update Form</title>
</head>
<body>
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
<table><tr>
</td><input type="TEXT" name="FirstName" size="30" value="<%=rs("FirstName")%
</td><input type="TEXT" name="LastName" size="30" value="<%=rs("LastName")%>
</td><input type="TEXT" name="Address" size="30" value="<%=rs("Address")%>"
</td><input type="TEXT" name="PhoneNumber" size="30" value="<%=rs("PhoneNumber"
</table>
<input type="hidden" name="ID" value="<% = rs("ID") %>">
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>
AND FINALLY THIS IS THE PAGE THAT DOESN'T WORK AND NEEDS TO BE FIXED:
<%
Set objConn = CreateObject("ADODB.Connec
objConn.ConnectionString = "Provider=Microsoft.Jet.OL
Server.MapPath ("database\TwoTables.mdb")
objConn.Open
myId = Request.form("ID")
myFirstName = request.form("FirstName")
myLastName = request.form("LastName")
myAddress = request.form("Address")
myPhoneNumber = request.form("PhoneNumber"
StrSQL = "Update First_Table SET " & _
"FirstName= '" & myFirstName & "'," & _
"LastName= '" & myLastName & "'," & _
"Where ID="&myId&""
objConn.Execute StrSQL
StrSQL = "Update Second_Table SET " & _
"Address= '" & myAddress & "'," & _
"PhoneNumber= '" & myPhoneNumber & "'" & _
"Where ID="&myId&""
objConn.Execute StrSQL
'Redirect to the UPDATED page
Response.Redirect "AllData.asp"
%>
THE PAGE ABOVE MIGHT BE COMPLETELY WRONG, I JUST USED THE INFO YOU SENT ME YESTERDAY, BUT I'M NOT SURE IT'S APPLIED CORRECTLY
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much, both of you were very helpful.
Not a problem.
You remind me of myself when I first started a couple of years ago.
Do not worry, you will catch on to it, and when you do.
You will get to where you will not want to stop coding, as it gets so intense
When you do something that simply blows you away, or you do something
That people tell you "Cannot be done". I have done this so many times
It is unreal.
Have a good one and good luck
Carrzkiss
Author Comments:
Thank you so much, both of you were very helpful.
You remind me of myself when I first started a couple of years ago.
Do not worry, you will catch on to it, and when you do.
You will get to where you will not want to stop coding, as it gets so intense
When you do something that simply blows you away, or you do something
That people tell you "Cannot be done". I have done this so many times
It is unreal.
Have a good one and good luck
Carrzkiss
Author Comments:
Thank you so much, both of you were very helpful.
<td><a href="UpdateData.asp?key=<
Now, you'll have a clickable link on each page that takes you to a second page (now called UpdateData). Create that page next.
On the new page, extract the parameter passed in with a call like this:
myId = Request.QueryString("key")
Create a data set and build a query like this:
StrSQL = "SELECT * FROM FirstTable WHERE FirstTable.ID = " & myId
Then, build a new form starting with this:
<form action="UpdateData2.asp" method="POST" enctype="application/x-www
Create an input field for each data element and provide a default value from the data set, like this:
<input type="TEXT" name="PhoneNumber" size="30" value="<%=Trim(rstData("Ph
Be sure to include the ID field in the form -- make it hidden if you don't want the user to see it.
Do this for each field that you want the user to be able to modify, then add a submit button and other attributes to make it pretty.
Finally, build page UpdateData2.asp. Use the following statements to pull data from the form:
myPhoneNumber = request.form("PhoneNumber"
Repeat for all inbound data elements.
Build an UPDATE statement with the new data, like this:
StrSQL = "UPDATE FirstTable SET "
StrSQL = StrSQL & "PhoneNumber = '" & myPhoneNumber & "' "
(repeat for each field)
StrSQL = StrSQL & "WHERE ID = " & myId
Then execute the statement.