Solved

ExecuteScalar Assistance

Posted on 2014-02-03
2
279 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

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.

Question has a verified solution.

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

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…
This article is the result of a quest to better understand Task Scheduler 2.0 and all the newer objects available in vbscript in this version over  the limited options we had scripting in Task Scheduler 1.0.  As I started my journey of knowledge I f…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

810 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