Re: Data Adapter passing by reference to stored procedure - C# Discussion Boards

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 9:07

My title probably doesn't quite explain my problem. I found some code for a fully editable grid. It was pointing to the Northwind database, pulling employee information. Okay, no problem. So now I am trying to update the code to fit what I need and I'm getting an error that I can't seem to fix.

Here is the ORIGINAL BindData code:

private void BindData()<br />
		{<br />
			 ds = new DataSet();<br />
			ds= SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetEmployees", null);<br />
			Session["ds"]=ds;<br />
			dt = ds.Tables[0];<br />
			Session["dt"]=dt;<br />
			DataGrid1.DataSource=dt;<br />
			DataGrid1.DataBind();<br />
			<br />
		}

This code uses a Microsoft SQLHelper.cs class.

Okay, so I created a stored proc called GetPartInfo. Basically there is a text box the user enters a part number, clicks a button and then the grid populates. Based on help from a couple of expert members from CodeProject this what I used to have...

private void BindData2()<br />
		{<br />
			strPartNumberInputReference = txtPartNumberInput.Text;<br />
			string SQLString = "SELECT c.cost, ct.Description AS ctDescription, p.PartNumber, pt.description, dd.DrawingNumber, dd.DrawingRevision, dd.DwgPath FROM costs c INNER JOIN Parts p ON c.PartID = p.Id INNER JOIN PartTypes pt on pt.ID = p.PartTypeID LEFT JOIN DraftingData dd on dd.PartID = p.ID LEFT JOIN CostTypes ct on ct.Id = c.CostTypeId WHERE p.PartNumber = @PartID"; <br />
			<br />
			SqlCommand cmd = new SqlCommand();<br />
			cmd.Connection = new SqlConnection(strConnectSQL);<br />
			cmd.CommandText = SQLString;<br />
			cmd.Parameters.Add ("@PartID", strPartNumberInputReference);<br />
			SqlDataAdapter adapter = new SqlDataAdapter(cmd);<br />
			adapter.Fill(ds);<br />
<br />
			<br />
			dgParts2.DataSource = ds;<br />
			dgParts2.DataBind();<br />
			txtPartNumberInput.Text = "";<br />
			<br />
		<br />
		}

As you can see I am passing @PartID.

So in my stored procedure I have...

CREATE PROCEDURE [dbo].[GetPartInfo]<br />
@PartID int AS<br />
SELECT c.cost, ct.Description AS ctDescription, p.PartNumber, pt.description, dd.DrawingNumber, dd.DrawingRevision, dd.DwgPath<br />
FROM costs c INNER JOIN Parts p ON c.PartID = p.Id <br />
INNER JOIN PartTypes pt on pt.ID = p.PartTypeID <br />
LEFT JOIN DraftingData dd on dd.PartID = p.ID LEFT JOIN CostTypes ct on ct.Id = c.CostTypeId <br />
WHERE p.PartNumber = @PartID;<br />
GO

Still trying to pass @PartID.

When I tried changing the line...
ds= SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetEmployees", null);to

ds= SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetPartInfo", ("@PartID"));

I get the error that my Input string was not in correct format.
Here is the code for ExecuteDataset:

public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)<br />
		{<br />
			//create & open a SqlConnection, and dispose of it after we are done.<br />
			using (SqlConnection cn = new SqlConnection(connectionString))<br />
			{<br />
				cn.Open();<br />
<br />
				//call the overload that takes a connection in place of the connection string<br />
				return ExecuteDataset(cn, commandType, commandText, commandParameters);<br />
			}<br />
		}

I've tried a bunch of different things and I still get the error. How do I fix this?

Thanks again for all those who help me on this board. Sucks being a newbie.

Not Active21-Jun-06 9:25

Not Active

21-Jun-06 9:25

I don't see any place where you are setting the actual value for @PartID. I'm not familar with the SqlHelper class but it seems as though you should be passing the value for PartID rather than the name of the paramater name

ds= SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetPartInfo", ( txtPartNumberInput.Text));

Re: Data Adapter passing by reference to stored procedure [modified]

leckey21-Jun-06 9:28

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 9:28

Originally this is how I set @PartID

