?
Solved

VB.Net Select Statement To SQL

Posted on 2014-01-30
8
Medium Priority
?
1,539 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 40

Assisted Solution

by:Jacques Bourgeois (James Burger)
Jacques Bourgeois (James Burger) earned 300 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
AWS Certified Solutions Architect - Associate

This course has been developed to provide you with the requisite knowledge to not only pass the AWS CSA certification exam but also gain the hands-on experience required to become a qualified AWS Solutions architect working in a real-world environment.

 
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 450 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

Cloud Training Guides

FREE GUIDES: In-depth and hand-crafted Linux, AWS, OpenStack, DevOps, Azure, and Cloud training guides created by Linux Academy instructors and the community.

Question has a verified solution.

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

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…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

752 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