Assigning SQL query result to a variable in VB.net
I am trying to assign a SQL query result to a variable in VB.Net
Here is the code I am using (without success)
Dim cmd2 As New SqlCommand
Dim SONum As String
cmd2.CommandText = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
SONum = cmd2.CommandText
When I run it I get exactly "SELECT SONum From test.ticket where TicketNum = " & ticket & "" and not the query result. I know why I'm getting the full command, but I want the query result.
Any Ideas how this can be done in VB.NET?
Here is the code I am using (without success)
Dim cmd2 As New SqlCommand
Dim SONum As String
cmd2.CommandText = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
SONum = cmd2.CommandText
When I run it I get exactly "SELECT SONum From test.ticket where TicketNum = " & ticket & "" and not the query result. I know why I'm getting the full command, but I want the query result.
Any Ideas how this can be done in VB.NET?
dont forget to assign your connection string for the second arguement( ie conn)
Here is the basic structure, I got from Microsoft.
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.connection(vs.71).aspx
[Visual Basic]
Public Sub CreateMySqlCommand()
Dim mySelectQuery As String = "SELECT * FROM Categories ORDER BY CategoryID"
Dim myConnectString As String = "Persist Security Info=False;Integrated Security=SSPI;database=nor
Dim myCommand As New SqlCommand(mySelectQuery)
myCommand.Connection = New SqlConnection(myConnectStr
myCommand.CommandTimeout = 15
myCommand.CommandType = CommandType.Text
End Sub 'CreateMySqlCommand
http://msdn2.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.connection(vs.71).aspx
[Visual Basic]
Public Sub CreateMySqlCommand()
Dim mySelectQuery As String = "SELECT * FROM Categories ORDER BY CategoryID"
Dim myConnectString As String = "Persist Security Info=False;Integrated Security=SSPI;database=nor
Dim myCommand As New SqlCommand(mySelectQuery)
myCommand.Connection = New SqlConnection(myConnectStr
myCommand.CommandTimeout = 15
myCommand.CommandType = CommandType.Text
End Sub 'CreateMySqlCommand
ASKER
Ok I understand where the first part goes and to change the conn to my SQLconn, but where does the SqlDataReader rdr = cmd.ExecuteReader() go?
ASKER
OK I think I know what you where talking about. Is this what you mean?
Private Sub ReadOrderData(ByVal connectionString As String)
Dim ticket As String = TxtTicket.Text
Dim queryString As String = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
Using Sqlconn As New SqlConnection(connectionSt
Dim command As New SqlCommand(queryString, Sqlconn)
Dim rdr As SqlDataReader = command.ExecuteReader()
Dim SONum As String
While rdr.Read()
Console.WriteLine(String.F
rdr(0), rdr(1)))
End While
rdr.Close()
End Using
End Sub
OK, know how does this add the query result to a variable?
Private Sub ReadOrderData(ByVal connectionString As String)
Dim ticket As String = TxtTicket.Text
Dim queryString As String = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
Using Sqlconn As New SqlConnection(connectionSt
Dim command As New SqlCommand(queryString, Sqlconn)
Dim rdr As SqlDataReader = command.ExecuteReader()
Dim SONum As String
While rdr.Read()
Console.WriteLine(String.F
rdr(0), rdr(1)))
End While
rdr.Close()
End Using
End Sub
OK, know how does this add the query result to a variable?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks Chris. This is what I ended up doing with your help.
Dim ticket As String = TxtTicket.Text
Dim queryString As String = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
Using SqlConn As New SqlConnection(connectionSt
' Define the command
Dim cmd As New SqlCommand(queryString, SqlConn)
Dim da As New SqlClient.SqlDataAdapter(c
Dim ds As New DataSet
da.Fill(ds, "SONum")
Dim dt As DataTable = ds.Tables("SONum")
Dim dv As DataView = dt.DefaultView
Txttest.DataBindings.Add("
End Using
Dim ticket As String = TxtTicket.Text
Dim queryString As String = "SELECT SONum From foy.ticket where TicketNum = " & ticket & ""
Using SqlConn As New SqlConnection(connectionSt
' Define the command
Dim cmd As New SqlCommand(queryString, SqlConn)
Dim da As New SqlClient.SqlDataAdapter(c
Dim ds As New DataSet
da.Fill(ds, "SONum")
Dim dt As DataTable = ds.Tables("SONum")
Dim dv As DataView = dt.DefaultView
Txttest.DataBindings.Add("
End Using
SqlCommand cmd = new SqlCommand("SELECT SONum From foy.ticket where TicketNum = " & ticket & "", conn)
SqlDataReader rdr = cmd.ExecuteReader()