Database Discussion Boards

Mike Dimmick5-May-04 1:54

5-May-04 1:54

Without knowing your data schema (you refer to two tables Booking and BookingDate in your example, but you don't join them) it's a bit difficult to know how to help.

GROUP BY and HAVING clauses may be helpful. GROUP BY allows data to be aggregated across groups, and HAVING allows you to select from the aggregated data. If WHERE applies to the data in the table, HAVING applies to the aggregated data.

Stability. What an interesting concept. -- Chris Maunder

Re: Max and Where

WoutL5-May-04 1:57

WoutL

5-May-04 1:57

Just try:

SELECT MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate

<br />
FROM Booking <br />
HAVING (BookingDate.bookingDate > ?) AND (BookingDate.bookingDate < ?)<br />
WHERE (Booking.fkChannelId = ? ) <br />

Maybe the HAVING part belongs after WHERE...

Wout Louwers

Re: Max and Where

OmegaSupreme5-May-04 3:28

OmegaSupreme

5-May-04 3:28

Thank you both for your help.

I tried adding the HAVING clause instead of WHERE and got the following errors:

Column 'BookingDate.bookingDate' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'Booking.fkChannelId' is invalid in the HAVING clause because it is not contained in either an aggregate function or the GROUP BY clause.

The tables I have are as follows each with a one to many relationship with the following table:

Ad
	pkAdId
Booking
	pkBookingId
	fkAdId
BookingDate
	pkBookingDateId
	fkBookingId
BookingHour
	pkBookingHourId
	fkBookingDateId

and heres the query as it stands :

SELECT DISTINCT 
		                      Ad.pkAdId, Ad.fileName, Ad.name,
 Ad.length, Ad.lastUpdated, MAX(BookingDate.bookingDate) AS MaxDate, MIN(BookingDate.bookingDate) AS MinDate, 
		                      AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots
		FROM         Ad INNER JOIN
		                      Booking ON Ad.pkAdId = Booking.fkAdId INNER JOIN
		                      BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId INNER JOIN
		                      BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId
		
		WHERE  (BookingDate.bookingDate >= @startDate) 
		AND (BookingDate.bookingDate <= @endDate) 
		AND (Booking.fkChannelId = @channelId) AND
		GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated
		ORDER BY Ad.fileName

Try not! Do or do not, there is no try. - Master Yoda

Re: Max and Where

WoutL5-May-04 3:50

WoutL

5-May-04 3:50

Try:

<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE  (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MinDate >= @startDate)<br />
AND MaxDate <= @endDate)<br />
ORDER BY Ad.fileName<br />

Wout Louwers

Re: Max and Where

OmegaSupreme5-May-04 4:00

OmegaSupreme

5-May-04 4:00

Hi Wout,

Thanks for your reply, unfortunatley Query Analyser complains :

Invalid column name 'MinDate'.
Invalid column name 'MaxDate'.

Also I want those bookings between the dates not whose max and min booking dates fall between the dates.

Sorry to be a pain and thanks again.

Try not! Do or do not, there is no try. - Master Yoda

Re: Max and Where

WoutL5-May-04 4:06

WoutL

5-May-04 4:06

<br />
SELECT DISTINCT <br />
Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated,<br />
MAX(BookingDate.bookingDate) AS MaxDate,<br />
MIN(BookingDate.bookingDate) AS MinDate,<br />
AVG(BookingHour.bookedSlots + BookingHour.bonusSlots) AS AverageSlots<br />
FROM Ad <br />
INNER JOIN Booking ON Ad.pkAdId = Booking.fkAdId <br />
INNER JOIN BookingDate ON Booking.pkBookingId = BookingDate.fkBookingId<br />
INNER JOIN BookingHour ON BookingDate.pkBookingDateId = BookingHour.fkBookingDateId<br />
WHERE (Booking.fkChannelId = @channelId)<br />
GROUP BY Ad.pkAdId, Ad.fileName, Ad.name, Ad.length, Ad.lastUpdated<br />
Having MAX(BookingDate.bookingDate) >= @startDate<br />
AND MIN(BookingDate.bookingDate) <= @endDate)<br />
ORDER BY Ad.fileName<br />

