need to import into a custom class using reader from a postsql database

Hello;

I have the below working code, but I would rather pull everything into a class then be able to work on it that way vs the way I have it.

Any thoughts or guidance on a better way to code this would be great!

     'get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
        Dim stopAddressList = From s In db.stop_address_details Select s
        Dim searchAddressList = stopAddressList.ToArray
        searchAddressList.Clone
  
        DataGridView1.DataSource=stopAddressList.ToArray ' debug code - remove
        command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                                     "from cops_reporting.distribution_stop_information " & _
                                     "where customer_no = '91000' and stop_signature <> '' and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 
        PgSqlConnection1.Open ' opens the connection to the postgresql server
     Label1.Text = "opening reader..."  
        Using reader As PgSqlDataReader =command.ExecuteReader()
            Label1.Text = "starting"
            Application.DoEvents
            While reader.Read
                totalRecordCount +=1
                Dim customerReferenceNo, stopName, recBy As String
                customerReferenceNo=reader.GetValue(0).ToString
                'go see if this is one of the sites we are monitoring
                Dim searchfor = From f In searchAddressList Where f.customer_reference_no = customerReferenceNo Select f
           
                label1.text = totalRecordCount.ToString              
                Label1.Update
                If searchfor.Count>0 ' found it, now see if we already have this POD in our database - the below code will be replaced with a more through routine
                    stopName =reader.GetValue(1).ToString
                    recBy=reader.GetValue(5).ToString
                    Dim dateOfStop As Date = reader.GetValue(3)
                    Dim timeOfStop As TimeSpan = reader.GetValue(4)
                    Dim datetimeOfStop As Date = dateOfStop.Add(timeOfStop)
                    foundRecordCount +=1
                    TextBox1.AppendText ("Stop at " & customerReferenceNo & " for " & stopName & " signed for by " & recBy &  " at " & datetimeOfStop.ToString & " - " & timeOfStop.ToString & vbCrLf)
                End If
                
            End While
            Label1.Text = "record count: " & foundRecordCount & " out of " & totalRecordCount
        End Using
        PgSqlConnection1.Close

Open in new window

Joe RuderAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Fernando SotoConnect With a Mentor RetiredCommented:
Sorry Joe I screwed up again.

I work in C# most of the time and when I post VB question I normally check it out in Visual Studio to make sure it is syntactically correct but I have been working away from my computer with the compiler.

In C# this is the start of a Lambda expression, crn => ...., but in Visual Basic it starts like this, Function(crn), so that said the line should be this, mind you I am still at a computer that does not have a compiler. ;=)

Dim pSqlRec As PostsqlData = postsqlDataList.Find (Function(crn) crn.CustomerReferenceNo =  record.customer_reference_no )

Open in new window


By the way I had to change the == back to =, we are in VB .Net.
0
 
Fernando SotoRetiredCommented:
Hi Joe;

Here is some sample code showing how I would try it. I do not think I forgot anything from your code.
''get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
'' ------------------------------------------------------------------------
'' This is not needed as I call the query below and just get what is needed
''Dim stopAddressList = From s In db.stop_address_details Select s
''Dim searchAddressList = stopAddressList.ToArray
''searchAddressList.Clone
''DataGridView1.DataSource=stopAddressList.ToArray '' debug code - remove
'' -------------------------------------------------------------------------

command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                             "from cops_reporting.distribution_stop_information " & _
                             "where customer_no = '91000' and stop_signature <> '' and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 
PgSqlConnection1.Open '' opens the connection to the postgresql server
Label1.Text = "opening reader..."

'' Data from the postsql database download from the server
Dim postsqlDataList As New List(Of PostsqlData)()
Dim totalRecordCount As Integer = 0
'' Load the postsqlDataList from the database
Using reader As PgSqlDataReader =command.ExecuteReader()
    While reader.Read
       totalRecordCount += 1
       Dim dataRecord As New PostsqlData()
       dataRecord.CustomerReferenceNo = reader.GetValue(0).ToString
       dataRecord.StopName = reader.GetValue(1).ToString
       dataRecord.RecBy = reader.GetValue(5).ToString
       dataRecord.DateOfStop = reader.GetValue(3)
       dataRecord.TimeOfStop reader.GetValue(4)
       postsqlDataList.Add(dataRecord)
    End While
End Using 

PgSqlConnection1.Close

'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()
Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

