[RESOLVED] Procedure or function expects parameter which was not supplied.

[BrailleSchool]

Hi all,

I was working on this thread in regard to DataRelations on multiple tables. If I use CommandType = CommandType.Text, the code does as expected. However, I decided to take the SQL query and throw it into a stored proc. I then changed my code to use the stored procedure and I get the error as per the title of this thread.

The stored procedure is as follows:

CREATE PROCEDURE [dbo].[sp_GetPhoneInfoByName]
@ClientName VARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
 
SELECT ClientInformation.ClientInfoID_PK,
       ClientInformation.ClientName,
       ClientInformation_PhoneNumbers.ClientPhoneNumber,
       ClientInformation_PhoneNumbers.ClientInfoID_PKFK,
       ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK,
       ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK,
       ClientInformation_PhoneNumberType.PhoneNumberTypeName
FROM ClientInformation
INNER JOIN ClientInformation_PhoneNumbers
ON ClientInformation.ClientInfoID_PK = ClientInformation_PhoneNumbers.ClientInfoID_PKFK
INNER JOIN ClientInformation_PhoneNumberType
ON ClientInformation_PhoneNumberType.PhoneNumberTypeID_PK = ClientInformation_PhoneNumbers.PhoneNumberTypeID_PKFK
WHERE ClientInformation.ClientName = @ClientName
END

The stored procedure created without issue. In my code, I have the following:

'Set the attributes for the SQLCommand.
                        With MySQLCommand
                            .Connection = SetDatabaseConnection
                            .CommandType = CommandType.StoredProcedure
                            .CommandText = "sp_GetPhoneInfoByName"
                            .Parameters.AddWithValue("@ClientName", "'" & cboClientName.Text.Trim.ToString & "'")
                        End With

When I use the SQL Profiler, I see that NULL is being passed to the stored procedure. I have looked online and haven't been able to find an answer. Anyone here have any ideas? Thanks.

[kareninstructor]

Could there be any apostrophes embedded in the param value?
Have you tried AddWithValue w/o apostrophes wrapping the value?

[BrailleSchool]

Could there be any apostrophes embedded in the param value?
Have you tried AddWithValue w/o apostrophes wrapping the value?

tried that and also just cboClientName.Text and get the same error.

[kareninstructor]

Have you tried hard coding a value for the param into AddWithValue

.Parameters.AddWithValue("@ClientName", "'SomeValue'")
or 
.Parameters.AddWithValue("@ClientName", "SomeValue")

Do you get the same result?

[BrailleSchool]

Have you tried hard coding a value for the param into AddWithValue

.Parameters.AddWithValue("@ClientName", "'SomeValue'")
or 
.Parameters.AddWithValue("@ClientName", "SomeValue")

Do you get the same result?

Nope, I tried that too and it works if I hard code it. Weird because the ComboBox has a value, so not sure why it's passing NULL to the SPROC!

[BrailleSchool]

i even tried assigning the ComboBox to a string variable and then using the string variable name, still got the error.

[BrailleSchool]

OK, i got it resolved. I closed out VB Pro and then relaunched. Changed the line to:

.Parameters.AddWithValue("@ClientName", cboClientName.Text.Trim.ToString)

and it now works. Strange.

[kareninstructor]

OK, i got it resolved. I closed out VB Pro and then relaunched. Changed the line to:

.Parameters.AddWithValue("@ClientName", cboClientName.Text.Trim.ToString)

and it now works. Strange.

Only strange that you had to close VS, it makes sense that no delimitor was needed.

[BrailleSchool]

Only strange that you had to close VS, it makes sense that no delimitor was needed.

Yep, sure is. VS gets weird on me sometimes.

[kareninstructor]

Yep, sure is. VS gets weird on me sometimes.

Next time something like this happens try "cleaning" the project then "rebuild" instead of closing VS.

[BrailleSchool]

Next time something like this happens try "cleaning" the project then "rebuild" instead of closing VS.

I always clean before rebuild.