-
Dec 19th, 2011, 10:41 PM
#1
Thread Starter
Member
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
-
Dec 19th, 2011, 11:00 PM
#2
Thread Starter
Member
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.
-
Dec 20th, 2011, 05:41 AM
#3
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.
-
Dec 20th, 2011, 06:28 AM
#4
Thread Starter
Member
Re: Simple VB/SQL help
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.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|