I'm in the process of making a dynamic data map. I've successfully pulled in data I need for one state however I need to do all 50 states, I know there's a cleaner way than doing 50+ recordsets, but cant seem to figure that out. I'm running two queries per state (acquirers & sellers) -- I also need to put the query results in a variable to place within my JS popups instead of the static data. Here's the current code I have for one state (Oregon). And here's the link where you can see what's going on. Again, the current popup data is 'static' the top of the page is showing the pulled data from the access db... code is below as well...hoping someone can steer me in the right direction here...Thanks!!http://www.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
hmm, i'm getting this error now....
_____________________
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Characters found after end of SQL statement.
/newsroom/mapmonthly/index
__________________________
line 66 is:
Set rsData = getDataCmd.Execute
http://www.morrisseygoodal
ok, I now understand how to put the RS into the array, which I will work on. To give you a tad more info.. The sql statement used above is getting all ACQUIRERS from the database as well as grabbing the distinct count of the acquirers. So the end result will look like this:
ACQUIRERS
abc company
def company
ghi company(3) 'meaning there's three ghi companies
and if there is no data the write:
ACQUIRERS(0)
Now I know once I retrieve the array i need to do some checking on the rs to get these results above. After the "if then" can turn the results into a variable? So I can just call variables like <%=strOregonAcquirers> <%=strOregonSellers> within my javascript popups for each state on my map?
I need the rs array to do following and if there isnt any data in the array to display "ACQUIRERS(0)". I'm unable to figure out how i can incorporate the 'Do While' into this array and how i can save this info so I can just place the variable in my popup. I'm hoping that makes some sense ....
<% if objrs.eof = false then
response.write "ACQUIRERS" %><br />
<%Do While Not objRS.EOF
dim count
count = objRS("Expr1")
if count > 1 then %><%=objRS("cat")%> (<%=count%>) <%else%> <%=objRS("cat")%> <% end if %>
The above code does exactly what a want but of course need the array equivalent: ie,
ACQUIRERS
abc company
def company
ghi company(3)
And then somehow save this data so I can place it in my map popups.
ok, i've got one array working thank you for all your input thus far!! I just need to figure out how to place the output within my map popups. Here's my working array code below:
<% strSQL = "SELECT [%$##@_Alias].Cat, [%$##@_Alias].State, Count(*) AS Expr1 FROM [select Seller as Cat, SellerState as State FROM 2007Data]. AS [%$##@_Alias] WHERE ((([%$##@_Alias].State)='O
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, ODBC_CNCT
MyArray = objrs.GetRows()
Const AcquirorStateCatOrdinal = 0
Const AcquirorStateOrdinal = 1
Const AcquirorNumOrdinal = 2
Ubound MyArray,1 'Returns the Number of Columns
Ubound MyArray,2 'Returns the Number of Rows
If IsArray(MyArray) = True Then
Response.write "SELLERS" & "<BR>" & vbNewLine
For lnLoopCounter = 0 To Ubound(MyArray,2)
Response.Write MyArray(AcquirorStateCatOr
Next
Else
Response.Write "SELLERS(0)"
End If%>
Which outputs this:
SELLERS
DuPont Engineering(2)
Gaylord Land Surveying (1)
Geo-Spatial Solutions Inc.(1)
WRG Design(1)
Now i need to get the above output into the onmouseover event (see below):
<area shape="rect" coords="106,101,141,133" href="#" onmouseover="Tip('<b>Oregon</
THANKS!!
Sorry for the delay in getting back to you, lots of meetings today... anyway, if you need to get your output into that HTML, here's what you'd do. Now, I have no idea where you're getting the coordinates -- your query doesn't seem to grab them which means they're not in your recordset/array, if I'm reading things correctly -- so these will all display on top of each other. You'll need some way to dynamically collect the coordinates, probably by way of some additional database fields.
If Access doesn't support multiple queries per connection then I think you'll have to. If I understand correctly, you're going to need one query per state so you can use your states array to loop that. I see you're using the abbreviation in your query so you'd have to make a change to your states array to hold both the abbreviation (for the query) and the full name (for display to the user). So, putting it all together would look something like this:
Another thought -- I seem to have the best ones *after* I've posted -- you'll probably want to take into account the possibility of having no data for a particular state. So I'd take this:
' Now we write the data
varPopupText = ""
For i = 0 to uBound(arrData,2)
varPopupText = varPopupText & arrData(0,i) & "(" & arrData(2,i) & ")" & "<br>"
Next
And turn it into this:
' Now we write the data
varPopupText = ""
If IsArray(arrData) = True Then
For i = 0 to uBound(arrData,2)
varPopupText = varPopupText & arrData(0,i) & "(" & arrData(2,i) & ")" & "<br>"
Next
Else
varPopupText = "No Data Found for this State"
End If
Hey there - I'm stuck again and have opened up a new ticket but since you have some familiarity with my question in the past i'm hoping you could steer me in the right direction. I would like to open up this question again for you and award you 500 points. I need to adjust the array that i've set up for all my 50 states. My query now needs to pull just he company name and new field "LINK" which i need to have the companyname link ie; <a href="http:company1LInk">co
<% 'AL ACQUIROR
strSQL = "SELECT [2009Data].Acquiror, [2009Data].LINK FROM 2009Data WHERE ((([2009Data].AcquirorStat
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open strSQL, ODBC_CNCT
If Not objRS.EOF Then
MyArray = objrs.GetRows()
Ubound MyArray,1 'Returns the Number of Columns
Ubound MyArray,2 'Returns the Number of Rows
Dim varALPopupTextA
For i = 0 to uBound(MyArray,2)
varALPopupTextA = varALPopupTextA & "- " & MyArray(0,i)
Next
Else
varALPopupTextA = "- (0)<BR>"
End If
objrs.Close()
Set objrs = Nothing
Business Accounts
Answer for Membership
by: saoirse1916Posted on 2009-07-28 at 12:29:48ID: 24964249
Rather than doing multiple recordsets and connections to the database I'd put all the SELECT statements into a single connection and jump through the recordsets using rs.NextRecordset(). Then save the data to arrays rather than keeping all those recordsets open. Something like this:
Select allOpen in new window