??

Wout Louwers

Re: Max and Where

OmegaSupreme5-May-04 4:39

OmegaSupreme

5-May-04 4:39

Worked like a charm,

thank you very much Big Grin | :-D

Try not! Do or do not, there is no try. - Master Yoda

More on DataSets

normanordas4-May-04 23:50

normanordas

4-May-04 23:50

1. Are there any considerations when to use untyped and typed datasets? I find typed-datasets more useful and less error prone in typo since you can call the members exactly by the name itself besides the fact that it adds some useful methods not present in its counterpart.
2. What condition should stop me from using typed-datasets? Are there limitations?
3. Does datasets supports concurrency..i.e. move the record pointer forward, backward, BOF and EOF? How about bookmarks?

Re: More on DataSets

Mike Dimmick5-May-04 1:57

Mike Dimmick

5-May-04 1:57

I'm not sure about #1 and #2, but about #3 I'm certain: a DataSet is a disconnected data store. It doesn't have any concept of the 'current record', there is no record pointer, and no persistent connection to the database is maintained.

Until you use Fill a second time, the DataSet contains only the data you originally fetched and your local modifications. Any local modifications are only replicated back to the server when you call Update on an appropriate data adapter object.

Stability. What an interesting concept. -- Chris Maunder

MSDE Usage

Roger Wright4-May-04 18:45

Roger Wright

4-May-04 18:45

I know I installed MSDE on this machine, and until I rebuild the server I can't get SQL Server to run. I need to build a quick and dirty app that doesn't depend on Access or SQL Server being installed on the host machine, so MSDE seems like the way to go. The trouble is, I can't find it, or any menu option to run it. Any ideas where I might find the magic switch?

Some people think of it as a six-pack; I consider it more of a support group.

Re: MSDE Usage

Maxwell Chen4-May-04 18:59

Maxwell Chen

4-May-04 18:59

Never mind!

Have you had a look at database services?

Right-click [My Computer] -- Manage (G) -- Services.

Maxwell Chen

Re: MSDE Usage

normanordas4-May-04 23:59

normanordas

4-May-04 23:59

Download and run the file SQL2KDeskSP3a.exe file from msdn website.
from your console prompt type "setup SAPWD=(Some password) SecurityMode=SQL"
You are now ready to use MSDE as your database.

Comparing two tables using C#, ADO.NET

Brett Slaski4-May-04 8:24

Brett Slaski

4-May-04 8:24

Comparing two tables from different databases

I need to compare two tables from two different databases to show records in one table that are not in another. I am using ADO.NET and C#. I don’t wish to use a database link in MSSQL because it’s too slow. All that is left is code. In case you are curious, one database is MS SQL 2k and the other is Pervasive2000i.

Lets use DatabaseA TableB And DatabaseC TableD
If I where able to write a simple sql statement to show records in AB not in CD could be:
SELECT AB.ID, AB.Name
FROM DatabaseA.TableB as AB Left Join DatabaseC.TableD as CD on AB.ID = CD.ID
WHERE CD.ID is null

I am looking for a way to do this in C#. Using data relations between DataTables in a DataSet I think I can set up a filter to achive the same information, but I have ran out of time to experiment. So I look to you for some assistance. Thank you for any contributions.

Brett Slaski

Crystal Reports

Mr. Cully4-May-04 1:09

Mr. Cully

4-May-04 1:09

Not sure if this is the right place to post this but I'm using Asp.net to design a report system. I want to show the clients what the final report will look like after exporting it as a crystal report but i have never used crystal reports before and dont know what the output looks like. Obviously it will look different depending on the information to be displayed but i just want to get an idea of how it is layed out. I have looked for a sample of a crstal report output but cant find any, so if anyone could post up an example it'd help alot
Thanks
Colum

Re: Crystal Reports

Mr. Cully4-May-04 5:09

Mr. Cully

4-May-04 5:09

right well now im looking to store information in a dataset (hard coded in, not extracted from a database or anything) and display it as a crystal report... anyone?

detach database

Dpriya3-May-04 23:58

Dpriya

3-May-04 23:58