For Each record In searchfor
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _
      record.CustomerReferenceNo, record.StopName, record.RecBy, _
      record.DatetimeOfStop.ToString(), record.timeOfStop.ToString(), vbCrLf)
  TextBox1.AppendText(outputLine)
Next
Label1.Text = "record count: " & searchfor.Count & " out of " & totalRecordCount


'' Custom class to hold data from postgresql database
Public Class PostsqlData                 
    Public Property CustomerReferenceNo As String
    Public Property StopName As String  
    Public Property RecBy As String
    Public Property DateOfStop As Date
    Public Property TimeOfStop As TimeSpan
    Public ReadOnly Property DatetimeOfStop As Date
        Get 
            Return DateOfStop.Add(TimeOfStop)
        End Get 
    End Property
End Class 

Open in new window

0
 
Joe RuderAuthor Commented:
Thank you,

I was traveling over the weekend, I will dig into this tomorrow -- I just didn't want you to think I had abandoned it.

Joe
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Fernando SotoRetiredCommented:
Not a problem Joe.
0
 
Joe RuderAuthor Commented:
I'm sorry...I just got back to this!

Not quite, I don't think I explained things correctly.

I have two databases, one in Postgresql (call it remote) and one Sqlserver (call it local)

I need to query the remote database for new records, then check each against the local.  If they are not there I create them in the local.  Another process then does some more things with them.

The code you removed is the code that pulls the current list from the local database that it compares against, in memory.

What I am wanting to do is to somehow return all the records from the postgresql database (remote) to something that I can just query against or even use as a datasource for a datagridview.datasource= thing.

Your line:
Dim postsqlDataList As New List(Of PostsqlData)()

gives me a PostsqlData is not defined error also...
edit: I am sorry, I need to create the class like you said -- I will try that now.

Is that a better explanation Fernando?
0
 
Joe RuderAuthor Commented:
I'm working through it now sir....sorry for the hasty post.
0
 
Joe RuderAuthor Commented:
Interesting!

Thank you for getting me this close, the only thing is that "searchfor" is pulling form the "local" database and as such does not have all the information from the other side.  It is pulling from the wrong place.

Does that make sense?

In this code:

 
Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

DataGridView1.DataSource=postsqlDataList.ToArray
For Each record In searchfor
        Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _
      record.customer_reference_no, record.stop_name, record.stop_addres, _
      record.DatetimeOftop.ToString(), record.timeOfStop.ToString(), vbCrLf

TextBox1.AppendText(outputLine)

Open in new window


Search for is filled with data from the local database, which does not have the received by, delivery times etc.

You ARE loading up the class however, which is exactly what I wanted so regardless you have certainly help a ton with this problem.

Do you know what I am trying to do above?

I am assuming that I can just step through the records one at a time returned from the "remote" then do a compare against the local, correct?

Thank you,

Joe

here is all the code,
0
 
Fernando SotoRetiredCommented:
Hi Joe;

Make the following change to the For Each loop to get the other records.

