Ubound error (Full Version)

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



Message

sgreen0 -> Ubound error (5/27/2005 20:31:29)

In another thread ("update records loop") Duane, and others, helped me with code to update several records in a table with similar info.

Here is the code I got: 

for i = 0 to Ubound(arrIDs,2) 
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (arrIDs(i), '"&Request.Form("ContactDate")&"', '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')" 
myConnection.Execute sSQL,,129


I'm getting an error, though.

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'Ubound'
/process/batch_contact_updater.asp, line 19

Line 19 is the very first line of code above.  I've tried fiddling with the ContactDate code (for instance, #"&Request.Form("ContactDate")&"#), but I still get the error.  I also tried removing the fixapost function...

I should point out that arrIDs comes from this code on the previous page:

strIDs = strIDs & FP_Field(fp_rs,"ID") &";"
arrIDs = Split(strIDs,";")

I can post more code if it will help.

Thanks.

Stephen



ou812 -> RE: Ubound error (5/28/2005 0:33:18)

I believe the error you are getting is telling you that array "arrIDs" is empty, or not an array. Meaning you probably do not have any records in it.

You could check to see if it is an array, then if so do the code, otherwise display no records found, or something. 
If isArray(arrIDs) then
for i = 0 to Ubound(arrIDs,2) 
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (arrIDs(i), '"&Request.Form("ContactDate")&"', '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')" 
myConnection.Execute sSQL,,129 
.
.
.
else
response.write "No records found"
end if



sgreen0 -> RE: Ubound error (5/28/2005 2:20:30)

Brian!

You were right. 

The next question is "Why is the Array empty?"

I collect the IDs directly after collecting the e-mail addresses into a variable.  This is the code: 

<!--#include file="../_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database Results component on this page is unable to display database content. The page must have a filename ending in '.asp', and the web must be hosted on a server that supports Active Server Pages.</div>");
   </SCRIPT>
<% end if %>
<%
fp_sQry="SELECT DISTINCT Prospects.ID, Prospects.Emailaddress FROM (SELECT Prospects.ID, Prospects.LastName, Prospects.FirstName, Prospects.EmailAddress, Prospects.HomePhone, Prospects.CellPhone, Prospects.Address, Prospects.Address2, Prospects.City, Prospects.State, Prospects.Code, Recruitment_Contacts.ContactDate, Recruitment_Contacts.ContactForm, Recruitment_Contacts.Notes FROM Prospects INNER JOIN Recruitment_Contacts ON Prospects.ID=Recruitment_Contacts.ID WHERE (Prospects.EmailAddress <> '') AND (Recruitment_Contacts.Notes = '"& session("uAskNotes") &"') ORDER BY LastName ASC,FirstName ASC)"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="academy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&CurrentYear=202&ANMTLevel=202&ARCLevel=202&Special Code=202&Sex=202&CraftOne=202&CraftTwo=202&CraftThree=202&Staff=202&Board=202&Committee=202&LastName=202&FirstName=202&Dear=202&Address=202&City=202&State=202&Code=202&Country=202&HomePhone=202&WorkPhone=202&CellPhone=202&FaxNumber=202&EmailAddress=202&AlternateEmailAddress=202&JoinDate=202&HomeTown=202&Notes=202&Picture=205&ProspectStatus=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<% 
sEmail = sEmail & FP_Field(fp_rs,"EmailAddress") &";" 
strIDs = strIDs & FP_Field(fp_rs,"ID") &";" 
arrIDs = Split(strIDs,";") 
%>

<!--#include file="../_fpclass/fpdbrgn2.inc"-->
<% response.write "Asknotes = " & session("uAskNotes") %><br>
<% response.write "sEmail = " & sEmail %> 


You can see that I even display the results of the e-mail variable immediately after.  It shows up fine and is correct.

Can you suggest the problem?

Thanks.

Stephen



BeTheBall -> RE: Ubound error (5/28/2005 10:21:56)

