Procedure Calling Problem

Hi,

I'm trying to do what may seem simple to most.

I have a procedure in my database that takes two parameters and returns results. I have a webpage that has two textboxes and a submit button. When the button is clicked the onClick even takes the two values, passes them as parameters to the procedure and binds the resutls from the procedure to a gridview. (At least that's what it's doing in my head, but not in real life ;-(...).

I've looked over countless examples online. All the ones that I've found either use one parameter or print the results to console. None do what I want it to do.

Can someone either show me some c# code on how to do this or forward me to the right tutorial/thread.

Thanks.

Answer this question

Procedure Calling Problem

Sharon.Danino

Hi,

Here is my code behind:
Gunnar Adler

Put your actual code here and how is the DB procedure (parameters type for example).

Regards.
corbin
I use different way of defining parameters for stored procedure, so for your case will be like:
command.Parameters.Add("@searchString", SqlDbType.NVarChar, 50).Value = "Saltspring Whale Tours";
command.Parameters.Add("@category", SqlDbType.VarChar, 50).Value = "Tour Operator";
If you need to fill DataTable you should use SqlDataAdapter instead of SqlDataReader.

DataTable dt = new DataTable();
SqlDataAdapter DA = new SqlDataAdapter(command);
DA.Fill(dt);

GridView1.DataSource = dt;
GridView1.DataBind();

You can check what is executed on your database using SQL Server Profiler.
There are other things that you should change, like put execution of sql commands in try catch blok, put connection close execution in finally blok, implement good sql exceptions handler, ....
etcheverrjc
Hi Shamdogg

Do you get any exceptions when you run your code Maybe you could add some exception handling as suggested by boban.s to determine what the exceptions are (if any). The code you have posted would appear to be fine. I would assume that you would get the same results using both mine and boban.s suggestions.

Also, you can change the code that I posted to use an SqlDataAdapter but I don't believe this will resolve any issues as from my understanding, the SqlDataAdapter uses the SelectCommand's ExecuteReader internally to fetch the data, so using either an SqlDataAdapter or an SqlDataReader to populate a DataTable will make no difference.

HTH
davidtcf
Hi

The following example gives you the basic outlines of what your procedure might look like to execute a stored procedure with two parameters:

        SqlConnection connection = new SqlConnection("Your connection string");

        SqlCommand command = new SqlCommand("Your procedure name", connection);

        command.CommandType = CommandType.StoredProcedure;

        //Add the parameters to the command's parameters collection

        command.Parameters.Add(new SqlParameter("Your parameter name", "Your parameter value"));

        command.Parameters.Add(new SqlParameter("Your parameter name", "Your parameter value"));

        //Open the connection to the database and retrieve the results

        connection.Open();

        SqlDataReader dr = command.ExecuteReader(CommandBehavior.CloseConnection);

        //Load the data reader into a DataTable

        DataTable dt = new DataTable("YourTableName");

        dt.Load(dr);

        dr.Close();

        //Display the contents of the DataTable in a grid

        GridView1.DataSource = dt;

        GridView1.DataBind();

HTH
SShor
Thanks for all your input guys. I didn't do much to do with catching exceptions because I was just doing this as a quick mockup. Well I thought it would be quick but then I ran into problems.

I'm working with a team of guys on a project. My job is to design and build the database and all its procedures. All I wanted to do was do a quick mock up of the interface to test some of the functionality without just using SQL Management Studio. We're using Java to do all of our programming logic; this part of the project isn't done yet so I thought I could do something quickly in Visual Studio as a temporary test.

I would have rather done this in C# and not Java but it wasn't my call ;-) . I'll try what you sugguested in a little while. I don't have time right now, but again thanks for all the input.
Procedure Calling Problem

Answer this question