Error in Calling Stored Procedure

I want to call a stored procedure in ASP.Net 2.0. I've already made sure that the SP contains no error by the Query Analyzer.

However, when I try to run this in the ASP.Net application, error occured.

This error message is {"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."}

Calling the SP via a Class

1Public Function ExecuteStoredProcedure(ByVal NameAs String,ByVal Para()As String,ByVal Value()As String)2If Para.Length <> Value.LengthThen Exit Function3 SqlCmd =New SqlCommand4 SqlCmd.Connection = SqlConn5 SqlCmd.CommandText = Name6 SqlCmd.CommandType = CommandType.StoredProcedure7For iAs Integer = 0To Para.Length - 18'SqlCmd.Parameters.AddWithValue(Para(i), Value(i))9Dim pAs New SqlParameter(Para(i), SqlDbType.NVarChar)10 p.Direction = ParameterDirection.Input11 p.Value = Value(i)12 SqlCmd.Parameters.Add(p)13Next14 SqlCmd.ExecuteNonQuery()15End Function

The Code behind file

1Protected Sub btnSubmit_Click(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles btnSubmit.Click2If txtFromDate.Text =""OrElse txtToDate.Text =""Then3 trMsg.Visible =True4 Exit Sub5 End If67 Dim tsAs New Thread(AddressOf GenerateReport)8 ts.Start()910 MultiView1.ActiveViewIndex = 111 lblConfirmationMessage.Text ="Report generating in progress." & vbNewLine &"You can continue using other functions."12End Sub1314 Protected Sub GenerateReport()15Dim userNameAs String =CType(Session("User"), clsUser).UserName16Dim fromAs String = txtFromDate.Text1718Dim connAs New clsConnector19 conn.OpenConnection()20'conn.Insert("EXEC gen_report '" + txtReportName.Text + "','" + txtDescription.Text + "','" + userName + "','" + txtFromDate.Text + "','" + txtToDate.Text + "';")21 conn.ExecuteStoredProcedure("gen_report", _22New String() {"@.name","@.remark","@.by","@.fromdate","@.todate"}, _23New String() {txtReportName.Text, txtDescription.Text, userName, txtFromDate.Text, txtToDate.Text})24 conn.CloseConnection()25End Sub26

Thanks!

Are u saving Date as string into a column with data type smalldatetime. In your code it seams that you r passing date value . Can u check this one.

|||

try to set timeout to bigger value

SqlCmd =New SqlCommand
4 SqlCmd.Connection = SqlConn
5 SqlCmd.CommandText = Name
sqlcmd.CommandTimeout=300

and maybe it will help, how long it take to create results for this procedure in Query analyzer? your timeout should be a little longer than this time.

|||

The data type is "datetime".

And I use the Now.ToString("yyyy-MM-dd HH:mm:ss") for passing the value.

|||So definitely it won't work. Try to pass it as a date object into the database. Moreover if your conversion of date in different format other than the one defined in db wil create error even if you pass data as dateobject. So try to pass data into DB in the relevent datatype itself|||

Actually, all the input parameter in the SP is nvarchar, but in different size. So, I use String in the code-behind file.

I also tried to set the connection timeout to 300, but the error message is still the same as the original one.

|||

did you tried to run the sp directly in query analyzer by passing the parameters you are passing from UI?

thanks,

satish.

|||

I've just tried to run the SP in the query analyzer and in the UI using the same set of parameters. It works in the query analyzer but not in the UI.

Also, the error is not due to the Thread. Because I tried to run the GenerateReport() without using the Thread and the same error occured.

One more point to mentioned is that I've set the timeout period to 300 seconds in the connection string, however, the UI show me that error message within 1 minute. Is that really timeout?

Thanks

|||

Hello,

Most likely, the Sql Connection problem to me.

Have you tried to debug step by step?

I don't feel good with this line on your function:

SqlCmd.Connection = SqlConn

Why don't you get the all the code from other class into your calling page, and debug it?

Just opinion!

|||

here i write some sample of stored procedure

i think this will help u

Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("Project1connectionString").ToString())
con.Open()

Dim DA As New SqlDataAdapter("select acceptqty,itemid from grndetail where grnid='" & Trim(ggrnid) & "'", con)
Dim ds As New DataSet
DA.Fill(ds)

Dim i As Integer
For i = 0 To ds.Tables(0).Rows.Count - 1 Step 1

Dim cmd1 As New SqlCommand
cmd1.CommandText = "aaceptqtygrn"//name of stored procedure
cmd1.CommandType = CommandType.StoredProcedure
cmd1.Connection = con
cmd1.Parameters.AddWithValue("@.itemId", ds.Tables(0).Rows(i).Item("itemid"))
cmd1.Parameters.AddWithValue("@.acceptqty", ds.Tables(0).Rows(i).Item("acceptqty"))
cmd1.ExecuteNonQuery()
Next

con.Close()

|||

Hi yipych,

