|
 |
asp_databases thread: Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ', frmItemPaymentType'.
Message #1 by "Lan Chi Tran" <lanchi1975@y...> on Thu, 26 Jul 2001 22:32:57
|
|
Hello expert,
Appreciated at your help on this matter. Your codes are so much simple
and clean. Thanks for your comments on each line of codes. They are very
helpful for a beginner like me.
Another problem appears to be related to my CreateEntries.asp not the
response_create_entries.asp. Please take a look at my textbox. I don't
know if the name that I make for my text box in this file
(CreateEntries.asp) has anything to do with my response_create_entries.asp
Thanks again. Hope it works this time.
*************
CreateEntries.asp
******************
<%
Dim objConn, myDSN, SQL, rstemp
myDSN="DSN=testdata;UID=sa"
set objConn=server.createobject("adodb.connection")
objConn.open myDSN
SQL="Select * from STANDARDS"
set rstemp=objConn.execute(SQL)
%>
<table border=1 CELLPADDING='2' BGCOLOR='#D5D0DF'>
<H3>Please fill out this form.</H3><tr>
<%
for each frmItem in rstemp.fields
Response.write(" <TR><TD><b>")
Response.write(frmItem.name)
Response.write("</b></TD><TD><INPUT TYPE=TEXT name=frmItem")
Response.write(frmItem.name)
Response.write("'></TD></TR>")
next
%>
</table>
<%
rstemp.close
set rstemp=nothing
objConn.close
set objConn=nothing
%>
<P>
<TR><TD><INPUT TYPE="SUBMIT" VALUE="CREATE ENTRIES">
</form></TABLE>
**********************
response_create_entries.asp
**************************
<%
' Declare variables
dim SQLFields, SQLValues, SQLstr, objConn
SQLFields = ""
SQLValues = ""
' Create strings for use in SQL Insert
For each frmItem in Request.Form
SQLFields = SQLFields & ", " & frmItem
SQLValues = SQLValues & ", '" & Request.Form(frmItem) & "'"
Next
' Remove the extra comma and space at the beginning
SQLFields = mid(SQLFields, 3)
SQLValues = mid(SQLValues, 3)
' Create the SQL string
SQLstr = "INSERT INTO Standards (" & SQLFields & ") VALUES (" & values &")"
' Open a connection to the database
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "testdata"
' Execute the query
objconn.execute(SQLstr)
' Close the connection
objconn.close
set objconn = nothing
%>
*********************
Message #2 by "Peter Foti (PeterF)" <PeterF@S...> on Fri, 27 Jul 2001 10:02:30 -0400
|
|
Hello again,
Ok. Here's where the problem is:
Response.write("</b></TD><TD><INPUT TYPE=3DTEXT name=3DfrmItem")
Response.write(frmItem.name)
Response.write("'></TD></TR>")
If the field in your database is called "PaymentType", you are creating
an input box called "frmItemPaymentType". That's fine, but you will
need to strip off the "frmItem" on the page that updates the database
(because the database doesn't know of anything called
"frmItemPaymentType"). A better (and easier) approach would be to
simply not add the "frmItem" to the input box. That way your input
boxes match the fields in your database.
Also, you really should surround your attributes in quotes. Especially
since you may have a table field that contains a space. Here is what I
would do:
Response.write("</b></TD><TD><INPUT TYPE=3D'TEXT' name=3D'")
Response.write(frmItem.name)
Response.write("'></TD></TR>")
Note, I added a single quote after the equal sign in the name attribute
(you had the closing single quote in your example, but not the
opening).
I also got rid of frmItem.
Hope this helps.
Peter
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Thursday, July 26, 2001 10:33 PM
> To: ASP Databases
> Subject: [asp_databases] Microsoft][ODBC SQL Server Driver][SQL
> Server]Line 1: Incorrect syntax near ', frmItemPaymentType'.
>
>
> Hello expert,
>
> Appreciated at your help on this matter. Your codes are so
> much simple
> and clean. Thanks for your comments on each line of codes.
> They are very
> helpful for a beginner like me.
>
> Another problem appears to be related to my CreateEntries.asp not the
> response_create_entries.asp. Please take a look at my
> textbox. I don't
> know if the name that I make for my text box in this file
> (CreateEntries.asp) has anything to do with my
> response_create_entries.asp
>
> Thanks again. Hope it works this time.
> *************
> CreateEntries.asp
> ******************
> <%
> Dim objConn, myDSN, SQL, rstemp
> myDSN=3D"DSN=3Dtestdata;UID=3Dsa"
> set objConn=3Dserver.createobject("adodb.connection")
> objConn.open myDSN
> SQL=3D"Select * from STANDARDS"
> set rstemp=3DobjConn.execute(SQL)
> %>
> <table border=3D1 CELLPADDING=3D'2' BGCOLOR=3D'#D5D0DF'>
> <H3>Please fill out this form.</H3><tr>
> <%
> for each frmItem in rstemp.fields
> Response.write(" <TR><TD><b>")
> Response.write(frmItem.name)
> Response.write("</b></TD><TD><INPUT TYPE=3DTEXT name=3DfrmItem")
> Response.write(frmItem.name)
> Response.write("'></TD></TR>")
> next
> %>
> </table>
> <%
> rstemp.close
> set rstemp=3Dnothing
> objConn.close
> set objConn=3Dnothing
> %>
> <P>
> <TR><TD><INPUT TYPE=3D"SUBMIT" VALUE=3D"CREATE ENTRIES">
>
>
> </form></TABLE>
> **********************
> response_create_entries.asp
> **************************
> <%
> ' Declare variables
> dim SQLFields, SQLValues, SQLstr, objConn
>
>
> SQLFields =3D ""
> SQLValues =3D ""
>
> ' Create strings for use in SQL Insert
> For each frmItem in Request.Form
> SQLFields =3D SQLFields & ", " & frmItem
> SQLValues =3D SQLValues & ", '" & Request.Form(frmItem) & "'"
> Next
>
> ' Remove the extra comma and space at the beginning
> SQLFields =3D mid(SQLFields, 3)
> SQLValues =3D mid(SQLValues, 3)
>
> ' Create the SQL string
> SQLstr =3D "INSERT INTO Standards (" & SQLFields & ") VALUES ("
> & values &")"
>
> ' Open a connection to the database
> Set objConn =3D Server.CreateObject ("ADODB.Connection")
> objConn.Open "testdata"
>
> ' Execute the query
> objconn.execute(SQLstr)
>
> ' Close the connection
> objconn.close
> set objconn =3D nothing
> %>
> *********************
Message #3 by "Lan Chi Tran" <lanchi1975@y...> on Sat, 28 Jul 2001 04:54:13
|
|
Hello Peter,
First of all, thank you very much for helping me. I read your codes over
and over to make sure that I understand them. To be honest, I am not good
at FOR LOOP and any kind of loop such as IF..ELSE...THEN or
DO..WHILE..LOOP. I bought the book (Beginning to ASP 3.0) and read
through it when I was in college (1 year ago) but I didn't get a chance to
use it in a real world and everything I read disappear by the time go by.
I know there are a lot of tutorials on the web to help me learning these
loops. I went to www.asp101.com, www.4guysfromrolla.com. and printed out
a lot of articles on ASP.
I am now working on building a web application using ASP. This web
application will display a table on the web interface called Standards
that will have all the fieldnames and values. And the timeframe for this
project is 2 weeks and next Wednesday will be the end of the second week.
You have already helped me with my INSERT. And now I am stuck with my
UPDATE and DELETE. Here is what I have for UPDATE. It works but my BOSS
doesn't want me to hard-code the fielnames. He wants s.t like what you
had in your INSERT query. The DELETE is very similar to UPDATE, I think I
can handle it once I got the UPDATE working.
This is too advance for me. I need to learn from the basic.
Appreciated any help you might offer.
********************
UpdateEntries.asp
********************
<%
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select ID, CreditCard, PaymentType, Versions, Platforms,
ShippingMethod, Reps, CompanyType, Upgrades, PriceModel From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%While Not RS.EOF%>
<option value="<%=RS("ID")%>">
<%=RS.Fields("CreditCard")%>,<%=RS.Fields("PaymentType")%> ,<%=RS.Fields
("Versions")%> ,<%=RS.Fields("Platforms")%> ,<%=RS.Fields
("ShippingMethod")%> ,<%=RS.Fields("Reps")%> ,<%=RS.Fields("CompanyType")%
> ,<%=RS.Fields("Upgrades")%>,<%=RS.Fields("PriceModel")%>
<%
RS.MoveNext
Wend
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
<input type="submit" value="Select">
</select>
</form>
<%
id = Request("ID")
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where Standards.ID = " & id
Set RS = MyConn.Execute(SQL)
%>
<form action="response_update_entries.asp" method="Post">
<input type="hidden" name="ID" value="<%=RS("ID")%>">
Credit Card <input type="text" name="CreditCard" size="20" value="<%=RS
("CreditCard")%>"><br>
PaymentType <input type="text" name="PaymentType" size="20" value="<%
=RS("PaymentType")%>"><br>
Versions <input type="text" name="Versions" size="20" value="<%=RS
("Versions")%>"><br>
Platforms <input type="text" name="Platforms" size="20" value="<%=RS
("Platforms")%>"><br>
Shipping Method<input type="text" name="ShippingMethod" size="20"
value="<%=RS("ShippingMethod")%>"><br>
Reps <input type="text" name="Reps" size="20" value="<%=RS
("Reps")%>"><br>
Company Type <input type="text" name="CompanyType" size="20" value="<%
=RS("CompanyType")%>"><br>
Upgrades <input type="text" name="Upgrades" size="20" value="<%=RS
("Upgrades")%>"><br>
Price Model <input type="text" name="PriceModel" size="20" value="<%=RS
("PriceModel")%>"><br>
<input type="submit" value="Update">
<%
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID"
End If
%>
</table>
</TABLE>
</BODY>
</HTML>
**********************
response_update_entries.asp
**********************
<%
CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
Versions= Replace(Request.Form("Versions"), "'", "''")
Platforms =Replace(Request.Form("Platforms"), "'", "''")
ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
Reps =Replace(Request.Form("Reps"), "'", "''")
CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
= '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
= '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
= '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
= '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "UpdateEntries.asp"
%>
</table>
<% Response.Write sitenav()%>
</TABLE>
</BODY>
</HTML>
<%
End If
%>
***************
Message #4 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 30 Jul 2001 13:53:35 -0400
|
|
Glad to be of help.
First let me say that this method could become very messy, especially if
you have a lot of fields in your database. You might end up with a
Select drop down list that is so wide it goes past the viewable area on
the screen. I can think of better ways to do this, but they would
require hard coding in some places (and it would take a while to explain
it, so I'll just cover what you're trying to do).
Below is some of you code that has been modified. I made it so that the
none of the fields are hard coded on the first field, except for the ID
field. You MUST have a special case for the ID field. Note that I did
not make any changes to your second file, as I am hoping you will be
able to see what I'm doing in the first file and apply the changes to
the second file yourself. It will help you to learn it I think. :)
Also, I did not include any of the opening or closing tags before the
ASP code in the first file, so don't forget to include that (the <body>
tags, etc.)
Hope this helps.
Pete
<%
' ********************
' UpdateEntries.asp
' ********************
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
' Get ALL fields
SQL = "Select * From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%
' Print out the results of the query as an OPTION in the select box
' Loop through all of the records
While Not RS.EOF
' Print the OPTION tag using the ID field to identify it
Response.write("<option value='" & RS("ID") & "'>")
' The OPTION text contains all of the fields in the recordset
' except for ID (no need to display that one)
RowStr = ""
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
RowStr = RowStr & ", " & recordField
End If
next
' Trim off the first comma
RowStr = mid(RowStr, 3)
' Print the row
Response.write(RowStr)
' Close the Option tag
Response.write("</option>")
RS.MoveNext
Wend
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
%>
</select>
<input type="submit" value="Select">
</form>
<%
id = Cstr(Request("ID"))
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where ID = " & id
Set RS = MyConn.Execute(SQL)
' Print the form tag
Response.write("<form action='response_update_entries.asp'
method='Post'>")
' Loop through all of the records (should only be one at this
point, but just to be safe)
While Not RS.EOF
' Print the hidden input tag using the ID field to
identify it
Response.write("<input type='hidden' name='ID' value='"
& RS("ID") & "'>")
' Print a text area for each field
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
Response.write(recordField.name)
Response.write(" <input type='text'
name='")
Response.write(recordField.name)
Response.write("' size='20' value='")
Response.write(recordField)
Response.write("'><br>")
End If
next
RS.MoveNext
Wend
Response.write("<input type='submit' value='Update'>")
Response.write("</form>")
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID</p>"
End If
%>
' **********************
' response_update_entries.asp
' **********************
<%
CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
Versions= Replace(Request.Form("Versions"), "'", "''")
Platforms =Replace(Request.Form("Platforms"), "'", "''")
ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
Reps =Replace(Request.Form("Reps"), "'", "''")
CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
= '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
= '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
= '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
= '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "UpdateEntries.asp"
%>
</table>
<% Response.Write sitenav()%>
</TABLE>
</BODY>
</HTML>
<%
End If
%>
***************
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Saturday, July 28, 2001 4:54 AM
To: ASP Databases
Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL
Server- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
Hello Peter,
First of all, thank you very much for helping me. I read your
codes over
and over to make sure that I understand them. To be honest, I
am not good
at FOR LOOP and any kind of loop such as IF..ELSE...THEN or
DO..WHILE..LOOP. I bought the book (Beginning to ASP 3.0) and read
through it when I was in college (1 year ago) but I didn't get
a chance to
use it in a real world and everything I read disappear by the
time go by.
I know there are a lot of tutorials on the web to help me
learning these
loops. I went to www.asp101.com, www.4guysfromrolla.com. and
printed out
a lot of articles on ASP.
I am now working on building a web application using ASP. This web
application will display a table on the web interface called Standards
that will have all the fieldnames and values. And the
timeframe for this
project is 2 weeks and next Wednesday will be the end of the
second week.
You have already helped me with my INSERT. And now I am stuck with my
UPDATE and DELETE. Here is what I have for UPDATE. It works
but my BOSS
doesn't want me to hard-code the fielnames. He wants s.t like what you
had in your INSERT query. The DELETE is very similar to
UPDATE, I think I
can handle it once I got the UPDATE working.
This is too advance for me. I need to learn from the basic.
Appreciated any help you might offer.
********************
UpdateEntries.asp
********************
<%
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select ID, CreditCard, PaymentType, Versions, Platforms,
ShippingMethod, Reps, CompanyType, Upgrades, PriceModel From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%While Not RS.EOF%>
<option value="<%=RS("ID")%>">
<%=RS.Fields("CreditCard")%>,<%=RS.Fields("PaymentType")%> ,<%=RS.Fields
("Versions")%> ,<%=RS.Fields("Platforms")%> ,<%=RS.Fields
("ShippingMethod")%> ,<%=RS.Fields("Reps")%>
,<%=RS.Fields("CompanyType")%
> ,<%=RS.Fields("Upgrades")%>,<%=RS.Fields("PriceModel")%>
<%
RS.MoveNext
Wend
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
<input type="submit" value="Select">
</select>
</form>
<%
id = Request("ID")
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where Standards.ID = " & id
Set RS = MyConn.Execute(SQL)
%>
<form action="response_update_entries.asp" method="Post">
<input type="hidden" name="ID" value="<%=RS("ID")%>">
Credit Card <input type="text" name="CreditCard" size="20"
value="<%=RS
("CreditCard")%>"><br>
PaymentType <input type="text" name="PaymentType" size="20"
value="<%
=RS("PaymentType")%>"><br>
Versions <input type="text" name="Versions" size="20"
value="<%=RS
("Versions")%>"><br>
Platforms <input type="text" name="Platforms" size="20"
value="<%=RS
("Platforms")%>"><br>
Shipping Method<input type="text" name="ShippingMethod" size="20"
value="<%=RS("ShippingMethod")%>"><br>
Reps <input type="text" name="Reps" size="20" value="<%=RS
("Reps")%>"><br>
Company Type <input type="text" name="CompanyType" size="20"
value="<%
=RS("CompanyType")%>"><br>
Upgrades <input type="text" name="Upgrades" size="20"
value="<%=RS
("Upgrades")%>"><br>
Price Model <input type="text" name="PriceModel" size="20"
value="<%=RS
("PriceModel")%>"><br>
<input type="submit" value="Update">
<%
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID"
End If
%>
</table>
</TABLE>
</BODY>
</HTML>
**********************
response_update_entries.asp
**********************
<%
CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
Versions= Replace(Request.Form("Versions"), "'", "''")
Platforms =Replace(Request.Form("Platforms"), "'", "''")
ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
Reps =Replace(Request.Form("Reps"), "'", "''")
CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
= '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
= '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
= '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
= '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "UpdateEntries.asp"
%>
</table>
<% Response.Write sitenav()%>
</TABLE>
</BODY>
</HTML>
<%
End If
%>
***************
Message #5 by "Peter Foti (PeterF)" <PeterF@S...> on Mon, 30 Jul 2001 14:48:08 -0400
|
|
Forgot to mention that I have not tested this code to verify that it
works (or checked for typos, etc.).
Also, I found a typo in my message. Reads:
"I made it so that the none of the fields are hard coded on the first
field, except for the ID field."
Should Read:
"I made it so that the none of the fields are hard coded, except for the
ID field."
Regards,
Peter
-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, July 30, 2001 1:54 PM
To: ASP Databases
Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se
rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
Glad to be of help.
First let me say that this method could become very messy, especially if
you have a lot of fields in your database. You might end up with a
Select drop down list that is so wide it goes past the viewable area on
the screen. I can think of better ways to do this, but they would
require hard coding in some places (and it would take a while to explain
it, so I'll just cover what you're trying to do).
Below is some of you code that has been modified. I made it so that the
none of the fields are hard coded on the first field, except for the ID
field. You MUST have a special case for the ID field. Note that I did
not make any changes to your second file, as I am hoping you will be
able to see what I'm doing in the first file and apply the changes to
the second file yourself. It will help you to learn it I think. :)
Also, I did not include any of the opening or closing tags before the
ASP code in the first file, so don't forget to include that (the <body>
tags, etc.)
Hope this helps.
Pete
<%
' ********************
' UpdateEntries.asp
' ********************
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
' Get ALL fields
SQL = "Select * From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%
' Print out the results of the query as an OPTION in the select box
' Loop through all of the records
While Not RS.EOF
' Print the OPTION tag using the ID field to identify it
Response.write("<option value='" & RS("ID") & "'>")
' The OPTION text contains all of the fields in the recordset
' except for ID (no need to display that one)
RowStr = ""
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
RowStr = RowStr & ", " & recordField
End If
next
' Trim off the first comma
RowStr = mid(RowStr, 3)
' Print the row
Response.write(RowStr)
' Close the Option tag
Response.write("</option>")
RS.MoveNext
Wend
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
%>
</select>
<input type="submit" value="Select">
</form>
<%
id = Cstr(Request("ID"))
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where ID = " & id
Set RS = MyConn.Execute(SQL)
' Print the form tag
Response.write("<form action='response_update_entries.asp'
method='Post'>")
' Loop through all of the records (should only be one at this
point, but just to be safe)
While Not RS.EOF
' Print the hidden input tag using the ID field to
identify it
Response.write("<input type='hidden' name='ID' value='"
& RS("ID") & "'>")
' Print a text area for each field
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
Response.write(recordField.name)
Response.write(" <input type='text'
name='")
Response.write(recordField.name)
Response.write("' size='20' value='")
Response.write(recordField)
Response.write("'><br>")
End If
next
RS.MoveNext
Wend
Response.write("<input type='submit' value='Update'>")
Response.write("</form>")
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID</p>"
End If
%>
' **********************
' response_update_entries.asp
' **********************
<%
CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
Versions= Replace(Request.Form("Versions"), "'", "''")
Platforms =Replace(Request.Form("Platforms"), "'", "''")
ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
Reps =Replace(Request.Form("Reps"), "'", "''")
CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
= '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
= '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
= '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
= '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "UpdateEntries.asp"
%>
</table>
<% Response.Write sitenav()%>
</TABLE>
</BODY>
</HTML>
<%
End If
%>
***************
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Saturday, July 28, 2001 4:54 AM
To: ASP Databases
Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL
Server- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
Hello Peter,
First of all, thank you very much for helping me. I read your
codes over
and over to make sure that I understand them. To be honest, I
am not good
at FOR LOOP and any kind of loop such as IF..ELSE...THEN or
DO..WHILE..LOOP. I bought the book (Beginning to ASP 3.0) and read
through it when I was in college (1 year ago) but I didn't get
a chance to
use it in a real world and everything I read disappear by the
time go by.
I know there are a lot of tutorials on the web to help me
learning these
loops. I went to www.asp101.com, www.4guysfromrolla.com. and
printed out
a lot of articles on ASP.
I am now working on building a web application using ASP. This web
application will display a table on the web interface called Standards
that will have all the fieldnames and values. And the
timeframe for this
project is 2 weeks and next Wednesday will be the end of the
second week.
You have already helped me with my INSERT. And now I am stuck with my
UPDATE and DELETE. Here is what I have for UPDATE. It works
but my BOSS
doesn't want me to hard-code the fielnames. He wants s.t like what you
had in your INSERT query. The DELETE is very similar to
UPDATE, I think I
can handle it once I got the UPDATE working.
This is too advance for me. I need to learn from the basic.
Appreciated any help you might offer.
********************
UpdateEntries.asp
********************
<%
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select ID, CreditCard, PaymentType, Versions, Platforms,
ShippingMethod, Reps, CompanyType, Upgrades, PriceModel From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%While Not RS.EOF%>
<option value="<%=RS("ID")%>">
<%=RS.Fields("CreditCard")%>,<%=RS.Fields("PaymentType")%> ,<%=RS.Fields
("Versions")%> ,<%=RS.Fields("Platforms")%> ,<%=RS.Fields
("ShippingMethod")%> ,<%=RS.Fields("Reps")%>
,<%=RS.Fields("CompanyType")%
> ,<%=RS.Fields("Upgrades")%>,<%=RS.Fields("PriceModel")%>
<%
RS.MoveNext
Wend
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
%>
<input type="submit" value="Select">
</select>
</form>
<%
id = Request("ID")
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where Standards.ID = " & id
Set RS = MyConn.Execute(SQL)
%>
<form action="response_update_entries.asp" method="Post">
<input type="hidden" name="ID" value="<%=RS("ID")%>">
Credit Card <input type="text" name="CreditCard" size="20"
value="<%=RS
("CreditCard")%>"><br>
PaymentType <input type="text" name="PaymentType" size="20"
value="<%
=RS("PaymentType")%>"><br>
Versions <input type="text" name="Versions" size="20"
value="<%=RS
("Versions")%>"><br>
Platforms <input type="text" name="Platforms" size="20"
value="<%=RS
("Platforms")%>"><br>
Shipping Method<input type="text" name="ShippingMethod" size="20"
value="<%=RS("ShippingMethod")%>"><br>
Reps <input type="text" name="Reps" size="20" value="<%=RS
("Reps")%>"><br>
Company Type <input type="text" name="CompanyType" size="20"
value="<%
=RS("CompanyType")%>"><br>
Upgrades <input type="text" name="Upgrades" size="20"
value="<%=RS
("Upgrades")%>"><br>
Price Model <input type="text" name="PriceModel" size="20"
value="<%=RS
("PriceModel")%>"><br>
<input type="submit" value="Update">
<%
RS.Close
MyConn.Close
Set RS = Nothing
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID"
End If
%>
</table>
</TABLE>
</BODY>
</HTML>
**********************
response_update_entries.asp
**********************
<%
CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
Versions= Replace(Request.Form("Versions"), "'", "''")
Platforms =Replace(Request.Form("Platforms"), "'", "''")
ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
Reps =Replace(Request.Form("Reps"), "'", "''")
CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
= '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
= '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
= '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
= '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "UpdateEntries.asp"
%>
</table>
<% Response.Write sitenav()%>
</TABLE>
</BODY>
</HTML>
<%
End If
%>
***************
Message #6 by "Lan Chi Tran" <lanchi1975@y...> on Tue, 31 Jul 2001 14:22:58
|
|
Morning Peter,
Your code works very well. Thanks very much for taking the time to write
the code. You just don't know how happy I am when I get it. I have been
working on the second part of the UPDATE and I will send it over to you
for feedback.
Thanks for the corrections that you sent me. You really shouldn't have
to. Your codes are very simple (and I don't mean it easy to understand)
and clean and so do your messages.
Have a wonderful day.
> Forgot to mention that I have not tested this code to verify that it
> works (or checked for typos, etc.).
>
> Also, I found a typo in my message. Reads:
>
> "I made it so that the none of the fields are hard coded on the first
> field, except for the ID field."
>
> Should Read:
>
> "I made it so that the none of the fields are hard coded, except for the
> ID field."
>
> Regards,
> Peter
>
>
> -----Original Message-----
> From: Peter Foti (PeterF) [mailto:PeterF@S...]
> Sent: Monday, July 30, 2001 1:54 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se
> rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
>
>
> Glad to be of help.
>
> First let me say that this method could become very messy, especially if
> you have a lot of fields in your database. You might end up with a
> Select drop down list that is so wide it goes past the viewable area on
> the screen. I can think of better ways to do this, but they would
> require hard coding in some places (and it would take a while to explain
> it, so I'll just cover what you're trying to do).
>
> Below is some of you code that has been modified. I made it so that the
> none of the fields are hard coded on the first field, except for the ID
> field. You MUST have a special case for the ID field. Note that I did
> not make any changes to your second file, as I am hoping you will be
> able to see what I'm doing in the first file and apply the changes to
> the second file yourself. It will help you to learn it I think. :)
>
> Also, I did not include any of the opening or closing tags before the
> ASP code in the first file, so don't forget to include that (the <body>
> tags, etc.)
>
> Hope this helps.
> Pete
>
>
> <%
> ' ********************
> ' UpdateEntries.asp
> ' ********************
>
> Dim MyConn, SQL, RS
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
>
> ' Get ALL fields
> SQL = "Select * From Standards"
>
> Set RS=MyConn.Execute(SQL)
> %>
>
> <form action="UpdateEntries.asp" method="Post">
> <select name="ID">
> <%
> ' Print out the results of the query as an OPTION in the select box
>
> ' Loop through all of the records
> While Not RS.EOF
> ' Print the OPTION tag using the ID field to identify it
> Response.write("<option value='" & RS("ID") & "'>")
>
> ' The OPTION text contains all of the fields in the recordset
> ' except for ID (no need to display that one)
> RowStr = ""
> for each recordField in RS.Fields
> if Not recordField.name = "ID" Then
> RowStr = RowStr & ", " & recordField
> End If
> next
>
> ' Trim off the first comma
> RowStr = mid(RowStr, 3)
>
> ' Print the row
> Response.write(RowStr)
>
> ' Close the Option tag
> Response.write("</option>")
>
> RS.MoveNext
> Wend
>
> ' Close the recordset
> RS.Close
> Set RS = Nothing
>
> ' Close the connection
> MyConn.Close
> Set MyConn = Nothing
> %>
>
> </select>
> <input type="submit" value="Select">
> </form>
>
> <%
> id = Cstr(Request("ID"))
>
> If id <> "" Then
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
>
> SQL = "Select * From Standards Where ID = " & id
> Set RS = MyConn.Execute(SQL)
>
> ' Print the form tag
> Response.write("<form action='response_update_entries.asp'
> method='Post'>")
>
> ' Loop through all of the records (should only be one at this
> point, but just to be safe)
> While Not RS.EOF
> ' Print the hidden input tag using the ID field to
> identify it
> Response.write("<input type='hidden' name='ID' value='"
> & RS("ID") & "'>")
>
> ' Print a text area for each field
> for each recordField in RS.Fields
> if Not recordField.name = "ID" Then
> Response.write(recordField.name)
> Response.write(" <input type='text'
> name='")
> Response.write(recordField.name)
> Response.write("' size='20' value='")
> Response.write(recordField)
> Response.write("'><br>")
> End If
> next
>
> RS.MoveNext
> Wend
>
> Response.write("<input type='submit' value='Update'>")
> Response.write("</form>")
>
> ' Close the recordset
> RS.Close
> Set RS = Nothing
>
> ' Close the connection
> MyConn.Close
> Set MyConn = Nothing
>
> Else
> Response.Write "<p>Please Select a Record that has an ID</p>"
> End If
> %>
>
>
>
>
>
>
> ' **********************
> ' response_update_entries.asp
> ' **********************
>
> <%
>
> CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
> PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
> Versions= Replace(Request.Form("Versions"), "'", "''")
> Platforms =Replace(Request.Form("Platforms"), "'", "''")
> ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
> Reps =Replace(Request.Form("Reps"), "'", "''")
> CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
> Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
> PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
>
>
> id = CStr(Request.Form("ID"))
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
>
> MyConn.Open "testdata"
>
> SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
> = '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
> = '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
> = '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
> = '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
> MyConn.Execute(SQL)
>
> MyConn.Close
> Set MyConn = Nothing
>
> Response.Redirect "UpdateEntries.asp"
> %>
>
> </table>
> <% Response.Write sitenav()%>
>
> </TABLE>
> </BODY>
>
> </HTML>
>
> <%
> End If
> %>
>
>
>
> ***************
>
>
>
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Saturday, July 28, 2001 4:54 AM
> To: ASP Databases
> Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL
> Server- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
>
>
> Hello Peter,
>
> First of all, thank you very much for helping me. I read your
> codes over
> and over to make sure that I understand them. To be honest, I
> am not good
> at FOR LOOP and any kind of loop such as IF..ELSE...THEN or
> DO..WHILE..LOOP. I bought the book (Beginning to ASP 3.0) and read
> through it when I was in college (1 year ago) but I didn't get
> a chance to
> use it in a real world and everything I read disappear by the
> time go by.
> I know there are a lot of tutorials on the web to help me
> learning these
> loops. I went to www.asp101.com, www.4guysfromrolla.com. and
> printed out
> a lot of articles on ASP.
>
> I am now working on building a web application using ASP. This web
> application will display a table on the web interface called Standards
> that will have all the fieldnames and values. And the
> timeframe for this
> project is 2 weeks and next Wednesday will be the end of the
> second week.
>
> You have already helped me with my INSERT. And now I am stuck with my
> UPDATE and DELETE. Here is what I have for UPDATE. It works
> but my BOSS
> doesn't want me to hard-code the fielnames. He wants s.t like what you
> had in your INSERT query. The DELETE is very similar to
> UPDATE, I think I
> can handle it once I got the UPDATE working.
>
> This is too advance for me. I need to learn from the basic.
>
> Appreciated any help you might offer.
>
> ********************
> UpdateEntries.asp
> ********************
>
> <%
> Dim MyConn, SQL, RS
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
>
> SQL = "Select ID, CreditCard, PaymentType, Versions, Platforms,
> ShippingMethod, Reps, CompanyType, Upgrades, PriceModel From Standards"
> Set RS=MyConn.Execute(SQL)
> %>
>
> <form action="UpdateEntries.asp" method="Post">
> <select name="ID">
>
> <%While Not RS.EOF%>
> <option value="<%=RS("ID")%>">
> <%=RS.Fields("CreditCard")%>,<%=RS.Fields("PaymentType")%> ,<%=RS.Fields
> ("Versions")%> ,<%=RS.Fields("Platforms")%> ,<%=RS.Fields
> ("ShippingMethod")%> ,<%=RS.Fields("Reps")%>
> ,<%=RS.Fields("CompanyType")%
> > ,<%=RS.Fields("Upgrades")%>,<%=RS.Fields("PriceModel")%>
>
> <%
> RS.MoveNext
> Wend
>
> RS.Close
> MyConn.Close
> Set RS = Nothing
> Set MyConn = Nothing
> %>
>
> <input type="submit" value="Select">
> </select>
> </form>
>
> <%
> id = Request("ID")
>
> If id <> "" Then
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
>
> SQL = "Select * From Standards Where Standards.ID = " & id
> Set RS = MyConn.Execute(SQL)
>
>
> %>
>
> <form action="response_update_entries.asp" method="Post">
> <input type="hidden" name="ID" value="<%=RS("ID")%>">
>
>
> Credit Card <input type="text" name="CreditCard" size="20"
> value="<%=RS
> ("CreditCard")%>"><br>
> PaymentType <input type="text" name="PaymentType" size="20"
> value="<%
> =RS("PaymentType")%>"><br>
> Versions <input type="text" name="Versions" size="20"
> value="<%=RS
> ("Versions")%>"><br>
> Platforms <input type="text" name="Platforms" size="20"
> value="<%=RS
> ("Platforms")%>"><br>
> Shipping Method<input type="text" name="ShippingMethod" size="20"
> value="<%=RS("ShippingMethod")%>"><br>
> Reps <input type="text" name="Reps" size="20" value="<%
=RS
> ("Reps")%>"><br>
> Company Type <input type="text" name="CompanyType" size="20"
> value="<%
> =RS("CompanyType")%>"><br>
> Upgrades <input type="text" name="Upgrades" size="20"
> value="<%=RS
> ("Upgrades")%>"><br>
> Price Model <input type="text" name="PriceModel" size="20"
> value="<%=RS
> ("PriceModel")%>"><br>
>
> <input type="submit" value="Update">
>
> <%
> RS.Close
> MyConn.Close
> Set RS = Nothing
> Set MyConn = Nothing
>
> Else
> Response.Write "<p>Please Select a Record that has an ID"
> End If
> %>
> </table>
>
>
> </TABLE>
> </BODY>
>
> </HTML>
>
>
>
>
>
> **********************
> response_update_entries.asp
> **********************
>
> <%
>
> CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
> PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
> Versions= Replace(Request.Form("Versions"), "'", "''")
> Platforms =Replace(Request.Form("Platforms"), "'", "''")
> ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
> Reps =Replace(Request.Form("Reps"), "'", "''")
> CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
> Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
> PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
>
>
> id = CStr(Request.Form("ID"))
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
>
> MyConn.Open "testdata"
>
> SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
> = '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
> = '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
> = '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
> = '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
> MyConn.Execute(SQL)
>
> MyConn.Close
> Set MyConn = Nothing
>
> Response.Redirect "UpdateEntries.asp"
> %>
>
> </table>
> <% Response.Write sitenav()%>
>
> </TABLE>
> </BODY>
>
> </HTML>
>
> <%
> End If
> %>
>
>
>
> ***************
Message #7 by "Lan Chi Tran" <lanchi1975@y...> on Tue, 31 Jul 2001 17:50:22
|
|
Hello Peter,
I keep getting this error:
**************************
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the column
prefix 'recordField'. This must match the object in the UPDATE
clause 'Standards'.
***************************
response_update_entries.asp
***************************
<%
'get the new values for the record(s) and place them into the fields of
each record
WhatShouldGoHerePeter =Replace(Request.Form
("recordField.Value"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set recordField.name="'request
(WhatShouldGoherePeter)"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "standards.asp"
%>
**************************************
I know you are going to laugh at my code. That's alright.
> Morning Peter,
>
> Your code works very well. Thanks very much for taking the time to
write
> the code. You just don't know how happy I am when I get it. I have
been
> working on the second part of the UPDATE and I will send it over to you
> for feedback.
>
> Thanks for the corrections that you sent me. You really shouldn't have
> to. Your codes are very simple (and I don't mean it easy to understand)
> and clean and so do your messages.
>
> Have a wonderful day.
>
> > Forgot to mention that I have not tested this code to verify that it
> > works (or checked for typos, etc.).
> >
> > Also, I found a typo in my message. Reads:
> >
> > "I made it so that the none of the fields are hard coded on the first
> > field, except for the ID field."
> >
> > Should Read:
> >
> > "I made it so that the none of the fields are hard coded, except for
the
> > ID field."
> >
> > Regards,
> > Peter
> >
> >
> > -----Original Message-----
> > From: Peter Foti (PeterF) [mailto:PeterF@S...]
> > Sent: Monday, July 30, 2001 1:54 PM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se
> > rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
> >
> >
> > Glad to be of help.
> >
> > First let me say that this method could become very messy, especially
if
> > you have a lot of fields in your database. You might end up with a
> > Select drop down list that is so wide it goes past the viewable area on
> > the screen. I can think of better ways to do this, but they would
> > require hard coding in some places (and it would take a while to
explain
> > it, so I'll just cover what you're trying to do).
> >
> > Below is some of you code that has been modified. I made it so that
the
> > none of the fields are hard coded on the first field, except for the ID
> > field. You MUST have a special case for the ID field. Note that I did
> > not make any changes to your second file, as I am hoping you will be
> > able to see what I'm doing in the first file and apply the changes to
> > the second file yourself. It will help you to learn it I think. :)
> >
> > Also, I did not include any of the opening or closing tags before the
> > ASP code in the first file, so don't forget to include that (the <body>
> > tags, etc.)
> >
> > Hope this helps.
> > Pete
> >
> >
> > <%
> > ' ********************
> > ' UpdateEntries.asp
> > ' ********************
> >
> > Dim MyConn, SQL, RS
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> > MyConn.Open "testdata"
> >
> > ' Get ALL fields
> > SQL = "Select * From Standards"
> >
> > Set RS=MyConn.Execute(SQL)
> > %>
> >
> > <form action="UpdateEntries.asp" method="Post">
> > <select name="ID">
> > <%
> > ' Print out the results of the query as an OPTION in the select box
> >
> > ' Loop through all of the records
> > While Not RS.EOF
> > ' Print the OPTION tag using the ID field to identify it
> > Response.write("<option value='" & RS("ID") & "'>")
> >
> > ' The OPTION text contains all of the fields in the recordset
> > ' except for ID (no need to display that one)
> > RowStr = ""
> > for each recordField in RS.Fields
> > if Not recordField.name = "ID" Then
> > RowStr = RowStr & ", " & recordField
> > End If
> > next
> >
> > ' Trim off the first comma
> > RowStr = mid(RowStr, 3)
> >
> > ' Print the row
> > Response.write(RowStr)
> >
> > ' Close the Option tag
> > Response.write("</option>")
> >
> > RS.MoveNext
> > Wend
> >
> > ' Close the recordset
> > RS.Close
> > Set RS = Nothing
> >
> > ' Close the connection
> > MyConn.Close
> > Set MyConn = Nothing
> > %>
> >
> > </select>
> > <input type="submit" value="Select">
> > </form>
> >
> > <%
> > id = Cstr(Request("ID"))
> >
> > If id <> "" Then
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> > MyConn.Open "testdata"
> >
> > SQL = "Select * From Standards Where ID = " & id
> > Set RS = MyConn.Execute(SQL)
> >
> > ' Print the form tag
> > Response.write("<form action='response_update_entries.asp'
> > method='Post'>")
> >
> > ' Loop through all of the records (should only be one at this
> > point, but just to be safe)
> > While Not RS.EOF
> > ' Print the hidden input tag using the ID field to
> > identify it
> > Response.write("<input type='hidden' name='ID' value='"
> > & RS("ID") & "'>")
> >
> > ' Print a text area for each field
> > for each recordField in RS.Fields
> > if Not recordField.name = "ID" Then
> > Response.write(recordField.name)
> > Response.write(" <input type='text'
> > name='")
> > Response.write(recordField.name)
> > Response.write("' size='20' value='")
> > Response.write(recordField)
> > Response.write("'><br>")
> > End If
> > next
> >
> > RS.MoveNext
> > Wend
> >
> > Response.write("<input type='submit' value='Update'>")
> > Response.write("</form>")
> >
> > ' Close the recordset
> > RS.Close
> > Set RS = Nothing
> >
> > ' Close the connection
> > MyConn.Close
> > Set MyConn = Nothing
> >
> > Else
> > Response.Write "<p>Please Select a Record that has an ID</p>"
> > End If
> > %>
> >
> >
> >
> >
> >
> >
> > ' **********************
> > ' response_update_entries.asp
> > ' **********************
> >
> > <%
> >
> > CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
> > PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
> > Versions= Replace(Request.Form("Versions"), "'", "''")
> > Platforms =Replace(Request.Form("Platforms"), "'", "''")
> > ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
> > Reps =Replace(Request.Form("Reps"), "'", "''")
> > CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
> > Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
> > PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
> >
> >
> > id = CStr(Request.Form("ID"))
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> >
> > MyConn.Open "testdata"
> >
> > SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
> > = '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
> > = '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
> > = '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
> > = '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
> > MyConn.Execute(SQL)
> >
> > MyConn.Close
> > Set MyConn = Nothing
> >
> > Response.Redirect "UpdateEntries.asp"
> > %>
> >
> > </table>
> > <% Response.Write sitenav()%>
> >
> > </TABLE>
> > </BODY>
> >
> > </HTML>
> >
> > <%
> > End If
> > %>
> >
> >
> >
> > ***************
> >
> >
> >
> > -----Original Message-----
> > From: Lan Chi Tran [mailto:lanchi1975@y...]
> > Sent: Saturday, July 28, 2001 4:54 AM
> > To: ASP Databases
> > Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL
> > Server- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
> >
> >
> > Hello Peter,
> >
> > First of all, thank you very much for helping me. I read your
> > codes over
> > and over to make sure that I understand them. To be honest, I
> > am not good
> > at FOR LOOP and any kind of loop such as IF..ELSE...THEN or
> > DO..WHILE..LOOP. I bought the book (Beginning to ASP 3.0) and read
> > through it when I was in college (1 year ago) but I didn't get
> > a chance to
> > use it in a real world and everything I read disappear by the
> > time go by.
> > I know there are a lot of tutorials on the web to help me
> > learning these
> > loops. I went to www.asp101.com, www.4guysfromrolla.com. and
> > printed out
> > a lot of articles on ASP.
> >
> > I am now working on building a web application using ASP. This web
> > application will display a table on the web interface called Standards
> > that will have all the fieldnames and values. And the
> > timeframe for this
> > project is 2 weeks and next Wednesday will be the end of the
> > second week.
> >
> > You have already helped me with my INSERT. And now I am stuck with my
> > UPDATE and DELETE. Here is what I have for UPDATE. It works
> > but my BOSS
> > doesn't want me to hard-code the fielnames. He wants s.t like what
you
> > had in your INSERT query. The DELETE is very similar to
> > UPDATE, I think I
> > can handle it once I got the UPDATE working.
> >
> > This is too advance for me. I need to learn from the basic.
> >
> > Appreciated any help you might offer.
> >
> > ********************
> > UpdateEntries.asp
> > ********************
> >
> > <%
> > Dim MyConn, SQL, RS
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> > MyConn.Open "testdata"
> >
> > SQL = "Select ID, CreditCard, PaymentType, Versions, Platforms,
> > ShippingMethod, Reps, CompanyType, Upgrades, PriceModel From Standards"
> > Set RS=MyConn.Execute(SQL)
> > %>
> >
> > <form action="UpdateEntries.asp" method="Post">
> > <select name="ID">
> >
> > <%While Not RS.EOF%>
> > <option value="<%=RS("ID")%>">
> > <%=RS.Fields("CreditCard")%>,<%=RS.Fields("PaymentType")%> ,<%
=RS.Fields
> > ("Versions")%> ,<%=RS.Fields("Platforms")%> ,<%=RS.Fields
> > ("ShippingMethod")%> ,<%=RS.Fields("Reps")%>
> > ,<%=RS.Fields("CompanyType")%
> > > ,<%=RS.Fields("Upgrades")%>,<%=RS.Fields("PriceModel")%>
> >
> > <%
> > RS.MoveNext
> > Wend
> >
> > RS.Close
> > MyConn.Close
> > Set RS = Nothing
> > Set MyConn = Nothing
> > %>
> >
> > <input type="submit" value="Select">
> > </select>
> > </form>
> >
> > <%
> > id = Request("ID")
> >
> > If id <> "" Then
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> > MyConn.Open "testdata"
> >
> > SQL = "Select * From Standards Where Standards.ID = " & id
> > Set RS = MyConn.Execute(SQL)
> >
> >
> > %>
> >
> > <form action="response_update_entries.asp" method="Post">
> > <input type="hidden" name="ID" value="<%=RS("ID")%>">
> >
> >
> > Credit Card <input type="text" name="CreditCard" size="20"
> > value="<%=RS
> > ("CreditCard")%>"><br>
> > PaymentType <input type="text" name="PaymentType" size="20"
> > value="<%
> > =RS("PaymentType")%>"><br>
> > Versions <input type="text" name="Versions" size="20"
> > value="<%=RS
> > ("Versions")%>"><br>
> > Platforms <input type="text" name="Platforms" size="20"
> > value="<%=RS
> > ("Platforms")%>"><br>
> > Shipping Method<input type="text" name="ShippingMethod" size="20"
> > value="<%=RS("ShippingMethod")%>"><br>
> > Reps <input type="text" name="Reps" size="20" value="<%
> =RS
> > ("Reps")%>"><br>
> > Company Type <input type="text" name="CompanyType" size="20"
> > value="<%
> > =RS("CompanyType")%>"><br>
> > Upgrades <input type="text" name="Upgrades" size="20"
> > value="<%=RS
> > ("Upgrades")%>"><br>
> > Price Model <input type="text" name="PriceModel" size="20"
> > value="<%=RS
> > ("PriceModel")%>"><br>
> >
> > <input type="submit" value="Update">
> >
> > <%
> > RS.Close
> > MyConn.Close
> > Set RS = Nothing
> > Set MyConn = Nothing
> >
> > Else
> > Response.Write "<p>Please Select a Record that has an ID"
> > End If
> > %>
> > </table>
> >
> >
> > </TABLE>
> > </BODY>
> >
> > </HTML>
> >
> >
> >
> >
> >
> > **********************
> > response_update_entries.asp
> > **********************
> >
> > <%
> >
> > CreditCard = Replace(Request.Form("CreditCard"), "'", "''")
> > PaymentType = Replace(Request.Form("PaymentType"), "'", "''")
> > Versions= Replace(Request.Form("Versions"), "'", "''")
> > Platforms =Replace(Request.Form("Platforms"), "'", "''")
> > ShippingMethod = Replace(Request.Form("ShippingMethod"), "'", "''")
> > Reps =Replace(Request.Form("Reps"), "'", "''")
> > CompanyType =Replace(Request.Form("CompanyType"), "'", "''")
> > Upgrades =Replace(Request.Form("Upgrades"), "'", "''")
> > PriceModel =Replace(Request.Form("PriceModel"), "'", "''")
> >
> >
> > id = CStr(Request.Form("ID"))
> >
> > Set MyConn=Server.CreateObject("ADODB.Connection")
> >
> > MyConn.Open "testdata"
> >
> > SQL = "Update Standards Set CreditCard = '"&CreditCard&"', PaymentType
> > = '"&PaymentType&"' , Versions = '"&Versions&"' , Platforms
> > = '"&Platforms&"' , ShippingMethod = '"&ShippingMethod&"' , Reps
> > = '"&Reps&"' , CompanyType = '"&CompanyType&"' , Upgrades
> > = '"&Upgrades&"' , PriceModel = '"&PriceModel&"' Where [ID] = "&id&""
> > MyConn.Execute(SQL)
> >
> > MyConn.Close
> > Set MyConn = Nothing
> >
> > Response.Redirect "UpdateEntries.asp"
> > %>
> >
> > </table>
> > <% Response.Write sitenav()%>
> >
> > </TABLE>
> > </BODY>
> >
> > </HTML>
> >
> > <%
> > End If
> > %>
> >
> >
> >
> > ***************
Message #8 by "Peter Foti (PeterF)" <PeterF@S...> on Tue, 31 Jul 2001 14:11:14 -0400
|
|
Hello,
Ok, lets examine it.
WhatShouldGoHerePeter = Replace(Request.Form("recordField.Value"), "'",
"''")
So you are trying to replace any single quotes with 2 single quotes
(that's good). But you also need to do this for each of the form items,
so you'll need a loop. Try doing it like this:
<%
' This function returns a string surrounded by single quotes
' and any single quotes inside the string are replaced
' with 2 single quotes
function sql_quote(str)
str = replace(str,"'","''")
sql_quote = "'"& str & "'"
end function
' Grab the ID of the record
id = Cstr(Request.Form("ID"))
' Get the form items into a string that we can use for our SQL
update
For each frmItem in Request.Form
If Not frmItem = "ID" Then
FormValues = FormValues & ", " & frmItem & "=" &
sql_quote(Request.Form(frmItem))
' This creates a string, something like:
' ", CreditCard='1111', name='Peter'"
End If
Next
' Trim off the extra comma at the beginning
FormValues = mid(FormValues, 3)
' Create our SQL string
SQL = "UPDATE Standards SET " & FormValues & " WHERE ID = " & id
' Open a connection
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
' Execute the update query
MyConn.Execute(SQL)
' Close the connection
MyConn.Close
Set MyConn = Nothing
' Redirect to a displayed page
Response.Redirect "standards.asp"
%>
Good luck.
Peter
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Tuesday, July 31, 2001 5:50 PM
To: ASP Databases
Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se-
rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
Hello Peter,
I keep getting this error:
**************************
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the column
prefix 'recordField'. This must match the object in the UPDATE
clause 'Standards'.
***************************
response_update_entries.asp
***************************
<%
'get the new values for the record(s) and place them into the fields of
each record
WhatShouldGoHerePeter =Replace(Request.Form
("recordField.Value"), "'", "''")
id = CStr(Request.Form("ID"))
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Update Standards Set recordField.name="'request
(WhatShouldGoherePeter)"' Where [ID] = "&id&""
MyConn.Execute(SQL)
MyConn.Close
Set MyConn = Nothing
Response.Redirect "standards.asp"
%>
**************************************
I know you are going to laugh at my code. That's alright.
> Morning Peter,
>
> Your code works very well. Thanks very much for taking the time to
write
> the code. You just don't know how happy I am when I get it. I have
been
> working on the second part of the UPDATE and I will send it
over to you
> for feedback.
>
> Thanks for the corrections that you sent me. You really
shouldn't have
> to. Your codes are very simple (and I don't mean it easy to
understand)
> and clean and so do your messages.
>
> Have a wonderful day.
>
> > Forgot to mention that I have not tested this code to verify that it
> > works (or checked for typos, etc.).
> >
> > Also, I found a typo in my message. Reads:
> >
> > "I made it so that the none of the fields are hard coded on
the first
> > field, except for the ID field."
> >
> > Should Read:
> >
> > "I made it so that the none of the fields are hard coded,
except for
the
> > ID field."
> >
> > Regards,
> > Peter
> >
> >
Message #9 by "Lan Chi Tran" <lanchi1975@y...> on Tue, 31 Jul 2001 19:41:30
|
|
Peter!!!
You're a savior! It works. Yeah!!! I cannot believe how simple that is!
I was making it out to be more complex than it really is. I had the right
idea but nothing would work. Thank you again!!
Now my job is to find another problem and ask for your help again since
you are my EXPERT. Just kidding, I think my job now is to go back and try
to understand your codes so that next time, if I have to build something
similar, I don't have to bother you again.
So many thanks for your quickly reply. See you next time. Not long.
Lan Chi.
\
> Hello,
>
> Ok, lets examine it.
>
> WhatShouldGoHerePeter = Replace(Request.Form("recordField.Value"), "'",
> "''")
>
> So you are trying to replace any single quotes with 2 single quotes
> (that's good). But you also need to do this for each of the form items,
> so you'll need a loop. Try doing it like this:
>
> <%
> ' This function returns a string surrounded by single quotes
> ' and any single quotes inside the string are replaced
> ' with 2 single quotes
> function sql_quote(str)
> str = replace(str,"'","''")
> sql_quote = "'"& str & "'"
> end function
>
> ' Grab the ID of the record
> id = Cstr(Request.Form("ID"))
>
> ' Get the form items into a string that we can use for our SQL
> update
> For each frmItem in Request.Form
> If Not frmItem = "ID" Then
> FormValues = FormValues & ", " & frmItem & "=" &
> sql_quote(Request.Form(frmItem))
> ' This creates a string, something like:
> ' ", CreditCard='1111', name='Peter'"
> End If
> Next
>
> ' Trim off the extra comma at the beginning
> FormValues = mid(FormValues, 3)
>
> ' Create our SQL string
> SQL = "UPDATE Standards SET " & FormValues & " WHERE ID = " & id
>
> ' Open a connection
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
>
> ' Execute the update query
> MyConn.Execute(SQL)
>
> ' Close the connection
> MyConn.Close
> Set MyConn = Nothing
>
> ' Redirect to a displayed page
> Response.Redirect "standards.asp"
> %>
>
> Good luck.
> Peter
>
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Tuesday, July 31, 2001 5:50 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se-
> rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'.
>
>
> Hello Peter,
>
> I keep getting this error:
>
> **************************
> Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
>
> [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot use the column
> prefix 'recordField'. This must match the object in the UPDATE
> clause 'Standards'.
>
> ***************************
> response_update_entries.asp
> ***************************
> <%
> 'get the new values for the record(s) and place them into the fields of
> each record
> WhatShouldGoHerePeter =Replace(Request.Form
> ("recordField.Value"), "'", "''")
> id = CStr(Request.Form("ID"))
>
> Set MyConn=Server.CreateObject("ADODB.Connection")
> MyConn.Open "testdata"
> SQL = "Update Standards Set recordField.name="'request
> (WhatShouldGoherePeter)"' Where [ID] = "&id&""
> MyConn.Execute(SQL)
> MyConn.Close
> Set MyConn = Nothing
> Response.Redirect "standards.asp"
> %>
> **************************************
>
> I know you are going to laugh at my code. That's alright.
>
>
>
> > Morning Peter,
> >
> > Your code works very well. Thanks very much for taking the time to
> write
> > the code. You just don't know how happy I am when I get it. I have
> been
> > working on the second part of the UPDATE and I will send it
> over to you
> > for feedback.
> >
> > Thanks for the corrections that you sent me. You really
> shouldn't have
> > to. Your codes are very simple (and I don't mean it easy to
> understand)
> > and clean and so do your messages.
> >
> > Have a wonderful day.
> >
> > > Forgot to mention that I have not tested this code to verify that it
> > > works (or checked for typos, etc.).
> > >
> > > Also, I found a typo in my message. Reads:
> > >
> > > "I made it so that the none of the fields are hard coded on
> the first
> > > field, except for the ID field."
> > >
> > > Should Read:
> > >
> > > "I made it so that the none of the fields are hard coded,
> except for
> the
> > > ID field."
> > >
> > > Regards,
> > > Peter
> > >
> > >
Message #10 by "Peter Foti (PeterF)" <PeterF@S...> on Tue, 31 Jul 2001 15:23:54 -0400
|
|
Glad I could be of help. If you have any questions regarding the code I
sent, feel free to ask me if you're not sure what I was doing. :)
Regards,
Peter Foti
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Tuesday, July 31, 2001 7:42 PM
To: ASP Databases
Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se-
- rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'- .
Peter!!!
You're a savior! It works. Yeah!!! I cannot believe how
simple that is!
I was making it out to be more complex than it really is. I had
the right
idea but nothing would work. Thank you again!!
Now my job is to find another problem and ask for your help again since
you are my EXPERT. Just kidding, I think my job now is to go
back and try
to understand your codes so that next time, if I have to build
something
similar, I don't have to bother you again.
So many thanks for your quickly reply. See you next time. Not long.
Lan Chi.
Message #11 by "Lan Chi Tran" <lanchi1975@y...> on Tue, 31 Jul 2001 22:30:06
|
|
YES YES YES!! I need help, please!
I'm having lots of problems understanding your codes.
Thanks so much for volunteering to help out. =)
**************************
**************************
CreateEntries.asp
**************************
Questions:
1) In the for....loop (for each frmItem in rstemp.fields)
Is (fields) a reserved word????
Also in this loop (Response.Write(frmItem.name)
Is (name) a reserved word as well?
*************************
*************************
<%
Dim objConn, myDSN, SQL, rstemp
myDSN="DSN=testdata;UID=sa"
set objConn=server.createobject("adodb.connection")
objConn.open myDSN
SQL="Select * from STANDARDS"
set rstemp=objConn.execute(SQL)
%>
<table border=1 CELLPADDING='2' BGCOLOR='#D5D0DF'>
<H3>Please fill out this form.</H3><tr>
<%
for each frmItem in rstemp.fields
Response.write("<TR><TD><b>")
Response.write(frmItem.name)
Response.write "</b></TD><TD><INPUT TYPE=TEXT name=" &
frmItem.name & "></TD></TR>"
next
%>
</table>
<%
rstemp.close
set rstemp=nothing
objConn.close
set objConn=nothing
%>
******************************
******************************
response_create_entries.asp
******************************
Question:
1) Everything is clear except, SQLFields= "", what does it mean?
*******************************
*******************************
<%
dim SQLFields, SQLValues, SQLstr, objConn
SQLFields = ""
SQLValues = ""
'Create strings for use in SQL Insert.
For each frmItem in Request.Form
SQLFields = SQLFields & ", " & frmItem
SQLValues = SQLValues & ", '" & Request.Form(frmItem) & "'"
Next
'Remove the extra comma and space at the beginning
SQLFields = mid(SQLFields, 3)
SQLValues = mid(SQLValues, 3)
SQLstr = "INSERT INTO Standards (" & SQLFields & ") VALUES (" &
sqlvalues &")"
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
objconn.execute(SQLstr)
objconn.close
set objconn = nothing
Response.Write "You have successfully added a new record. Please click
the Standards link below to see the change."
Response.Write sitenav()
End If
%>
**********************************
**********************************
UpdateEntries.asp
This is confusing me.
***********************************
Questions:
1) Please help me understand your WHILE NOT......LOOP. I don't even know
where to ask. Sorry.
Is it a nested loop????
OK, let's begin. If there is no record, then EOF (While Not RS.EOF)
If that is the case, why do we need to print out the option tag/option
value???
With FOR .....LOOP.
Please be patient with me here.
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
RowStr = RowStr & ", " & recordField
End If
next
Are you saying that if a record without ID, then display Rowstr and
recordField??? GOSSSSSSSSSSSSSSH!!!
I can't visualize what this loop is doing????? Please put this in
words.
2) What does (Wend) means? While Not......Wend??? Like (MoveNext)????
3) What Cstr means??? id = Cstr(Request("ID"))
4) In the second WHILE....LOOP, you have this FOR....LOOP
if Not recordField.name = "ID" Then
Response.write("<table border=1
CELLPADDING='2' BGCOLOR='#D5D0DF'><TD><B>")
Response.write(recordField.name)
Response.write("<input type='text' name='")
Response.write(recordField.name)
Response.write("' size='20' value='")
Response.write(recordField)
Response.write("'><br>")
Response.write("</TD></B></table>")
End If
I found that you had two Response.write for RecordField.name and I
comment out the second one, it works fine.
Is there reason why you duplicate them????
What does the recordField by itself represent? Response.Write
(recordField)
**********************************
**********************************
<%
Dim MyConn, SQL, RS
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
' Get ALL fields
SQL = "Select * From Standards"
Set RS=MyConn.Execute(SQL)
%>
<form action="UpdateEntries.asp" method="Post">
<select name="ID">
<%
' Print out the results of the query as an OPTION in the select box
' Loop through all of the records
While Not RS.EOF
' Print the OPTION tag using the ID field to identify it
Response.write("<option value='" & RS("ID") & "'>")
' The OPTION text contains all of the fields in the recordset
' except for ID (no need to display that one)
RowStr = ""
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
RowStr = RowStr & ", " & recordField
End If
next
' Trim off the first comma
RowStr = mid(RowStr, 3)
' Print the row
Response.write(RowStr)
' Close the Option tag
Response.write("</option>")
RS.MoveNext
Wend
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
%>
</select>
<input type="submit" value="Select">
</form>
<%
id = Cstr(Request("ID"))
If id <> "" Then
Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "testdata"
SQL = "Select * From Standards Where ID = " & id
Set RS = MyConn.Execute(SQL)
' Print the form tag
Response.write("<form action='response_update_entries.asp'
method='Post'>")
' Loop through all of the records (should only be one at this
point, but just to be safe)
While Not RS.EOF
' Print the hidden input tag using the ID field to
identify it
Response.write("<input type='hidden' name='ID' value='" &
RS("ID") & "'>")
Response.write("<table border=1 CELLPADDING='2'
BGCOLOR='#D5D0DF'>")
' Print a text area for each field
for each recordField in RS.Fields
if Not recordField.name = "ID" Then
Response.write("<table border=1
CELLPADDING='2' BGCOLOR='#D5D0DF'><TD><B>")
Response.write(recordField.name)
Response.write("<input type='text' name='")
Response.write(recordField.name)
Response.write("' size='20' value='")
Response.write(recordField)
Response.write("'><br>")
Response.write("</TD></B></table>")
End If
next
RS.MoveNext
Wend
Response.write("<input type='submit' value='Update'>")
Response.write("</form>")
' Close the recordset
RS.Close
Set RS = Nothing
' Close the connection
MyConn.Close
Set MyConn = Nothing
Else
Response.Write "<p>Please Select a Record that has an ID</p>"
End If
%>
************************************
************************************
DONE!!!!!!!!!!
Glad I could be of help. If you have any questions regarding the code I
> sent, feel free to ask me if you're not sure what I was doing. :)
>
> Regards,
> Peter Foti
>
>
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Tuesday, July 31, 2001 7:42 PM
> To: ASP Databases
> Subject: [asp_databases] RE: Microsoft][ODBC SQL Server Driver][SQL Se-
> - rver- ]Line 1: Incorrect syntax near ', frmItemPaymentType'- .
>
>
> Peter!!!
>
> You're a savior! It works. Yeah!!! I cannot believe how
> simple that is!
> I was making it out to be more complex than it really is. I had
> the right
> idea but nothing would work. Thank you again!!
>
> Now my job is to find another problem and ask for your help again since
> you are my EXPERT. Just kidding, I think my job now is to go
> back and try
> to understand your codes so that next time, if I have to build
> something
> similar, I don't have to bother you again.
>
> So many thanks for your quickly reply. See you next time. Not long.
>
> Lan Chi.
>
|
|
|
View Cart
