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

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:

vb Code:

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:

vb Code:

'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.

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

Quote:

---

Originally Posted by kevininstructor

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.

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

Code:

---

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

---

Do you get the same result?

Quote:

---

Originally Posted by kevininstructor

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

Code:

---

.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!

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

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

vb Code:

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

and it now works. Strange.

Quote:

---

Originally Posted by BrailleSchool

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

vb Code:

.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.

Quote:

---

Originally Posted by kevininstructor

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.

Quote:

---

Originally Posted by BrailleSchool

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.

Quote:

---

Originally Posted by kevininstructor

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

---

I always clean before rebuild.