Can this be done? Arrays? (Full Version)

All Forums >> [Web Development] >> ASP and Database



Message

rikki -> Can this be done? Arrays? (9/23/2005 14:49:42)

Can you build in 2 arrays into the sql?
For example...
My code looks like this: 
<%If Instr(arrEquipment,"11") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Tattoos") 
Else 
Response.write("<input type='checkbox' name='11' size='3'> [color=#6666CC]<input type='text' name='Quantity' size='3'> [/color] Tattoos" ) 
End IF

The coloured part is what I want the 2nd array for..
Or maybe relooking at what I want to do change the response.write to a text.. but I still have to array the field "Quantity"
Make sense?

I changed my insert statement to look like this 
<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 


FOR i = 1 to 17
	IF Request("Equipment" & i) <> "" THEN 

FOR a = 1 to 7
IF Request("Quantity" & a) <> "" THEN  
	mySQL = "INSERT INTO tEvents"
	mySQL = mySQL & "(Date1, Date2 , Staff, Details, Equipment" 		
	mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
	mySQL = mySQL & "#" & Request.Form("Date2") & "#," 
	mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
	mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
	mySQL = mySQL & "" & Replace(Request.Form("Equipment" & i),"'","''") & ","
	mySQL = mySQL & "" & Replace(Request.Form("Quantity" & a),"'","''") & ")"


conntemp.execute (mySQL) 

end if
END IF 

NEXT


conntemp.close 
set conntemp=nothing 
Response.redirect ("sendemail.asp")
%><p> </p>

The bold part is what I added.. I referred to the new array as "a" instead of "i"

Thanks



rdouglass -> RE: Can this be done? Arrays? (9/23/2005 16:37:31)

I don't really see an array there but 2 nested loops. That's OK; we know what you mean. [;)]

It does look like 2 things are missing. I put them in bold:

FOR i = 1 to 17
IF Request("Equipment" & i) <> "" THEN

FOR a = 1 to 7
IF Request("Quantity" & a) <> "" THEN
mySQL = "INSERT INTO tEvents"
mySQL = mySQL & "(Date1, Date2 , Staff, Details, Equipment,Quantity)"
mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
mySQL = mySQL & "#" & Request.Form("Date2") & "#,"
mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
mySQL = mySQL & "" & Replace(Request.Form("Equipment" & i),"'","''") & ","
mySQL = mySQL & "" & Replace(Request.Form("Quantity" & a),"'","''") & ")"


conntemp.execute (mySQL)

end if

NEXT

END IF

NEXT


At least that's what I see first. That any help?



rikki -> RE: Can this be done? Arrays? (9/23/2005 21:40:36)

I think I'm on the right track here.. but it's still not inserting into the database...
I made 2 changes.. I had the wrong number for Equipment. Do you see anything else?
If Quantity is numeric field in the access database... I"ve got the field set for "text" in the input form so they can enter the number required. Is that correct? 


<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 


FOR i = 1 to 10
	IF Request("Equipment" & i) <> "" THEN 

FOR a = 1 to 7
IF Request("Quantity" & a) <> "" THEN 

	mySQL = "INSERT INTO tEvents"
	mySQL = mySQL & "(Date1, Date2 , Staff, Details, Equipment, Quantity)" 		
	mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
	mySQL = mySQL & "#" & Request.Form("Date2") & "#," 
	mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
	mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
	mySQL = mySQL & "" & Replace(Request.Form("Equipment" & i),"'","''") & ","
	mySQL = mySQL & "" & Replace(Request.Form("Quantity" & a),"'","''") & ")"

conntemp.execute (mySQL) 

END IF 

NEXT

END IF 
NEXT

conntemp.close 
set conntemp=nothing 
Response.redirect ("sendemail.asp")
%><p> </p>


This is at the top of my input form.. I was modifying code I had used before... Does this look right? 
<% 
Dim conntemp, myDSN, myRS, mySQL, arrEquipment, arrQuantity, varDate1, varDate2
varDate1 = Request.Form("Date1") 
varDate2 = Request.Form("Date2") 
Set conntemp=Server.CreateObject("ADODB.Connection") 
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb") 
conntemp.open myDSN 
mySQL = "SELECT Equipment FROM tEvents WHERE ((#"&varDate1&"# Between Date1 And Date2) OR (#"&varDate2&"# Between Date1 And Date2))"
Set myRS = Server.CreateObject("ADODB.Recordset") 
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then 
RecordFound=0 
Else 
arrEquipment = myRS.GetString(,,,"<br>") 
RecordFound=-1 
End if 
myRS.Close 
Set myRS = nothing 
conntemp.Close 
Set conntemp = nothing 
%> 
<html>

