Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~
You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegr oups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

Determine a date not in the table

Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~You'd have to have a table of all potential dates and then do a left join
from it to the other table, filtering on where the PK of the other table is
null.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"FurRelKT" <furrelkt@.gmail.com> wrote in message
news:1180643461.759433.264850@.p77g2000hsh.googlegroups.com...
Hello, this might be a stupid question, but how would i go about
getting the dates that are NOT in the table.
For instance:
Table contains these dates: like a calendar with sat and sun missing,
but also some of the dates are weekdays too. I need to find out which
date(s) are missing from the list that are Weekends and not the normal
weekdays.
Table
06/01/2007
06/04/2007
06/05/2007
06/06/2007
06/07/2007
06/11/2007
So, the dates missing were the sat and sun (06/02/2007, 06/03/2007)
and the weekday 06/08/2007.
How would i create a query to find the missing dates?
Thanks for any insight or suggestions.
K~

DetailsView Control (Please Help!)

Can someone please tell me what I am doing wrong...

I have a simple webform that has contains a GridView and a DetailsView. Once the GridView is populated the user simply selects a record from the list. The DetailsView is them populated with all of the data from the selected record.

The DetailsView is bound to a SQL DataSource/CustomerData. I have made sure that the DV control has the Edit/Update Command listed. The user can click the Edit button and successfully edit the fields but when the user clicks the "Update" button I am getting the following message.

Updating is not supported by data source 'CustomerData' unless UpdateCommand is specified

Why isnt the control handling the Update.

Many Thanks!!!!

T

You still need to have your SqlDataSource for the detailview configured to handle the editing...

This sample is doing the same tihng as you described. Chaeck your code against it to see what is the difference.

http://www.asp.net/QuickStart/util/srcview.aspx?path=~/aspnet/samples/data/GridViewMasterDetailsInsert.src

If you still have question, please post back here.

|||

Limno,

First thank you for responding... I did do some more research after posting and found that I was not calling the Update Command. I subsequently have added that... I am not getting any errors but... Its not updating the database. I looked at the link you supplied and it appears that I have everything setup correctly... Obviously not!!!!

Any guidance would be much appreciated.

Here is my code:

<asp:DetailsViewID="DVCustomerInfo"runat="server"Height="22px"Width="215px"CellPadding="4"Font-Size="0.65em"ForeColor="#333333"GridLines="None"HeaderText="Customer Info."AllowPaging="True"AutoGenerateRows="False"DataKeyNames="WorkSheetID,Origin,DealerName,DateCreated,SalesRep,BkupSalesRep,FirstName,MiddleName,LastName,Address,City,State,Zip,HomePhone,WorkPhone,CellPhone,Email,County,SalesTax,SS,DOB"DataSourceID="ApplicantSQLDS">

<FooterStyleBackColor="#1C5E55"Font-Bold="True"ForeColor="White"/><CommandRowStyleBackColor="#C5BBAF"Font-Bold="True"/><EditRowStyleBackColor="#7C6F57"/><RowStyleBackColor="#E3EAEB"/><PagerStyleBackColor="#666666"ForeColor="White"HorizontalAlign="Center"/><FieldHeaderStyleBackColor="#D0D0D0"Font-Bold="True"/><HeaderStyleBackColor="Black"Font-Bold="True"ForeColor="White"/><AlternatingRowStyleBackColor="White"/><Fields><asp:BoundFieldDataField="WorkSheetID"HeaderText="WorkSheetID"ReadOnly="True"SortExpression="WorkSheetID"/><asp:BoundFieldDataField="Origin"HeaderText="Origin"ReadOnly="True"SortExpression="Origin"/><asp:BoundFieldDataField="DealerName"HeaderText="DealerName"ReadOnly="True"SortExpression="DealerName"/><asp:BoundFieldDataField="DateCreated"HeaderText="DateCreated"ReadOnly="True"SortExpression="DateCreated"/><asp:BoundFieldDataField="SalesRep"HeaderText="SalesRep"ReadOnly="True"SortExpression="SalesRep"/><asp:BoundFieldDataField="BkupSalesRep"HeaderText="BkupSalesRep"SortExpression="BkupSalesRep"/><asp:BoundFieldDataField="FirstName"HeaderText="FirstName"ReadOnly="True"SortExpression="FirstName"/><asp:BoundFieldDataField="MiddleName"HeaderText="MiddleName"ReadOnly="True"SortExpression="MiddleName"/><asp:BoundFieldDataField="LastName"HeaderText="LastName"ReadOnly="True"SortExpression="LastName"/><asp:BoundFieldDataField="Address"HeaderText="Address"SortExpression="Address"/><asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/><asp:BoundFieldDataField="State"HeaderText="State"SortExpression="State"/><asp:BoundFieldDataField="Zip"HeaderText="Zip"SortExpression="Zip"/><asp:BoundFieldDataField="HomePhone"HeaderText="HomePhone"SortExpression="HomePhone"/><asp:BoundFieldDataField="WorkPhone"HeaderText="WorkPhone"SortExpression="WorkPhone"/><asp:BoundFieldDataField="CellPhone"HeaderText="CellPhone"SortExpression="CellPhone"/><asp:BoundFieldDataField="Email"HeaderText="Email"SortExpression="Email"/><asp:BoundFieldDataField="County"HeaderText="County"SortExpression="County"/><asp:BoundFieldDataField="SalesTax"HeaderText="SalesTax"SortExpression="SalesTax"/><asp:BoundFieldDataField="SS"HeaderText="SS"SortExpression="SS"/><asp:BoundFieldDataField="DOB"HeaderText="DOB"SortExpression="DOB"/><asp:CommandFieldButtonType="Button"CausesValidation="False"ShowEditButton="True"/></Fields></asp:DetailsView><asp:SqlDataSourceID="ApplicantSQLDS"runat="server"ConnectionString="<%$ ConnectionStrings:CustomerInfoConnection %>"SelectCommand="SELECT [WorkSheetID], [Origin], [DealerName], [DateCreated], [SalesRep], [BkupSalesRep], [FirstName], [MiddleName], [LastName], [Address], [City], [State], [Zip], [HomePhone], [WorkPhone], [CellPhone],, [County], [SalesTax], [SS], [DOB] FROM [CustomerTable] WHERE ([WorkSheetID] = @.WorkSheetID)"UpdateCommand="UPDATE [CustomerTable] SET [BkupSalesRep] = @.BkupSalesRep, [Address] = @.Address, [City] = @.City, [State] = @.State, [Zip] = @.Zip, [HomePhone] = @.HomePhone, [WorkPhone] = @.WorkPhone, [CellPhone] = @.CellPhone, = @.Email, [County] = @.County, [SalesTax] = @.SalesTax, [SS] = @.SS, [DOB] = @.DOB WHERE [WorkSheetID] = @.WorkSheetID"><UpdateParameters><asp:ParameterName="BkupSalesRep"Type="String"/><asp:ParameterName="Address"Type="String"/><asp:ParameterName="City"Type="String"/><asp:ParameterName="State"Type="String"/><asp:ParameterName="Zip"Type="String"/><asp:ParameterName="HomePhone"Type="String"/><asp:ParameterName="WorkPhone"Type="String"/><asp:ParameterName="CellPhone"Type="String"/><asp:ParameterName="Email"Type="String"/><asp:ParameterName="County"Type="String"/><asp:ParameterName="SalesTax"Type="String"/><asp:ParameterName="SS"Type="String"/><asp:ParameterName="DOB"Type="String"/></UpdateParameters><SelectParameters><asp:ControlParameterControlID="GridView1"Name="WorkSheetID"PropertyName="SelectedValue"Type="String"/></SelectParameters></asp:SqlDataSource>

