Solved

ExecuteScalar Assistance

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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 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…

948 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

20 Experts available now in Live!

Get 1:1 Help Now