Sql statement from listview.

[Dman]

I have a listview that pulls a name and email address from a table just fine.

When I click on the name, I can't seem to retrieve the email address. I put a messagebox that states the selected record before the statement and it clearly states the name. After I set up a message box to catch the email address and it is blank. The table only has 1 record for now and I know the record is in there. Here's the code. Can anyone spot the issue?

Sub loadlvemailaddy
   ' This part populates the ListView
   lvEmailAddy.Clear
      Main.dbCursor = Main.SQL1.ExecQuery("SELECT name, email FROM emailaddresses ORDER by name")
   
         For i = 0 To Main.dbCursor.RowCount - 1
         Main.dbCursor.Position = i      

            ' Set up the ListView    
            lvEmailAddy.AddSingleLine(Main.dbCursor.GetString("name") & " :" & Main.dbCursor.GetString("email"))
            lvEmailAddy.SingleLineLayout.ItemHeight = 60dip
            lvEmailAddy.SingleLineLayout.Label.TextSize = 16
            lvEmailAddy.SingleLineLayout.Label.TextColor = Colors.White
            lvEmailAddy.SingleLineLayout.Label.Gravity = Gravity.LEFT
            lvEmailAddy.ScrollingBackgroundColor = Colors.Transparent
         Next

End Sub

Sub lvEmailAddy_ItemClick (Position As Int, Value As Object)
    Dim emailaddy As String
      Dim selectedrecord As String
          selectedrecord = Value
           countit = selectedrecord.IndexOf(":")
              selectedrecord = selectedrecord.SubString2(0,countit)

         Msgbox(selectedrecord,"")


                  emailaddy = Main.SQL1.ExecQuerySingleResult("SELECT email FROM emailaddresses WHERE name = '" & selectedrecord & "'"))

          Msgbox(emailaddy,"")


       'sendinvoice
       'Msgbox("Invoice sent","")
       Panel1.Visible = False

   
End Sub

 

[NJDude]

I think the problem is here:

emailaddy = Main.SQL1.ExecQuerySingleResult("SELECT email FROM emailaddresses WHERE name = '" & selectedrecord & "'"))

You are selecting EMAIL only and testing NAME, it should be:

emailaddy = Main.SQL1.ExecQuerySingleResult("SELECT name, email FROM emailaddresses WHERE name = '" & selectedrecord & "'"))

 

[Dman]

I tried that and still nothing. That's why I am confused.

That and I am old.

 

[Informatix]

I can't test your project without a database, but here are a few things that could help:

1) move these lines out of the loop:

lvEmailAddy.SingleLineLayout.ItemHeight = 60dip
lvEmailAddy.SingleLineLayout.Label.TextSize = 16
lvEmailAddy.SingleLineLayout.Label.TextColor = Colors.White
lvEmailAddy.SingleLineLayout.Label.Gravity = Gravity.LEFT
lvEmailAddy.ScrollingBackgroundColor = Colors.Transparent

It's useless to set these values each time, because they are set for all items.

2) Trim "selectedrecord" to ensure there's no space as last character.

3) Replace:

emailaddy = Main.SQL1.ExecQuerySingleResult("SELECT email FROM emailaddresses WHERE name = '" & selectedrecord & "'")

by

emailaddy = Main.SQL1.ExecQuerySingleResult2("SELECT email FROM emailaddresses WHERE name=?", Array As String(selectedrecord))