quote:

I should point out that arrIDs comes from this code on the previous page:


What are you doing to get the data from the previous page?

What action gets you from page 1 to page 2?



sgreen0 -> RE: Ubound error (5/28/2005 11:17:13)

Duane!

As usual, you asked ust the right question.

I am passing all the data from Page 1 to Page 2 via a form (inlcuding some hidden fields).  I tried this for the array:

<input type="hidden" name="ID_Array" value=<%=arrIDs%>>

And on Page 2:

<% arrIDs=request.form("ID_Array") %>

But this cause an error:

Response object error 'ASP 0106 : 80020005'
Type Mismatch
/admin/ANMT_batch_contact_database_update.asp, line 0
An unhandled data type was encountered.

Is there a better (correct) way to pass an array from on page to another??

Thanks.

Stephen



ou812 -> RE: Ubound error (5/28/2005 12:08:09)

Your error usually means you are mixing string/numric variables with array variables. I believe you are passing the array incorrectly.
Here's a nice page on passing arrays using Post/Hidden: http://www.4guysfromrolla.com/webtech/101999-1.shtml

So, to build your variable to pass you might do something like:
<% For i = LBound(arrIDs) to UBound(arrIDs) %>
<INPUT TYPE=HIDDEN NAME=ID_Array VALUE="<%=arrIDs(i)%>">
<% Next %>

Then recieve with:
temp_arrIDs = Request("ID_Array")
arrIDs = Split(temp_arrIDs,";")

Hopefully this helps more than not!



sgreen0 -> RE: Ubound error (5/28/2005 13:32:04)

Thanks, Brian!

I included the code you recommended and got a new error:

 Microsoft VBScript runtime error '800a0009'
Subscript out of range: 'Ubound'
/process/batch_contact_updater.asp, line 23

That line is the second line of:

