Only one insert record server behavior?

[StargazeR82]

Why can you only have one insert record server behavior per page? I need to insert into two tables from one form? Any idea on how to do this?

 

[schase]

Its because DW is drop and drag code - one size fits almost all.

And they put the code right on the same page as the form.

what I would recommend - and there really is a couple ways to do this. Is to hand code.

First set your form to goto page2. with method of post.

Then on page two have something like this.


this first part - Dim's does two things, you first bring in the values from the form - note if you want to use a form value more than once you need to dim, session, cookie or insert and retrieve it.
the replace(xxxxx,"'","''&quot protects against SQL injection attack as well as allowing ' to be inserted.
Note items like strPremAmount are numbers - no need to do the ' item there




<% Dim strCSRname,strPremAmount,strClientName,strNotes
strCSRname=replace(request.form(&quot;txtProducer&quot,&quot;'&quot;,&quot;''&quot
strPremAmount=request.form(&quot;txtPremium&quot
strClientName=replace(request.form(&quot;txtClientName&quot,&quot;'&quot;,&quot;''&quot
strNotes=replace(request.form(&quot;txtNotes&quot,&quot;'&quot;,&quot;''&quot
%>



ok now that that is done, i am going to start the asp.
note - during the recordset items below - it MUST go in the same order as in your database.



<!--#INCLUDE file=&quot;connections/myconn.asp&quot;-->
<% Dim rs
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open myConn

IF Request.Form(&quot;txtClientName&quot<>&quot;&quot; THEN

SQLstmt = &quot;Insert into tblClient(fldClientNumber,fldClientName,fldPremium,,fldProducer,fldNotes)&quot;
SQLstmt = SQLstmt & &quot;VALUES('&quot;
SQLstmt = SQLstmt & &quot;','&quot; & strClientName & &quot;'&quot;
SQLstmt = SQLstmt & &quot;,&quot; & strPremAmount & &quot;&quot;
SQLstmt = SQLstmt & &quot;,&quot; & strCSRname & &quot;&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & strNotes & &quot;'&quot; & &quot&quot;
Set RS = Conn.Execute(SQLstmt)
End IF
set rs=nothing
Conn.Close
set conn=nothing
%>



Ok, note above see the ' next to ,&quot;? the ' needs to be on each side of whatever is inserted as text - there are no ' when you are inserting numbers (look at strPremAmount)
Now onto the second recordset.



<%
Set Conn = Server.CreateObject(&quot;ADODB.Connection&quot
conn.open myConn

IF Request.Form(&quot;txtClientName&quot<>&quot;&quot; THEN

SQLstmt = &quot;Insert into tbltwo(fldBusinessType,fldNotes)&quot;
SQLstmt = SQLstmt & &quot;VALUES('&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & ValOccupation & &quot;'&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & strInsCarrier & &quot;'&quot;
SQLstmt = SQLstmt & &quot;,'&quot; & strNotes & &quot;'&quot; & &quot&quot;
Set RS = Conn.Execute(SQLstmt)
End IF
set rs=nothing
Conn.Close
set conn=nothing
%>



You can then follow it up with a response.redirect to another page to exit on out.

All nice and neat, clean, easy. I know it looks very confusing, but sit for a moment and logically look it all over. You can see it is very methodical. &quot;Never underestimate the power of determination&quot;

Stuart

 

[lebisol]

grab a video tutorial here:

http://books.ulv.edu/Passy.html

sorry it was made for a &quot;slow friend&quot; of mine so excuse the (voice comments)

 

[TonyU]

[tt] WOW!!!!!

that guys sounded drunk... but good tutorial tho.. [sup]

T ® Ñ ¥
To keep a lamp burning we have to keep puting oil in it.

​

[/sup]

 

[StargazeR82]

Thanks, lebisol. That tutorial was just what I have been looking for.

 

[lebisol]

I think he was....LOL!
another note left out in tutorial...
sometimes it is &quot;a good idea&quot; (as mentioned in the post above by schase)
to add this line at the end of the command and set it to nothing:
....
set conn_name=nothing
%>
.....
I really can't tell when to use is except that sometimes I get errors unless I add it..other times it works great!

BTW.....TonyU ....the &quot;drunk&quot; effect was an inside joke for a friend of mine....also I DO have an accent
ANYWAY....the knowledge was passed and that's what I care about!