Results 1 to 4 of 4

Thread: Simple VB/SQL help

  1. #1

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    51

    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

  2. #2

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    51

    Re: Simple VB/SQL 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.

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    Re: Simple VB/SQL help

    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.

  4. #4

    Thread Starter
    Member
    Join Date
    Jul 2011
    Posts
    51

    Re: Simple VB/SQL help

    Quote Originally Posted by jmcilhinney View Post
    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width