PDA

View Full Version : SQL - placing all pins



Tomm
12-23-2008, 06:22 AM
Hi all.

using vb.net, I'm creating a sql query from a series of checkboxes and then placing the pins on the map and organising them in to pushin sets.

The problem I'm having is that it is placing ALL of the pins that I haven't asked for into the default "my pushpin" set, which I then delete.

So whilst the user won't see it, it is still taking the data from the database, and so it generating the map slower than it should be.

Here's the code.



Dim dbSQLConn As New SqlClient.SqlConnection
dbSQLConn.ConnectionString = strConn
dbSQLConn.Open()


'''''''''''
'''''''''''
'''''''''''
' sql adapter to read sql statement above
'''''''''''
'''''''''''
'''''''''''

Dim SQLda As New SqlClient.SqlDataAdapter
Dim sqlCommand As New SqlClient.SqlCommand
Dim dsData As DataSet
sqlCommand.Connection = dbSQLConn
sqlCommand.CommandText = strSQLAll

SQLda.SelectCommand = sqlCommand

'start dataset and fill it with the SQL results
dsData = New DataSet
SQLda.Fill(dsData)



For Each dr In dsData.Tables(0).Rows
'If NULL data in Postcode, show message box and carry on

If dr("PostCode") Is DBNull.Value Then
sw.WriteLine("Null postcode value at " & dr("ServiceID") & " " & dr("Name"))

Else
'set FindResults to look for PostCode, not long/lat and plots Pins
oFR = oMap.FindResults(dr("PostCode"))
If oFR.Count > 0 Then


oPin = oMap.AddPushpin(oFR(1), dr("ServiceID") & " " & dr("Name")) 'default balloon data unless specified from checkboxes

'Balloon information for each pin

If chkPostCode.Checked = True Then
strBalloonText = strBalloonText + "Postcode: " & dr("PostCode") & Chr(13)
End If

If chkTelephone.Checked = True Then
strBalloonText = strBalloonText + "Telephone: " & dr("Telephone") & Chr(13)
End If
If chkLocManager.Checked = True Then
strBalloonText = strBalloonText + "Locality Manager: " & dr("LocalityManager") & Chr(13)
End If

If chkDivRegInfo.Checked = True Then
strBalloonText = strBalloonText + "Division: " & dr("DivisionName") & Chr(13) & "Region: " & dr("RegionName") & Chr(13)
End If







'''''''''''
'''''''''''
'''''''''''
'Organise pins into datasets (and symbols) depending on Region
'''''''''''
'''''''''''
'''''''''''
'there are a few of these types of statements

If chkNorthReg.Checked = True Then

If dr("RegionID") = "1" Then
'oLoc = oMap.GetLocation(oFR(1), dr("Name"))
'oPin.MoveTo(oDSNorth)
oPin.MoveTo(oMap.DataSets.Item("Region1"))
End If

End If




Else
'MapPoint can't place all pins (can't find location)
sw.WriteLine("Unable to find post code " & dr("PostCode") & " for service " & dr("ServiceID") & " " & dr("Name"))
End If

End If


Next

Dim o As Object = "My Pushpins"
Dim ds As MapPoint.DataSet = MPApp.ActiveMap.DataSets(o)
ds.Delete()


MsgBox("Done")



So obviously I'm doing something wrong as it is putting all the pins on there. So any clues would be great. Or any tips/pointers on how to improve it all would also be welcomed :)

Merry Christmas all.

Tom

Mattys Consulting
12-23-2008, 09:44 PM
Tomm,

I have to ask you why you left out the SQL statement from your post?
It would seem logical to query for only the data you need
and add it to its own dataset ...

Mike Mattys

Tomm
12-24-2008, 05:07 AM
Tomm,

I have to ask you why you left out the SQL statement from your post?
It would seem logical to query for only the data you need
and add it to its own dataset ...

Mike Mattys


Ooops, I did leave out the SQL. Which, looking at the results if I run it directly in the database, gives me some dodgy data.....

SELECT * FROM viewMap WHERE (Type1='true') OR (Type3='true') OR (Type2='true') AND (RegionID='REG-1' OR RegionID='REG-2')

Paul Larson
12-24-2008, 09:57 AM
I think you want to change your SQL statement to

SELECT * FROM viewMap WHERE ((Type1='true') OR (Type3='true') OR (Type2='true')) AND (RegionID='REG-1' OR RegionID='REG-2')

...which just adds a set of parens around the typeN conditions.
Optionally, you might be able to use the following, depending on your SQL platform:

SELECT * FROM viewMap
WHERE 'true' IN (Type1,Type2,Type3)
AND RegionID in ('REG-1','REG-2')


HTH
~Paul

Tomm
01-14-2009, 06:06 AM
Hi, been away from this project for a while.

Thanks for that SQL tip, but I need to extend it a little futher.




SELECT * FROM viewMap WHERE ((Type1='true') OR (Type2='true') OR (Type3='true')) AND ((RegionID='N1' OR RegionID='N2') OR (RegionID='W1'))



So a location with a Type1 can be in either N1,N2, or W1.

Tomm
01-14-2009, 06:38 AM
Actually, think that has done it, and I had a problem in my vb.net code.

Comparing my map results to results in the sql using the generated query, it all looks fine.

I then use this code to delete the default My Pushpins dataset



Dim o As Object = "My Pushpins"
Dim ds As MapPoint.DataSet = MPApp.ActiveMap.DataSets(o)
ds.Delete()