November 2006 - Posts
I didn't realize how difficult it can be to query on dates. Because a date consists of a date and a time, when you want to see if a date is the current date, using the getdate() mechanism, it won't match because the time isn't exact. How can you correct this? Using the datediff method in SQL Server. DateDiff takes two dates, and verifies certain parameters (the list is in the MSDN documentation link a few words over). I've used it more in this context though:
select * from MyTable where datediff(dayofyear, RequestDate, getdate()) = 0
DateDiff returns an integer value if the dates match, which would equal zero, or if they don't, which will be a positive or negative number based on which date is greater. This way, you can search based only on the date. You can also use the day parameter to determine if it is the same day, and a variety of other terms defined in the list. You need to use a mechanism like this, or one of the other date methods, to make date determinations.
I was trying to bulk insert some data, and I found a page that had this (don't have the link anymore), which I used it to get it to work:
BULK INSERT tblYLT
FROM 'C:\YLT.txt' WITH (DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', ROWTERMINATOR = '\n');
tblYLT was a table that I had already existing in a database, which I sucked in some data from YLT.txt. tblYLT already had the columns that would be inserted, and the sizes must be the correct size, or an error will occur. Also, don't have the header column in the file; they get sucked into the data. There may be a flag for this.
This is something I see asked about frequently, so I decided to post a solution on my blog. What happens if you need to bind data to a list that has static values (values defined in the Items collection, or <asp:ListItem> elements defined in the list)?
The solution is to first change the AppendDataBoundItems property to true, so that data binding doesn't clear the current list, but appends to them. You can define static properties in many ways; I like to use the ASPX page, because this way it reduces the amount of code in the code-behind page.
<asp:DropDownList id="d" runat="server" AppendDataBoundItems="true" DataSourceID="sds">
<asp:ListItem Value="X" Selected="True">Select An Item</asp:ListItem>
</asp:DropDownList>
This item will appear in position 0, because data bound items are appended after it. I specify selected equals true, just out of habit to ensure the right item is selected. You can also use the DataSource property to specify an alternative data source, and invoke DataBind(), if you would like to bind that way as well.
