Simple VB/SQL help

I'm really new and self taught so I don't know exactly how to ask questions here :(
That said I wrote this

Code:

---

        Dim sqlConnections As New System.Data.SqlClient.SqlConnection(DataString)
        Dim acmd As New System.Data.SqlClient.SqlCommand

        cmd.CommandType = System.Data.CommandType.Text
        cmd.CommandText = "SELECT TOP 1 ChangeLogID AS TheRow FROM ChangeLog ORDER BY ChangelogID DESC"
        cmd.Connection = sqlConnections

        sqlConnections.Open()
        Dim TheRow = acmd("TheRow") 'this is the part that I don't know

        acmd.ExecuteNonQuery()
        sqlConnections.Close()

        MsgBox("The magic number is " & TheRow)

---

and Dim TheRow = acmd("TheRow") doesn't work as I am sure it shouldn't since I am just relying on my old vbscript calls.

I'm strong in old ASP and VBS but I just started learing visual basic a couple of weeks ago and well its both awesome and horrible... but.....

How do I properly call the "TheRow" from the "As TheRow" in "cmd.CommandText = "SELECT TOP 1 ChangeLogID AS TheRow FROM ChangeLog ORDER BY ChangelogID DESC"" and set it to a variable?

The rest of the code works lol. I just can't set a needed variable from the data I am calling :(

please help

I should have googled a little more :(

Ended up using this

Code:

---

        Dim myReader As SqlDataReader
        Dim conn As New System.Data.SqlClient.SqlConnection(DataString)
        conn.Open()
        Dim temp As String
        Try
            Dim sql As String = "SELECT TOP 1 ChangeLogID AS TheRow FROM ChangeLog ORDER BY ChangelogID DESC"
            Dim comm As SqlCommand = New SqlCommand(sql, conn)
            myReader = comm.ExecuteReader
            If myReader.HasRows Then
                While myReader.Read()
                    temp = myReader.Item("TheRow")
                End While
            End If
            myReader.Close()
        Catch ex As Exception

        End Try
        conn.Close()

---

works like a charm. :)

While your code may work, it's not optimal. First, there's no point using HasRows and While. You know for a fact that there can never be more than one record returned because you are specifying it in the query. If you test HasRows and it is True then you know for a fact that there is exactly one row, so what's the loop for? If you use While then that will handle both possible cases, i.e. zero rows and one row, so what's the HasRows check for? Just use one or the other.

That said, you shouldn't be using a data reader at all. The ExecuteScalar method is specifically intended for queries that return a single value, which yours does.

Quote:

---

Originally Posted by jmcilhinney

While your code may work, it's not optimal. First, there's no point using HasRows and While. You know for a fact that there can never be more than one record returned because you are specifying it in the query. If you test HasRows and it is True then you know for a fact that there is exactly one row, so what's the loop for? If you use While then that will handle both possible cases, i.e. zero rows and one row, so what's the HasRows check for? Just use one or the other.

That said, you shouldn't be using a data reader at all. The ExecuteScalar method is specifically intended for queries that return a single value, which yours does.

---

I'm assuming you glossed right over the part where I said I was REALLY new to vb and not one word you said made any sense to me. I'm SURE there are better, more efficient, ways of doing so and since you took the time to waiste a pefectly good answer on me using words that sound like greek to me I would have thought you'd also take the time to leave an example or something so one such as myself would be able to see what you're saying and maybe then some of those big words might have meant something.

Thanks for replying though.