If isArray(arrIDs) then
for i = 0 to Ubound(arrIDs,2)
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (arrIDs(i), #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"
myConnection.Execute sSQL,,129
Next
else
response.write "No records found"
end if

Does that mean that we're still not getting the array through?

Stephen



BeTheBall -> RE: Ubound error (5/28/2005 13:54:50)

quote:

Then recieve with:
temp_arrIDs = Request("ID_Array")
arrIDs = Split(temp_arrIDs,";")


That isn't working because you already got rid of the ";" when you created the array on page 1.

Pass the string, not the array.

<input type="hidden" name="ID_Array" value="<%= strIDs %>">

Then use the split function on page 2 to create the array:

arrIDs = Split(Request.Form("ID_Array"),";")



sgreen0 -> RE: Ubound error (5/28/2005 14:08:12)

I'm so close, I can feel it...

Here is the code:

Page 1:

strIDs = strIDs & FP_Field(fp_rs,"ID") &";"
arrIDs = Split(strIDs,";")

then

<input type="hidden" name="ID_Array" value="<%=strIDs%>">

Page 2:

arrIDs = Split(Request.Form("ID_Array"),";")

then

If isArray(arrIDs) then
for i = 0 to Ubound(arrIDs,2)
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (arrIDs(i), #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"
myConnection.Execute sSQL,,129
Next
else
response.write "No records found"
end if

I'm getting this error:

Microsoft VBScript runtime error '800a0009'
Subscript out of range: 'Ubound'
/process/batch_contact_updater.asp, line 21

Line 21 is:

for i = 0 to Ubound(arrIDs,2)

Everything LOOKS right, but...

Thanks for all your help.

Stephen




BeTheBall -> RE: Ubound error (5/28/2005 14:40:00)

Duh! on my part.  It is a one-dimensional array.  Use:


for i = 0 to Ubound(arrIDs)



sgreen0 -> RE: Ubound error (5/28/2005 15:33:03)

Now I have another error in the SQL statement.

sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (arrIDs(i), #"&Request.Form("ContactDate")&"#, '"&Request.Form("ContactForm")&"', '"&Request.Form("Notes")&"')"

The error:

Microsoft JET Database Engine error '80040e14'
Undefined function 'arrIDs' in expression.
/process/batch_contact_updater.asp, line 24
 
Can you spot the problem?
 
Thanks for all your help.
 
Stephen



BeTheBall -> RE: Ubound error (5/28/2005 15:37:38)

If isArray(arrIDs) then
for i = 0 to Ubound(arrIDs)
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"
myConnection.Execute sSQL,,129
Next
else
response.write "No records found"
end if



sgreen0 -> RE: Ubound error (5/28/2005 16:36:16)

Thanks, Duane.

Still getting this error:

Request object error 'ASP 0102 : 80004005'
Expecting string input
/process/batch_contact_updater.asp, line 23
The function expects a string as input.

The line now looks like this:

sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"

Thanks again.

Stephen

PS  I'll be offline till this evening (PST)...



BeTheBall -> RE: Ubound error (5/28/2005 17:27:39)

Wonder if that is your Notes field causing that.  To test, try:

sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm) VALUES ("&arrIDs(i)&", #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"')"

Also, please post the full code for the page that performs the db insert.



sgreen0 -> RE: Ubound error (5/28/2005 23:15:08)

It wasn't the Notes field.  Here is the full page: 

<% 
function fixapost(mytext) 
fixapost=replace(mytext,"'","''") 
end function 
%> 
<% 
Dim myConnString 
Dim myConnection 
Dim sSQL 
Dim arrIDs
arrIDs = Split(Request.Form("ID_Array"),";") 
MyConnString = Application("academy_ConnectionString") 
Set myConnection = Server.CreateObject("ADODB.Connection") 
myConnection.Open myConnString 
If isArray(arrIDs) then 
for i = 0 to Ubound(arrIDs) 
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form(ContactDate)&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')" myConnection.Execute sSQL,,129 
Next 
else 
response.write "No records found" 
end if 
myConnection.Close 
Set myConnection = Nothing 

If Err <> 0 Then ' error occurred
 strErr   = Err.Description
 Response.Write "halted : " & strErr
else
 bSuccess   = True
response.redirect("[link=http://www.anmt.org/admin/admin8.asp]http://www.anmt.org/admin/admin8.asp[/link]")
End If
response.redirect("[link=http://www.anmt.org/admin/admin8.asp]http://www.anmt.org/admin/admin8.asp[/link]")
%>


What do you think?

Stephen



BeTheBall -> RE: Ubound error (5/29/2005 0:29:19)

Missing double quotes here:

#"&Request.Form(ContactDate)&"#

Should be:

#"&Request.Form("ContactDate")&"#



sgreen0 -> RE: Ubound error (5/29/2005 0:41:09)

sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form("ContactDate")&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"

Now a different error:

Microsoft JET Database Engine error '80040e14'
Syntax error in INSERT INTO statement.
/process/batch_contact_updater.asp, line 24

Stephen



Spooky -> RE: Ubound error (5/29/2005 1:20:14)

I dont see where you execute it above, so it must be a different script you are running?
What you need to do is response.write the SQL string and then response.end before the error occurs



sgreen0 -> RE: Ubound error (5/29/2005 1:26:40)

Spooky!

Somehow the execute statement got tacked on to the end of the SQL statement.  It doesn't look that way in my file. 

If isArray(arrIDs) then 
for i = 0 to Ubound(arrIDs) 
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form("ContactDate")&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')" 
myConnection.Execute sSQL,,129 
Next 
else 
response.write "No records found" 
end if


I'll try what you suggest and report back...

Stephen



Spooky -> RE: Ubound error (5/29/2005 1:29:51) 

response.write sSQL &"<br>"
'myConnection.Execute sSQL,,129 
Next
response.end



sgreen0 -> RE: Ubound error (5/29/2005 1:32:41)

I got the SQL to print on the screen:

INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES (, #5/28/2005#, 'Email sent', 'Testing 1,2,3...')

ID is empty!

I placed the response.write after the loop:

If isArray(arrIDs) then
for i = 0 to Ubound(arrIDs)
sSQL = "INSERT INTO Recruitment_Contacts (ID, ContactDate, ContactForm, Notes) VALUES ("&arrIDs(i)&", #"&Request.Form("ContactDate")&"#, '"&Request.Form("ContactForm")&"', '"&fixapost(Request.Form("Notes"))&"')"
'myConnection.Execute sSQL,,129
Next
else
response.write "No records found"
end if
response.write sSQL
response.end

Stephen



sgreen0 -> RE: Ubound error (5/29/2005 1:40:18)

I'm wondering if the ID's are being collected in the previous page.  Here is the code: 

<%
fp_sQry="SELECT DISTINCT Prospects.ID, Prospects.Emailaddress FROM (SELECT Prospects.ID, Prospects.LastName, Prospects.FirstName, Prospects.EmailAddress, Prospects.HomePhone, Prospects.CellPhone, Prospects.Address, Prospects.Address2, Prospects.City, Prospects.State, Prospects.Code, Recruitment_Contacts.ContactDate, Recruitment_Contacts.ContactForm, Recruitment_Contacts.Notes FROM Prospects INNER JOIN Recruitment_Contacts ON Prospects.ID=Recruitment_Contacts.ID WHERE (Prospects.EmailAddress <> '') AND (Recruitment_Contacts.Notes = '"& session("uAskNotes") &"') ORDER BY LastName ASC,FirstName ASC)"
fp_sDefault=""
fp_sNoRecords="No records returned."
fp_sDataConn="academy"
fp_iMaxRecords=0
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=False
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&ID=3&CurrentYear=202&ANMTLevel=202&ARCLevel=202&Special Code=202&Sex=202&CraftOne=202&CraftTwo=202&CraftThree=202&Staff=202&Board=202&Committee=202&LastName=202&FirstName=202&Dear=202&Address=202&City=202&State=202&Code=202&Country=202&HomePhone=202&WorkPhone=202&CellPhone=202&FaxNumber=202&EmailAddress=202&AlternateEmailAddress=202&JoinDate=202&HomeTown=202&Notes=202&Picture=205&ProspectStatus=202&"
fp_iDisplayCols=1
fp_fCustomQuery=False
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="../_fpclass/fpdbrgn1.inc"-->
<% 
sEmail = sEmail & FP_Field(fp_rs,"EmailAddress") &";" 
strIDs = strIDs & FP_Field(fp_rs,"ID") &";" 
arrIDs = Split(strIDs,";") 
%>


The e-mails are being collected properly, so I assumed the ID's would, too.

Stephen



sgreen0 -> RE: Ubound error (5/29/2005 2:16:47)

Here's something.  After collecting the ID's I inserted a response.write for strIDs and then did a loop for arrIDs. 

<% Dim iLoop
  For iLoop = LBound(arrIDs) to UBound(arrIDs)
  Response.Write arrIDs(iLoop) & ","
  Next
%>

Here are the results:

strIDs = 921;922;923;924
arrIDs = 921,922,923,924,,

There seems to be an empty space in the array!  Perhaps that is causing the problem.  How is that getting there?

Stephen



sgreen0 -> RE: Ubound error (5/29/2005 2:51:42)

Try this one on for size.  When collecting e-mail addresses, I separated them with semi-colons.  An extra ; at the end shouldn't cause a problem.

But an extra semi-colon in strIDs causes the Split function to see one more (empty) variable to include in the array.

Does that make sense?  I don't really understand arrays that well, yet.  So I'm guessing here.

I tried figuring out how to remove the trailing semi-colon from strIDs before using it to create arrIDs, but I haven't succeeded yet.

Anyone?

Stephen



Spooky -> RE: Ubound error (5/29/2005 5:12:43)

arrIDs = Split(Left(Request.Form("ID_Array"),Len(Request.Form("ID_Array"))-1),";")



BeTheBall -> RE: Ubound error (5/29/2005 10:00:44)

I am sure Spooky's fix will work, but I am puzzled as to why you would be getting an extra empty value.  When you test, how many email addresses are being returned?  It almost seems as though you are somehow getting one more email address than ID meaning that one of the email addresses has no associated ID.

What do you see on page one if you update this code:

<!--#include file="../_fpclass/fpdbrgn2.inc"-->
<% response.write "Asknotes = " & session("uAskNotes") %><br>
<% response.write "sEmail = " & sEmail %>

to:

<!--#include file="../_fpclass/fpdbrgn2.inc"-->
<% response.write "Asknotes = " & session("uAskNotes") %><br>
<% response.write "sEmail = " & sEmail %>
<% response.write "strIDs = " & strIDs%>




sgreen0 -> RE: Ubound error (5/30/2005 4:22:26)

In fact, Spooky's code did work.

Here are the results of those three lines:

Asknotes = Testing
sEmail = elise@jacobschimes.com;sgreen0@charter.net;elise@anmt.org;edewsberry@charter.net;
strIDs = 921;922;923;924;

Since I have your attention, it seems that though the database update portion of the code works now, the e-mail doesn't.  It has something to do with the e-mail list (above).  When placed in in the BCC and sent to myself, I get the message, but the BCC's don't arrive.

Our mail server says something like:

Can't find charter.net

Any ideas?

Stephen



Spooky -> RE: Ubound error (5/30/2005 5:44:02)

I dont see the email code above? be aware though - some servers wont let you send BCC



sgreen0 -> RE: Ubound error (5/30/2005 11:10:49)

Sorry, I didn't post the e-mail portion of the code earlier. 

<%
Function die(strInput)
response.write strInput
response.end
End Function
'change to address of your own SMTP server
strHost = "mail.anmt.org"
strReturn = Request.Form("Return")
strBody = ""
strMessage = trim(Request.Form("Message"))
if not strMessage = "" then
strBody = strBody & "Message: " & vbcrlf & strMessage & vbcrlf
end if
'////////////////////////
'die strBody
'////////////////////////
Set Mail = Server.CreateObject("Persits.MailSender")
' enter valid SMTP host
Mail.Host = strHost
Mail.From   = "[email=academy@anmt.org]academy@anmt.org[/email]" ' From address
'Mail.FromName  = trim(Request.Form("FromName")) ' optional
Mail.AddAddress  "[email=elise@anmt.org]elise@anmt.org[/email]"
Mail.AddBCC       trim(Request.Form("Emails"))
' message subject
Mail.Subject  = Request.Form("Subject")
' message body
Mail.Body   = strBody
strErr = ""
bSuccess = False
On Error Resume Next ' catch errors
Mail.Send ' send message
If Err <> 0 Then ' error occurred
strErr   = Err.Description
Response.Write "halted : " & strErr
else
bSuccess   = True
'  Response.Redirect(strReturn)
End If
%>


Now, it occurs to me that when I've used multiple e-mails before like this - email1;email2;email3;etc... - that I cut and pasted into Outlook.  Perhaps I should use a different delimiter for this?

Thanks.

Stephen



ou812 -> RE: Ubound error (5/30/2005 12:40:36)

I've not used Persists before, but I gave it a lookup and found that you need to add them one by one, by going through your array of emails. http://support.persits.com/show.asp?code=PS02010983

Off the top of my head, maybe something like: 
all_BCCs = split(Emails,";")
For i = 0 to ubound(all_BCCs)
       sub_bcc = all_BCCs(i)
       Mail.AddBCC trim(sub_bcc)
Next


<EDIT>
Oh, and not sure if you always have Bcc's or not, so you may want to see if so ahead of the for/loop. (ie If all_BCCS <>"" or something then)
</EDIT>



Page: [1] 2   next >   >>

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.140625