Insert into multiple tables plus array split

[schase]

I've tried to mix and match some code - well basically I have an entry table that I want to send to multiple tables.
Heres what I have, I get a error that I'm using a reserve name field - which I don't see. If anybody has any ideas how to make something like this work, please let me know.

Thank you in advance.

%
dim fldSurveyName, fldSurveyID, fldStation, fldCableYes, fldRadio, fldVehicle, fldSalesPerson, fldComment1, fldComment2
fldSurveyName = request.form("fldSurveyName&quot
fldSurveyID = request.form("fldSurveyID&quot
fldStation = request.form("fldStation&quot
fldCableYes = request.form("fldCableYes&quot
fldRadio = request.form("fldRadio&quot
fldVehicle = request.form("fldVehicle&quot
fldSalesPerson = request.form("fldSalesPerson&quot
fldComment1 = request.form("fldComment1&quot
fldComment2 = request.form("fldComment2&quot


dim sql1, sql2, sql3, sql4

sql1 = "INSERT INTO tblEntries fldSurveyID, fldStation, fldCableYes, fldRadio, fldVehicle, fldSalesPerson, fldComment1, fldComment2 VALUES ("
sql1 = sql1 & fldSurveyID & ","
sql1 = sql1 & fldStation & ","
sql1 = sql1 & fldCableYes & ","
sql1 = sql1 & fldRadio & ","
sql1 = sql1 & fldVehicle & ","
sql1 = sql1 & fldSalesPerson & ","
sql1 = sql1 & fldComment1 & ","
sql1 = sql1 & fldComment2 & &quot"

sql2 = "INSERT INTO tblSurveySOB"
if request(&quot;Selected2&quot <> &quot;&quot; Then
fldBroughtarray=split(request(&quot;fldBrought&quot,&quot;,&quot
for i=0 to ubound(fldBroughtarray)
if len(trim(fldBroughtarray(i))) > 0 then
rs.addnew
rs(&quot;fldSurveySOB&quot=trim(fldBroughtarray(i))
rs(&quot;fldSurveyID&quot= fldSurveyID
rs.update
end if
next
end if

sql3 = &quot;INSERT INTO tblSurveyCable&quot;
if request(&quot;Selected2&quot <> &quot;&quot; Then
fldNetworksarray=split(request(&quot;fldNetworks&quot,&quot;,&quot
for i=0 to ubound(fldNetworksarray)
if len(trim(fldNetworksarray(i))) > 0 then
rs.addnew
rs(&quot;fldSurveyCable&quot=trim(fldBroughtarray(i))
rs(&quot;fldSurveyID&quot= fldSurveyID
rs.update
end if
next
end if

sql4 = &quot;INSERT INTO tblSurveyTime&quot;
if request(&quot;Selected2&quot <> &quot;&quot; Then
fldTimearray=split(request(&quot;fldTime&quot,&quot;,&quot
for i=0 to ubound(fldTimearray)
if len(trim(fldTimearray(i))) > 0 then
rs.addnew
rs(&quot;fldSurveyTime&quot=trim(fldTimearray(i))
rs(&quot;fldSurveyID&quot= fldSurveyID
rs.update
end if
next
end if

dim con
set con = server.createobject (&quot;ADODB.Connection&quot
con.open (&quot;DSN=dbAdvertising&quot

con.execute sql1
con.execute sql2
con.execute sql3
con.execute sql4

con.close
set con = nothing
%> &quot;Insert witty remark here&quot;

Stuart

 

[SarkMan]

put () around fields in insert statement as follows

sql1 = &quot;INSERT INTO tblEntries (fldSurveyID, fldStation, fldCableYes, fldRadio, fldVehicle, fldSalesPerson, fldComment1, fldComment2) VALUES (&quot;
sql1 = sql1 & fldSurveyID & &quot;,&quot;
sql1 = sql1 & fldStation & &quot;,&quot;
sql1 = sql1 & fldCableYes & &quot;,&quot;
sql1 = sql1 & fldRadio & &quot;,&quot;
sql1 = sql1 & fldVehicle & &quot;,&quot;
sql1 = sql1 & fldSalesPerson & &quot;,&quot;
sql1 = sql1 & fldComment1 & &quot;,&quot;
sql1 = sql1 & fldComment2 & &quot&quot;

also if any of the fields are varchar/string then put quotes around the values as below

sql1 = &quot;INSERT INTO tblEntries (fldSurveyID, fldStation, fldCableYes, fldRadio, fldVehicle, fldSalesPerson, fldComment1, fldComment2) VALUES (&quot;
sql1 = sql1 & fldSurveyID & &quot;,&quot;
sql1 = sql1 & fldStation & &quot;,&quot;
sql1 = sql1 & fldCableYes & &quot;,&quot;
sql1 = sql1 & fldRadio & &quot;,&quot;
sql1 = sql1 & fldVehicle & &quot;,'&quot;
sql1 = sql1 & fldSalesPerson & &quot;',&quot;
sql1 = sql1 & fldComment1 & &quot;,&quot;
sql1 = sql1 & fldComment2 & &quot&quot;

 

[schase]

Tried the () around the field names, but got the same error. &quot;Insert witty remark here&quot;

Stuart

 

[SarkMan]

Here is some code that works with SQL server for the addnew method.


<%@ Language=VBScript %>

<!--METADATA TYPE=&quot;typelib&quot; UUID=&quot;00000205-0000-0010-8000-00AA006D2EA4&quot; NAME=&quot;ADODB Type Library&quot; -->

<HTML>
<HEAD>
<META NAME=&quot;GENERATOR&quot; Content=&quot;Microsoft Visual Studio 6.0&quot;>
</HEAD>
<BODY>
<%

set cn=server.CreateObject(&quot;ADODB.Connection&quot
set rs=server.CreateObject(&quot;ADODB.Recordset&quot

cn.Open &quot;YourConnection string&quot;



rs.Open &quot;Table1&quot;,cn,adOpenKeyset,adLockOptimistic ,adCmdTableDirect

rs.AddNew
rs(&quot;LastName&quot=&quot;LastName&quot;
rs(&quot;firstName&quot=&quot;FirstName&quot;
rs(&quot;Birthdate&quot=&quot;1/16/1975&quot;
rs.Update

Response.Write &quot;error&quot; & err.description
%>

</BODY>
</HTML>

Also you don't need to
con.execute sql2
con.execute sql3
con.execute sql4
since there are not doing anything

 

[schase]

hmmm,

I figured I'd need too as they insert into separate tables.

Thats why I needed the unbound array. &quot;Insert witty remark here&quot;

Stuart

 

[SarkMan]

When you use the addnew and update method, the database is getting updated.

i.e. the following code is all you need to update the database when using the addnew

set cn=server.CreateObject(&quot;ADODB.Connection&quot
set rs=server.CreateObject(&quot;ADODB.Recordset&quot
cn.Open &quot;YourConnection string&quot;
rs.Open &quot;TableName&quot;,cn,adOpenKeyset,adLockOptimistic ,adCmdTableDirect


rs.AddNew
rs(&quot;Field1&quot=&quot;NewValue1&quot;
rs(&quot;Field2&quot=&quot;NewValue2&quot;
rs(&quot;Field3&quot=&quot;NewValue3&quot;
rs.Update