Multiple fields return with one select command into an list or array

[Socarsky]

I try to get multiple fields from DB as you can see the below in string line. But I can only get concanetated of name and surname.
I need to get the others in an list or array, but how?

    Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
        Dim mylist As New List(Of String)
        cs.Open()
        Dim PsqlQuery As String = "SELECT RTRIM(Name)+' '+Surname, email, city, telno from Contacts WHERE surname LIKE 'Bas%';"
        Dim readData As PsqlDataReader
        Dim adapt As New PsqlDataAdapter
        Dim cmd As New PsqlCommand
        cmd.CommandText = PsqlQuery
        cmd.Connection = cs
        adapt.SelectCommand = cmd
        readData = cmd.ExecuteReader
        While readData.Read()
            mylist.Add(readData(0).ToString)
        End While
        cs.Close()
        Dim str As String
        For Each str In mylist
            MessageBox.Show(str)
        Next
    End Sub

 

[IanRyder]

Hi,

The thing to remember is that each item in a List is only one object. However, that object can be of any Type but as you have it now you are holding objects of type String which is why, at this point, you can only add your name to the List and nothing else.

So, what you need to do is to create an object that can hold more than one bit of information and then add that object to the List.

This is commonly referred to as "Creating you Own Custom Class or Structure". i.e:-

Public Class CustomerInfo
  Public Property CustomerName As String
  Public Property EmailAddress As String
  Public Property City As String
  Public Property TelNo As String
  'etc etc etc
End Class

Now, first of all, you need to change your List to a List of CustomerInfo and then when you execute your DataReader you create an instance of your CustomerInfo class and then assign each field of the DataReader to a field in your CustomerInfo Class and then you add the instance of your CustomerInfo class to the List. i.e:-

Dim mylist As New List(Of CustomerInfo)
 
While readData.Read()
  Dim myCustomer As New CustomerInfo
  With myCustomer
    .CustomerName = readData(0).ToString
    .EmailAddress = readData(1).ToString
    .City = readData(2).ToString
    .TelNo = readData(3).ToString
  End With
  mylist.Add(myCustomer)
End While

Hope that helps.

Cheers,

Ian

 

[Socarsky]

Thanks a lot Ian, spot on! Here is the final state

    Private Sub btnSelect_Click(sender As Object, e As EventArgs) Handles btnSelect.Click
        Dim thelist As New List(Of String)
        cs.Open()
        Dim PsqlQuery As String = "SELECT RTRIM(Name)+' '+Surname, name, surname, ID from Contacts WHERE surname LIKE 'Bas%';"
        Dim readData As PsqlDataReader
        Dim adapt As New PsqlDataAdapter
        Dim cmd As New PsqlCommand
        cmd.CommandText = PsqlQuery
        cmd.Connection = cs
        adapt.SelectCommand = cmd
        readData = cmd.ExecuteReader
        Dim mylist As New List(Of CustomerInfo)
        While readData.Read()
            Dim myCustomer As New CustomerInfo
            With myCustomer
                .CustomerName = readData(0).ToString
                .EmailAddress = readData(1).ToString
                .City = readData(2).ToString
                .TelNo = readData(3).ToString
            End With
            mylist.Add(myCustomer)
        End While
        cs.Close()
        For i As Integer = 0 To 1
            MessageBox.Show(mylist(i).CustomerName.ToString & _
                            " " & mylist(i).EmailAddress.ToString & _
                            " " & mylist(i).City.ToString & _
                            " " & mylist(i).TelNo.ToString)
        Next
    End Sub

 

[IanRyder]

Hi,

Looks good, but I have one comment for you. What if there happens to be more than 2 records returned by your Where clause:-

WHERE surname LIKE 'Bas%'

You currently use:-

For i As Integer = 0 To 1
  MessageBox.Show(mylist(i).CustomerName.ToString & _
                  " " & mylist(i).EmailAddress.ToString & _
                  " " & mylist(i).City.ToString & _
                  " " & mylist(i).TelNo.ToString)
Next

Which implies that there can only ever be two records returned from your query but this may not be True due to the use of the wildcard in the Where clause. I would therefore suggest that you use something like:-

For Each myCust As CustomerInfo In mylist
  MessageBox.Show(myCust.CustomerName.ToString & _
                  " " & myCust.EmailAddress.ToString & _
                  " " & myCust.City.ToString & _
                  " " & myCust.TelNo.ToString)
Next

The difference here is that every customer returned from the query and entered into the List would be displayed.

Hope that helps.

Cheers,

Ian

 

[Socarsky]

I don't know what to tell you, excellent.. Ian, appreciate

 

[Socarsky]

There is another issue which needs to put the whole string of returned Psql result with format into a email's body to send. But I tried a couple methods but fail.

