VB dataset issue

Question

The idea was to create a message box that stores my user name, message, and post datetime into the database as messages are sent.

Soon came to realize, what if the user changed his name?

So I decided to use the user id (icn) to identify the message poster instead. However, my chunk of codes keep giving me the same error. Says that there are no rows in the dataset ds2.

I've tried my Query on my SQL and it works perfectly so I really really need help to spot the error in my chunk of codes here.

---

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        Dim name As String
        Dim icn As String
        Dim message As String
        Dim time As String
        Dim tags As String = ""

        Dim strConn As System.Configuration.ConnectionStringSettings
        strConn = ConfigurationManager.ConnectionStrings("ufadb")

        Dim conn As SqlConnection = New SqlConnection(strConn.ToString())
        Dim cmd As New SqlCommand("Select * From Message", conn)

        Dim daMessages As SqlDataAdapter = New SqlDataAdapter(cmd)
        Dim ds As New DataSet

        cmd.Connection.Open()
        daMessages.Fill(ds, "Messages")
        cmd.Connection.Close()

        If ds.Tables("Messages").Rows.Count > 0 Then
            Dim n As Integer = ds.Tables("Messages").Rows.Count
            Dim i As Integer
            For i = 0 To n - 1
                icn = ds.Tables("Messages").Rows(i).Item("icn")
                Dim cmd2 As New SqlCommand("SELECT name FROM Member inner join Message ON Member.icn = Message.icn WHERE message.icn = @icn", conn)
                cmd2.Parameters.AddWithValue("@icn", icn)
                Dim daName As SqlDataAdapter = New SqlDataAdapter(cmd2)
                Dim ds2 As New DataSet
                cmd2.Connection.Open()
                daName.Fill(ds2, "PosterName")
                cmd2.Connection.Close()
                name = ds2.Tables("PosterName").Rows(0).Item("name")
                message = ds.Tables("Messages").Rows(i).Item("message")
                time = ds.Tables("Messages").Rows(i).Item("timePosted")
                tags = time + vbCrLf + name + ": " + vbCrLf + message + vbCrLf + tags
            Next
            txtBoard.Text = tags
        Else
            txtBoard.Text = "nothing to display"
        End If
    End Sub

---

Answer 1

Hi Gabriel, would it be more efficient to combine both cmd and cmd2, such that cmd becomes "SELECT msg.*,mem.Name FROM Message msg INNER JOIN Member mem ON msg.icn = mem.icn"?

This way, your Member.name would be in the same dataset as your Messages table, making your code much cleaner.

-Joel