SELECT     CONVERT(varchar(10), upgradedate, 101) as Expr1,  newurl , affiliateurl , amountpaid 
FROM         tblCommissions
WHERE     (affiliateid = @affilaiteid)
I have this working.   Now in my codebehind, the intellisense comes up and there's no Expr1.  This column ( off to the right) in the rows returned, contains the date like 7/12/08 How do I access this date?  I need to compare it with 2 other dates.  I want to see this work with either my foreach loop on the rows, or through a dataview.  I tried this and got stuck. See line 27.
Also, how do I pass the userId parameter to the query through the buttonclick event? The user selects 2 dates with the date picker and then clicks the button to retrieve stats. 
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
1    Dim tblCommissionAdapter As New tblCommissionsTableAdapter
2            Dim tblCommission As commissions.tblCommissionsDataTable
3            tblCommission = tblCommissionAdapter.GetCommissionsbyUser(SiteUser.CurrUserID)
4            Dim dateTime1 As String = Me.GMDatePicker1.DateString
5            Dim dateTime2 As String = Me.GMDatePicker2.DateString
6    
7            Dim totcommissions As Integer = 0
8            Dim usercommissions As String = ""
9            Response.Write("<table>")
10           For Each tblCommissionrow As commissions.tblCommissionsRow In tblCommission
11   
12   
13               usercommissions += ("<tr><td>" & tblCommissionrow.newurl & "</td><td>" &  & "<tr><td>" & tblCommissionrow.amountpaid & "$10")
14               'totcommissions = totcommissions + 10
15               ' End If
16   
17               'Response.Write("</td></tr></table><br / >------------------------------<br /> Total Commissions: " & totcommissions)
18               'Dim dt As Data.DataTable = getdata()
19               'Response.Write(dateTime1)
20               'Response.Write(tblCommissionrow.upgradedate)
21               'Response.Write(dateTime2)
22               'Exit Sub
23           Next

24           'Dim dv As New Data.DataView(tblCommission)
25           'Response.Write(dv)
26           'Exit Sub
27           'Dim s As String = [String].Format(New Globalization.CultureInfo("en-us").DateTimeFormat, "Expr1 >= #{0:d}# and Expr1 <= #{1:d}#")
28           'dv.RowFilter = s
29           'Me.GridView1.DataSource = dv
30           'GridView1.DataBind()
31   
32           'Dim ds As Data.DataSet = New Data.DataSet("myDataSet")
33           'Dim dataview1 = New dataview(ds.Tables[0].DefaultViewManager)
34           'Return usercommissions
35       End Sub
  
 
Scot King
 SEO Software Tool | Search Engine Optimization

"Get your page rank high"

 Please post a table creation script and state which column is to be used for the date range.
Please specify what version of SQL Server and Visual Studio you are using. 
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

SELECT     CONVERT(varchar(10), upgradedate, 101) as Expr1,  newurl , affiliateurl , amountpaid 
FROM         tblCommissions
WHERE     (affiliateid = @affilaiteid)
The above query results in the following:
1    SET ANSI_NULLS ON
2    GO
3    SET QUOTED_IDENTIFIER ON
4    GO
5    SET ANSI_PADDING ON
6    GO
7    CREATE TABLE [dbo].[tblCommissions](
8    	[commissionid] [int] IDENTITY(1,1) NOT NULL,
9    	[newurl] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
10   	[affiliateurl] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
11   	[upgradedate] [smalldatetime] NULL,
12   	[affiliateid] [int] NULL,
13   	[amountpaid] [int] NULL,
14    CONSTRAINT [PK_tblCommissions] PRIMARY KEY CLUSTERED 
15   (
16   	[commissionid] ASC
17   )WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
18   ) ON [PRIMARY]
19   
20   GO
21   SET ANSI_PADDING OFF
 
