DISTINCT to ShortDateString, not DISTINCT to the DateTime; How?

Hello,

I have written a small asp.net application, which keeps record of the proposals coming from the branch offices of a bank in a table

CREATEd as a TABLE Proposals ( ID smallint identity(7,1), BranchID char(5), Proposal_Date datetime )

This app also calculates the total number of proposals coming from a specific branch in a given date by
SELECTing COUNT(BranchID) FROM Proposals WHEREBranchID=@.prmBranchID ANDProposal_Date=@.prmDate
and prints them in a table (my target table).

This target table has as many rows as the result of the "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals"
and excluding the first column which displays those DISTINCT Proposal_Dates, it also has as many columns as the result of the
"SELECT DISTINCT BranchID FROM Proposals".
This target table converts the DateTime values ToShortDateString so
that we are able to see comfortably which branch office has sent how many proposals in a given day.

So far so good, and everything works fine except one thing:

Certain DateTime values in the Proposals table which are of the same day but of different hours (for ex: 11.11.2005 08:30:45 and
11.11.2005 10:45:30) cause some trouble in the target table, where "SELECT COUNT( DISTINCT Proposal_Date ) FROM Proposals" is executed, because (as you might already guess) it displays two identical dates in ShortDateString form, and this doesn't make much sense (i.e. it causes redundant rows)

What I need to do is to get a result like (in a neat fashion :)

"SELECT COUNT( DISTINCT Proposal_Date ) <<DISTINCT ONLY IN THE DAYS AND NOT IN HOURS OR MINUTES OR SECONDS>> FROM Proposals"

So, how to do it in a suitable way?

Thanks in advance.

Try this -

Select Distinct Convert(char(10), Proposal_Date, 101)

It will eliminate the time part from the selection

Regards

Ash

|||

This one will solve your problem:

SELECT

DISTINCTCONVERT(NCHAR(8), Proposal_Date, 112)AS Proposal_Date

FROM

Proposals

You can look up from Books Online for the date, convert functions to help you understand more about the date and time. If you need more help, please post again.

|||

Hello,

Yeah that was what I neeed to know, and it solved the problem.

