Avatar of wally_davis
wally_davisFlag for United States of America asked on

Calling a SQL SP from VB.NET and it fails on the .ExecutNonQuery method of my routine

I'm calling a SQL SP from VB.NET and it fails on the .ExecutNonQuery method of my routine.
Here's what I'm attempting to do
1. pull one piece of data out from the DB, i.e. from the Table/Column  Update.Status
2. Store the retrieve data value to a local vb.net variable
3. The other two conditions that must be compared, are the Network.Mac and the Uptime.mac (actual mac addresses that are pulled from our Main.Mac Main table/column) columns WHERE Network.Workstation = 'PCNAME'.
4. So, I'm trying to run the Stored Procedure from vb.net and trying to have that Stored Procedure (ADScan_CheckIfWkstnIsDisabled) return or OUTPUT the Update.Status value back to the vb code where it can then be stored in a variable and then have an If....Then condition will verify whether the variable contains the value 'Disabled', if it does, exit the While Loop and do nothing further. If the Value does not = 'Disabled' then update the database Uptime.Status column WHERE Network.Workstation_Name = 'PCNAME'. So, it sounds like I somehow need to get this data output sent back to me or somehow read.
The Error I'm getting is "InvalidOperationException was unhandled. String[1]: the Size property has an invalid size of 0."
I would appreciate someones help. Thanks,
WD
Below is both the SQL Stored Procedure and VB.NET Code:
--- SQL SP Code ---
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
 
ALTER PROCEDURE [dbo].[ADScan_CheckIfWkstnIsDisabled]
  @Workstation_Name varchar(MAX),
 @Status varchar(11) OUTPUT
AS
BEGIN
--  DECLARE @Status varchar(11)
--  Set @Status = 'Disabled'
  SELECT U.Status, N.Workstation_Name
  From Uptime U INNER JOIN Network N ON U.Mac=N.Mac
  WHERE U.Status = @Status And N.Workstation_Name = @Workstation_Name
END
--- VB.NET CODE ---
'Check first to see if the workstation is already disabled. If it is, exit While loop.
                        Dim cmdGetStatus As New SqlClient.SqlCommand("ADScan_CheckIfWkstnIsDisabled", sqlCon)
                        cmdGetStatus.CommandType = CommandType.StoredProcedure
                        'sqlCom.CommandText = "ADScan_CheckIfWkstnIsDisabled"
                        Dim parmStatus As SqlClient.SqlParameter = cmdGetStatus.CreateParameter
                        Dim parmWkstn As SqlClient.SqlParameter = cmdGetStatus.CreateParameter
                        With parmWkstn
                            .ParameterName = "@Workstation_Name"
                            .SqlDbType = SqlDbType.VarChar
                            .Direction = ParameterDirection.Input
                            .Value = host
                        End With
                        With parmStatus
                            .ParameterName = "@Status"
                            .SqlDbType = SqlDbType.VarChar
                            .Direction = ParameterDirection.Output
                        End With
                        cmdGetStatus.Parameters.Add(parmWkstn)
                        cmdGetStatus.Parameters.Add(parmStatus)
                        cmdGetStatus.ExecuteNonQuery()
 
                        Return (parmStatus)

Open in new window

Microsoft SQL Server 2005

Avatar of undefined
Last Comment
wally_davis

8/22/2022 - Mon
Guy Hengel [angelIII / a3]

as the message tells:
  Dim cmdGetStatus As New SqlClient.SqlCommand("ADScan_CheckIfWkstnIsDisabled", sqlCon)
                        cmdGetStatus.CommandType = CommandType.StoredProcedure
                        'sqlCom.CommandText = "ADScan_CheckIfWkstnIsDisabled"
                        Dim parmStatus As SqlClient.SqlParameter = cmdGetStatus.CreateParameter
                        Dim parmWkstn As SqlClient.SqlParameter = cmdGetStatus.CreateParameter
                        With parmWkstn
                            .ParameterName = "@Workstation_Name"
                            .SqlDbType = SqlDbType.VarChar
                            .Direction = ParameterDirection.Input
                            .Size = 8000
                            .Value = host
                         End With
                        With parmStatus
                            .ParameterName = "@Status"
                            .SqlDbType = SqlDbType.VarChar
                            .Size = 11
                            .Direction = ParameterDirection.Output
                        End With
                        cmdGetStatus.Parameters.Add(parmWkstn)
                        cmdGetStatus.Parameters.Add(parmStatus)
                        cmdGetStatus.ExecuteNonQuery()
 
                        Return (parmStatus)

Open in new window

ASKER
wally_davis

I failed to look for the .Size property...simply because I just didn't know the property was there.
Ok, that part is fixed, but, it is not returning the Value I expect from the Uptime.Status column. The value I should see returned is "Disabled" for this particular computer. I get this error when it does a return on the "parmStatus" value --> "Data is Null. This method or property cannot be called on Null values." I verified that the Uptime.Status column actually contained a value for this computer and it does. Do I need to pass a value in the SQL SP? I was expecting the SP to look at the aformentioned table and retrieve/display it. It looks as if the SP is written correctly. Anything else I might be missing?
Guy Hengel [angelIII / a3]

that's the procedure not setting the parameter value, actually:
ALTER PROCEDURE [dbo].[ADScan_CheckIfWkstnIsDisabled]
  @Workstation_Name varchar(MAX),
 @Status varchar(11) OUTPUT
