Executing query inside a data reader?

[icycold68]

Hello,

I was wondering if it is possible to execute an UPDATE SQL query within a data reader object? For example, would it be possible to make the code below work. Clearly at the moment this generates an error saying that there is already an open data set associated with this connection.

Do While myReader2.Read()

lat2 = myReader2("lat").ToString()
lon2 = myReader2("lng").ToString()

unit = "M"


Dim return_distance As String = distance(lat1, lon1, lat2, lon2, unit)

If return_distance.length <> 1 Then

trimmed_distance = return_distance.SubString(0,4)


Else

trimmed_distance = return_distance

End If

Dim converted_distance As Integer

converted_distance = CInt(trimmed_distance)

Dim outcode As String

If trimmed_distance <= 30 Then

postcode_match.Add(myReader2("outcode").ToString())

Dim strSQL As String = "UPDATE tbl_sales SET distance_temp = '" & trimmed_distance & "' WHERE PostCode LIKE '%" & myReader2("outcode").ToString() & "%'"

myCommand.Connection = conn
myCommand.CommandText = strSQL
myCommand.ExecuteNonQuery()


distance_list.Add(trimmed_distance)

End If

Loop

myReader2.Close()

 

[IanRyder]

Hi,

I wrote this a few days ago ready for you to post back to our last questions but you never responded, so I never posted, but an old company of mine has recently asked me to do something similar with an existing telemarketing system of theirs so I thought I would share the experience to see if it helps with your thought process. It may then help us better to direct you with your own code.

My goal was to maximise a salesman's use of time using the following this simple logic:-
1) A user makes the first appointment of the day for a salesman with a company at a particular postcode.

2) To maximise a salesman's use of time all other appointments for the day must be made within a certain radius, say 10 miles, from the first appointment.

I have created a solution for this by doing the following:-
1) Create a data table with every postcode in the UK containing their Latitude and Longitude coordinates. This being 1.7 million records and the postcode field being uniquely indexed.

2) I modified their Prospects database to add the Latitude and Longitude coordinates to every prospect, linked by the postcode to the Postcode database in point 1. The Latitude and Longitude fields are indexed.

3) I then created an Entity Data Source in my project which contains the two tables above so that they can be accessed in my project.

4) When the first appointment is made for the salesman I use the entered postcode to search the postcodes database to obtain the Latitude and Longitude for the entered postcode.

Now, I have to get all the records in the prospects table that are within a radius of the entered postcode. I could do this by reading every prospect into my project and use a calculation to figure out the distance between postcodes. The prospects table has circa 1.2 millions records which therefore means if I do this it will take a decade to run every time a postcode is entered.

My solution was to use SQL to do most of the work by limiting the record selection returned to the project from the database in relation to the postcode that was entered. I do this by calculating the Latitude and Longitude limits in the North, South, East, West plane in relation to the specified radius that is needed and I pass this as a where clause to the postcode table record selection in my Entity Data Source. Therefore rather than returning 1.2 million records it may only return 1000 for example.

5) Using the above note, I calculate the limits of the Latitude and Longitude selection criteria (Basically calculating a Grid Square of Postcodes to return) and query the Entity Data Source to return the prospect records that are needed.

6) Now I narrow down the returned data set by calculating the actual distance between the entered postcode and the prospect postcode. If the distance is outside of the required radius then the prospect is discarded otherwise the CompanyName, Postcode, TelephoneNo and DistanceFromSource are added to a customer defined class type and added to a list for processing further.

7) Once I have a full list of prospects within the required radius I then sort the list using a custom sort routine to sort the prospects in ascending order of the distance from the entered postcode.

8) This list is then displayed to the user for selection of their next appointment for the salesman.

All in all, my project is returning correct record selections in less than half a second which is more than usable for the user.

Let us know if this helps at all.

Cheers,

Ian

 

[Icone]

I'll set an example for you.

cmd = new oledb.oledbcommand("Select * from tblsample where field1= 'sample'")

if con.state = connection.closed then con.open

dr = cmd.executereader()

if dr.read = true then

dim a as string = "newString"

dim aa as integer = dr(0) // row ID

cmdd = new oledb.oledbcommand("update tblsample set field1 = '" & a & "' where field_id= " & aa & "

if conn.stae = connection.closed then conn.open

drr = cmdd.executereader()

drr.close

conn.close

end if

 

[icycold68]

Thank you for your help on this matter everyone.