Solved

ExecuteScalar Assistance

Posted on 2014-02-03
2
272 Views
Last Modified: 2014-02-04
When executing the below command, the value (Machine Name) isn't being returned and passed into the next command (strQuery2).

Please assist


                strQuery = "SELECT SERVERPROPERTY ('MachineName') "
                'strQuery2 = "ALTER DATABASE " & database & "  & MachineName & "  "
 



                Dim cmdr As SqlCommand
                Dim cmd As SqlCommand


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

                'cmd = New SqlCommand(strQuery2, con)
                'cmd.ExecuteNonQuery()
0
Comment
Question by:LeVette
2 Comments
 
LVL 22

Accepted Solution

by:
Snarf0001 earned 250 total points
ID: 39830385
You're assigning "MachineName" to the string before you've filled it with the query.
Move it underneath the first execute:

strQuery = "SELECT SERVERPROPERTY ('MachineName') "

Dim cmdr As SqlCommand
Dim cmd As SqlCommand


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

'strQuery2 = "ALTER DATABASE " & database & "  & MachineName & "  "
'cmd = New SqlCommand(strQuery2, con)
'cmd.ExecuteNonQuery()
0
 
LVL 16

Assisted Solution

by:Surendra Nath
Surendra Nath earned 250 total points
ID: 39830391
you have to write it as below

            strQuery = "SELECT SERVERPROPERTY ('MachineName') "
 



                Dim cmdr As SqlCommand
                Dim cmd As SqlCommand


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

                strQuery2 = "ALTER DATABASE " & database & "  & MachineName & "  "

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

Open in new window


The reason why your previous code didn't work is, by the time the strQuery2 = assignment stuff executed the MachineName variable didn't have any value.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

708 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now