Hi,

I had developed a setup project which attaches a database while installing it & detaches it when uninstalled. Installation works fine. While uninstalling it throws an exception:"Cannot detach the database ,it is currently in use".
How do I forcefully detach a database (even though it is connected to any appln)?

Thankx
Priya

Re: detach database

Mazdak4-May-04 5:38

Mazdak

4-May-04 5:38

WHen you detach a datatabse , no connection should be exist to database. Do you close connections in your appliications before detaching , or do you logged into that databe while you are uninstalling your application?

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

How I access database through a Map Network drive?

ATC3-May-04 8:13

ATC

3-May-04 8:13

Hi,

I have 3 PC reside in a LAN, my VC++ application install in all 3 PC that must access the same database Main_database.mdb to do a multi-tasks job.

Because of that I have placed my Main_database.mdb in a Map Network drive Z: in order all 3 PC can access, my question is:

1. When one application connects to Main_database.mdb and using it ... is that the whole database is copied temporary into its memory then travels back & forward (between this PC & Z: ) in order to update data inside the z:Main_database.mdb?

2. I verified the speed of update Main_database.mdb is not slow at all, but if the same PC, I use another application which also uses the same Main_database.mdb at the same time ... then both of them run very slow! Can someone explains why?

*) I have known that if I use XML to acess database it will be safe & faster method!

Thanks for any help for understand more about acess database method!

Anh

Re: How I access database through a Map Network drive?

Mike Ellison4-May-04 8:31

Mike Ellison

4-May-04 8:31

Hi Anh. I don't know if I can answer either of the two questions you've posed, but I can offer a suggestion. Although Access databases can be used the way you've described (back-end data store on a shared network drive) you will probably see several advantages to migrating the back-end to run on a true client-server RDBMS. You could migrate the data to the MSDE for example - it functions well for a small workgroup database without the limitations of file-sharing databases. And - someone correct me if I'm wrong - I think an Access/Office license allows you to use the MSDE this way at no extra cost.

how to put an image in sql database

Member 7402202-May-04 11:15

Member 740220

2-May-04 11:15

i've make a column named "PIC" in my DB & made it of tiype "IMAGE" put when i'm goin to fill tables i can't put an image " hyper link" how do i put it??? thnx 4 all

Re: how to put an image in sql database

Mazdak2-May-04 21:22

Mazdak

2-May-04 21:22

Assumed you are using C# and SQLServer 2000:

http://www.codeproject.com/cs/database/albumviewer.asp[^]

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

How to convert null dates to String?

Anonymous1-May-04 15:46

Anonymous

1-May-04 15:46

I have a date database field, but I am storing the field values as string in front end. I want to covert that value to date field while inserting into database. But the problem is converting a null value to date field.

Please help

Re: How to convert null dates to String?

Jeff Varszegi1-May-04 15:59

Jeff Varszegi

1-May-04 15:59

Your question is nebulous. In the case of a null value, what do you want to happen? Store a null value, or some default value? If the former, pass NULL. If the latter, pass the default (or set up the table column to have a default value and don't pass anything at all).

Regards,

Jeff Varszegi

EEEP! An Extensible Expression Evaluation Package

Re: How to convert null dates to String?

-Dr_X-8-May-04 15:57

-Dr_X-

8-May-04 15:57

I use the following functions in conjunction with a datatable to retrieve and set the date values with the front end.

Friend Function DateToDb(ByVal val As Object) As Object
   'Validates a datevalue before saving to db
   If IsDate(val) Then
     Return val
   Else
     Return DBNull.Value
   End If

 End Function

 Friend Function DateFromDb(ByVal val As Object, Optional ByVal DateFormat As String = "MM/dd/yyyy") As Object
   'Validate the date from the database. Return "" if null
   If TypeOf val Is Date Then
     Return Format(val, DateFormat)
   End If
   Return C_ES

 End Function

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Preserve formatting and storing in Database

Anonymous1-May-04 8:48

Anonymous

1-May-04 8:48

Hello,

I have a problem in preserving the format of the data...

Consider this live example...

when i insert this message, the reply will appear as it is i type..

How to preserve that format?

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.