<head>



<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Staff </title>
<meta name="Microsoft Theme" content="ice 011">
</head>

<body> 
<% 
If RecordFound <> 0 then 
Response.Write ("") 
else 
Response.Write(arrEquipment) 
End if 
%>



rdouglass -> RE: Can this be done? Arrays? (9/23/2005 23:36:11)

quote:

conntemp.execute (mySQL)


I'd suggest commenting out that line for a moment and adding this line below it:

'conntemp.execute (mySQL)
Response.write(mySQL & "<br>")

I always want to look at the actual SQL we're sending to the db. What does that look like? Are we trying to pass Null or blank quantities to a number field or anything funny like that? Stuff like that you always have to consider when doing INSERT's and such in a loop. [;)]

So if we look at the SQL it should be a good place to start.



rikki -> RE: Can this be done? Arrays? (9/24/2005 12:01:36)


quote:


I'd suggest commenting out that line for a moment and adding this line below it:

'conntemp.execute (mySQL)
Response.write(mySQL & "<br>")

I did that but didn't work... my response.write at the end is working but not the my SQL ...
I started back at the beginning. Page 1 (the form) looks like this: 
<% 
Dim conntemp, myDSN, myRS, mySQL, arrEquipment, arrQuantity, varDate1, varDate2
varDate1 = Request.Form("Date1") 
varDate2 = Request.Form("Date2") 
Set conntemp=Server.CreateObject("ADODB.Connection") 
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb") 
conntemp.open myDSN 
mySQL = "SELECT Equipment FROM tEvents WHERE ((#"&varDate1&"# Between Date1 And Date2) OR (#"&varDate2&"# Between Date1 And Date2))"
Set myRS = Server.CreateObject("ADODB.Recordset") 
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then 
RecordFound=0 
Else 
arrEquipment = myRS.GetString(,,,"<br>") 
RecordFound=-1 
End if 
myRS.Close 
Set myRS = nothing 
conntemp.Close 
Set conntemp = nothing
%> 
<html>

<head>



<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Staff </title>
<meta name="Microsoft Theme" content="ice 011">
</head>

<body> 
<% 
If RecordFound <> 0 then 
Response.Write ("") 
else 
Response.Write(arrEquipment) 
End if 
%> 



<p>

</p>
 <form method="POST" action="InsertQuery.asp" onsubmit="return FrontPage_Form1_Validator(this)" name="FrontPage_Form1" language="JavaScript">
   <div align="left"> 
                  <b>Equipment Booking</b><br>
</div>
   <div align="left"> 
                  <font size="1">
                  <input type="text" name="Date11" size="12" value="<%=varDate1%>" disabled> 
                  (to change these dates click the back button on your browser)</div>
<input type="text" name="Date21" size="12" value="<%=varDate2%>" disabled> (mm/dd/year)<br>
   <br>

   Please select the equipment your are booking:<p>TS = at the SWO Admin Centre 
   - London</p>
   <p>BF = at the Hamilton Centre <br>
     </font><br>
<%If Instr(arrEquipment,"1") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Blue Display Unit") 
Else 
Response.write("<input type='checkbox' name='1' value='1'> Blue Display TS") 
End IF%> <br>
<br>
<%If Instr(arrEquipment,"2") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Popcorn Machine TS") 
Else 
Response.write("<input type='checkbox' name='2' value='1'> Popcorn Machine TS") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"3") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Popcorn Machine BF1") 
Else 
Response.write("<input type='checkbox' name='3' value='1'> Popcorn Machine BF1") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"4") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Popcorn Machine BF2") 
Else 
Response.write("<input type='checkbox' name='4' value='1'> Popcorn Machine BF2") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"5") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Laminator") 
Else 
Response.write("<input type='checkbox' name='5' value='1'> Laminator") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"6") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Shelter 1") 
Else 
Response.write("<input type='checkbox' name='6' value='1'> Shelter 1") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"7") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Shelter 2") 
Else 
Response.write("<input type='checkbox' name='7' value='1'> Shelter 2") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"8") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Printer") 
Else 
Response.write("<input type='checkbox' name='8' value='1'> Printer") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"9") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Binding Machine 1") 
Else 
Response.write("<input type='checkbox' name='9' value='1'> Binding Machine 1") 
End IF%><br>
<br>