AS
BEGIN
--  DECLARE @Status varchar(11)
--  Set @Status = 'Disabled'
  SELECT @Status  = U.Status 
  From Uptime U INNER JOIN Network N ON U.Mac=N.Mac
  WHERE U.Status = @Status And N.Workstation_Name = @Workstation_Name
END

Open in new window

All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER
wally_davis

Angellll, I uncommented lines 6 and 7 in the SP, still, no value gets returned. Says its Null. Have I missed anything else? Do I need a SqlDataReader to return what I want or should this routine and SP work as it stands?
ALTER PROCEDURE [dbo].[ADScan_CheckIfWkstnIsDisabled]
  @Workstation_Name varchar(MAX),
 @Status varchar(11) OUTPUT
AS
BEGIN
    DECLARE @Status varchar(11)
    Set @Status = 'Disabled'
  SELECT @Status  = U.Status
  From Uptime U INNER JOIN Network N ON U.Mac=N.Mac
  WHERE U.Status = @Status And N.Workstation_Name = @Workstation_Name
END
Keep in mind that this routine didn't have a .Value set. I added and set .SqlValue = Nothing.
With parmStatus
                            .ParameterName = "@Status"
                            .SqlDbType = SqlDbType.VarChar
                            .Size = 11
                            .Direction
End With
Guy Hengel [angelIII / a3]

sorry:
ALTER PROCEDURE [dbo].[ADScan_CheckIfWkstnIsDisabled]
  @Workstation_Name varchar(MAX),
 @Status varchar(11) OUTPUT
AS
BEGIN
--  DECLARE @Status varchar(11)
--  Set @Status = 'Disabled'
  SELECT @Status  = U.Status 
  From Uptime U INNER JOIN Network N ON U.Mac=N.Mac
  WHERE N.Workstation_Name = @Workstation_Name
END

Open in new window

ASKER
wally_davis

That's the SP I originally had and that didn't work.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Guy Hengel [angelIII / a3]

>That's the SP I originally had and that didn't work.
in how far?
ASKER
wally_davis

k, I right clicked on the Exec Stored Procedure and that worked if I plug the values in manually. What I'm trying to figure out is how do you get the SP to return the Value it's supposed to find. i.e. How does the value that's in the specific Column get passed back to the SP that should, in return pass it back to vb.net? I guess that's where I'm getting confused. I'm thinking that if it has two variables created in the SP, one is an INPUT, which would get passed in by vb.net, the second, which is an OUTPUT, would be retrieved by the Select Statement in the SP and then passed back to vb.net. That's how I'm thinking it works but yet the OUTPUT Variable in the SP returns nothing.
Guy Hengel [angelIII / a3]

>k, I right clicked on the Exec Stored Procedure and that worked if I plug the values in manually.
well, what is the matter of "providing" the value...

check out this script:


DECLARE @status VARCHAR(11)
DECLARE @workstation VARCHAR(MAX)
 
SET @workstation = 'some_workstation_name'
 
EXEC [dbo].[ADScan_CheckIfWkstnIsDisabled] @workstation  ,  @status OUTPUT
 
SELECT @workstation, @status

Open in new window

Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
ASKER
wally_davis

I guess what I'm expecting the SP to do is if I give it the workstation_name value,i.e. the INPUT, via vb.net code, that the SP will return (the OUTPUT) the @Status value back to vb.net, based on the Select statement in the SP, without having to directly add either the @Status or the @Workstation_Name values to the SP. Is this or is this not possible? My thinking is that if I provide the @Workstation_Name value directly into the SP, what about all the other workstations I need to feed into my vb.net code to perform this same operation. I would also believe that the SP will find the @Status value and feed that back into vb.net. I'm still new to the vb.net and sql programming game so please bear with me. I hope I'm making sense.  Thanks for your patience Angellll. I really want to get this to work.
Guy Hengel [angelIII / a3]

>without having to directly add either the @Status or the @Workstation_Name values to the SP.
so, you want to get a status AND workstation value back?
but you do want to provide the "host" value (at least, that is what you do in the vb.net code?

now, please explain, in plain english, what the vb.net application should be doing. no sql terms, no vb.net code. just plain english, from the end-user point of view.
ASKER
wally_davis

k, this Module, which will run from a Command Shell process, that will either get initiated by an At job or a Service, whole purpose is to clean up our database. No one will know it's there or is running.
If the workstation doesn't announce itself after 24 hours, one of our SP's will look at Column called Uptime.DaysOffline and increment by 1, each day the PC is offline. Once it gets to 30 days, the Uptime.Status will be set to "Disabled".
Within the code, it will connect to Active Directory, look at the AccountDisabled value and if it equals Disabled we then want to update the database and set the Workstations Uptime.Status column to Disabled and set the Uptime.Days offline column to equal 30 , or 30 days, if it doesn't already. HOWEVER, if the Uptime.Status column is already set to Disabled then it does not need to update the Database and exit While routine. That's a small portion of what it does but is a key part of evaluating Active Directory to see if the Computer exists, Doesn't exist or is Disabled.
If you need further explanation please let me know. :)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
wally_davis

The SP Works like a charm! At least my vb code was not the problem. Nice work Angellll and thank you for your remarkable patience. :)