I have tried your code and its working for me. I think its the problem with your connection string or some other connection related issues. I have placed the sample code here.

http://www.igold.in/forum/samples/WebVb100.zip

Let me know if it helps you

Error in ActiveX script in DTS

Hi,
When i m trying to execute the following function i m getting
Error Message is Invalid Procedure Call or arguement DTSSource

'************************************************* *********************
' Visual Basic Transformation Script
'************************************************* ***********************

' Copy each source column to the destination column
Function Main()
DTSDestination("acct_id") = DTSSource("Account_ID")
DTSDestination("acct_nm") = DTSSource("Account_Name")
DTSDestination("acct_type") = 1
DTSDestination("acct_sts") = DTSSource("Enabled")

Main = DTSTransformStat_OK
End Function

thnks in advanceis DTSDestination("acct_type") of type integer,numeric or decimal?

if not, I think you have to add double quotes around the "1". Hope it helps :)

Error handling problem

I am running SQL 2000.
I have several procedures where I do general validation on entered values.
User procedures typically call these "checkprocedures" to verify all entered
values.
I thought I grasped the idea of error handling but must have done something
wrong.
When a wrong value is found I throw an exception in the checking procedure
and return an arbitrary return code other than 0, typically 1.
In the calling procedure I check the @.@.ERROR value and then take action
based on that. The problem is that excution does not stop after the exceptio
n
is thrown even if I explicitly end with a Return in the calling procedure.
What is wrong?
Why does not the execution in the main procedure stop?
Maybe it is overkill to use both exceptions and return values:
Should I skip the exception throwing and rely on the Return values?
Should I skip the Return values and rely on the exception throwing?
My idea was to avoid defining the validation and exception messages on many
places.
Code example enclosed below:
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
GO
Calling procedure contains this code:
....
-- Make sure the given language key is valid
EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
SET @.ErrorNumber = @.@.ERROR
IF @.ErrorNumber <> 0
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...It seems it is the RETURN statement after the RAISERROR that resets the
@.@.ERROR to 0 again, making the calling procedure ....
What would you recommend?
Should I use RAISERROR or RETURN in the check procedure to inform the
calling procedure about the error?
I tend to the RAISERROR ....|||Since the procedure executes OK even if @.LanguageID is NULL, @.@.ERROR in your
calling statement will always be 0. @.@.ERROR traps the error number for the
most recently executed statement.
You need something like this
CREATE PROCEDURE dbo.CheckLanguage
@.LanguageKey char(5),
@.LanguageID int OUT
AS
SET @.LanguageID = NULL
-- Check if given language exists
SELECT @.LanguageID = LanguageID
FROM Language
WHERE LanguageKey = @.LanguageKey
IF @.LanguageID IS NULL
BEGIN
RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
16, 1, @.LanguageKey)
RETURN 1
END
Calling procedure contains this code:
....
-- Make sure the given language key is valid
DECLARE @.return_status int
EXEC @.return_status = CheckLanguage @.LanguageKey, @.LanguageID OUT
IF @.return_status = 1
BEGIN
SET @.Response = 'Error_LanguageKey'
RETURN 0
END
...
"Jakob Lithner" wrote:

> I am running SQL 2000.
> I have several procedures where I do general validation on entered values.
> User procedures typically call these "checkprocedures" to verify all enter
ed
> values.
> I thought I grasped the idea of error handling but must have done somethin
g
> wrong.
> When a wrong value is found I throw an exception in the checking procedure
> and return an arbitrary return code other than 0, typically 1.
> In the calling procedure I check the @.@.ERROR value and then take action
> based on that. The problem is that excution does not stop after the except
ion
> is thrown even if I explicitly end with a Return in the calling procedure.
> What is wrong?
> Why does not the execution in the main procedure stop?
> Maybe it is overkill to use both exceptions and return values:
> Should I skip the exception throwing and rely on the Return values?
> Should I skip the Return values and rely on the exception throwing?
> My idea was to avoid defining the validation and exception messages on man
y
> places.
>
> Code example enclosed below:
> CREATE PROCEDURE dbo.CheckLanguage
> @.LanguageKey char(5),
> @.LanguageID int OUT
> AS
> SET @.LanguageID = NULL
> -- Check if given language exists
> SELECT @.LanguageID = LanguageID
> FROM Language
> WHERE LanguageKey = @.LanguageKey
>
> IF @.LanguageID IS NULL
> BEGIN
> RAISERROR ('This LanguageKey is not recognized as a valid language: %s',
> 16, 1, @.LanguageKey)
> RETURN 1
> END
> GO
>
> Calling procedure contains this code:
> ....
> -- Make sure the given language key is valid
> EXEC CheckLanguage @.LanguageKey, @.LanguageID OUT
> SET @.ErrorNumber = @.@.ERROR
> IF @.ErrorNumber <> 0
> BEGIN
> SET @.Response = 'Error_LanguageKey'
> RETURN 0
> END
> ...|||Thanks, I guess that is the best solution!