Adding multiple records into database 1

[Beng79]

Hi All,

I have the following script:
<%
Dim connectAdd
Dim recstAdd
Dim SQL
Dim strProjID1, strProjID2, strProjID3, strProjID4, strProjID5
Dim strFeature1, strFeature2, strFeature3, strFeature4, strFeature5
%>
<%
Set connectAdd = Server.CreateObject("ADODB.Connection")
connectAdd.Open("DRIVER={Microsoft Access Driver (*.mdb)};" _
& "DBQ=" & Server.MapPath("Estimation.mdb"))

strProjID1 = request.form("ProjID1")
strProjID2 = request.form("ProjID2")
strProjID3 = request.form("ProjID3")
strProjID4 = request.form("ProjID4")
strProjID5 = request.form("ProjID5")

strFeature1 = request.form("Feature1")
strFeature2 = request.form("Feature2")
strFeature3 = request.form("Feature3")
strFeature4 = request.form("Feature4")
strFeature5 = request.form("Feature5")

SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "','" & strProjID1 & "')"
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature2 & "','" & strProjID2 & "')"
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature3 & "','" & strProjID3 & "')"
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature4 & "','" & strProjID4 & "')"
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature5 & "','" & strProjID5 & "')"

Set recstAdd = connectAdd.Execute(SQL)

Response.Redirect("New Features Added.htm")

recstAdd.Close
Set recstAdd = Nothing
connectAdd.Close
Set connectAdd = Nothing
%>

The intention for the script is to capture data entered from the textboxes into the database.

I have tried with only 1 entry of data which means ->
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "','" & strProjID1 & "')"
and it works well.

But with multiple insert statements, only the last entry ->
SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature5 & "','" & strProjID5 & "')"
is captured into the database.

Anyone knows how can I capture multiple entries?

Thank you

 

[Sheco]

Every time you set the value of the SQL variable the previous value is lost.

So you'll need to execute the INSERT statement for each unique value of SQL like this:

    SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature1 & "','" & strProjID1 & "')"
    connectAdd.Execute SQL

    SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature2 & "','" & strProjID2 & "')"
    connectAdd.Execute SQL

    SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature3 & "','" & strProjID3 & "')"
    connectAdd.Execute SQL

    SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature4 & "','" & strProjID4 & "')"
    connectAdd.Execute SQL

    SQL="INSERT INTO FuncFeat(FeatName, ProjID) VALUES ('"& strFeature5 & "','" & strProjID5 & "')"     
    connectAdd.Execute SQL

 

[Dashley]

Beng79.

Its because the last SQL= statment is what it equals and it ignores the first.
If you say
Sql=1
sql=2
sql=3

the sql will equal 3 since its the last value You set Sql to.

The way your doing it you'll have to have 5 sql statements:
sql1
sql2
sql3
slq4
sql5

You could just loop through the form values.

For I = 1 to request.form.count
If request.form.item(I) <> "" then

Put you sql here

execute the sql

end if
next