troubleshooting Question

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

Avatar of wally_davis
wally_davisFlag for United States of America asked on
Microsoft SQL Server 2005
14 Comments1 Solution333 ViewsLast Modified:
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)
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 14 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 14 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros