Wednesday, March 28, 2012

Problem returning data

I have a stored procedure (in SQL Server 2005 Express) that returns a string. The problem is when I call it from my web page I get only the first character of the string. This is my SP:

ALTER PROCEDURE

dbo.usp_CalcDeliveryCharge

@.mintDistance

int,

@.mintCustomer_ID

int= 0,

@.mintRate

int= 0OUTPUT,

@.mstrZone

nchar(10) =null OUTPUT

AS

/* SET NOCOUNT ON */SELECT@.mintRate=RATE,

@.mstrZone=ZONE

FROMtblRatesWHERECustomer_ID=@.mintCustomer_IDANDMile_Range_Min <= @.mintDistanceANDMile_Range_Max >= @.mintDistance

And this is my code:

sql_Command.CommandText =

"usp_CalcDeliveryCharge"

sql_Command.CommandType = CommandType.StoredProcedure

sql_Command.Parameters.Clear()

sql_Command.Parameters.AddWithValue(

"@.mintDistance", intApproxMiles)

sql_Command.Parameters.AddWithValue(

"@.mintCustomer_ID", Profile.CompanyID)

sql_Conn.Open()

sql_Reader = sql_Command.ExecuteReader()

While (sql_Reader.Read())Me.lblZone.Text = sql_Reader.Item(0).ToStringEndWhile

sql_Conn.Close()

sql_Reader.Close()

sql_Command.Dispose()

If you are using OUTPUT parameters you need to add the output parameters in the asp.net code also and set their direction as output and retrieve the values through those parameters not through datareader.
checkthis article if it helps.

No comments:

Post a Comment