<%If Instr(arrEquipment,"10") Then 
Response.Write ("<img src='button2.gif' width='70' height='27'> Binding Machine 2") 
Else 
Response.write("<input type='checkbox' name='10' value='1'> Binding Machine 2") 
End IF%><br>
<br>
<%
Response.write("<input type='text' name='Quantity1' size='4'> Tattoos") 
%><br>
<br>
<br>
<%
Response.write("<input type='text' name='Quantity2' size='4'> Pamphlets - Beavers/Cubs") 
%><br>

<%
Response.write("<input type='text' name='Quantity3' size='4'> Pamphlets - Scouts/Vent") 
%><br>
<br>
<br>
<br>
<br>



   <b>
   <span style="font-weight: 700; font-size: 8pt"><font size="1">
   <br>


 
</font></span><font size="1">
   <br>
   </font><span style="font-weight: 700; ">Contact information <br>
   </span><span style="font-weight: 700; font-size: 8pt"> <font size="1">
<br>
Name:  <!--webbot bot="Validation" s-display-name="Please select a name" b-value-required="TRUE" --><select size="1" name="Staff">
<option>Select Name</option>
<option>Debbie</option>
<option>Sarah</option>
<option>Lorne</option>
<option>John</option>
<option>Fran</option>
<option>Amy</option>
<option>Elizabeth</option>
<option>Danielle</option>
<option>Michelle</option>
<option>Grant</option>
<option>David</option>
<option>Cheryl</option>
<option>Marie-Claire</option>
</select>   <font color="#FF0000"><br>
<br>
   </font> <font color="#FF861F">Details (When will it be returned etc.)<br>
   </font> <textarea rows="2" name="Details" cols="48"></textarea></p>
<p><input type="submit" value="Submit" name="Submit"></p>
   <input type="hidden" name="Date1" value="<%=varDate1%>">
	<input type="hidden" name="Date2" value="<%=varDate2%>">
	<input type="hidden" name="Date_Added" size="20" value="<%=Now()%>">

</form>


 
</font></span></body>

</html>

The insert query looks like this now: 
<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 


FOR i = 1 to 10
	IF Request("Equipment" & i) <> "" THEN 
FOR a = 1 to 3
IF Request("Quantity" & a) <> "" THEN 

	mySQL = "INSERT INTO tEvents"
	mySQL = mySQL & "(Date1, Date2 , Staff, Details, Equipment, Quantity)" 		
	mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
	mySQL = mySQL & "#" & Request.Form("Date2") & "#," 
	mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
	mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
	mySQL = mySQL & "" & Replace(Request.Form("Equipment" & i),"'","''") & ","
	mySQL = mySQL & "" & Replace(Request.Form("Quantity" & a),"'","''") & ")"
'conntemp.execute (mySQL) 
Response.write(mySQL & "<br>") 

end if
next
END IF 
NEXT

conntemp.close 
set conntemp=nothing 
Response.write "Thanks"
%><p> </p>



BeTheBall -> RE: Can this be done? Arrays? (9/24/2005 12:47:46)

Here is part of your problem. Your SQL:

mySQL = "INSERT INTO tEvents"
mySQL = mySQL & "(Date1, Date2 , Staff, Details, Equipment, Quantity)"
mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
mySQL = mySQL & "#" & Request.Form("Date2") & "#,"
mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
mySQL = mySQL & "" & Replace(Request.Form("Equipment" & i),"'","''") & ","
mySQL = mySQL & "" & Replace(Request.Form("Quantity" & a),"'","''") & ")"

From your form:

<input type="text" name="Date11" size="12" value="<%=varDate1%>" disabled>
(to change these dates click the back button on your browser)</div>
<input type="text" name="Date21" size="12" value="<%=varDate2%>" disabled>

Do you see? Your text fields have different names than what you are calling in your SQL.

By the way, that's what Frontpage does when you copy a form field named Date1 and paste it. It appends a one at the end so 1 becomes 11 and 2 becomes 21.



rikki -> RE: Can this be done? Arrays? (9/24/2005 16:07:27)

I've really taken a step back and am trying to rework this.. to figure out a couple of things.

Form 1 passes 2 date values to form 2
Form 2 take those values.. tells me which ones are populated and then inserts to database via Insert2.asp