For Each record In searchfor
  Dim pSqlRec As PostsqlData = postsqlDataList.Find( crn => crn.CustomerReferenceNo = record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Open in new window

0
 
Joe RuderAuthor Commented:
Hello and thank you,

It is saying that crn is not declared.
If I dim it as a object it does not work either.

I have tried google....what does the '=>' do and what is it called?

Thank you,

Joe
0
 
Fernando SotoRetiredCommented:
My mistake this is the correct version of the line of code in the For Each loop.

Dim pSqlRec As PostsqlData = postsqlDataList.Find( crn => crn.CustomerReferenceNo == record.customer_reference_no )

Open in new window


Please note the == meaning a test of being equal to each other and NOT an assignment of the right into the left variable. Because of this crn was not defined.
0
 
Joe RuderAuthor Commented:
Hmm...

It is stating "Unexpected end of statement"  Missing ')' with the =>, the second crn and the == marked as errors.

What does the => mean/do?
0
 
Joe RuderAuthor Commented:
If I :

dim crn as postsqlData it does let the crn.customerReferenceNo work, but the => and == are still popping errors.

Sorry to be such a PITA...

Joe
0
 
Fernando SotoRetiredCommented:
The line of code looks fine. Please do the following.

Post the complete code I ask you to implement. Copy from your code and paste here.
Please post the complete error message for all the errors.
0
 
Joe RuderAuthor Commented:
Thank you Fernando;

here is the Code with the errors::
'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()

Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

 For Each record In searchfor

   Dim pSqlRec As PostsqlData = postsqlDataList.Find (crn => crn.CustomerReferenceNo ==  record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Open in new window


Errors:

The one for => says: Unexpected end of expression - missing ')'

the first crn (crn.customerReferenceNo ==) says unknown entity crn

== says unexpected end of statement
and at the very end of the line it states:
missing line break - unexpected end of statement

Here is the entire code:
  Dim db As New firstChoiceEntities

        ''get all of the stop address details - move them into a in memory clone to stop it from going back to the database for each search later
'' ------------------------------------------------------------------------
' This is not needed as I call the query below and just get what is needed
'Dim stopAddressList = From s In db.stop_address_details Select s
'Dim searchAddressList = stopAddressList.ToArray
'searchAddressList.Clone
'DataGridView1.DataSource=stopAddressList.ToArray '' debug code - remove
'' -------------------------------------------------------------------------
Dim lastDatetimeUpdated As Date = "1/1/2013"
Dim endDate As Date = "12/6/2014"
        Dim checkit As string

                Dim command As PgSqlCommand = PgSqlConnection1.CreateCommand

command.CommandText="select  distinct on (customer_reference) customer_reference, stop_name, stop_address, actual_service_date, Actual_arrival_time, stop_signature " & _
                             "from cops_reporting.distribution_stop_information " & _
                             "where customer_no = '91000' and stop_signature <> '' and actual_service_date IS NOT NULL and datetime_updated > '"& lastDatetimeUpdated & "' and datetime_updated < '" & endDate &"'" 

PgSqlConnection1.Open '' opens the connection to the postgresql server
Label1.Text = "opening reader..."

'' Data from the postsql database download from the server
Dim postsqlDataList As New List(Of PostsqlData)()
Dim totalRecordCount As Integer = 0
'' Load the postsqlDataList from the database
Using reader As PgSqlDataReader =command.ExecuteReader()
    While reader.Read
       totalRecordCount += 1
                Label1.Text = totalRecordCount.ToString
                Label1.Update
       Dim dataRecord As New PostsqlData()
       dataRecord.CustomerReferenceNo = reader.GetValue(0).ToString
       dataRecord.StopName = reader.GetValue(1).ToString
       dataRecord.RecBy = reader.GetValue(5).ToString
       dataRecord.DateOfStop = reader.GetValue(3)
                Try
dataRecord.TimeOfStop = reader.GetValue(4)
                Catch ex As Exception
                    'no time - use default of 9:00 am
                    dataRecord.TimeOfStop=TimeSpan.Parse("9")
                End Try
       
       postsqlDataList.Add(dataRecord)
    End While
End Using 

PgSqlConnection1.Close

'' This EF query will return only the records that are needed ant not the complete
'' table from the database
Dim lookUpList = postsqlDataList.Select(Function(crn) crn.CustomerReferenceNo).ToList()

Dim searchfor = (From s In db.stop_address_details _
                 Where lookUpList.Contains(s.customer_reference_no) _
                 Select s).ToList()

 For Each record In searchfor

   Dim pSqlRec As PostsqlData = postsqlDataList.Find (crn => crn.CustomerReferenceNo ==  record.customer_reference_no )
  '' From this point on records from remote database can be accessed through the variable pSqlRec
  '' and from the local database through record.
  Dim outputLine = String.Format("Stop at {0} for {1} signed for by {2} at {3} - {4}{5}", _                                                                                     
      pSqlRec.CustomerReferenceNo, pSqlRec.StopName, pSqlRec.RecBy, _                                                                                                              
      pSqlRec.DatetimeOfStop.ToString(), pSqlRec.timeOfStop.ToString(), vbCrLf)                                                                                                   
  TextBox1.AppendText(outputLine)                                                                                                                                               
Next

Label1.Text = "record count: " & searchfor.Count & " out of " & totalRecordCount

End Sub

Open in new window



Is that enough detail or would a screen shot be helpful?

Joe
0
 
Joe RuderAuthor Commented:
0
 
Joe RuderAuthor Commented:
Cool....I am looking forward to the day I feel comfortable enough to work away from the complier!

I am working through it now...I'll post back shortly.

Joe
0
 
Joe RuderAuthor Commented:
That's awesome.

That is SUCH a better way of doing it, that will help me going forward as well.

I really appreciate you taking the time to help me out on this sir!

Sincerely,

Joe Ruder
0
 
Joe RuderAuthor Commented:
Thanks again!
0
 
Fernando SotoRetiredCommented:
Not a problem Joe, glad to help. Next time I will leave the errors out.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.