Solved

ExecuteScalar Assistance

Posted on 2014-02-03
2
296 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
[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
2 Comments
 
LVL 23

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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
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 utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month6 days, 16 hours left to enroll

623 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