Forums
This topic is locked
Insert multiple rows: from table form to SQL table
15 Sep 2008 12:58:12 ed trvl posted:
Hi there,I'm using Dreamweaver & I'm trying to insert multiple rows into an SQL database, I need one row from the table for to insert in one row into the SQL table, any help would be greatly appreciated, thanks in advance
Here's the code if it helps:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../../Connections/BILLING.asp" -->
<%
FEQUIPID = Request.form("EQUIPID"
' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.MakeEQUIPID = Replace(FEQUIPID, ", ", "' OR EQUIPID= '"
' Replace comma-space to single quote - OR Recity= - single quote%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_BILLING_STRING
Recordset1.Source = "SELECT *, EQUIPID FROM dbo.BP_Progressor_Non_P2P_Equip_Id WHERE (EQUIPID = '" & MakeEQUIPID & "')"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<div align="center">
<form id="form1" name="form1">
<p> </p>
<table width="751">
<tr>
<td width="175"><div align="center"></div></td>
<td width="73"><div align="center"></div></td>
<td width="144"> </td>
<td width="167"><div align="center"></div></td>
<td width="168"><div align="center"></div></td>
</tr>
<tr>
<td><div align="center"><%=(Recordset1.Fields.Item("EQUIPID"
.Value)%></div></td><td><div align="center"><%=(Recordset1.Fields.Item("EQUIPNAME"
.Value)%></div></td><td><div align="center">
<input name="Job_No" type="text" id="Job_No" value="<%= Request.QueryString("Job_No"
%>" /></div></td>
<td><div align="center">
<input name="VAT" type="text" id="VAT" />
</div></td>
<td><div align="center">
<input name="Rate" type="text" id="Rate" />
</div></td>
</tr>
</table>
<p>
<input type="submit" name="Submit" value="Submit" />
</p>
<p> </p>
</form>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
Replies
Replied 16 Sep 2008 13:00:18
16 Sep 2008 13:00:18 ed trvl replied:
The Select page is allows multi selection on a dynamic list. Therefore the number of rows to insert is also dynamic depending on the selection of the user.
I'm almost there but the page won't insert for some reason, and I'm not getting any error, any suggestions? Thanks in advance
Insert page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../../../Connections/BILLING.asp" -->
<%
If Request.Form("insert" = "true" Then
Dim i, currentField
for i = 1 to Request.Form("icount"
currentField= "RATE" & i
VAT1= "VAT" & i
If Request.Form(currentField) <> "" Then
'Insert the actor entered
Call sAddEquip(Request.Form(VAT1), Request.Form(currentField))
End If
Next
Sub sAddEquip(pVAT, pRATE_PER_PERIOD)
set ProgressorMultiInsert = Server.CreateObject("ADODB.Command"
ProgressorMultiInsert.ActiveConnection = MM_BILLING_STRING
ProgressorMultiInsert.CommandText = "INSERT INTO BP_Progressor_Non_P2P_RC ( VAT, RATE_PER_PERIOD ) VALUES ( " & pVAT & ", " & pRATE_PER_PERIOD & " ) "
ProgressorMultiInsert.CommandType = 1
ProgressorMultiInsert.CommandTimeout = 0
ProgressorMultiInsert.Prepared = true
ProgressorMultiInsert.Execute()
End Sub
End If
%>
<%
FEQUIPID = Request.form("EQUIPID" ' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.
MakeEQUIPID = Replace(FEQUIPID, ", ", "' OR EQUIPID= '" ' Replace comma-space to single quote - OR EQUIPID= - single quote
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_BILLING_STRING
Recordset1.Source = "SELECT SUM(EQUIPID1) AS icount FROM (SELECT COUNT(EQUIPID) AS EQUIPID1 FROM dbo.BP_Progressor_Non_P2P_Equip_Id GROUP BY EQUIPID HAVING (EQUIPID = '" & MakeEQUIPID & "')) DERIVEDTBL "
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
FEQUIPID = Request.form("EQUIPID" ' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.
MakeEQUIPID = Replace(FEQUIPID, ", ", "' OR EQUIPID= '" ' Replace comma-space to single quote - OR EQUIPID= - single quote
%>
<%
Dim Recordset2
Dim Recordset2_numRows
Set Recordset2 = Server.CreateObject("ADODB.Recordset"
Recordset2.ActiveConnection = MM_BILLING_STRING
Recordset2.Source = "SELECT *, EQUIPID FROM dbo.BP_Progressor_Non_P2P_Equip_Id WHERE (EQUIPID = '" & MakeEQUIPID & "')"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()
Recordset2_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<p align="center"> </p>
<p> </p>
<div align="center">
<p> </p>
<form action="Insert.asp" method="post" name="form1" id="form1">
<p>
<input name="icount" type="text" id="icount" value="<%= Request.Form("icount" %>" />
</p>
<table width="703">
<tr>
<td width="151"><div align="center"></div></td>
<td width="181"><div align="center"></div></td>
<td width="144"><div align="center"></div></td>
<td width="144"><div align="center"></div></td>
<td width="101"><div align="center"></div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset2.EOF))
%>
<tr>
<td><div align="center"><%=(Recordset2.Fields.Item("EQUIPID".Value)%></div></td>
<td><div align="center"><%=(Recordset2.Fields.Item("EQUIPNAME".Value)%></div></td>
<td><div align="center">
<input name="VAT<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" type="text" id="VAT<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" />
</div></td>
<td><div align="center">
<input name="RATE<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" type="text" id="RATE<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" />
</div></td>
<td><div align="center"></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset2.MoveNext()
Wend
%>
</table>
<p> </p>
<p>
<input name="insert" type="hidden" id="insert" value="true" />
<input type="submit" name="Submit" value="Submit" />
</p>
<p> </p>
</form>
<p> </p>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
Edited by - edtrvl on 16 Sep 2008 20:17:46
I'm almost there but the page won't insert for some reason, and I'm not getting any error, any suggestions? Thanks in advance
Insert page:
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<!--#include file="../../../../Connections/BILLING.asp" -->
<%
If Request.Form("insert"
= "true" ThenDim i, currentField
for i = 1 to Request.Form("icount"
currentField= "RATE" & i
VAT1= "VAT" & i
If Request.Form(currentField) <> "" Then
'Insert the actor entered
Call sAddEquip(Request.Form(VAT1), Request.Form(currentField))
End If
Next
Sub sAddEquip(pVAT, pRATE_PER_PERIOD)
set ProgressorMultiInsert = Server.CreateObject("ADODB.Command"
ProgressorMultiInsert.ActiveConnection = MM_BILLING_STRING
ProgressorMultiInsert.CommandText = "INSERT INTO BP_Progressor_Non_P2P_RC ( VAT, RATE_PER_PERIOD ) VALUES ( " & pVAT & ", " & pRATE_PER_PERIOD & " ) "
ProgressorMultiInsert.CommandType = 1
ProgressorMultiInsert.CommandTimeout = 0
ProgressorMultiInsert.Prepared = true
ProgressorMultiInsert.Execute()
End Sub
End If
%>
<%
FEQUIPID = Request.form("EQUIPID"
' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.MakeEQUIPID = Replace(FEQUIPID, ", ", "' OR EQUIPID= '"
' Replace comma-space to single quote - OR EQUIPID= - single quote%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset"
Recordset1.ActiveConnection = MM_BILLING_STRING
Recordset1.Source = "SELECT SUM(EQUIPID1) AS icount FROM (SELECT COUNT(EQUIPID) AS EQUIPID1 FROM dbo.BP_Progressor_Non_P2P_Equip_Id GROUP BY EQUIPID HAVING (EQUIPID = '" & MakeEQUIPID & "')) DERIVEDTBL "
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
FEQUIPID = Request.form("EQUIPID"
' Convert the info from the form to a string: Amsterdam, Schiphol, ... etc.MakeEQUIPID = Replace(FEQUIPID, ", ", "' OR EQUIPID= '"
' Replace comma-space to single quote - OR EQUIPID= - single quote%>
<%
Dim Recordset2
Dim Recordset2_numRows
Set Recordset2 = Server.CreateObject("ADODB.Recordset"
Recordset2.ActiveConnection = MM_BILLING_STRING
Recordset2.Source = "SELECT *, EQUIPID FROM dbo.BP_Progressor_Non_P2P_Equip_Id WHERE (EQUIPID = '" & MakeEQUIPID & "')"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()
Recordset2_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat1__numRows
%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="www.w3.org/1999/xhtml">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
<title>Untitled Document</title>
</head>
<body>
<p align="center"> </p>
<p> </p>
<div align="center">
<p> </p>
<form action="Insert.asp" method="post" name="form1" id="form1">
<p>
<input name="icount" type="text" id="icount" value="<%= Request.Form("icount"
%>" /></p>
<table width="703">
<tr>
<td width="151"><div align="center"></div></td>
<td width="181"><div align="center"></div></td>
<td width="144"><div align="center"></div></td>
<td width="144"><div align="center"></div></td>
<td width="101"><div align="center"></div></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT Recordset2.EOF))
%>
<tr>
<td><div align="center"><%=(Recordset2.Fields.Item("EQUIPID"
.Value)%></div></td><td><div align="center"><%=(Recordset2.Fields.Item("EQUIPNAME"
.Value)%></div></td><td><div align="center">
<input name="VAT<%=(Recordset2.Fields.Item("Non_P2P_Id"
.Value)%>" type="text" id="VAT<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" /></div></td>
<td><div align="center">
<input name="RATE<%=(Recordset2.Fields.Item("Non_P2P_Id"
.Value)%>" type="text" id="RATE<%=(Recordset2.Fields.Item("Non_P2P_Id".Value)%>" /></div></td>
<td><div align="center"></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset2.MoveNext()
Wend
%>
</table>
<p> </p>
<p>
<input name="insert" type="hidden" id="insert" value="true" />
<input type="submit" name="Submit" value="Submit" />
</p>
<p> </p>
</form>
<p> </p>
</div>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
Edited by - edtrvl on 16 Sep 2008 20:17:46
Replied 20 Oct 2008 17:34:28
20 Oct 2008 17:34:28 ed trvl replied:
Any suggestions?