Last post Mar 25, 2008 10:07 AM by mthakershi
Jan 06, 2006 11:42 AM|aschreiber|LINK
I have been desperately trying to do what looks like the simplest thing. I created a datasource that links to my local SQL Server 2005 database. I test out the datasource and it prompts me for 4 parameters which all accept the
default value of NULL. When I click "Test Query" from the datasource it executes just fine. I link the GridView control to the datasource and I know that the selected datasource and the GridView control are "talking to one another" because when I click "configure
datasource" or "edit columns" from the GridView control it pops open the datasource control properties or column information that the stored procedure returns from the query, respectively.
Here is my code:
Jan 06, 2006 03:08 PM|tfsmag|LINK
Jan 06, 2006 03:31 PM|SinkableHail|LINK
Jan 07, 2006 09:39 PM|aschreiber|LINK
Thanks for replaying. At first I was getting an actual error message. Now when I try it, no rows are getting returned at all. By looking at my compiled asp.net page you wouldn't even know that a gridview was on it. Before I was
at least getting an error. I know that there is data in the database and rows should be getting returned. I mentioned in my previous post that when I test the query from the datasource control it prompts me for the stored procedure parameters and then returns
a resultset with data! I don't enter any values for the parameters because I specified in my stored procedure that each parameter can accept null as a default. As an experiment I created a totally new page with a new stored procedure that doesn't use any parameters.
It just retrieves all of the records from a table called "tbContacts". That Gridview actually worked and produced data. The problem seems to occur only when I use parameters and don't enter a value.
The reason I don't want to enter any values is because I am using a dynamic query in my stored procedure that needs to sum up totals based on items selected from 3 dropdown lists. If no dropdown lists are selected then I want
to get a "grand-total". I then have a "Centers" dropdown list where if the user selects a particular center, then the parameter passed into the stored procedure is that Center's ID. The stored procedure has a conditional statement that looks something like
declare s_query varchar(255)
s_query = 'select product_name, sum(cost) cost from tbLicenses'
if (p_center_id is not null)
s_query = s_query + ' and center_id = ' + p_center_id
s_query = s_query + ' group by product_name'
That's basically all I want to do. It's so odd that testing the stored procedure from the datasource itself works but not from the GridView control.
Thanks in advance.
Jan 07, 2006 09:44 PM|aschreiber|LINK
Thanks for the quick reply. I replied to the guy who replied to me before you did with some more information regarding my problem.
The error that I was getting earlier was:
The DataSourceID of GridView must be the ID of a control of type IDataSource
I am no longer getting that error (at least not at the moment). Instead I get nothing at all (at least not in the GridView control). Like I mentioned above, when I used a GridView and a different stored procedure that doesn't
use any parameters it works fine and returns data. Also, as I mentioned above I can successfully test the stored procedure using the datasource -but not through the GridView control.
Jan 08, 2006 12:11 PM|azamsharp|LINK
Jan 08, 2006 01:15 PM|aschreiber|LINK
Jan 08, 2006 01:40 PM|aschreiber|LINK
When I add the SqlDataSource to my page and set it's properties using the dropdown list (or clicking on the little arrow in the top right-hand corner of the SqlDataSource and accessing it's tasks) I am given the option of either
using a custom connection string or Visual Studio creates a default one for me (i.e. there is already a new connectionString with the name "ASPNETDBConnectionString") right there in the dropdown list. I assume that because the name of the database I am trying
to connect to is called ASPNETDB that it just appends the word "ConnectionString" to the name of my database as the default name.
Once I select that it automatically creates that entry in my web.config and then on the next screen I tell Visual Studio that I want to use a stored procedure.
On the screen following that one I select the stored procedure to use.
On the next screen it retrieves the parameters (if any exist) that the stored procedure requires and allows me to specify where the parameter source should come from (i.e. querystring, control, cookie, session etc.). I can also
specify a default value for each input parameter as well.
Finally when I get to the last screen I am given the opportunity to test my stored procedure by clicking the "Test Query" button. When I do this a window opens prompting me to enter values for each of the parameters. I don't enter
any values and it returns 2 rows (which is what I expect it to return).
So my test works. I then create the GridView control and specify that this new SqlDataSource be the GridView's datasource and the columns get updated in Visual Studio to show that in fact it's "communicating correctly" with my
SqlDataSource. However, when I try to refresh the schema and it prompts me for the parameter values when testing the GridView control from Visual Studio I get an error message that says:
Unable to retrieve schema. Ensure that the ConnectionString and SelectCommand properties are valid.
This problem only seems to happen when I use a
GridView control that uses a stored procedure that
does take input parameters.
I have been able to successfully use a GridView control with a stored procedure that
does not take any input parameters.
I have also been able to successfully use a
DataGrid control that uses a stored procedure that does take input parameters.
I also cannot get any data returned when I try to run this page in a browser.
I don't understand why I seem to be the only person who has encountered this problem. It seems that what I am trying to do is a VERY standard procedure.
Thanks in advance for any help on this one.
Jan 08, 2006 08:39 PM|azamsharp|LINK
Jan 08, 2006 09:14 PM|aschreiber|LINK
My ConnectionString in web.config is saved like this:
I am also using this same ConnectionString for other controls and it works fine.
Jan 10, 2006 10:58 PM|aschreiber|LINK
THIS IS WHAT MY REAL STORED PROCEDURE LOOKS LIKE.
Jan 11, 2006 10:30 AM|acadalzo|LINK
Jan 11, 2006 12:17 PM|acadalzo|LINK
I found the answer. You have to set CancelSelectOnNullParameter=false on the SqlDataSource control. Otherwise it defaults to true.
Jan 12, 2006 02:35 PM|aschreiber|LINK
Do you know how long I've been looking for a solution for this? Nowhere on the Internet (i.e. Google, codeproject, aspalliance, asp101, asp.net, MSDN etc.) did anyone even mention this. I can't believe I am the first person to
have come up with an issue like this.
You are the man!
Jan 12, 2006 08:34 PM|aschreiber|LINK
Now that I finally have my GridView and dropdownlists working, I have one more problem. I cannot add a default item to any of the dropdownlists that were populated using an SqlDataSource and databound. I try to add <asp:ListItem>
tags but nothing appears. I just want to insert a default "select x from this list ..." text in each dropdownlist.
I even tried doing it in code:
ListItem("choose a center",
ListItem("choose a mission",
ListItem("choose a project",
And it still didn't work. It only works if I have static items.
Jan 12, 2006 10:10 PM|acadalzo|LINK
I suspect the data binding is happening after you have inserted your ListItem, so it is wiping it out. Try putting your code in a DropDownList DataBound event handler.
protected void ListBox1_DataBound(object sender,
ListItem("<Choose One>", ""));
Jan 13, 2006 11:06 AM|aschreiber|LINK
I actually did this instead and it worked too:
void Secure_Maintenance_PreRenderComplete(object sender,
selCenters.SelectedIndex = 0;
selMissions.SelectedIndex = 0;
selProjects.SelectedIndex = 0;
Thank you for responding though.
Sep 23, 2006 08:28 PM|jbeechii|LINK
THANK YOU!! Ive been trying to solve this issue all darn day!
Oct 06, 2006 10:29 AM|mramsey|LINK
Where ever you are, and where ever you work... YOU DESERVE A RAISE.... i have searched for DAYS!!!! trying to figure out why my dynamic parameter based sqldatasource was not returning anything to the page, but doing test query in the datasource returned
results... Like the other person posted... NO WHERE on the net is that little setting listed!!!!!
again Thanks for the lightbulb moment!!! [Idea]
Dec 21, 2006 03:54 PM|Michael.Shorten|LINK
Well, here's a variation of the original problem...
I have a stored proc that has no parameters, generates dynamic SQL, uses sp_execsql and I get the dreaded 'Unable to refresh Schema' error.
Looking at the trace didn't give me any clues - the grid works when I have Autogenerate Columns on (which is not helpful for my UI) and I get results in the test query.
So that magical setting doesn't seem to work for me.
Here's the sproc:
IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'GetKeywordDriverMatrix')
DROP Procedure RSUser.GetKeywordDriverMatrix
CREATE Procedure RSUser.GetKeywordDriverMatrix
DECLARE @strList nvarchar(2048)
DECLARE @sql nvarchar(4000)
DECLARE @sql_1 nvarchar(2048)
SET @sql = 'SELECT keyword_key, keyword, [AllDriversFlag] = CASE WHEN
((SELECT COUNT(*) FROM Reports.KeywordDrivers) = (SELECT
COUNT(inner_kdl.driver_key) FROM Reports.KeywordDriverLink inner_kdl
WHERE inner_kdl.keyword_key = outer_k.keyword_key)) THEN ''True'' ELSE
SET @sql_1 = 'CASE WHEN ((SELECT keyword_key FROM Reports.KeywordDriverLink
WHERE keyword_key = outer_k.keyword_key AND driver_key = @@@) IS NOT NULL )
THEN ''True'' ELSE ''False'' END'
/* Dynamically create the columns and True/False indicator if the keyword is
associated with the driver, for each driver in the KeywordDrivers table
SELECT @strList = COALESCE(@strList + ', ', '') + '[' + (list.driver) + '] = ' +
REPLACE(@sql_1, '@@@', driver_key)
( SELECT driver, driver_key
FROM Reports.KeywordDrivers) list
SET @sql = @sql + @strList + ' FROM Reports.Keywords outer_k'
EXEC sp_executesql @sql
GRANT EXEC ON Stored_Procedure_Name TO PUBLIC
Apr 13, 2007 04:19 PM|SFBay|LINK
Jul 30, 2007 05:19 AM|kelarens|LINK
Mar 19, 2008 05:04 PM|mthakershi|LINK
Hi, I am facing the same problem. Did you find solution/workaround for yours?
GridView, SQLDataSource using stored procedure
DataSource is not able to pass column information to the gridview. So I can't configure anything design time. Refresh schema throws "unable to retrieve schema. ensure that the connectionstring and selectcommand properties are valid"
Please help me.
Mar 20, 2008 08:18 AM|Michael.Shorten|LINK
Wow, it's been awhile since I had to think about this problem. No, I don't remember how exactly I did it - I'll have to go home and look at my code tonight and see if I can remember. I know I solved it and it wasn't easy at all, but it's been a year and
I've been doing as much as I can to forget that particular project. :D
Mar 24, 2008 11:16 AM|mthakershi|LINK
Hi, thank you for your help.
Would be great if you can recollect how you fixed it.
Mar 24, 2008 08:03 PM|Michael.Shorten|LINK
Well, I dug through the code and you're not going to like the answer.
The bottom line is that I could never find a way of doing it through one call to my stored proc. So, what I do is in PageInit, I call a method called BuildColumnsDynamically. I basically call the stored proc, get the first row back, loop through the columns,
get the number and type, build my grid that way. That's basically the approach I took.
I should also note that my grid was bound to a datasource, so this approach worked for that situation. I should also note that I did this every time the page was called, whether post back or not. Some caching of the result set would eliminate the constant
call db/build grid approach, but either way, you have to reubuild on each page load.
Probably not what you wanted to hear, but there it is.
Mar 25, 2008 10:07 AM|mthakershi|LINK
Thank you for your reply. Can't believe MS has't fixed this yet.
As a work around, I have kept a select statement which is exactly same to what
will get. I uncomment it at design time when I need to make changes to the datasource or gridview. I comment it at run-time.
Some people are not facing the problem though. If you come across a permanent fix, please post it here.