SQL Parameter Added to Command, but Watch on Command States "Paramete' is not a member "

Bob
Bob used Ask the Experts™
on
A SQL SP returns the message ex.Message = "Conversion from type 'DBNull' to type 'String' is not valid." Turns out that putting a watch on cmd.Parameters the watch states error BC30456: 'Paramete' is not a member of 'SqlCommand'.      Cannot reason why this is so. The parameter is created and added to command.parameters. If a string literal is used it works. Thank you for any help.

 Dim cmd As New SqlCommand("[HTS].[TaskCompleted_SELECT_CompletedTasks_02_USES HortDoc_ID]") With {
                .CommandTimeout = 60,
                .CommandType = Data.CommandType.StoredProcedure
            }

             Dim HortDoc_ID = New SqlParameter With {
                .Direction = ParameterDirection.Input,
                .DbType = DbType.String,
                .Size = 12,
                .ParameterName = "@HortDoc_ID",
                .Value = g_HortDoc_ID.ToString,  'doesn't work
                .Value = "28" 'works
            }            

cmd.Parameters.Add(HortDoc_ID)

            Dim conn As New SqlConnection(mConnectionStrings.ConnHortDB)
            conn.Open()
            cmd.Connection = conn

            Dim str As String
            str = cmd.ExecuteScalar
            Return str
            conn.Close()

Open in new window


ALTER PROCEDURE [HTS].[TaskCompleted_SELECT_CompletedTasks_02_USES HortDoc_ID]
(
	@HortDoc_ID	as varchar(12)
)
AS

declare @objcursor as cursor 
 
declare 
    @vsql       as nvarchar(max)
    ,@vquery    as nvarchar(max)
    ,@id        as int
    ,@value     as varchar(50)
    
DECLARE @list varchar(1000)
DECLARE @listTemp varchar(100)
DECLARE @item varchar(300)

set @vquery = 'SELECT DISTINCT Task_CD from HTS.TaskCompleted WHERE HortDoc_ID = ' +  @HortDoc_ID
.
.
.

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Chinmay PatelChief Technology Ninja
Distinguished Expert 2018

Commented:
Somewhere in your codebase, instead of Parameter, someone has used Parameter. Also it was not caught during the compile time so either it is on reflection OR with a dynamic variable. Did you see anything when you do a quick search on your codebase or in your Data Access Layer?

Commented:
I would say that the field g_HortDoc_ID is null and you are getting the error there. It not handling the conversion of a null id to string.
larryhSr. Software Engineer

Commented:
I agree with @twol.  In your code I don't see g_HortDoc_ID being declared or initialized to a value.  Perhaps it's declared elsewhere in your code but perhaps you never assigned a value to it.
Seems g_HortDoc_ID was initialized as DBNull, so g_HortDoc_ID.ToString is not valid. You can check for DBNull first:
Dim s = If(IsDBNull(g_HortDoc_ID), "", g_HortDoc_ID.ToString)
.Value = s

Open in new window

or use default parameter properties to leave checking for .Net:
 Dim cmd As New SqlCommand("[HTS].[TaskCompleted_SELECT_CompletedTasks_02_USES HortDoc_ID]") With {
                .CommandTimeout = 60,
                .CommandType = Data.CommandType.StoredProcedure
            }
cmd.Parameters.AddWithValue("@HortDoc_ID",g_HortDoc_ID)

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial