|
 |
asp_databases thread: Insert/update (ASP) - your help is very appreciated!
Message #1 by "Lan Chi Tran" <lanchi1975@y...> on Thu, 26 Jul 2001 15:55:56
|
|
This might be unclear to you but I have tried my best to explain this.
So, please bare with me on this. My question is: Is there any way to
make an insert/update statement without hard-coded the fieldnames?
EX: THis is what you usually see:
INSERT INTO Customers (firstname, lastname)
VALUES('" & request ("firstname")& "', '" & request("lastname")"
Problem with this INSERT/UPDATE is: If a user add a new fieldname (column)
in the database, then he/she has to go back to her asp code
(INSERT/UPDATE) and add that new fieldname.
Question: Wouldn't it be easier if we could make an INSERT/UPDATE
dynamically without hard-coded those fieldnames? So that it doensn't
matter how many new fieldnames being added, there is no need for us to go
back to our INSERT/UPDATE and make the changes.
DETAILS: This loop will display all the current fieldnames. Notice that
I didn't hard-code any fieldname. Howerver, I don't know how to make the
INSERT/UPDATE.
<%
for each fieldname in rstemp.fields%>
<TR><TD><b><%=fieldname.name%></TD></b><TD><INPUT TYPE=TEXT name=<%
=fieldname.name %>></TD></TD></TR>
<% next %>
*************************
Message #2 by "Craig Flannigan" <ckf@k...> on Thu, 26 Jul 2001 16:04:50 +0100
|
|
you can automatically retrieve a list of fieldnames from a table, then
execute your Insert/Update query.
That way you could use the results to build the start of the query.
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Thursday 26 July 2001 15:56
To: ASP Databases
Subject: [asp_databases] Insert/update (ASP) - your help is very
appreciated!
This might be unclear to you but I have tried my best to explain this.
So, please bare with me on this. My question is: Is there any way to
make an insert/update statement without hard-coded the fieldnames?
EX: THis is what you usually see:
INSERT INTO Customers (firstname, lastname)
VALUES('" & request ("firstname")& "', '" & request("lastname")"
Problem with this INSERT/UPDATE is: If a user add a new fieldname (column)
in the database, then he/she has to go back to her asp code
(INSERT/UPDATE) and add that new fieldname.
Question: Wouldn't it be easier if we could make an INSERT/UPDATE
dynamically without hard-coded those fieldnames? So that it doensn't
matter how many new fieldnames being added, there is no need for us to go
back to our INSERT/UPDATE and make the changes.
DETAILS: This loop will display all the current fieldnames. Notice that
I didn't hard-code any fieldname. Howerver, I don't know how to make the
INSERT/UPDATE.
<%
for each fieldname in rstemp.fields%>
<TR><TD><b><%=fieldname.name%></TD></b><TD><INPUT TYPE=TEXT name=<%
=fieldname.name %>></TD></TD></TR>
<% next %>
Message #3 by Steve Carter <Steve.Carter@t...> on Thu, 26 Jul 2001 16:18:48 +0100
|
|
Try like this
<%
dim names, values, SQL
' Get the names
names = ""
for each f in rstemp.fields
names = names & ", " & f.name
next
names = mid(names,3)
' Now do the same for values (but strings must
' be in single quotes and dates must be in hashes
values = ""
for each f in rstemp.fields
select case f.type ' look this up, I'm guessing now
case adVarChar, adChar ' look this up
' It's a string value, so use quotes
values = values & ", '" & request.form(f.name) & "'"
case adInteger ' look this up
values = values & ", " & request.form(f.name)
case adDate ' look this up
values = values & ", #" & request.form(f.name) & "#"
case else
' Other type, throw it away since I don't know what to do with it
' and raise an error maybe
end select
next
values = mid(values,3)
SQL = "INSERT INTO Customers (" & names & ") VALUES " & values
%>
Message #4 by "Eoghan O'Byrne" <Eoghan.O'Byrne@W...> on Thu, 26 Jul 2001 16:25:29 +0100
|
|
Hey Lan,
You can use a loop like that to make the SQL query,
<%
columns =3D ""
for each fieldname in rstemp.fields
WHILE NOT rstemp.EOF
IF NOT rstemp.EOF THEN
columns =3D columns & fieldname.name & ", "
END IF
WEND
next
colLen=3D LEN(columns) - 2
columns =3D TRIM(LEFT(columns, colLen))
values =3D ""
FOR EACH fieldname IN rstemp.fields
WHILE NOT rstemp.EOF
IF NOT rstemp.EOF THEN
values =3D values & "'" & fieldname.Value & "', "
END IF
WEND
NEXT
valLen =3D LEN(values) - 2
values =3D TRIM(LEFT(values, valLen))
strSQL =3D "INSERT INTO table(" & columns & ") VALUES (" & values & ")"
Hope this helps,
Regards,
Eoghan O'Byrne,
Webmaster,
Web Applications Developer,
Ward Solutions Ltd.
Phone + 353 1 6420100
Desk + 353 1 6420129
Mobile + 353 87 4145794
Fax + 353 1 6420110
email Eoghan.O'Byrne@w...
www.ward.ie
-----Original Message-----
From: Lan Chi Tran [mailto:lanchi1975@y...]
Sent: Thursday, July 26, 2001 4:56 PM
To: ASP Databases
Subject: [asp_databases] Insert/update (ASP) - your help is very
appreciated!
This might be unclear to you but I have tried my best to explain this.
So, please bare with me on this. My question is: Is there any way to
make an insert/update statement without hard-coded the fieldnames?
EX: THis is what you usually see:
INSERT INTO Customers (firstname, lastname)
VALUES('" & request ("firstname")& "', '" & request("lastname")"
Problem with this INSERT/UPDATE is: If a user add a new fieldname
(column)
in the database, then he/she has to go back to her asp code
(INSERT/UPDATE) and add that new fieldname.
Question: Wouldn't it be easier if we could make an INSERT/UPDATE
dynamically without hard-coded those fieldnames? So that it doensn't
matter how many new fieldnames being added, there is no need for us to
go
back to our INSERT/UPDATE and make the changes.
DETAILS: This loop will display all the current fieldnames. Notice
that
I didn't hard-code any fieldname. Howerver, I don't know how to make
the
INSERT/UPDATE.
<%
for each fieldname in rstemp.fields%>
<TR><TD><b><%=3Dfieldname.name%></TD></b><TD><INPUT TYPE=3DTEXT
name=3D<%
=3Dfieldname.name %>></TD></TD></TR>
<% next %>
*************************
Message #5 by "Peter Foti (PeterF)" <PeterF@S...> on Thu, 26 Jul 2001 12:44:12 -0400
|
|
Perhaps you could loop through the Request.form to generate your ASP
code?
<%
SQLFields =3D ""
SQLValues =3D ""
For each frmItem in Request.Form
SQLFields =3D SQLFields & ", " & intNumItems
SQLValues =3D SQLValues & ", '" & Request.Form(intNumItems) & "'"
Next
SQLFields =3D mid(SQLFields, 3)
SQLValues =3D mid(SQLValues, 3)
SQLstr =3D "INSERT INTO Customers (" & SQLFields & ") VALUES(" &
SQLValues
& ")"
%>
Note that the above example will only work if all of the values are in
text or memo fields in the database.
Also, it looks like you have some extra closing tags in there, and some
closing tags out of order (the bold should be ended before the TD)
Also, your code will run faster if you don't switch back and forth
between ASP and HTML code. That is, put the contents of your FOR loop
in ASP and use Response.write. Like so:
<%
for each fieldname in rstemp.fields
Response.write(" <TR>")
Response.write(" <TD>")
Response.write(" <b>" & fieldname.name &
"</b>")
Response.write(" </TD>")
Response.write(" <TD>")
Response.write(" <INPUT TYPE=3D'TEXT'
name=3D'" & fieldname.name & "'>")
Response.write(" </TD>")
Response.write(" </TR>")
next
%>
This example could be simplified, but I wanted you to see where the
closing tags belonged. Here's the faster version:
<%
for each fieldname in rstemp.fields
Response.write(" <TR><TD><b>"
Response.write(fieldname.name)
Response.write("</b></TD><TD><INPUT TYPE=3D'TEXT' name=3D'")
Response.write(fieldname.name)
Response.write("'></TD></TR>")
next
%>
You "could" put this all into one response.write, but you would have to
concatinate the strings (using the & ) and this is slower than doing
separate response.writes.
Hope this helps,
Peter
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Thursday, July 26, 2001 3:56 PM
> To: ASP Databases
> Subject: [asp_databases] Insert/update (ASP) - your help is very
> appreciated!
>
>
> This might be unclear to you but I have tried my best to
> explain this.
> So, please bare with me on this. My question is: Is there
> any way to
> make an insert/update statement without hard-coded the fieldnames?
>
> EX: THis is what you usually see:
>
> INSERT INTO Customers (firstname, lastname)
> VALUES('" & request ("firstname")& "', '" &
> request("lastname")"
>
> Problem with this INSERT/UPDATE is: If a user add a new
> fieldname (column)
> in the database, then he/she has to go back to her asp code
> (INSERT/UPDATE) and add that new fieldname.
>
> Question: Wouldn't it be easier if we could make an INSERT/UPDATE
> dynamically without hard-coded those fieldnames? So that it doensn't
> matter how many new fieldnames being added, there is no need
> for us to go
> back to our INSERT/UPDATE and make the changes.
>
> DETAILS: This loop will display all the current fieldnames.
> Notice that
> I didn't hard-code any fieldname. Howerver, I don't know how
> to make the
> INSERT/UPDATE.
>
> <%
> for each fieldname in rstemp.fields%>
> <TR><TD><b><%=3Dfieldname.name%></TD></b><TD><INPUT TYPE=3DTEXT
name=3D<%
> =3Dfieldname.name %>></TD></TD></TR>
> <% next %>
>
>
> *************************
>
>
Message #6 by "Lan Chi Tran" <lanchi1975@y...> on Thu, 26 Jul 2001 19:19:30
|
|
Solution #5.
The error I am getting is something to do with (Expected end of
statement ). Since I have no clue on how it works, I can't figure out how
to solve this. Please advise. Thank you very much.
**************************
create_entries.asp
*************************
<<%
Dim objConn, myDSN, SQL
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 fieldname in rstemp.fields
Response.write(" <TR><TD><b>")
Response.write(fieldname.name)
Response.write("</b></TD><TD><INPUT TYPE=3D'TEXT' name=3D'")
Response.write(fieldname.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></TD>
<TD><INPUT TYPE=RESET VALUE=RESET></TD>
</TR>
</form></TABLE>
*********************************
response_create_entries6.asp
*********************************
<%
<%
dim objConn, rs, names, values, SQL, SQLstr
Set objConn = Server.CreateObject ("ADODB.Connection")
objConn.Open "testdata"
set rstemp=objConn.execute(SQL)
SQLFields =3D ""
SQLValues =3D ""
For each frmItem in Request.Form
SQLFields =3D SQLFields & ", " & intNumItems
SQLValues =3D SQLValues & ", '" & Request.Form(intNumItems) & "'"
Next
SQLFields =3D mid(SQLFields, 3)
SQLValues =3D mid(SQLValues, 3)
SQLstr =3D "INSERT INTO Standards (" & SQLFields & ") VALUES(" &
SQLValue
objconn.execute(SQLstr)
rstemp.close
set rstemp=nothing
objconn.close
set objconn = nothing
%>
</form>
</table>
*******************************
ERRORS---
*********************************
Microsoft VBScript compilation error '800a0401'
Expected end of statement
/ASP_Modify/response_create_entries7.asp, line 7
SQLFields =3D ""
------------^
LINE 7 is (SQLFields =3D "")
**************************************************
Message #7 by "Peter Foti (PeterF)" <PeterF@S...> on Thu, 26 Jul 2001 14:45:39 -0400
|
|
Hello Lan,
It seems that when my message posted, it did not come through as it
should have. That is, there are a bunch of 3D and 20 values spewed
into
the message that were not there when I sent it. I assume it must have
something to do with the way my mail messages are formatted or
something.
Do this:
1. Get rid of the 3D in the INPUT (there are 2 of them).
2. Get rid of the 3D when SQLFields and SQLValues are initialized to
an
empty string.
3. Get rid of the 3D inside the FOR loop (there are 2 of them).
4. Get rid of the equal sign and the 20 after the NEXT at the end of
the loop.
5. Get rid of the 3D in the mid statements (there are 2 of them).
6. Get rid of the 3D in the SQLstr initialization.
Also, you should surround the values of your Submit buttons. Like so:
<INPUT TYPE=3D"SUBMIT" VALUE=3D"CREATE ENTRIES">
Give this a shot and see if you have any better results. :)
Good luck,
Peter
> -----Original Message-----
> From: Lan Chi Tran [mailto:lanchi1975@y...]
> Sent: Thursday, July 26, 2001 7:20 PM
> To: ASP Databases
> Subject: [asp_databases] INSERT/UPDATE (ASP). YOUR HELP IS VERY
> APPRECIATED!!!
>
>
> Solution #5.
> The error I am getting is something to do with (Expected end of
> statement ). Since I have no clue on how it works, I can't
> figure out how
> to solve this. Please advise. Thank you very much.
>
> **************************
> create_entries.asp
> *************************
> <<%
> Dim objConn, myDSN, SQL
> 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 fieldname in rstemp.fields
> Response.write(" <TR><TD><b>")
> Response.write(fieldname.name)
> Response.write("</b></TD><TD><INPUT TYPE=3D3D'TEXT' name=3D3D'")
> Response.write(fieldname.name)
> Response.write("'></TD></TR>")
> next
> %>
> </table>
> <%
> rstemp.close
> set rstemp=3Dnothing
> objConn.close
> set objConn=3Dnothing
> %>
> <P>
> <TR><TD><INPUT TYPE=3DSUBMIT VALUE=3DCREATE ENTRIES></TD>
> <TD><INPUT TYPE=3DRESET VALUE=3DRESET></TD>
> </TR>
> </form></TABLE>
>
> *********************************
> response_create_entries6.asp
> *********************************
> <%
>
>
> <%
> dim objConn, rs, names, values, SQL, SQLstr
>
> Set objConn =3D Server.CreateObject ("ADODB.Connection")
> objConn.Open "testdata"
> set rstemp=3DobjConn.execute(SQL)
>
> SQLFields =3D3D ""
> SQLValues =3D3D ""
>
> For each frmItem in Request.Form
> SQLFields =3D3D SQLFields & ", " & intNumItems
> SQLValues =3D3D SQLValues & ", '" &
> Request.Form(intNumItems) & "'"
> Next
> =3D20
> SQLFields =3D3D mid(SQLFields, 3)
> SQLValues =3D3D mid(SQLValues, 3)
>
> SQLstr =3D3D "INSERT INTO Standards (" & SQLFields & ") VALUES(" &
=3D
> SQLValue
>
> objconn.execute(SQLstr)
> rstemp.close
> set rstemp=3Dnothing
> objconn.close
> set objconn =3D nothing
>
> %>
> </form>
> </table>
> *******************************
> ERRORS---
> *********************************
> Microsoft VBScript compilation error '800a0401'
>
> Expected end of statement
>
> /ASP_Modify/response_create_entries7.asp, line 7
>
> SQLFields =3D3D ""
> ------------^
>
>
> LINE 7 is (SQLFields =3D3D "")
> **************************************************
>
>
|
|
|
View Cart
