Unable to get a result from a stored procedure when using a varchar parameter in

Posted on 2012-08-21
Last Modified: 2012-08-21
Below is my code.  I'm trying to use a stored procedure with one varchar paramenter in using my sql database table.  I'm using a dropdown list for the parameter value.

My table:                  
FacNo    varchar(7)
FacType varchar(10)
FacDesc varchar(15)
GrowNo varchar(6)

Table row I'm retrieving data from:
S12216     2         John Smith       008606

Stored procedure:
ALTER PROCEDURE dbo.RetrieveGrowNo
@FacDesc varchar


select GrowNo
from ProjectTable
Where FacDesc = @FacDesc


Here is my code:
        Dim strGrowNo As String = " "
        Dim FacDesc As String
        FacDesc = DropDownList.SelectedValue

        Dim myconnection As New SqlConnection(ConnectionString)
        Dim GetGrowerNumber As String = "RetrieveGrowNo"
        Dim sqlcmd As New SqlCommand(GetGrowerNumber, myconnection)
        sqlcmd.CommandType = CommandType.StoredProcedure

        sqlcmd.Parameters.Add("@FacDesc", SqlDbType.VarChar)
        sqlcmd.Parameters("@FacDesc").Value = FacDesc

        strGrowNo = sqlcmd.ExecuteScalar()

        label1.Text = strGrowNo

When I run the debuger there is a value in FacDesc.  But no value is returned into strGrowNo.  

I've also tried to use a reader between the open and close connection to return the value, but it doesn't work either.

Dim reader As SqlDataReader
        reader = sqlcmd.ExecuteReader()

        While reader.Read()
            strGrowNo = reader.GetString(reader.GetOrdinal("GrowNo"))
        End While

In my other stored procedures I have the same problem.  Unable to retrieve anything from the database using a varchar parameter.  The only way I can get any stored procedure to retrieve a value is if I change the parameter in my stored procedure to the int data type (for values that could be int like GrowNo).

Any help is appriciated.
Question by:CoopIS
    LVL 69

    Assisted Solution

    by:Éric Moreau
    have you tried providing length to your @FacDesc parameter and to your parameters?
    LVL 142

    Accepted Solution

    please put a size to the parameter:

    ALTER PROCEDURE dbo.RetrieveGrowNo
    @FacDesc varchar(20)

    otherwise it consideres it as varchar(1), and truncates you input
    LVL 50

    Expert Comment

    by:Julian Hansen
    What happens if you do this

    sqlcmd.Parameters("@FacDesc").Value = 'John Smith'

    Does it still not return anything?
    LVL 2

    Assisted Solution

    you have to specify the field length in your stored procedure as same as the table field length

    modify like below

    alter PROCEDURE dbo.RetrieveGrowNo
    @FacDesc varchar(15)

    select GrowNo
    from ProjectTable
    Where FacDesc = @FacDesc

    LVL 50

    Expert Comment

    by:Julian Hansen
    What if you do this

    SqlParameter param = sqlcmd.Parameters.Add("@FacDesc", SqlDbType.VarChar)
    param.Direction = ParameterDirection.ReturnValue

    Open in new window


    Author Comment

    Adding the field length solved it.

    Thank you all very much!

    Cooper IS

    Featured Post

    6 Surprising Benefits of Threat Intelligence

    All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

    Join & Write a Comment

    Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
    Naughty Me. While I was changing the database name from DB1 to DB_PROD1 (yep it's not real database name ^v^), I changed the database name and notified my application fellows that I did it. They turn on the application, and everything is working. A …
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now