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

Posted on 2012-08-21
Medium Priority
Last Modified: 2012-08-21
Below is my code.  I'm trying to use a stored procedure with one varchar paramenter in vb.net 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 70

Assisted Solution

by:Éric Moreau
Éric Moreau earned 668 total points
ID: 38316189
have you tried providing length to your @FacDesc parameter and to your parameters?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 668 total points
ID: 38316193
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 60

Expert Comment

by:Julian Hansen
ID: 38316212
What happens if you do this

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

Does it still not return anything?
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Assisted Solution

itmarsolutions earned 664 total points
ID: 38316213
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 60

Expert Comment

by:Julian Hansen
ID: 38316225
What if you do this

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

Open in new window


Author Comment

ID: 38316270
Adding the field length solved it.

Thank you all very much!

Cooper IS

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Screencast - Getting to Know the Pipeline
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…

862 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