Dim emailBody As String = ""

        For Each myCust As CustomerInfo In mylist
            If (myCust.result <= -1) And (myCust.aStock <> 0) Then
                emailBody += ((myCust.Kod.ToString & _
                            " " & myCust.Name.ToString & _
                            " " & myCust.aStock.ToString & _
                            " " & myCust.sStock.ToString & _
                            " " & myCust.result.ToString) & vbCrLf)
            End If
        Next
        Dim MsgSend As New MailMessage("sbs@gmail.com", "sbs@esas.com", "Some Safety Stocks", emailBody)
        smtpClient.Send(MsgSend)

 

[IanRyder]

Hi,

For the future, and to get the best results from this forum, please remember the following two things:-

1) For a new question, please create a new thread and ask your new question.
2) Please explain in detail What you have done, what you are expecting, what is actually happening and what errors you are getting.

That said, you are using the SmtpClient object incorrectly. You need to create a new instance in the SmtpClient object, configure your email host information and then use the Send method to send your email. i.e:-

Dim myEmailClient As New SmtpClient
'Configure your host details
myEmailClient.Send(MsgSend)

Hope that helps.

Cheers,

Ian

 

[Socarsky]

There is no problem by sending email Ian, I haven't pasted the details of SmtpClient. The problem is string format. I want to get lines of string in a good row viewing.

Like the below:

Module Module1
        Sub Main()
            ' Format String.
            Dim format As String = "{0,-10} {1,10}"

            ' Construct lines.
            Dim line1 As String = String.Format(format, 100, 5)
            Dim line2 As String = String.Format(format, "Carrot", "Giraffe")
            Dim line3 As String = String.Format(format, True, False)

            ' Print them.
            Console.WriteLine(line1)
            Console.WriteLine(line2)
            Console.WriteLine(line3)
        End Sub
    End Module

 

[IanRyder]

Errrrr? On the basis that you already know exactly how you want to format your Email body, what are you asking us to help you with?

Confused!

Ian

 

[Socarsky]

I applied all the lines of code the below but its messy
I want to see a decent one, thats all

Dim format As String = "{0,-30} {1,-70} {2,-50} {3,-20} {4,-20}"
        Dim emailBody As String = ""
        For Each myCust As CustomerInfo In mylist
            If (myCust.result <= -1) And (myCust.aStock <> 0) Then
                emailBody += (String.Format(format, myCust.Kod.ToString, myCust.Name.ToString, myCust.aStock.ToString, myCust.sStock.ToString, myCust.result.ToString)) & vbCrLf
            End If
        Next

 

[IanRyder]

Hi,

Next time you ask a question, please remember this thread, and ask yourself this question before you post:-

"How do I ask my question in one go rather than having to cause 5 posts before someone understands what I am trying to do?"

If you applied your coding to a console application window as a demonstration you would see that this works fine. So what else do you think could cause this misplaced formatting? You are going to need to use a fixed width font to achieve your formatting correctly.

Cheers,

Ian

 

[Socarsky]

Hi,
If you applied your coding to a console application window as a demonstration you would see that this works fine. So what else do you think could cause this misplaced formatting? You are going to need to use a fixed width font to achieve your formatting correctly.
Cheers,
Ian

I handled the issue that I wanted someone to help me last time..
Thanks a lot Ian,

 
Dim usernamePassword As New Net.NetworkCredential("ssssss@gmail.com", "pppppp")
Dim emailBody As String = ""

        For Each myCust As CustomerInfo In mylist
            If (myCust.result <= -1) And (myCust.aStock <> 0) Then
                emailBody += (("<tr><td>" & myCust.Kod.ToString & "</td><td>" & myCust.Name.ToString & "</td><td>" & myCust.aStock.ToString & "</td><td>" & myCust.sStock.ToString & "</td><td>" & myCust.result.ToString & "</td>"))
            End If
        Next
        Dim htmlHeader As String = "<table border=""1""><tr><th>Stock Code</th><th>Definition of Stock</th><th>Stock Amount</th><th>Safety Stock</th><th>Difference</th></tr>"
        Dim bottom As String = "</tr></table>"
        Dim objMM As New MailMessage

        Dim fromAddress As MailAddress
        fromAddress = New MailAddress("sssssss@gmail.com", "Sinan")
        Try
            objMM.Body = "<span style=""font-family:Consonlas;font-size: 14pt;"">" & htmlHeader & "" & emailBody & "" & bottom & "</span>"
            With objMM
                .IsBodyHtml = True
                .To.Add("fuattt@esss.com.tr")
                .From = fromAddress
                .Priority = MailPriority.High
                .Subject = "Status of Safety Stock"
                .Body = objMM.Body
            End With
            smtpClient.Port = 587
            smtpClient.EnableSsl = True
            smtpClient.Credentials = usernamePassword
            smtpClient.Send(objMM)
        Catch ex As Exception
            'Empty catch
            Throw ex

        End Try