Forums
This topic is locked
multiple inserts
Posted 17 Jan 2002 22:56:52
1
has voted
17 Jan 2002 22:56:52 David Behan posted:
anyone know how to do multiple inserts on the one page???? 1 insert for each line. Maybe tick box on left - if ticked it will insert record!!!---------------------
David Behan - bmor.com
Replies
Replied 17 Jan 2002 23:24:44
17 Jan 2002 23:24:44 Owen Eastwick replied:
Here's some code from a page (MultiUpdate.asp) that updates multiple fields based on the status of a radio button:
Basically what I've done is put the update command within the repeat region, if the status changes (In or Out) as determined by the radio button value the record is updated.
You could do something very similar by using Command(Stored Procedure) then select Insert from the Type list. Then place the Insert command within the repeat region in a similar fashion. Then place an if condition around it something like:
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Edited by - oeastwick on 17 Jan 2002 23:27:39
<%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/TestTDSFdemoOLE2.asp" -->
<
%set rsTest = Server.CreateObject("ADODB.Recordset")
rsTest.ActiveConnection = MM_TestTDSFdemoOLE2_STRING
rsTest.Source = "SELECT * FROM tblTest"
rsTest.CursorType = 0
rsTest.CursorLocation = 2
rsTest.LockType = 3
rsTest.Open()
rsTest_numRows = 0
%>
<
%Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsTest_numRows = rsTest_numRows + Repeat1__numRows
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<% If Request.QueryString("Updated") = 1 Then %>
<b> <font face="Arial, Helvetica, sans-serif" size="-2">Records Updated</font></b>
<% End If %>
<form name="form1" method="post" action="MultiUpdate.asp">
<table width="370" border="1" cellspacing="0" cellpadding="2" bordercolor="#333333">
<tr bgcolor="#CCCCCC">
<td width="63"><b><font face="Arial, Helvetica, sans-serif" size="-2">Rec</font></b></td>
<td width="124"><b><font face="Arial, Helvetica, sans-serif" size="-2">Text</font></b></td>
<td width="125"><b><font face="Arial, Helvetica, sans-serif" size="-2">Status</font></b></td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsTest.EOF))
%>
<tr>
<td width="63"><font face="Arial, Helvetica, sans-serif" size="-2"><%=(rsTest.Fields.Item("RecID").Value)%> </font></td>
<td width="124"><font face="Arial, Helvetica, sans-serif" size="-2"><%=(rsTest.Fields.Item("TextField").Value)%></font></td>
<td width="125"><font face="Arial, Helvetica, sans-serif" size="-2">
In
<input <%If (CStr(rsTest.Fields.Item("StatusField").Value) = CStr("In")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="radStatus<%=(rsTest.Fields.Item("RecID").Value)%>" value="In">
Out
<input <%If (CStr(rsTest.Fields.Item("StatusField").Value) = CStr("Out")) Then Response.Write("CHECKED") : Response.Write("")%> type="radio" name="radStatus<%=(rsTest.Fields.Item("RecID").Value)%>" value="Out">
</font></td>
</tr>
<
%If Request("Submit") <> "" AND (rsTest.Fields.Item("StatusField").Value) <> Request("radStatus") Then
varStatus = Request("radStatus"& (rsTest.Fields.Item("RecID").Value))
set Command1 = Server.CreateObject("ADODB.Command")
Command1.ActiveConnection = MM_TestTDSFdemoOLE2_STRING
Command1.CommandText = "UPDATE tblTest SET StatusField = '" & varStatus & "' WHERE RecID = " & (rsTest.Fields.Item("RecID").Value)
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsTest.MoveNext()
Wend
%>
</table>
<p>
<input type="submit" name="Submit" value="Submit">
</p>
</form>
</body>
</html>
<
%rsTest.Close()
%>
<
%If Request ("submit") <> "" Then
Response.Redirect("MultiUpdate.asp?Updated=1")
End If
%>
Basically what I've done is put the update command within the repeat region, if the status changes (In or Out) as determined by the radio button value the record is updated.
You could do something very similar by using Command(Stored Procedure) then select Insert from the Type list. Then place the Insert command within the repeat region in a similar fashion. Then place an if condition around it something like:
REPEAT
If Request("checkboxName") <> "" Then
COMMAND INSERT CODE HERE
End If
WEND
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Edited by - oeastwick on 17 Jan 2002 23:27:39
Replied 18 Jan 2002 02:10:48
18 Jan 2002 02:10:48 Andrew Ross replied:
<BLOCKQUOTE id=quote><font size=1 face="Verdana, Arial, Helvetica" id=quote>quote:<hr height=1 noshade id=quote>
anyone know how to do multiple inserts on the one page???? 1 insert for each line. Maybe tick box on left - if ticked it will insert record!!!
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I'm struggling with an insert page for my Contacts database with the same problem. I have a repeat region that is populated by a table called Categories. Each category has a check box beside it and I want to be able to insert the Categories that are checked into a table called ContactCategories. This will let me asign contacts to multiple categories. It works fine if I check one, but if I check multiples I get this error message:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
/canphotodirect/TMPkz02uq3zia.asp, line 90"
This is the code from line 90: MM_editCmd.Execute
I tried the Command(Stored Procedure) option that Owen suggested above and it didn't work at all.
This is one part of a puzzle that I just can't figure out. The next part is to retrieve the ContactID of the new record and insert that into the ContactCategories table as well.
Any suggestions will be greatly appreciated.
Andrew Ross
anyone know how to do multiple inserts on the one page???? 1 insert for each line. Maybe tick box on left - if ticked it will insert record!!!
<hr height=1 noshade id=quote></BLOCKQUOTE id=quote></font id=quote><font face="Verdana, Arial, Helvetica" size=2 id=quote>
I'm struggling with an insert page for my Contacts database with the same problem. I have a repeat region that is populated by a table called Categories. Each category has a check box beside it and I want to be able to insert the Categories that are checked into a table called ContactCategories. This will let me asign contacts to multiple categories. It works fine if I check one, but if I check multiples I get this error message:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Number of query values and destination fields are not the same.
/canphotodirect/TMPkz02uq3zia.asp, line 90"
This is the code from line 90: MM_editCmd.Execute
I tried the Command(Stored Procedure) option that Owen suggested above and it didn't work at all.
This is one part of a puzzle that I just can't figure out. The next part is to retrieve the ContactID of the new record and insert that into the ContactCategories table as well.
Any suggestions will be greatly appreciated.
Andrew Ross
Replied 18 Jan 2002 02:32:37
18 Jan 2002 02:32:37 Owen Eastwick replied:
Notice this bit of the code:
name="radStatus<%=(rsTest.Fields.Item("RecID"
.Value)%>"
Here I have dynamically named the radiobutton to be equivalent to the reocordID
So as it loops through the repeat region the radio buttons are named radStatus1, radStatus2, radStatus3 etc.
Each form element will need a unique name so set up something similar that will rename the element on each pass through the repeat region loop.
Notice that I have also refered to te radio buttons in a similar manner when collecting their values at the update command.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
name="radStatus<%=(rsTest.Fields.Item("RecID"
.Value)%>"Here I have dynamically named the radiobutton to be equivalent to the reocordID
So as it loops through the repeat region the radio buttons are named radStatus1, radStatus2, radStatus3 etc.
Each form element will need a unique name so set up something similar that will rename the element on each pass through the repeat region loop.
Notice that I have also refered to te radio buttons in a similar manner when collecting their values at the update command.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 18 Jan 2002 05:52:52
18 Jan 2002 05:52:52 Andrew Ross replied:
Thanks for the tip Owen. I have tried again, but still no luck. Now I get the error:
"Microsoft VBScript runtime error '800a01a8'
Object required: 'rsCategories'
/canphotodirect/TMP2r1fwq49wg.asp, line 5"
Any chance you could look at this code and make some suggestions?
%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/canphoto.asp" -->
<%
Line 5 if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))
%>
<%
set rsContactCategories = Server.CreateObject("ADODB.Recordset"
rsContactCategories.ActiveConnection = MM_canphoto_STRING
rsContactCategories.Source = "SELECT * FROM ContactCategories"
rsContactCategories.CursorType = 0
rsContactCategories.CursorLocation = 2
rsContactCategories.LockType = 3
rsContactCategories.Open()
rsContactCategories_numRows = 0
%>
<%
set rsCategories = Server.CreateObject("ADODB.Recordset"
rsCategories.ActiveConnection = MM_canphoto_STRING
rsCategories.Source = "SELECT * FROM Categories ORDER BY Category"
rsCategories.CursorType = 0
rsCategories.CursorLocation = 2
rsCategories.LockType = 3
rsCategories.Open()
rsCategories_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsCategories_numRows = rsCategories_numRows + Repeat1__numRows
%>
<html>
<head>
<title>insertdummy</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form2" method="post" action="insertdummy2.asp">
<table width="75%" border="0" align="center">
<tr>
<td width="10%">Categories</td>
<td width="90%">
<input type="submit" name="Submit2" value="Insert Data">
</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>">
</td>
<td width="90%"><%=(rsCategories.Fields.Item("Category".Value)%></td>
</tr>
<%
REPEAT
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES ( " + Replace(Command1__varCategories, "'", "''" + " "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
</table>
</form>
</body>
</html>
<%
rsContactCategories.Close()
%>
<%
rsCategories.Close()
%>
"Microsoft VBScript runtime error '800a01a8'
Object required: 'rsCategories'
/canphotodirect/TMP2r1fwq49wg.asp, line 5"
Any chance you could look at this code and make some suggestions?
%@LANGUAGE="VBSCRIPT"%>
<!--#include file="Connections/canphoto.asp" -->
<%
Line 5 if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))%>
<%
set rsContactCategories = Server.CreateObject("ADODB.Recordset"
rsContactCategories.ActiveConnection = MM_canphoto_STRING
rsContactCategories.Source = "SELECT * FROM ContactCategories"
rsContactCategories.CursorType = 0
rsContactCategories.CursorLocation = 2
rsContactCategories.LockType = 3
rsContactCategories.Open()
rsContactCategories_numRows = 0
%>
<%
set rsCategories = Server.CreateObject("ADODB.Recordset"
rsCategories.ActiveConnection = MM_canphoto_STRING
rsCategories.Source = "SELECT * FROM Categories ORDER BY Category"
rsCategories.CursorType = 0
rsCategories.CursorLocation = 2
rsCategories.LockType = 3
rsCategories.Open()
rsCategories_numRows = 0
%>
<%
Dim Repeat1__numRows
Repeat1__numRows = -1
Dim Repeat1__index
Repeat1__index = 0
rsCategories_numRows = rsCategories_numRows + Repeat1__numRows
%>
<html>
<head>
<title>insertdummy</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF" text="#000000">
<form name="form2" method="post" action="insertdummy2.asp">
<table width="75%" border="0" align="center">
<tr>
<td width="10%">Categories</td>
<td width="90%">
<input type="submit" name="Submit2" value="Insert Data">
</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>"></td>
<td width="90%"><%=(rsCategories.Fields.Item("Category"
.Value)%></td></tr>
<%
REPEAT
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES ( " + Replace(Command1__varCategories, "'", "''"
+ " "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
</table>
</form>
</body>
</html>
<%
rsContactCategories.Close()
%>
<%
rsCategories.Close()
%>
Replied 18 Jan 2002 14:52:11
18 Jan 2002 14:52:11 Owen Eastwick replied:
This bit of code:
<%
if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))
%>
Needs to be within the repeat region, like:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>">
</td>
<td width="90%"><%=(rsCategories.Fields.Item("Category".Value)%></td>
</tr>
<%
if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))
%>
<%
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES ( " + Replace(Command1__varCategories, "'", "''" + " "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
If you look at the code you posted, you will see that this piece of code was before the recordset code, so it's referring to a recordset that doesn't yet esxist. Remember that code is executed from the top of the page to the bottom, unless there is a repeat region or loop, in which case the repeat is executed from top to bottom the specified number of times (or until the condition is true) prior to any code that follows. e.g
<%
varSum = varOne + varTwo
varOne = 5
varTwo = 6
%> <--- This will fail because varOne & varTwo are unspecified when the sum is done.
<%
varOne = 5
varTwo = 6
varSum = varOne + varTwo
%> <--- varSum will contain 11.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
<%
if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))%>
Needs to be within the repeat region, like:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>"></td>
<td width="90%"><%=(rsCategories.Fields.Item("Category"
.Value)%></td></tr>
<%
if(Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value))%>
<%
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES ( " + Replace(Command1__varCategories, "'", "''"
+ " "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
If you look at the code you posted, you will see that this piece of code was before the recordset code, so it's referring to a recordset that doesn't yet esxist. Remember that code is executed from the top of the page to the bottom, unless there is a repeat region or loop, in which case the repeat is executed from top to bottom the specified number of times (or until the condition is true) prior to any code that follows. e.g
<%
varSum = varOne + varTwo
varOne = 5
varTwo = 6
%> <--- This will fail because varOne & varTwo are unspecified when the sum is done.
<%
varOne = 5
varTwo = 6
varSum = varOne + varTwo
%> <--- varSum will contain 11.
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 18 Jan 2002 16:35:05
18 Jan 2002 16:35:05 Andrew Ross replied:
Thanks again for your response (and your patience!). I thought that might be the problem, but moving the code into the repeat region now results in this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/canphotodirect/insertdummy2.asp, line 65 "
Line 65 is: Command1.Execute()
Here's the full repeat region of the revised code:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>">
</td>
<td width="90%"><%=(rsCategories.Fields.Item("Category".Value)%></td>
</tr>
<%
if(Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)" <> "" then Command1__varCategories = Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"
%>
<%
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''" + " "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
"Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in INSERT INTO statement.
/canphotodirect/insertdummy2.asp, line 65 "
Line 65 is: Command1.Execute()
Here's the full repeat region of the revised code:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>"></td>
<td width="90%"><%=(rsCategories.Fields.Item("Category"
.Value)%></td></tr>
<%
if(Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"
<> "" then Command1__varCategories = Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"%>
<%
If Request("chkCategories"& (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''"
+ " "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
Replied 18 Jan 2002 18:09:34
18 Jan 2002 18:09:34 Owen Eastwick replied:
Look at this bit of code:
<%
if(Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)" <> "" then Command1__varCategories = Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"
%>
Should be:
<%
If (Request("chkCategories" &(rsCategories.Fields.Item('Category_ID').Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item('Category_ID').Value))
%>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
<%
if(Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"
<> "" then Command1__varCategories = Request("chkCategories(rsCategories.Fields.Item('Category_ID').Value)"%>
Should be:
<%
If (Request("chkCategories" &(rsCategories.Fields.Item('Category_ID').Value)) <> ""
then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item('Category_ID').Value))%>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 18 Jan 2002 22:00:10
18 Jan 2002 22:00:10 Andrew Ross replied:
It didn't work with single quotes in ('Category_ID'), but with double quotes it did.
This is the code that worked...
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))
%>
But... it only worked once and then I got this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.
/canphotodirect/insertdummy2.asp, line 6"
Macromedia says this is either a Win2K issue (I'm using Win98SE) or Time-out value (It's set it 5000 already).
Maybe this is a coincidence, but this insert page is starting to get the best of me <img src=../images/dmxzone/forum/icon_smile_blackeye.gif border=0 align=middle>. What amazes me is that of all the tutorials I read on creating a database driven site, none of them mentioned how difficult it would be to create the insert, update and delete pages for a database with more than one table. So now I have a database with search and results pages (Please don't take offense Owen, but they are based on your tutorial) that work fine, but I have no way to maintain the records.
If you would be interested in creating the pages for me Owen, email me privately so we can discuss your fees. Because I think I've had it.<img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle>
Andrew
This is the code that worked...
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))%>
But... it only worked once and then I got this error:
"Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use.
/canphotodirect/insertdummy2.asp, line 6"
Macromedia says this is either a Win2K issue (I'm using Win98SE) or Time-out value (It's set it 5000 already).
Maybe this is a coincidence, but this insert page is starting to get the best of me <img src=../images/dmxzone/forum/icon_smile_blackeye.gif border=0 align=middle>. What amazes me is that of all the tutorials I read on creating a database driven site, none of them mentioned how difficult it would be to create the insert, update and delete pages for a database with more than one table. So now I have a database with search and results pages (Please don't take offense Owen, but they are based on your tutorial) that work fine, but I have no way to maintain the records.
If you would be interested in creating the pages for me Owen, email me privately so we can discuss your fees. Because I think I've had it.<img src=../images/dmxzone/forum/icon_smile_dissapprove.gif border=0 align=middle>
Andrew
Replied 18 Jan 2002 22:33:42
18 Jan 2002 22:33:42 Owen Eastwick replied:
"(Please don't take offense Owen, but they are based on your tutorial)" - why would I be, that's what it's there for. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use, yup seen this one before.
This isn't a problem with the page, it is a permission problem. It's on of those problems I always manage to sort, but can never remember exactly how next time it crops up.
Anyway check these out:
www.webwizguide.com/asp/faq/access_database_faq.asp
support.microsoft.com/default.aspx?scid=kb;en-us;Q166029
Also, try closing UD and Access and accessing the page directly through IE, try a restart. I'm afraid this is one of those problems where I just muck about with settings until it works - a true professional. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
[Microsoft][ODBC Microsoft Access Driver] Could not use '(unknown)'; file already in use, yup seen this one before.
This isn't a problem with the page, it is a permission problem. It's on of those problems I always manage to sort, but can never remember exactly how next time it crops up.
Anyway check these out:
www.webwizguide.com/asp/faq/access_database_faq.asp
support.microsoft.com/default.aspx?scid=kb;en-us;Q166029
Also, try closing UD and Access and accessing the page directly through IE, try a restart. I'm afraid this is one of those problems where I just muck about with settings until it works - a true professional. <img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>
Regards
Owen.
Multiple Parameter UD4 / Access 2000 Database Search Tutorial:
www.tdsf.co.uk/tdsfdemo
Replied 19 Jan 2002 15:53:25
19 Jan 2002 15:53:25 Andrew Ross replied:
It looks like my ODBC problems are fixed and I'm ready to try the next step. Now I have two insert pages, one that uses the standard "UD insert" to insert fields into my Contacts table, and the one above that uses a command to insert multiple Categories into my ContactCategories tables.
Now I need to merge these two pages into one or get them to work together by retrieving the newly created Contact_ID from tbl Contacts and adding it to the page we worked on above so it can also be inserted with the Category_ID into ContactCategories.
Any suggestions? I have read Rick Curtis' tut, but it is for SQL and uses stored procedures. I am using Access 2K and can't see how to make it work for me.
Cheers,
Andrew
Now I need to merge these two pages into one or get them to work together by retrieving the newly created Contact_ID from tbl Contacts and adding it to the page we worked on above so it can also be inserted with the Category_ID into ContactCategories.
Any suggestions? I have read Rick Curtis' tut, but it is for SQL and uses stored procedures. I am using Access 2K and can't see how to make it work for me.
Cheers,
Andrew
Replied 19 Jan 2002 19:05:03
19 Jan 2002 19:05:03 Andrew Ross replied:
Thanks to the Charon Demos (www.charon.co.uk) I have figured out how to pass a session variable with the last Contact_ID to my multiple insert page that we worked on above. Now how do I incorporate this variable <%=Session("Contact_ID"%> into the insert statement below?
<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID) VALUES (" + Replace(Command1__varCategories, "'", "''" + " "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
%> into the insert statement below? <%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID) VALUES (" + Replace(Command1__varCategories, "'", "''"
+ " "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
Replied 19 Jan 2002 21:36:13
19 Jan 2002 21:36:13 David Behan replied:
Thanks lads,
I let yous do all the tweaking. Now I am off to try all this code out. Will get back to you if it does not work for me.
Thanks again...
---------------------
David Behan - bmor.com
I let yous do all the tweaking. Now I am off to try all this code out. Will get back to you if it does not work for me.
Thanks again...
---------------------
David Behan - bmor.com
Replied 20 Jan 2002 02:23:28
20 Jan 2002 02:23:28 Andrew Ross replied:
Please if anyone can help, this will solve the last piece of the puzzle for me. I can retrieve the last Contact_ID either as a session variable or as a record set using "SELECT max(Contact_ID) FROM Contacts". But nothing I try will insert this value into the ContactCategories table at the same time as the Category_ID.
Here's the code that I have now:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>">
<input type="text" name="Contact_ID" value="<%=(rsMaxContact_ID.Fields.Item("Expr1000".Value)%>">
</td>
<td width="90%"><%=(rsCategories.Fields.Item("Category".Value)%></td>
</tr>
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))
if(Request("Contact_ID" <> "" then Command1__varContact_ID = Request("Contact_ID"
%>
<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES ('" + Replace(Command1__varCategories, "'", "''" + "', '" + Replace(Command1__varContact_ID, "'", "''" + "') "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
Here's the code that I have now:
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>"><input type="text" name="Contact_ID" value="<%=(rsMaxContact_ID.Fields.Item("Expr1000"
.Value)%>"></td>
<td width="90%"><%=(rsCategories.Fields.Item("Category"
.Value)%></td></tr>
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))if(Request("Contact_ID"
<> "" then Command1__varContact_ID = Request("Contact_ID"%>
<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES ('" + Replace(Command1__varCategories, "'", "''"
+ "', '" + Replace(Command1__varContact_ID, "'", "''" + "') "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
Replied 20 Jan 2002 22:06:00
20 Jan 2002 22:06:00 Andrew Ross replied:
Well, after much work I think my pages are finally working<img src=../images/dmxzone/forum/icon_smile_big.gif border=0 align=middle>, but I couldn't have done it without UD Zone. You guys rock!!!
I made a break through when I realized that I hadn't changed my form action when I re-saved the file with a new name. After I re-set the form action I got a data type error and a search on google groups revealed the problem. I had to remove the single qoutes from my variables in the sql section of the command, because my table field properties are set up as "number". It now looks like this:
INSERT INTO ContactCategories (Category_ID, Contact_ID)
VALUES (varCategories, varContact_ID)
Then I had to apply Owen's advice again and create a unique name for each field with the session variable contain the new Contact_ID. I used a text field so I could have a visual confirmation that the ID is being passed to the page okay, but it could be a hidden field. This is how the final code in the repeat region looks. Cheers!
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>">
<input type="text" name="Contact_ID<%=(rsCategories.Fields.Item("Category_ID".Value)%>" value="<%=Session("Contact_ID"%>">
</td>
<td width="90%"><%=(rsCategories.Fields.Item("Category".Value)%></td>
</tr>
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))
if(Request("Contact_ID" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" then Command1__varContact_ID = Request("Contact_ID" & (rsCategories.Fields.Item("Category_ID".Value))
%>
<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value)) <> "" Then
set Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''" + ", " + Replace(Command1__varContact_ID, "'", "''" + " "
Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>
I made a break through when I realized that I hadn't changed my form action when I re-saved the file with a new name. After I re-set the form action I got a data type error and a search on google groups revealed the problem. I had to remove the single qoutes from my variables in the sql section of the command, because my table field properties are set up as "number". It now looks like this:
INSERT INTO ContactCategories (Category_ID, Contact_ID)
VALUES (varCategories, varContact_ID)
Then I had to apply Owen's advice again and create a unique name for each field with the session variable contain the new Contact_ID. I used a text field so I could have a visual confirmation that the ID is being passed to the page okay, but it could be a hidden field. This is how the final code in the repeat region looks. Cheers!
<%
While ((Repeat1__numRows <> 0) AND (NOT rsCategories.EOF))
%>
<tr>
<td width="10%">
<input type="checkbox" name="chkCategories<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=(rsCategories.Fields.Item("Category_ID".Value)%>"><input type="text" name="Contact_ID<%=(rsCategories.Fields.Item("Category_ID"
.Value)%>" value="<%=Session("Contact_ID"%>"></td>
<td width="90%"><%=(rsCategories.Fields.Item("Category"
.Value)%></td></tr>
<%
if(Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varCategories = Request("chkCategories" & (rsCategories.Fields.Item("Category_ID".Value))if(Request("Contact_ID" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" then Command1__varContact_ID = Request("Contact_ID" & (rsCategories.Fields.Item("Category_ID".Value))%>
<%
If Request("chkCategories" & (rsCategories.Fields.Item("Category_ID"
.Value)) <> "" Thenset Command1 = Server.CreateObject("ADODB.Command"
Command1.ActiveConnection = MM_canphoto_STRING
Command1.CommandText = "INSERT INTO ContactCategories (Category_ID, Contact_ID) VALUES (" + Replace(Command1__varCategories, "'", "''"
+ ", " + Replace(Command1__varContact_ID, "'", "''" + " "Command1.CommandType = 1
Command1.CommandTimeout = 0
Command1.Prepared = true
Command1.Execute()
End If
%>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rsCategories.MoveNext()
Wend
%>