1    Also, here's a portion of the aspx page
2    
3    <cc3:GMDatePicker ID="GMDatePicker2" runat="server" CalendarWidth="150" TextBoxWidth="75" Width="150">
4                        <CalendarDayHeaderStyle BackColor="#C0FFFF" />
5                    </cc3:GMDatePicker>
6                </td>
7                <td style="width: 20px; height: 39px;">
8                    End Date:</td>
9                <td style="width: 149px; height: 39px">
10                     
11                   <cc3:GMDatePicker ID="GMDatePicker1" runat="server" CalendarWidth="100px" TextBoxWidth="75">
12                       <CalendarDayHeaderStyle BackColor="#C0FFFF" />
13                   </cc3:GMDatePicker>
14               </td>
15               <td style="width: 128px; height: 39px">
16               </td>
17           </tr>
18       </table>
19       <br />
20       <center>
21              <asp:Button ID="Button1" runat="server" Text="Get Stats" /></center>
22       <br />
23       <br />
24       <center>
25           <asp:Label ID="Label1" runat="server" Width="350px" Font-Size="X-Small" Font-Bold="true"></asp:Label></center>
26       <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
27           AutoGenerateColumns="False" DataKeyNames="commissionid" DataSourceID="ObjectDataSource1"
28           HorizontalAlign="Center" PageSize="20" Width="328px">
29           <Columns>
30               <asp:BoundField DataField="commissionid" HeaderText="commissionid" InsertVisible="False"
31                   ReadOnly="True" SortExpression="commissionid" />
32               <asp:BoundField DataField="newurl" HeaderText="newurl" SortExpression="newurl" />
33               <asp:BoundField DataField="affiliateurl" HeaderText="affiliateurl" SortExpression="affiliateurl" />
34               <asp:BoundField DataField="upgradedate" HeaderText="upgradedate" SortExpression="upgradedate" />
35               <asp:BoundField DataField="amountpaid" HeaderText="amountpaid" SortExpression="amountpaid" />
36           </Columns>
37       </asp:GridView>
38       <asp:ObjectDataSource ID="ObjectDataSource1" runat="server" DeleteMethod="Delete"
39           InsertMethod="Insert" OldValuesParameterFormatString="original_{0}" SelectMethod="GetCommissionsbyUser"
40           TypeName="commissionsTableAdapters.tblCommissionsTableAdapter" UpdateMethod="Update">
41           <DeleteParameters>
42               <asp:Parameter Name="Original_commissionid" Type="Int32" />
43           </DeleteParameters>
44           <UpdateParameters>
45               <asp:Parameter Name="newurl" Type="String" />
46               <asp:Parameter Name="affiliateurl" Type="String" />
47               <asp:Parameter Name="upgradedate" Type="DateTime" />
48               <asp:Parameter Name="affiliateid" Type="Int32" />
49               <asp:Parameter Name="amountpaid" Type="Int32" />
50               <asp:Parameter Name="Original_commissionid" Type="Int32" />
51           </UpdateParameters>
52           <InsertParameters>
53               <asp:Parameter Name="newurl" Type="String" />
54               <asp:Parameter Name="affiliateurl" Type="String" />
55               <asp:Parameter Name="upgradedate" Type="DateTime" />
56               <asp:Parameter Name="affiliateid" Type="Int32" />
57               <asp:Parameter Name="amountpaid" Type="Int32" />
58           </InsertParameters>
59           <SelectParameters>
60               <asp:Parameter Name="affilaiteid" Type="Int32" />
61           </SelectParameters>
62       </asp:ObjectDataSource>
63           
 
 
Scot King
 SEO Software Tool | Search Engine Optimization

"Get your page rank high"

 Thank you for posting the TSQL. To produce a table creation script in either Query Analyser or SQl Server management studio, select the table in the object viewer, right click and select produce script.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

 Thank you for posting the TSQL. To produce a table creation script in either Query Analyser or SQl Server management studio, select the table in the object viewer, right click and select produce script.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

I don't understand your response which you have posted twice. 
Scot King
 SEO Software Tool | Search Engine Optimization

"Get your page rank high"

 With a table creation script, I can reproduce your table on my laptop this evening and produce some TSQL and wrapper code for you. I am willing to help you but need to re-produce your table within my database.
Don't forget to click "Mark as Answer" on the post that helped you.
This credits that member, earns you a point and marks your thread as Resolved so we will all know you have been helped. 

The option "produce script" is not available in my SQL management studio express. Is that feature on the full version? Can you not just create the table using the create table code? When I right click on my table in the object explorer, I have script table as, create to, and I choose new query editor window. From there I copy and here it is:
USE [LinkExchanger] 
GO
/****** Object: Table [dbo].[tblCommissions] Script Date: 07/14/2008 08:37:03 ******/
SET ANSI_NULLS ON

GO
SET QUOTED_IDENTIFIER ON

GO
SET ANSI_PADDING ON

GO
CREATE TABLE [dbo].[tblCommissions](

[commissionid] [int] IDENTITY(1,1) NOT NULL,

[newurl] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[affiliateurl] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,

[upgradedate] [smalldatetime] NULL,

[affiliateid] [int] NULL,

[amountpaid] [int] NULL,

CONSTRAINT [PK_tblCommissions] PRIMARY KEY CLUSTERED 

(
[commissionid] ASC

)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] 
GO
SET ANSI_PADDING OFF
Scot King
 SEO Software Tool | Search Engine Optimization

"Get your page rank high"