How to access Expr1 from query (datetime converted to date)

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"
0
sking
7/13/2008 7:19:40 PM
asp.net.object-datasource 16182 articles. 0 followers. Follow

7 Replies
862 Views

Similar Articles

[PageSpeed] 41

 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.
0
TATWORTH
7/13/2008 9:22:22 PM

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"
0
sking
7/13/2008 10:52:45 PM

 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.
0
TATWORTH
7/14/2008 6:57:03 AM

 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.
0
TATWORTH
7/14/2008 6:58:12 AM

I don't understand your response which you have posted twice. 


Scot King
SEO Software Tool | Search Engine Optimization

"Get your page rank high"
0
sking
7/14/2008 3:00:13 PM

 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.
0
TATWORTH
7/14/2008 3:07:54 PM

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"
0
sking
7/14/2008 3:39:01 PM
Reply:

Similar Artilces:

Parse DateTime into Date or select DateTime as Date from Access
I am getting DateTime values out of an Access Database and I want to only use the Date part of this field. I have tried to cast the returned value to a DateTime value and then format the DateTime to only show the Date part in the toString() fcn. <asp:Label runat="server" Text='<%# getDate(DataBinder.Eval(Container, "DataItem.ACTIVE_FROM"))%>'> But I can't cast the object to a DateTime object. I have also tried using DateTime.Parse() but this gives an error saying that the format is not correct.         {       ...

converting an object (from access db) to a datetime format?
At present i am unsing the below code: lblroundDate.Text = objRdr["roundDate"]+""; This displays my date from the database in the following format: 27/05/2007 00:00:00 I am trying to loose the time on the end and just have the date, i have tried playing around with ToLongDateString(); but keep getting comp errors, any help appreciated.  Hi, you can use a Date datatype to get just tghe date in your string. Like this  Date myDate = objRdr["roundDate"]; String JustDate = myDate.ToShortDateString; lblroundDate.Text = JustDate;  That way...

Issue when converting XML dateTime to .NET DateTime
Hi, I am experiencing an issue when deserializing XML dateTime nodes in ASP.NET in some timezones. The construct 0001-01-01T00:00:00.0000000+ZZ:ZZ where ZZ:ZZ is the timezone offset for the timezone on the current system should produce a DateTime value equalling the constant DateTime.MinValue when converted. This works some of the time, such as for the Paris time offset of +1: (CDate("0001-01-01T00:00:00.0000000+01:00") = DateTime.MinValue) = True But does not work for Sydney time offset of +10: (CDate("0001-01-01T00:00:00.0000000+10:00") = DateTime....

How to use Convert date statement in cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToSt...

Convert .NET DateTime format to SQL DateTime format
Hi!!I'd like to know if there is a function to convert the DateTime format used in C# (VS2005) : dd/mm/yyyy HH:mm:ss to the format used in SQL Databases: yyyymmdd HH:mm:ssThanks folks!...

Access object query statement. Query all rows which have data
I need to write a query and pass it to my command that will in turn execute Access table object in vb and come up with all the records that have data in certain columns. With another words if field x=null avoid and don't select. Here is the code I've got and I am trying to tune it up so I can achieve the above stated effect:        Dim strConnectionString As String        Dim objConnection As New OleDbConnection        Dim objCommand As New OleDbCommand    ...

How to convert Julian date into Calendar date (VB.Net)
Hi all, I have some dates which are in Julian format and I need to convert them into calendar dates  for example if the Julian date is 1, I need to be able to convert it into 1/1/2007, If the julian date is 66 then I would need to convert it to 3/7/2007 and so on. Every year would start with 1 and end with 365365 = 12/31/2007. Is there a vb.net function or tutorial somewhere ? thanks for any help.    1) Your dates aren't in Julian format!! 2) How do you know which year you are referring to? 3) Have you tried the AddDays function of the Date/DateTim...

OleDb
I have a very complex query with 6 table Joins. I created the query in Access because I thought that the query would be compiled or prepared. I did some tests to compare the time with which I could access 100 results from the Access Query and then the same 100 results from just pasting the query string into the OleDb Command object. I noticed that pasting the query into OleDB command object was slightly faster then just selecting from the Query in Access. Can anyone Concur? A query is a view, and I don't think there's much difference in performance when you add a where clau...

Innovation to ADO.NET
Matisse Software Inc. provides its native ADO.NET Data provider for Matisse 6.0, the Database for .NET. Matisse Software has pushed the envelope of Microsoft ADO.NET architecture and extended it into a powerful Object Access Services component for .NET. Matisse’s extension of ADO.NET demonstrates that ADO.NET can evolve beyond the Microsoft architecture to benefit .NET developers. Unlike the standard ADO.NET data provider, which returns data in a table format to integrate seamlessly with relational products, Matisse has extended the ADO.NET architecture to return data in an object format....