|||

Hi:

Is theWorkSheetID column your primary key in your database table? You need primary key to do update.

You may not need put all columns inDataKeyNames. If WorkSheetID is the primary key of the table, you can set

DataKeyNames="WorkSheetID".

|||

I found the problem...I must say its always interesting to learn soemthing new...

Anyway... I found that I had my DataKeyNames set incorrectly... I had it set to all of the fields rather then the 1 primary key WorksheetID.

I am successfully updating... Many thanks again for replying.

T

Detail information within other detail (different table)

I have a one page report that prints information from several tables. The
report contains a list object slightly smaller than the body size (8X10.5)
and a rectangle object slightly smaller than the list object. The rectangle
object contains 130 plus textboxes for labels and actual fields to be
printed. This is a profile type report used to print all information for a
member (name, address, city, state, and several other fields) and requires
the whole page. There can be further detail linked to this member. In this
case, committees that the member serves on. I can't figure out how to get the
top 5 committees to print with all of the other detail. Does anyone know a
"best method" to accomplish this?
Thanks,
JohnNevermind. Finally used a subreport which solved the problem very nicely.
John
"John Joslin" wrote:
> I have a one page report that prints information from several tables. The
> report contains a list object slightly smaller than the body size (8X10.5)
> and a rectangle object slightly smaller than the list object. The rectangle
> object contains 130 plus textboxes for labels and actual fields to be
> printed. This is a profile type report used to print all information for a
> member (name, address, city, state, and several other fields) and requires
> the whole page. There can be further detail linked to this member. In this
> case, committees that the member serves on. I can't figure out how to get the
> top 5 committees to print with all of the other detail. Does anyone know a
> "best method" to accomplish this?
> Thanks,
> John

Design Question for Large Table

Hi,

What's the most efficient way to store the following information:

* Table contains 1 million listings
* Each listing can be geo-targeted to any of the 200+ countries
* Searches return listings based on geo-location

Storage options:

Option #1 (normalized)
* ListingsTable (PK listingID int) [1 million rows]
* ListingGeoLocations (listingID, geoLocationID) [could be up to 200 million rows]

Option #2 (denormalized)
* ListingsTable (PK listingID int, binary(32) with bit-mask consisting of 200 bits one for each location)

Did anyone have experience with similar structures? Which option is more efficient?

Thanks,
Avoption 2 sounds inferior to me

let's say you were looking for listings corresponding to the 37th bit in the bitmask, how would you find them?

by inspecting the bitmask of all 1 million rows, that's how|||I'm too tired to answer

Look up normalization on google|||Option #1.

As Rudy pointed out, you are going to have problems doing bitwise calculations on bitmasks over 30 bits, after which you exceed the capacity of the BigInt datatype.

Also, while under scenario #1 your table COULD reach 200 million rows, in reality what is the average number of countries you expect for each geolocation? Probably significantly less than 200.|||I understand that option #1 is normalized and will allow the search for listings in a specific country using query. However, say when a search is performed, it is based on an set of keywords (which are also tied to listings).

So say you looking for "dentists". You can about 100 listings that are related. Now, the APPLICATION (not SQL) will simply "filter" the 100 listings by doing geo-specific bitwise comparissons and will only display the listings that have the proper country bit set.

Still Option #1? The search needs to be very fast.|||You are NOT going to do bitwise operations on a 200 value bitmask.|||So say you looking for "dentists". You can about 100 listings that are related. you can? you can what? how does that work?|||you can? you can what? how does that work?

Ask Harry potter|||Why not have a
Listings table (Pk ListingID)
Locations Table (PK LocationID)
and,
ListingLocations table (PK ListingID, LocationID)

ListingLocations is an intersection table, used to define a many-to-many relationship. The two required fields, ListingID and LocationID are also foreign keys to the appropriate table.