Solved

VB.Net Select Statement To SQL

Posted on 2014-01-30
8
1,404 Views
Last Modified: 2014-01-31
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

0
Comment
Question by:LeVette
8 Comments
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 100 total points
ID: 39821628
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
 
LVL 1

Expert Comment

by:kevwit
ID: 39821632
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
 

Author Comment

by:LeVette
ID: 39821663
Ok, I can use the execute ExecuteScalar.  How will that look?  Is there some sample code?  Something I can follow.
0
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 
LVL 40
ID: 39821683
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
 

Author Comment

by:LeVette
ID: 39821785
I am new to vb.net.  Looking for insight into how to incorporate the execute Scalar in the sample code I provided.
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 150 total points
ID: 39823885
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
 

Author Comment

by:LeVette
ID: 39824642
Thank you tons, I corrected my errors and that worked.
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question