VB.Net Select Statement To SQL

What is the best way to query SQL Server for a value and pass it to the application?  I am querying SQL for the machine name and I would like to take the value returned from SQL and use it in the code to continue processing.
 Private Sub EnableTarget(ByVal strAction As Action)

        Dim strMessage As String = String.Empty
        Dim strCaption As String = String.Empty
        strConn = "Data Source=" & Target & ";Database=" & master & ";Integrated Security=True"

        database = Target.Text


        con = New SqlConnection(strConn)
        con.Open()


        Dim strQuery, strQuery2, strQuery3 As String

        If strAction = Action.EnableMirroringTarget Then

            Try
                strQuery = "USE master SELECT SERVERPROPERTY ('MachineName') "
                strQuery2 = "ALTER DATABASE " & database & " MachineName"
                
                Dim cmdr As SqlCommand
                Dim cmd As SqlCommand

                cmdr = New SqlCommand(strQuery, con)
                Dim rdr As SqlDataReader = cmdr.ExecuteReader

                If rdr.HasRows Then
                    Do While (rdr.Read())

                    Loop
                    '        MachineName.cmdr.Item("MachineName")

                    '    Loop

                    'Else
                    '    Console.Write("No Record Returned")
                End If
                rdr.Close()


                cmd = New SqlCommand(strQuery2, con)
                cmd.ExecuteNonQuery()

                cmd = New SqlCommand(strQuery3, con)
                cmd.ExecuteNonQuery()

            Catch ex As Exception

Open in new window

LeVetteAsked:
Who is Participating?
 
CodeCruiserConnect With a Mentor Commented:
Here is your modified code


 Private Sub EnableTarget(ByVal strAction As Action)

        Dim strMessage As String = String.Empty
        Dim strCaption As String = String.Empty
        Dim strMachineName As String
        strConn = "Data Source=" & Target & ";Database=" & master & ";Integrated Security=True"

        database = Target.Text


        con = New SqlConnection(strConn)
        con.Open()


        Dim strQuery, strQuery2, strQuery3 As String

        If strAction = Action.EnableMirroringTarget Then

            Try
                strQuery = "USE master SELECT SERVERPROPERTY ('MachineName') "
                strQuery2 = "ALTER DATABASE " & database & " MachineName"
                
                Dim cmdr As SqlCommand
                Dim cmd As SqlCommand

                cmdr = New SqlCommand(strQuery, con)
      
                strMachineName = cmdr.ExecuteScalar()

                cmd = New SqlCommand(strQuery2, con)
                cmd.ExecuteNonQuery()

                cmd = New SqlCommand(strQuery3, con)
                cmd.ExecuteNonQuery()

Open in new window

0
 
Jacques Bourgeois (James Burger)Connect With a Mentor PresidentCommented:
Since you are returning only one value, the DataReader is a little overkill.

ExecuteScalar is a method of the Command object thas has been created expressly for returning only one value, and would thus be a better choice.

cmdr = New SqlCommand(strQuery, con)
MachineName = Cstr(cmdr.ExecuteScalar)
0
 
kevwitCommented:
You might try the .Net forum for this. Also your execution account would need to have at least serveradmin SQL permissions so that you could alter those properties.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
LeVetteAuthor Commented:
Ok, I can use the execute ExecuteScalar.  How will that look?  Is there some sample code?  Something I can follow.
0
 
Jacques Bourgeois (James Burger)PresidentCommented:
I gave you the sample code.

Simply call ExecuteScalar as in my example. I assume that MachineName is a String. Then reuse that String as you need.
0
 
LeVetteAuthor Commented:
I am new to vb.net.  Looking for insight into how to incorporate the execute Scalar in the sample code I provided.
0
 
LeVetteAuthor Commented:
Thank you tons, I corrected my errors and that worked.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.