Because I have the value of 'Equipment' not getting passed I decided to start back at the beginning with just 3 values in this array... This is what I have...
Everything is getting inserted except the equipment value.
I have the insert page doing a response.write.
The Equipment value is blank... 



<% 
Dim conntemp, myDSN, myRS, mySQL, arrEquipment, varDate1, varDate2
varDate1 = Request.Form("Date1") 
varDate2 = Request.Form("Date2") 
Set conntemp=Server.CreateObject("ADODB.Connection") 
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb") 
conntemp.open myDSN 
mySQL = "SELECT Equipment FROM tEvents WHERE ((#"&varDate1&"# Between Date1 And Date2) OR (#"&varDate2&"# Between Date1 And Date2))"
Set myRS = Server.CreateObject("ADODB.Recordset") 
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then 
RecordFound=0 
Else 
arrEquipment = myRS.GetString(,,,"<br>") 
RecordFound=-1 
End if 
myRS.Close 
Set myRS = nothing 
conntemp.Close 
Set conntemp = nothing
%> 
<html>

<head>



<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Staff </title>
<meta name="Microsoft Theme" content="ice 011">
</head>

<body> 
<% 
If RecordFound <> 0 then 
Response.Write ("") 
else 
Response.Write(arrEquipment) 
End if 
%> 


<form method="POST" action="Insert2.asp">
<font color="#FF0000">Date Start: [<%=varDate1%>]
                  (to change these dates click the back button on your browser)</div>
Date End: [<%=varDate2%>] </font> 
<p>Details<br>
  <textarea rows="3" name="Details" cols="27"></textarea></p>
  <p>Staff: <input type="text" name="Staff" size="16"></p>

  <p>Equipment
<% If Instr(arrEquipment, 1) Then %> <br>
  Blue Display: 
<img src="button2.gif" width="75" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment1" value="1"> 
<%End If%> 
 <br>
<br>
  Popcorn Machine TS<% If Instr(arrEquipment, 2) Then %> 
<img src="button2.gif" width="80" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment2" value="2"> 
<%End If%> 
 <br>
Popcorn Machine BF1:  <% If Instr(arrEquipment, 3) Then %> 
<img src="button2.gif" width="75" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment3" value="3"> 
<%End If%> 

 <br><br>
Tattoos: How Many?  <input type="text" name="Tattoos" size="4"></p>
  <p>Pamphlets: Enter how many<br>
  BC <input type="text" name="PamphletsBC" size="4"><br>
  SV <input type="text" name="PamphletsSV" size="4"></p>
  <p><input type="submit" value="Submit" name="B1" size="20"></p>
   <input type="hidden" name="Date1" value="<%=varDate1%>">
	<input type="hidden" name="Date2" value="<%=varDate2%>">

</form>

</body>

</html>



BeTheBall -> RE: Can this be done? Arrays? (9/24/2005 23:43:19)

What is supposed to happen on the above page? There is no code that would pass the value of Equipment as there is no form field hidden or otherwise named Equipment.



rikki -> RE: Can this be done? Arrays? (9/25/2005 9:41:46)


quote:

There is no code that would pass the value of Equipment as there is no form field hidden or otherwise named Equipment.

That's I guess why it doesn't work..

quote:

<% If Instr(arrEquipment, 1) Then %> <br>
Blue Display:
<img src="button2.gif" width="75" height="30">
<%Else%>
<input type="checkbox" name="Equipment1" value="1">
<%End If%>

This code I thought would pass the value... and it's not working..
I want the image to show if it's already booked, and the checkbox to show otherwise.

This code isn't working... any ideas.




BeTheBall -> RE: Can this be done? Arrays? (9/25/2005 10:51:41)

I kind of thought that was where you were going. So, if some are booked and others are not and then a user checks a box or two, what should happen? Is the image showing when it should, or is that part broken as well?



rikki -> RE: Can this be done? Arrays? (9/25/2005 16:54:04)

The image is showing but the Equipment field isn't getting inserted.



BeTheBall -> RE: Can this be done? Arrays? (9/25/2005 18:57:00)

I am still confused as to what is supposed to be inserted into the Equipment field. Can you elaborate a bit on what should happen?



rikki -> RE: Can this be done? Arrays? (9/25/2005 19:24:16)

The Equipment field has a numeric value assigned.
If it's not already "booked" the checkbox then shows and the person can "book" it.
The image shows if it's already booked.