convert an sql datetime column to .net datetime data type in front end...?
I have a field (ldate) in a table that typically displays data in the following format:2007-11-12 20:30:47.000In the front end I want the time and the date separately. But as I try to cast it to a datetime object it is giving me a System.NullReferenceException. What I tried to do was just this: Protected Sub gvTopics_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles gvTopics.RowDataBound Dim img As Image = gvTopics.FindControl("imgIcon") Dim lbDate, lbTime As Label lbDate = gvTopics.FindControl(...

.NET Datetime object
I want to know everything about the DateTime Class ,I mean where can I find the information about this class . You may find some help from here.This posting is provided "AS IS" with no warranties, and confers no rights. Do below link helps you for your need! It will have all the info what you want! http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatetimeclasstopic.asp Hope it helps!Sreedharhttp://www.w3coder.orgweblog http://weblogs.asp.net/skoganti...

convert datetime to date
having issues with the reporting excel imports. problem with the import is that gridview has populated with "10/10/2009 12:00:00 am" instead of just "10/10/2009" as the original 'excel' cell only has date data listed in the column. tried using convert without luck. checked the msdn forums and copied the exmaples, but this did not work in my scenario."SELECT convert(char(10),[month],101) * FROM [sheet5$]"  'Month' being the column name. there are other columns in the table which do not need the convert logic applied. Here is th...

Access Database Connection/Query Problem: "Object reference not set to an instance of an object"
I have been working on a web site in Visual Web Developer for several months.  For no apparent reason, I can no longer create, modify, or connect to my Microsoft Access database source.  The database is still where it has always been, still opens, etc.  Below are a few of the symptoms.  Does anyone know what would cause this and more importantly how I can fix it? (1) I can no longer use the "Query Builder" with existing or new aspx pages; when I try I get this message: "Object reference not set to an instance of an object" (2) I tried using the "Design Custom Statement...

Problem in converting MS Access OLE Object[Image] column to BLOB (binary large object bitmap)
Hi All, i have a table in MS Access with CandidateId and Image column. Image column is in OLE object  format. i need to move this to SQL server 2005 with CandidateId column with integer and candidate Image column to Image datatype. its very udgent, i need any tool to move this to SQL server 2005 or i need a code to move this table from MS Access to SQL server 2005 in C#. please do the needfull ASAP. waiting for your reply with regards       Use SSIS to import or DTSWizard. The best approach would be using SQL Server Migration Assistant for Access http://www.mi...

Web resources about - How to access Expr1 from query (datetime converted to date) - asp.net.object-datasource

A Converted British Family Sheltering a Christian Missionary from the Persecution of the Druids - Wikipedia ...
A Converted British Family Sheltering a Christian Missionary from the Persecution of the Druids is a painting by William Holman Hunt that was ...

Vitrue Offers Data On Brands That Have Converted To Facebook Timeline
Facebook marketing platform Vitrue shared some data on brands in its client base that have already made the switch to timeline for pages. With ...

Facebook Now Allows Personal Profiles To Be Converted Into Business Pages
For the first time, Facebook now allows personal user profiles to be converted into official business Pages, turning all their friends into fans. ...

epicstep_starter [Converted] - Flickr - Photo Sharing!
Explore figarointhehouse's photos on Flickr. figarointhehouse has uploaded 6 photos to Flickr.

Converted To Islam Sister Julia - Women In Islam are Diamonds! - YouTube
Sister Julia converted to Islam very short time ago and she speaks in this video about women in Islam and specially marriage. -...

Boko Haram says kidnapped schoolgirls have converted and married
Boko Haram has claimed the 219 schoolgirls it kidnapped in Nigeria earlier this year have converted to Islam and been married off.

Crucifix 'raping machine' used to assault kidnap victims on converted bus, court told
A truck driver who abducted and sexually abused three women had built a "raping machine" in a bus he had converted into a home, a court has heard. ...

World's coolest converted hotels
... to stay somewhere a little bit different on your next holiday? Travel website Virtual Tourist has compiled this list of some of the best converted ...

Oprah Winfrey preaches self-help to the converted in stripped-down stage marathon
Oprah Winfrey made her Australian stage debut in Melbourne on Wednesday night with the premiere of a show that stripped her famous talk-show ...

Kevin charming only the converted
THERE is a looming and dangerous prospect for Labor that the "Rudd factor" is either illusory or having an effect in the wrong places or both. ...

Resources last updated: 12/19/2015 6:58:49 PM