private void BindData2()<br />
		{<br />
			strPartNumberInputReference = txtPartNumberInput.Text;<br />
			string SQLString = "SELECT c.cost, ct.Description AS ctDescription, p.PartNumber, pt.description, dd.DrawingNumber, dd.DrawingRevision, dd.DwgPath FROM costs c INNER JOIN Parts p ON c.PartID = p.Id INNER JOIN PartTypes pt on pt.ID = p.PartTypeID LEFT JOIN DraftingData dd on dd.PartID = p.ID LEFT JOIN CostTypes ct on ct.Id = c.CostTypeId WHERE p.PartNumber = @PartID"; <br />
			<br />
			SqlCommand cmd = new SqlCommand();<br />
			cmd.Connection = new SqlConnection(strConnectSQL);<br />
			cmd.CommandText = SQLString;<br />
			cmd.Parameters.Add ("@PartID", strPartNumberInputReference);<br />
			SqlDataAdapter adapter = new SqlDataAdapter(cmd);<br />
			adapter.Fill(ds);<br />
<br />
			<br />
			dgParts2.DataSource = ds;<br />
			dgParts2.DataBind();<br />
			txtPartNumberInput.Text = "";<br />
			<br />
		<br />
		}

I was taking the text from the textbox, and passing it that way. BTW, tried what you wrote but still got the same error.

Oh, the SQLHelper class gave this in the comments:

/// e.g.:  <br />
		///  DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24));

So I tried that changing my code to:
DataSet ds=ExecuteDataset (connString, CommandType.StoredProcedure, "GetPartID", new SqlParameter ("@PartID"));
But SqlParameter isn't recognized.

-- modified at 15:29 Wednesday 21st June, 2006

ashishinfra21-Jun-06 20:07

ashishinfra

21-Jun-06 20:07

I suspect that this line should be modified

cmd.Parameters.Add ("@PartID", strPartNumberInputReference);

cmd.Parameters.Add ("@PartID",sqldbtype.Varchar);
cmd.Parameters["@PartID"].Value = strPartNumberInputReference;

Please tell if that helped

Re: Data Adapter passing by reference to stored procedure

Josh Smith21-Jun-06 9:43

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 9:43

The saga continues. Smile | :)

Your post is a bit misleading. Your call to ExecuteDataset looks like this:

ds= SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetPartInfo", "@PartID");

But the method signature looks like this:

public static DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters)

You are not calling the method whose signature you displayed. Notice that you are not passing a CommandType value. If you were to pass CommandType.StoredProcedure, that might help. But, a more immediate error/oversight is that you are not actually passing SqlParameter objects! Your "@PartID" argument is a string, not an SqlParameter object. You need to create a new SqlParameter and pass it the "@PartID" value as it's name. Pass that object into the ExecuteDataset method.

Josh

leckey21-Jun-06 9:47

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 9:47

Josh, thank you so much for you ongoing (!) help. I've not worked with SQLParameters before. Do you know of a good article or can you give me another push?

Josh Smith21-Jun-06 10:06

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 10:06

What is it that you need to know? You can create one like this:

SqlParameter param = new SqlParameter( "@PartID", partIDValue );

and then just pass it into a method like any other variable.

Josh

leckey21-Jun-06 10:17

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 10:17

Then why does it still hate this?

private void BindData()<br />
		{<br />
			strPartNumberInputReference = txtSearchPart.Text;<br />
			ds = new DataSet();<br />
			SqlParameter param = new SqlParameter("@PartID", strPartNumberInputReference);		<br />
			ds=SqlHelper.ExecuteDataset(this.connectionString, "dbo.GetPartInfo", "@PartID");<br />
			Session["ds"]=ds;<br />
			dt = ds.Tables[0];<br />
			Session["dt"]=dt;<br />
			dgParts.DataSource=dt;<br />
			dgParts.DataBind();<br />
			txtSearchPart.Text = "";<br />
			<br />
		}

Josh Smith21-Jun-06 10:25

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 10:25

leckey wrote:
Then why does it still hate this?

Because it is mean and it's father used to beat it with a baseball bat.

You aren't passing the 'param' object into the ExecuteDataset method. Creating it is one thing, using it another.

Cheers & beers,
Josh

leckey21-Jun-06 10:39

Re: Data Adapter passing by reference to stored procedure

21-Jun-06 10:39

Thanks again! A new problem has arisen. I get "Object must implement IConvertible." Trying to research this so I can stop bugging CodeProject...In my stored procedure I have
@PartID varchar

Is this the issue?

Josh Smith21-Jun-06 10:44

21-Jun-06 10:44

The problem is not in the stored procedure. SPs have no concept of IConvertible, that's a .NET thing. Turn on your debugger and see where the exception is thrown. It is impossible to figure out what the issue is based on just that error message alone.