All it is, is a numeric field. 
<input type="checkbox" name="Equipment1" value="1" <%If Instr(arrEquipment,"1") Then Response.Write("disabled")End IF%>> 
Blue Display<br>
<br>
  <input type="checkbox" name="Equipment2" value="2" <%If Instr(arrEquipment,"2") Then Response.Write("disabled")End IF%>> 
Popcorn Machine TS<br>
<br>
  <input type="checkbox" name="Equipment3" value="3" <%If Instr(arrEquipment,"3") Then Response.Write("disabled")End IF%>> 
Popcorn Machine BF1<br>

 <br>
  <input type="checkbox" name="Equipment4" value="4" <%If Instr(arrEquipment,"4") Then Response.Write("disabled")End IF%>> 
Popcorn Machine BF2<br>

But that doesn't allow me to use the image...



Spooky -> RE: Can this be done? Arrays? (9/25/2005 20:38:02)

Does this work?
From memory, it was posted on similar lines before? 

<%If Instr(arrEquipment,"1") Then%>
<img src=""......>
<%Else%> 
<input type="checkbox" name="Equipment1" value="1" >
<%End IF%>




BeTheBall -> RE: Can this be done? Arrays? (9/25/2005 21:56:33)

Just to make sure I follow what's going on, let me explain what appears to be happening. It appears you want to insert a new db record for each checkbox a user marks. Is that right? However, it appears to me that the quantity field is not always pertinent, however, this line:

FOR a = 1 to 3
IF Request("Quantity" & a) <> "" THEN

would make it so if the quantity is blank, no records are inserted. I think it may be helpful if you explained in detail from the user's perspective what is supposed to happen. I understand that the user will access the page and that some of the items will not be available and thus shown with an image, not a checkbox. Available items will have a checkbox. I am not clear where quantity fits in or on exactly what you want to have happen on the database side once a user submits the form.



rikki -> RE: Can this be done? Arrays? (9/25/2005 22:22:46)


quote:

However, it appears to me that the quantity field is not always pertinent, however, this line:

FOR a = 1 to 3
IF Request("Quantity" & a) <> "" THEN

would make it so if the quantity is blank, no records are inserted.


Well, having the additional code above was driving me crazy so I took the field I was using for Quantity and actually created 3 fields so they each inserted separately.

Changed this code back to what's below and the insert works. 

<% If Instr(arrEquipment, "1") Then %> 
<img src="button2.gif" width="70" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment1" value="1"> 
<%End If%>

I'm getting an insert...

Just to try and explain what's needed: I was asked by my boss to create a booking system for our office equipment that can be taken out.
When the user enters the page, they enter a date, the database is queried and the results is a list of those things that still can be booked.
I don't want to end up with duplicate bookings...
I think it's working.. I'll keep trying to add the rest of the values and see what I get..





Spooky -> RE: Can this be done? Arrays? (9/25/2005 23:06:04)

Will the array only go as high as "9"?
If not - "1" will find a match in "10"



rikki -> RE: Can this be done? Arrays? (9/26/2005 11:53:08)

The array should go to 10 once I put them all in...
I nver thought of this
quote:

Will the array only go as high as "9"?
If not - "1" will find a match in "10"

How do I avoid that?

I've reworked somethings - The array is currently only going to "2" This is the code for the insert statement which gives me this error. All the values are being passed...Line 26 is the conntemp.execute statement 

<!--#include File='Login_Check.asp'-->
<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 

FOR i = 1 to 2
	IF Request("Equipment" & i) <> "" THEN 


mySQL = "INSERT INTO tEvents"
mySQL = mySQL & "(Date1, Date2, Details, Equipment, Staff, Tattoos, PamphletsBC, PamphletsSV)" 
mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
mySQL = mySQL & "#" & Request.Form("Date2") & "#,"
mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
mySQL = mySQL & "" & Request.Form("Equipment" & i) & ","
mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
mySQL = mySQL & "" & Request.Form("Tattoos") & ","
mySQL = mySQL & "" & Request.Form("PamphletsBC") & ","
mySQL = mySQL & "" & Request.Form("PamphletsSV") & ")"

conntemp.execute (mySQL) 


end if 
next

conntemp.close 
set conntemp=nothing 
Response.redirect "BookList.asp"
%>


Microsoft JET Database Engine error '80040e14' 

Syntax error in INSERT INTO statement. 

/campbookings1/StaffCal/Insert2.asp, line 26





