Last post Nov 04, 2011 06:27 AM by bbcompent1
Nov 03, 2011 10:15 AM|TRINAKRIAE|LINK
I have a GridView that uses a SQLDataSource as data provider. The SelectCommand in the SQLDataSource is parametrized (meaning it has variable such as @product).
I would like to debug the query in order to see if the parameters are replaced by the values as I expect. If I check from the c# code the
SqlDataSource.SelectCommand property it gives me the query string not parametrized yet.
The next instruction is already the DataBind which fills the GridView. Anybody might help? Thanks
Nov 03, 2011 10:28 AM|bbcompent1|LINK
Ok, well depending on what the parameter is, you can actually use code behind to debug. Add a label control to your page and in the code behind, add a code block that assigns the label's text the value of the parameter you are trying to pass into SQL. You
set a breakpoint at that line and then F11 to see the value. It will also response to the screen what your variable's value is.
If its a session variable, you can accomplish it this way:
Label1.Text = Session["Yourvar"]
Post a reply if you need more help.
Nov 03, 2011 10:41 AM|TRINAKRIAE|LINK
Dear ddcompent1, thanks for your answer. The parameter is already taken from a web control. I use
Response.Write and Response.End to check the query specified on the
SelectCommand. I also tried:
protected void AvailabilitySQLDataSource_Selected(object sender, SqlDataSourceStatusEventArgs e)
but what I got is the querystring not parametrized.
For more details see my other
Nov 03, 2011 10:54 AM|bbcompent1|LINK
Ok, so this is coming from a querystring. If you look at the address in your web browser, does it have a querystring specified? Are you identifying the name of the querystring? Are you specifying the querystring name? In the case below, the querystring name
would be id
Nov 03, 2011 10:57 AM|TRINAKRIAE|LINK
sorry there was a misunderstanding. As querystring I meant the string specified in the SelectCommand not the request querystring.
Nov 03, 2011 11:00 AM|bbcompent1|LINK
Alright, please post all your code from that page so I can see the whole thing...
Nov 03, 2011 11:10 AM|TRINAKRIAE|LINK
<asp:DropDownList runat="server" ID="ddCategory" appenddatabounditems="True" autopostback="True"
<asp:Listitem Text="All Categories" Value="None"></asp:Listitem>
<asp:ListItem Text="Men" Value="Men"></asp:ListItem>
<asp:ListItem Text="Women" Value="Women"></asp:ListItem>
</asp:DropDownList><br /><br />
<b>Serial No:</b> <asp:TextBox ID="txtSerialNo" runat="server" Text=""></asp:TextBox>
<asp:GridView ID="gvCheckAvailability" runat="server" AllowSorting="True"
<asp:BoundField DataField="ROWID" HeaderText="ROWID" ReadOnly="True" SortExpression="ROWID" />
<asp:BoundField DataField="SRNUM" HeaderText="Serial Nr." SortExpression="SRNUM" />
<asp:BoundField DataField="NAME" HeaderText="Product Name" SortExpression="NAME" />
<asp:BoundField DataField="FUNCT" HeaderText="Function" SortExpression="FUNCT" />
<asp:BoundField DataField="LOC" HeaderText="Location" SortExpression="LOC" />
<asp:BoundField DataField="STAGE" HeaderText="Stage" SortExpression="STAGE" />
<asp:BoundField DataField="CATEGORY" HeaderText="Category" SortExpression="CATEGORY" />
<asp:SqlDataSource ID="AvailabilitySQLDataSource" runat="server"
ConnectionString="<%$ ConnectionStrings:prodConnectionString %>"
SelectCommand="select c.*, (select top 1 a.status from reservation a, product b where a.prodid = b.rowid and a.prodid=c.rowid) as stockstatus, year(getdate()) as [YEAR] from product as c
where ([CATEGORY] = CASE WHEN @Category = 'None' THEN [CATEGORY] ELSE @Category END) and
(c.[SRNUM] = CASE WHEN @SerialNo = '' THEN c.[SRNUM] ELSE @SerialNo END)
order by c.name">
<asp:controlparameter controlid="ddCategory" name="Category" propertyname="SelectedValue" type="string" />
<asp:ControlParameter ControlID="txtSerialNo" Name="SerialNo" propertyname="Text" DefaultValue=""/>
Nov 03, 2011 11:19 AM|bbcompent1|LINK
<asp:TextBox ID="txtSerialNo" runat="server" Text=""></asp:TextBox>
I can see htis code working, however when you enter stuff into the text box, it doesn't do a postback. For that system you've coded to work, you'd need to enter the Serial # and then select the dropdown category
Nov 03, 2011 11:21 AM|bbcompent1|LINK
So in your text box, you may want to add an onchange event to call a databind.
Nov 03, 2011 11:28 AM|TRINAKRIAE|LINK
I already used Text = "" and it does not work. I use a button to call the DataBind method of the GridView. I am really stressed it is since this morning I am trying to make it work. The starnge thing is that if I remove the textbox from the parameters and
selectcommand, leaving just the dropdown it works perfectly.
Is there any way to "open" the SQLDataSource and "see" what it sends to the SQL Server? Thanks
Nov 03, 2011 11:35 AM|bbcompent1|LINK
Setting the text value to blank will give you a null result. Leave the variable out. You can use code behind to check the value of that text box but you have to have some event fired to get the page to do something like this (Set a breakpoint on the line
protected void TextBox1_TextChanged(object sender, EventArgs e)
//Do a data rebind of your SQLDataSource
string myvar = TextBox1.Text.ToString();
Nov 03, 2011 12:01 PM|TRINAKRIAE|LINK
I have to use the button because I have four more TextBoxes that act as filter. The users do not like to have the GridView refreshed everytime they change a textbox.
The only solution is to check the final query submitted to the DB. Any way to do it? thanks
Nov 03, 2011 12:15 PM|bbcompent1|LINK
Oh, you are using a button? Ok, because essentially when you click the button, it should have a click event which should bind the datasource. I thought you were basing it around the dropdown box, not a submit button. I would say if in the code behind you
have a button1_click event, I would do my databind there in the code behind.
IMHO, usually I do my query code in code behind because it is much easier because then if you do need to debug, you can see what values are passed to the query by setting breakpoints.
Nov 03, 2011 12:17 PM|bbcompent1|LINK
<asp:DropDownList runat="server" ID="ddCategory" appenddatabounditems="True" autopostback="True" datatextfield="CategoryName" datavaluefield="Category" onselectedindexchanged="ddCategory_SelectedIndexChanged">
At what point in the page does this dropdownlist come into play? Do you have a default value selected for the dropdown list? I would make one change to it where <asp:Listitem Text="All Categories" Value="None"></asp:Listitem> should be more like
this: <asp:Listitem Text="All Categories" Value="%" Selected></asp:Listitem>
Nov 03, 2011 01:31 PM|TRINAKRIAE|LINK
I also put the DataBind inside the onclick event handler. I guess i am gonna rewrite the query in the code and I will not use the selectcommand provided by the SQLDataSource. I am an MVC programmer so imagine how frustrating it is not to have full control
of my application.
Is it possible to use the SQLDataSource and define the SelectCommand just in the code? Thanks
Nov 03, 2011 01:37 PM|bbcompent1|LINK
I'd recommend using Code Behind SQL Connections. Not only is it cleaner and easier to maintain, it also separates the visual elements from Business Logic. Also, it lets you step through each line of code if you need to debug. You could use a reader control
since you're only reading the data. Only time you need to use a regular data source object is if you are deleting, updating, inserting.
Nov 04, 2011 05:25 AM|TRINAKRIAE|LINK
Many thanks! Last question. If I want to add the SelectCommand programmatically, in case the page is loaded for the firstime (no postback) in which event handler shall I place the statements:
MySqlDataSource.SelectCommand = myquery;
Nov 04, 2011 06:05 AM|bbcompent1|LINK
I would put that into the page load and when you open the page the first time it will do the databind.
Nov 04, 2011 06:24 AM|TRINAKRIAE|LINK
I found the problem after experimenting with some attrubutes. The attribute
CancelSelectOnNullParameter of the SQLDataSource is true for default!!!
That means if one of the textboes is empty the paramter is null and the select is not performed!!! It is really annoying that this attribute is set to true by default, people get crazy!!!
Just set CancelSelectOnNullParameter to false and the page works perfectly!
Nov 04, 2011 06:27 AM|bbcompent1|LINK
Wow, now that is one of those little tidbits that is news to me. Great catch! You may have just saved future generations hours of suffering and struggling :) KUDOS!!!