Josh

Paint Event Handler

ZeAugusto21-Jun-06 7:23

ZeAugusto

21-Jun-06 7:23

Hi,

I'm trying to create a custom control to draw a graph in a canvas in real-time, similarly to an osciloscope. I have a main control loop that performs some calculations and updates the current point coordinates (x and y, with x corresponding to the time). I used to use Flash, but due to performance issues I decided to try out using C# (waht means I'm new to C#).

I'm already able to draw lines on my control, but I'm not able to update the canvas whenever a new point is calculated. The problem is that I don't know how to trigger the paint event. I've already registered it, but I don't know how to call it after the point is calculated.

I hope I made myself clear...

Thanks in advance,

Ze Augusto

Josh Smith21-Jun-06 7:25

21-Jun-06 7:25

Call Invalidate() on the control. That will post a request for a repaint, but if you really need an immediate repaint you should call Update() after calling Invalidate(). Note, you can also call Refresh(), which basically calls those two methods for you.

Re: Paint Event Handler [modified]

ZeAugusto21-Jun-06 7:42

ZeAugusto

21-Jun-06 7:42

First, thanks for your help. I did what you suggested, and it did solve the repaint issue. Now I can see the lines been updated.

Now, the problem is that the screen is getting erased before the new point is drawn (as it would be expected from your description), but I need to keep the points already drawn and add the new point to the end of the line, forming a continuous curve. Then, when that line reaches the end (right border) of the canvas, I should send the carrier back to the begining (left border) and continue drawing, with a blank region in front of it, as if there was an eraser in front of the current point.

Thanks again!

led mike21-Jun-06 8:01

led mike

21-Jun-06 8:01

ZeAugusto wrote:
as if there was an eraser in front of the current point.

No erasing but the graphics system does not remember what you previously drew, you have to do that and draw everything in the window (or invalid region) during a paint event.

Last modified: Wednesday, June 21, 2006 12:58:57 PM --

Josh Smith21-Jun-06 8:06

21-Jun-06 8:06

Rather than invalidating the control, call yourCustomControl.CreateGraphics() to get a Graphics object which will render onto your control surface. Then perform the painting in a separate method (not the Paint event handler). That new method will draw only the new point(s) and not erase the other ones. You'll probably want to have your Paint event handler call into this new method so that the rendering logic is factored properly.

Josh

Jun Du21-Jun-06 9:05

Jun Du

21-Jun-06 9:05

I think a proper way is using double-buffering. You maintain a graphical buffer and you draw to the buffer. Whenever you want to update the screen, copy the buffer to the screen. There are two approaches to the buffer drawing:
1) If you just add (not erase, not change) contents to the screen, grab the graphic object and draw to the buffer on the top of existing contents.
2) If you erase or change any existing contents on the screen, you need to repaint the buffer's background and then redraw it completely.

I've assumed you have fair understanding about how Windows graphics work. If you need more guidance, drop a line.

Best,
Jun

ZeAugusto21-Jun-06 12:05

ZeAugusto

21-Jun-06 12:05

Thanks for all the replies!

As I didn't know how to add content without refreshing the whole object, I ended up implementing a mixture of your suggestion with Josh's and Led's ones. I used the GraphicsPath class instead of just drawing single line segments, adding the newly calculated points to it, and then redrew the entire path at every Refresh() call (which happens whenever I add a point to the graph, every 40 ms). At the end of the line, I clone this path to a second one, and reset the first. This way, I work with two paths, the most recent one "above" the oldest, and redraw both when a new point is added.

To solve that "erasing" part I mentioned before, I used a little trick. I draw a black rectangle (because the background of the graph region is also black) between the two paths, and resize it so that it is always a few pixels wider than the top path. This way, it looks like there is an eraser in front of the line being drawn. It's quite similar to what I've already done in Flash.

But I have a new question, though. As far as I draw all these objects every 40 miliseconds, I wonder if I might experience some performance issues. The application is intended to support an average of 5 to 10 simutaneous instances of this control, so that might become a problem... Or am I just being paranoid because of my Flash experience?

Thanks,
Ze

Jun Du22-Jun-06 4:27

Jun Du

22-Jun-06 4:27

ZeAugusto wrote:
I wonder if I might experience some performance issues.

Can't say for sure. Keep your fingers crossed.

ZeAugusto wrote:
The application is intended to support an average of 5 to 10 simutaneous instances of this control, so that might become a problem...

It also depends on how large (portion of screen taken) your control is.

Best,
Jun