(apparently, I'm still new to T-SQL :)

Thanks.

|||

I know your problem is resolved but try the link below for more about the SQL Server DateTime convert function codes. And if you want Short Datetime you use SmallDateTime data type it gives you DateTime less seconds because it has less resolution. Hope this helps.

http://www.sqljunkies.com/Article/6676BEAE-1967-402D-9578-9A1C7FD826E5.scuk

DISTINCT only certain columns in on table and pull their IDs

Please help.? (I am using ASP, VB, SQL)

I have a table with Office address information and it's ID. There could be a lot of offices in one city. But I would like to display only unique cities with certain names they start with and their id's.

So for example I might have?

ID 1 - Office 1 - Chicago

ID 2 - Building 2 - Chicago

So I want to show

Office 1 - Chicago (my link will contain the ID in it)

So far I have

SELECT DISTINCT City , State FROM dbo.Offices WHERE (City IS NOT NULL) AND name like 'office%' ORDER BY City ASC

This works as far as pulling unique cities and names but now I need to get those City's ID. Can I use DISTICT for certain columns? Or how do I do it so that when it does pull out the unique cities it pulls their IDs as well?

?? Select ID and (DISTINCT City , State) FROM dbo.Offices ??

Thank you.

sscoder,

are you saying that the cities id is in another table. And are you just looking for the set of distinct city, state, id 's? is so just add id into your select statement

select distinct ID, City, State FROM Offices

if this is not what you are asking, can you include a bit more info...-- jp

|||City, State, ID are in the same table.

I want to find unqiue cities and then their IDs.?I'm using the ID in the hyperlink for a string.?

If I add ID to the distinct query?it will take priority and it will not list out unique cities. So instead of displaying

Chicago
Florida
Las Vegas

It will display this if I add ID to distinct
Chicago
Chicago
Chicago
Florida
Florida
Las Vegas

I need unique cities and then their ids.? Is there a way?

Thank you.

|||i see what you are saying but it you want the distinct city and a city may have more than 1 id, then how do you know which id you want to be returned?|||would only one of the city records be of type 'office'?|||there are many cities but each office has a different name. The every main office name start with 'Office' so I only want to pull up unique cities with name starting with 'Office%'. So there will never be two same cities. Only one office per city. That's where I put "WHERE name like 'office%' "

So the list will be:

Chicago - Office 1

Florida - Office 2

Las Vegas - Office 3

The query does this already. I just can't get the ID for that one row. That's what I need help on. If that "Office 1" in "Chicago" has an ID of 2. I want to get that. How??

Displaying Updated Columns

I want to use a gridview in my asp.net app to just show changes to one field in an employee table. I have a history table trigger on all fields already for other reasons, however for this purpose i want to be able to show the user what the column said before and after the change.

I read up on the columns_updated function and it seems like it could work however It seems there would be an easier way than figuring out what the bitmask is on my 14 column table for a change on the 3rd column?

Any ideas.Okay I think I already solved this using a stored procedure joining my employee table and employee history table.sql

Displaying Parent / Child / Grandchild data

I am working on an ASP.NET site to display information about authors and books. I'm working on the Author page right now and I'm having trouble getting it to display the data I want.

I'm trying to do everything with declarative controls and as little code-behind as possible.

I have a datasource on the main page that selects the Author data and provides this to a FormView. Inside the FormView is another datasource that takes the AuthorID as a parameter and selects the list of books, which is provided to a DataList.

Inside the DataList I display some limited Book information. I also want to display some child items of the Book (grandchildren of the author), for example other authors who contributed to that book, or alternative titles. I have tried using datasources in each DataList row with a parameter set to the BookID, and it works just great, but the response time is just not acceptable. Each datasource of possible several dozen is making its own call to the database and it's just too slow.

If I use code I can grab all the data in one operation and use relations in the DataBinding event (I believe) to select the data I want, but this is a bit cumbersome and I expect will cause trouble if I want to eventually use an ObjectDataSource.

Incidentally, on a display-only page I use the XMLDataSource and it works great, but I need read-write access on the Edit page.

Is there a good way to do what I'm trying to do?

Thanks,

Graham

Here's an alternative:

Instead of putting the books in a datalist you could put them in a gridview. Handle the row selected event of the gridview and programatically bind only the 'grandchild' details specific to the selected book.

|||

That's probably what I'll end up doing, but I'm trying to avoid code as much as possible. In code it's pretty simple to avoid many trips to the database when fetching information, at the cost of some added complexity. I'm just looking to see if there's an equivalent way to do that declaratively.

It would be nice if you could bind to a datasource, specify a data member, and also throw in some selection criteria - filter it at the bound control, not at the datasource level. That's similar to what you can do with the XMLDataSource and XPathSelect.

|||

Yeah I see what you're saying.I've never managed to getsomething like that working entirely declaratively though with asp.netcontrols. Even so, if you just do the grandchild stuff programaticallyit should only be a couple of lines of code.

You could take a look at some third party grid controls made by ComponentArt, Infragistics, Telerik etc.

Displaying images clarafication

I have seen many postings regarding asp. sql and images. One posting
on another group stated that images (jpg, etc) can be called by a db
instead of being stored on the db.

I am asking for some help with coding such a thing. What I want to do
is include in the db the location (url) for the image so I can add it
a pserson's record set and then be able to have it called from the db
to post on a web page.
This is for an awards system.

Thanks for any help.

Davedavestrike (davestrike@.nventure.com) writes:
> I have seen many postings regarding asp. sql and images. One posting
> on another group stated that images (jpg, etc) can be called by a db
> instead of being stored on the db.
> I am asking for some help with coding such a thing. What I want to do
> is include in the db the location (url) for the image so I can add it
> a pserson's record set and then be able to have it called from the db
> to post on a web page.
> This is for an awards system.

I think you have misunderstood something. SQL Server is not able
to invoke an URL. Well, OK, you write an XP or an COM object for the
task, but that would be to retrieve data from the URL into the server.

The usual alternative to storing images within the database, is to
only store a filename (or for that matter a URL), and the you retrieve
that filename to the application, and then the application retrieves
the file.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Displaying a relational database as it is displayed in MS Access

Can this be done in ASP.Net, as it stands my database views in my ASP.Net application are just standard

Unlike the view in MS Access which shows the collapsable linked data below the data (from a different table)

Many thanks

Rich

Yes, it's possible to create a GUI similar to the one that comes with Access. It's not a trivial undertaking though. You can use CSS, javascript and Ajax to overcome the inherent limitations of html.|||If your tables/views are relatively narrow and have few rows (the table can be convenantly viewed on a screen without scrolling), then use a DataGrid or GridView, and set it to automatic columns - this will give you a read-only view of the database. Code behind can be written to allow updates, but you do need to enforce any business rules.|||Have a look at http://www.codeproject.com/cs/database/DBViewer.asp A simple database viewer to manipulate SQL Server data types (in particular: image, binary, varbinary and text).|||Also try http://www.codeproject.com/asp/ute.asp "Viewer and Editor for any table in any Database you can reach from your IIS/PWS."

Display Reports in ASP.Net 2.0

What is the best way to display a report in ASP.Net 2.0. Are there web parts
like there are in SharePoint? Or is it that ReportViewer control? I am
running SQL 2005 Reporting Services.
Thanks,
DougSYou have three options.
1. Integrate using URL integration.
2. Integrate using web services and handle the nuts and bolts yourself
3. Use the new reportviewer control that comes with VS 2005 (it does not
come with RS 2005). This control is a major improvement over the example
(which is really all the previous one was) that shipped with RS 2000. This
control uses web services internally. My suggestion is to take a long hard
look at using this control. It is far and away the best way to integrate
with your own ASP.net application (this control is a asp.net 2.0 control).
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"DougS" <doug@.nospam.com> wrote in message
news:%23J9lY8mTGHA.1672@.tk2msftngp13.phx.gbl...
> What is the best way to display a report in ASP.Net 2.0. Are there web
> parts like there are in SharePoint? Or is it that ReportViewer control? I
> am running SQL 2005 Reporting Services.
> Thanks,
> DougS
>|||Thanks much. You're right, it is much better than the old ReportViewer.
DougS
"Bruce L-C [MVP]" <bruce_lcNOSPAM@.hotmail.com> wrote in message
news:ONyGdyoTGHA.5972@.TK2MSFTNGP10.phx.gbl...
> You have three options.
> 1. Integrate using URL integration.
> 2. Integrate using web services and handle the nuts and bolts yourself
> 3. Use the new reportviewer control that comes with VS 2005 (it does not
> come with RS 2005). This control is a major improvement over the example
> (which is really all the previous one was) that shipped with RS 2000. This
> control uses web services internally. My suggestion is to take a long hard
> look at using this control. It is far and away the best way to integrate
> with your own ASP.net application (this control is a asp.net 2.0 control).
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "DougS" <doug@.nospam.com> wrote in message
> news:%23J9lY8mTGHA.1672@.tk2msftngp13.phx.gbl...
>> What is the best way to display a report in ASP.Net 2.0. Are there web
>> parts like there are in SharePoint? Or is it that ReportViewer control? I
>> am running SQL 2005 Reporting Services.
>> Thanks,
>> DougS
>

Display image to ASP .NET from bynary data MSSQL 2000

Hi I have image in database sql server 2000 and want to display in ASP .net page. I make the memorystream from that data and write directly to page using Response.Write([bynary stream]) but U know it only display one image and other HTML is gone.

Normaly a HTML or ASP.net page will display image using local files like this <img src=..........> but how if I want to display it from memory stream that doesn't have physically in storage ? Or should I save that binary to storage first and then reference it ?

So anyone that have solution for this problem please help me how to this thing .........

Hi, Take a lok at the MSDN articles on images and bitmaps.

http://msdn2.microsoft.com/en-us/library/a343dky2.aspx

regards

smcoxon

|||

You don't understand. I'm not ask about image !

My question is how to display a binary image in memorystream to ASP.NET page via HTTP ?

I try to directly write by HttpResponse.Write( ... ) but U know it display the image only......

What I want is this image can be displayed in ASP.net page ......, I can't use <img src='.......'> ,

because it's on memory stream not in URL format or in local file in your hard drive.

|||

Hi,

smcoxon has given part of the answer but that is related to winforms. The picture box can accept the byte stream that you get from the binary datatype you stored the image in.

For aspx pages.

You have to first create a page which just serves the images.

so your image tag would be

<img src="http://pics.10026.com/?src=YourImageServingPage.aspx?imageID=1" ...

On YourImageServingPage.aspx

You would remove all mark up and then add this to your code behind

Response.Clear();
Response.ContentType="image/jpeg"; or any image type you are serving.

You would create a BitMap class load your bytes from database and write the image to the Response.Write stream.

You would find many articles on this Search Key: dynamic image generation

Happy programming,
Anton

Display DB Table in ASP

hello forum friends,

i need to display the database table in ASP

page.how it is possible,can anyone explain me

with code.

Regards

Prathap

What about:

http://www.stardeveloper.com/articles/display.html?article=2002061201&page=1

Jens K. Suessmeyer

http://www.sqlserver2005.de

Display data without binding

Hi All

I am new to VS 2005 and ASP.NET. I used to use Dreamweaver to design but now I am trying VS.

What I want to do is to display data retrieved with a SqlDataSource in a web page. I know how to do it by binding it to the various controls (Grid, DataList, DetailsView, FormView, Repeater.) available but how can I display it without using any of the mentioned (Grid, DataList, DetailsView, FormView, Repeater)?

Any help apprecited.

Cheers

you can create a dataset or datatable and loop through it, then write the content of datatable and dataset on the page.