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