Spooky -> RE: Can this be done? Arrays? (9/26/2005 14:22:50)

You would need to do this and check the SQL : 

response.write mySQL
response.end
conntemp.execute (mySQL)



rikki -> RE: Can this be done? Arrays? (9/26/2005 14:59:34)

Spooky
This is the code that's being passed..
INSERT INTO tEvents(Equipment, Tattoos, PamphletsBC, PamphletsSV, Staff, Details, Date1, Date2, )VALUES(1,200,200,200)'Sarah','no details',#12/12/2006#,#12/25/2006#)

It seems to be all there... but is this right?
(1,200,200,200) - is this an extra bracket?

I did find one mistake... Date2, ) - extra comma...
Took that out... and now I get this on insert.
Microsoft JET Database Engine error '80040e14'

Number of query values and destination fields are not the same.

/campbookings1/StaffCal/Insert2.asp, line 27

There are 10 fields in the table...
first 2 are Event_ID (autonumber) ; then Date_Added (defaults to today's date)







BeTheBall -> RE: Can this be done? Arrays? (9/26/2005 15:13:22)

If that is the SQL being written, then I am assuming you have changed this: 

<!--#include File='Login_Check.asp'-->
<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 

FOR i = 1 to 2
	IF Request("Equipment" & i) <> "" THEN 


mySQL = "INSERT INTO tEvents"
mySQL = mySQL & "(Date1, Date2, Details, Equipment, Staff, Tattoos, PamphletsBC, PamphletsSV)" 
mySQL = mySQL & "VALUES(#" & Request.Form("Date1") & "#,"
mySQL = mySQL & "#" & Request.Form("Date2") & "#,"
mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
mySQL = mySQL & "" & Request.Form("Equipment" & i) & ","
mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
mySQL = mySQL & "" & Request.Form("Tattoos") & ","
mySQL = mySQL & "" & Request.Form("PamphletsBC") & ","
mySQL = mySQL & "" & Request.Form("PamphletsSV") & ")"

conntemp.execute (mySQL) 


end if 
next

conntemp.close 
set conntemp=nothing 
Response.redirect "BookList.asp"
%>


Because the order of the fields is completely different. What does the INSERT query look like now?



rikki -> RE: Can this be done? Arrays? (9/26/2005 15:17:14)


quote:

ORIGINAL: BeTheBall

What does the INSERT query look like now? 


<!--#include File='Login_Check.asp'-->
<% 
DIM conntemp, mySQL, myDSN 

myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb")
'myDSN would be specific to your environment

set conntemp=server.createobject("adodb.connection") 
conntemp.open myDSN 

FOR i = 1 to 2
	IF Request("Equipment" & i) <> "" THEN 


mySQL = "INSERT INTO tEvents"
mySQL = mySQL & "(Equipment, Tattoos, PamphletsBC, PamphletsSV, Staff, Details, Date1, Date2)" 
mySQL = mySQL & "VALUES(" & Request.Form("Equipment" & i) & ","
mySQL = mySQL & "" & Request.Form("Tattoos") & ","
mySQL = mySQL & "" & Request.Form("PamphletsBC") & ","
mySQL = mySQL & "" & Request.Form("PamphletsSV") & ","
mySQL = mySQL & "'" & Replace(Request.Form("Staff"),"'","''") & "',"
mySQL = mySQL & "'" & Replace(Request.Form("Details"),"'","''") & "',"
mySQL = mySQL & "#" & Request.Form("Date1") & "#,"
mySQL = mySQL & "#" & Request.Form("Date2") & "#)"


conntemp.execute (mySQL) 


end if 
next

conntemp.close 
set conntemp=nothing 
Response.redirect "BookList.asp"
%>


I've changed it to match the order of the fields being passed from the form.




BeTheBall -> RE: Can this be done? Arrays? (9/26/2005 15:45:09)

So if you response.write the SQL, do you still get this:

INSERT INTO tEvents(Equipment, Tattoos, PamphletsBC, PamphletsSV, Staff, Details, Date1, Date2, )VALUES(1,200,200,200)'Sarah','no details',#12/12/2006#,#12/25/2006#)

Also, have you redone the form since your original post? If so, you may be wise to post the new code for it as well.



rikki -> RE: Can this be done? Arrays? (9/26/2005 18:50:16)

That's the response.write I get..

quote:


Also, have you redone the form since your original post?

Yes,,,..
Here's the form 
<!--#include File='Login_Check.asp'-->

<% 
Dim conntemp, myDSN, myRS, mySQL, arrEquipment, varDate1, varDate2
varDate1 = Request.Form("Date1") 
varDate2 = Request.Form("Date2") 
Set conntemp=Server.CreateObject("ADODB.Connection") 
myDSN ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.Mappath("../fpdb/index.mdb") 
conntemp.open myDSN 
mySQL = "SELECT Equipment FROM tEvents WHERE ((#"&varDate1&"# Between Date1 And Date2) OR (#"&varDate2&"# Between Date1 And Date2))"
Set myRS = Server.CreateObject("ADODB.Recordset") 
myRS.Open mySQL, conntemp, 0, 1
If myRS.eof OR myRS.bof then 
RecordFound=0 
Else 
arrEquipment = myRS.GetString(,,,"<br>") 
RecordFound=-1 
End if 
myRS.Close 
Set myRS = nothing 
conntemp.Close 
Set conntemp = nothing
%> 
<html>

<head>



<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>Staff </title>
<meta name="Microsoft Theme" content="ice 011">
</head>

<body> 
<% 
If RecordFound <> 0 then 
Response.Write ("") 
else 
Response.Write(arrEquipment) 
End if 
%> 



<p>

</p>
 <form method="POST" action="Insert2.asp" onsubmit="return FrontPage_Form1_Validator(this)" name="FrontPage_Form1">
   <div align="left"> 
                  <b>Equipment Booking</b><br></div>
   <div align="left"> 
                  <font size="1">
<b></b>(to change these dates click the back button on your browser)</div>
<b></b> (mm/dd/year)<br>
   <br>

    <p>TS = at the SWO Admin Centre 
   - London</p>
   <p>BF = at the Hamilton Centre<br>
</p>
   <hr>
   <p>Please select the equipment your are booking: <br>
     </font><br>
Blue Display: <% If Instr(arrEquipment, 1) Then %> 
<img src="button2.gif" width="70" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment1" value="1"> 
<%End If%> 
 <br>
<br>
Popcorn Machine TS:<% If Instr(arrEquipment, 2) Then %> 
<img src="button2.gif" width="70" height="30"> 
<%Else%> 
<input type="checkbox" name="Equipment2" value="2"> 
<%End If%> 
<br>
<br>
   <p>Tattoos: <input type="text" name="Tattoos" size="4">
<br>
<br>
Pamphlets: Beavers/Cubs <input type="text" name="PamphletsBC" size="4">
Scouts/Venturer: <input type="text" name="PamphletsSV" size="4">
<br>
   <p>



   <b>
   <span style="font-weight: 700; ">Contact information <br>
   </span><span style="font-weight: 700; font-size: 8pt"> <font size="1">
<br>
Name:  <!--webbot bot="Validation" s-display-name="Please select a name" b-value-required="TRUE" --><select size="1" name="Staff">
<option>Select Name</option>
<option>Debbie</option>
<option>Sarah</option>
<option>Lorne</option>
<option>John</option>
<option>Fran</option>
<option>Amy</option>
<option>Elizabeth</option>
<option>Danielle</option>
<option>Michelle</option>
<option>Grant</option>
<option>David</option>
<option>Cheryl</option>
<option>Marie-Claire</option>
</select>   <font color="#FF0000"><br>
<br>
   </font> <font color="#FF861F">Details (When will it be returned etc.)<br>
   </font> <textarea rows="2" name="Details" cols="48"></textarea></p>
<p><input type="submit" value="Submit" name="Submit"></p>
      <input type="hidden" name="Date1" value="<%=varDate1%>">
      <input type="hidden" name="Date2" value="<%=varDate2%>">


</form>


 
</font></span></body>

</html>



Spooky -> RE: Can this be done? Arrays? (9/26/2005 19:00:13)

Are you actually entering 1,200,200,200 in the respective fields?
I cant see where the ")" is coming from?



BeTheBall -> RE: Can this be done? Arrays? (9/26/2005 19:37:56)


quote:

ORIGINAL: Spooky

Are you actually entering 1,200,200,200 in the respective fields?
I cant see where the ")" is coming from?


Ditto. I am stumped. Looking at your form and your insert statement, I see no way the ")" would get in there unless it was entered in the form field itself. Not only that, according to your SQL there should be a comma where the mysterious ")" is appearing.



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.171875