So, I was trying to build a datagrid control in SharePoint designer today off of (what I thought) was a fairly simple query with a couple of parameters. So I used the GUI (might have been my first mistake, but what are you going to do in designer). I built my query using the ASP.NET SQLDataSource Control (very similar to Visual Studio). I specified my parameters after they were auto-detected by the wizard and I got rows returned when I tested my query (so I assumed it was working...). Then I dropped a simple data grid control on it and did a couple of formatting things (not the problem) and tried to load the page. The thing was completely broken, so I ran back through the wizard for the SQL Data Source control and noticed that it was asking for parameter types when I tried to load the page. So, I was forced to <gasp> look into the code created by SharePoint designer and I discovered that when it creates the control using the HTML tags it leaves out two very important tags (depending on what you're doing obviously) in my case I was populating my parameters with values then evaluating those values in my primary select statement. So, this is what I had to do to correct:
In the <Select Parameters> tag you get this by default from designer once you're done with the wizard:
<asp:querystringparameter QueryStringField="GETDATE()" Name="currTime" /> \\broken
What you need for the data grid to work (if you are already populating you parameters, is this:
<asp:querystringparameter QueryStringField="GETDATE()" Name="currTime" Type="DateTime" Direction="Output" />
\\ Adds the return type of your parameter and the output method which may not be output in your case, but was in mine.
Anyway this caused me a few minutes of confusion this morning and I wanted to pass it on for those of you who might be struggling with the same thing.
Thanks,
Toby