Problem with Lesson 3 in the Analysis Services Tutorial

I am at the step of "Adding an Attribute and Defining Display Folders" where I am experiencing problems. The steps are:

2. In the Attributes pane, select the following attributes by holding down
the CTRL key to select multiple attributes, and then in the Properties
window, set the AttributeHierarchyDisplayFolder property for the selected attributes to "Location":

? City

? Country-Region

? Postal Code

? State-Province

3. In the Hierarchies and Levels pane, click Customer Geography, and then select "Location" as the value of the DisplayFolder property in the
Properties window.

The problem is that there are no choices in the DisplayFolder property of the Customer Geography hierarchy. I do not know how to force "Location" into the property drop down list. The rest of the tutorial has been fine up to this point. What am I missing?

I would guess probably you missed something when defining the cube (Lesson 2). All I can suggest is to start from scratch and do it again carefully following the tutorial instructions.

|||

hello,

i think you should be able to just type in the Location as the value for the display folder in the properties window (as i'd expect that in addition to picking from the list of available values, you should be able to also enter a new one).

hope this helps,

Problem with image with external URL

I get such warning :
"Images with external URL references will not display if the report is published to a report server without an UnattendedExecutionAccount"
And picture dont shows.
Can anybody help me ?
Thanks.
P.S I have SP1Please see section "4.1.4 External Images" in the SP1 readme for more
information.
--
Bryan Keller
Developer Documentation
SQL Server Reporting Services
A friendly reminder that this posting is provided "AS IS" with no
warranties, and confers no rights.
"Ivan" <Ivan@.discussions.microsoft.com> wrote in message
news:AEE72F48-A0A2-46AE-B4A2-982E25BCBDFA@.microsoft.com...
> I get such warning :
> "Images with external URL references will not display if the report is
published to a report server without an UnattendedExecutionAccount"
> And picture dont shows.
> Can anybody help me ?
> Thanks.
> P.S I have SP1
>

Problem with identity user in a report

Hi,

I use forms authentication with my Report manager. The problem is that I can't display the same information to all the users into the report. For example, in my report, i have one dataset that give me a list of all the store of a compagny. And i have to display only the store onto the user is working. If the user is a boss, he can see the information of all the stores. So i have to know in my report what is the login he uses in the form authetication and i don't know how to do.

Excuse me for my bad english and thank you.

Regards,

Shaouk.

Well Shaouk what i have understand from your point is that for that you must have your data base structure like that you must know which company is the user associated to. Means your company table should have column of user id Or if user can be associated with mulitiple companies then third table with comanyID and UserID should exist SO you may know that the logged in user is dealing with this or those comapnies.

Then after user logged in send userID to the stored procedure from which you are geting the company list so you may filter them on basis of that userId or if using dataset with already populated companies with userId's associated with them then filter that dataset on the basis of that logged in UserID

I Hope that works

Regards

Nabil Khalid

|||

Hello Shaouk,

From within your report, you can make use of User!UserID to determine who is running the report.

Hope this helps.

Jarret

Problem with ID 17981

Hello,

I′m trying to do an update through a system but this message display a problem that I didn′t get to identify exactly the problem to looking for a solution.

"Table error: Object ID 17981, index ID 1, page (0:871674153). Test (offset + len < PAGESIZE) failed. Slot 10, offset 0x1a8 is i
Error while undoing logged operation in database "

Could anyone help me please?

Thanks,

Maurício

Try checking the table with "dbcc checktable" and the db with "dbcc checkdb".

AMB

problem with group by

hello all,

i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

this my scripts

$query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
$query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
$query .= " ticket.t_summary, ticket.t_category, ";
$query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
$query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
$query .= " FROM events, tmpeid, category, ticket ";
$query .= " WHERE ticket.t_id = events.t_id ";
$query .= " AND events.e_id=tmpeid.e_id";
$query .= " GROUP BY events.t_id";
$query .= " HAVING COUNT(events.t_id) >= 1 ";

this is error msg that i've found

Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

can anybody solve for me???Hello,

the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.
f.e

SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
(also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

(will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)

a

SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

(works also fine)

So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

or ...

group every field in the list

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.comsql

problem with expoting data to excel

I have a problem while exporting the crystal report to Excel sheet.
I am trying to display running total at the end of my report(In the report footer). It is displayed in several lines using a formula field. I have used appended chr(13) within the formula to break the values into different lines. But finally when I am exporting the report to excel all the lines are displayed in a single cell. What should I do to display them in multiple cells when exported to excel. Is there any ascii value which can be used instead of chr(13) , which will help me print the values in different cellsIf you use chr(13), it will be exported to the same cell. Instead use seperate formula to have the value and place it next to first formula

Problem with Embedded Image

Hi,

I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.

How can I fix the location of textboxes that renders at one location all the time ?

Thanks,

Shyamkumar123

I am having this problem and others, too. I have a large background image, and I'm placing textboxes on top. I designed it with just dummy data in the textboxes, got it laid out just fine. But after putting actual data in and conditionally hiding some textboxes, the problem shows up in the Preview tab in SQL Studio and in an Adobe Acrobat export. Text boxes do not render in their correct locations.

Furthermore, I cannot get the text box to line up at all where I want it. In the Preview, a textbox will render higher up than I intend. If I move it downward in the Layout tab, the textbox renders much lower than I intend. I have my choice of rendering above or below where I want it, but I just can't render it at the correct location even using direct manipulation of the location attributes of the textbox properties. If I correct a rendering by 0.01 inches, it moves a half inch downward on the rendering and pdf.

Maybe it's a rounding error in the application with regard to the textbox locations... But I had no problems when I laid out the form with plain text in all textboxes and none of them hidden. All textboxes are set to CanGrow = False

Suggestions are welcome...

|||

Shyam,

I found a workaround to my problem. It fixes the problem of Layout not looking like Preview or Export to PDF. I had to set all textboxes to hidden = false. Then instead of making them conditionally hidden, I made them all visible but made the data conditionally empty.

Moral of the story: don't hide textboxes.

Problem with Embedded Image

Hi,

I have a report with background embedded image. I have placed few textboxes on top of them to display data. Every time I set the location of the textbox, it is behaving strangely by rendering at one location one time and changing location next time when it is rendered. I am viewing it in PDF format.

How can I fix the location of textboxes that renders at one location all the time ?

Thanks,

Shyamkumar123

I am having this problem and others, too. I have a large background image, and I'm placing textboxes on top. I designed it with just dummy data in the textboxes, got it laid out just fine. But after putting actual data in and conditionally hiding some textboxes, the problem shows up in the Preview tab in SQL Studio and in an Adobe Acrobat export. Text boxes do not render in their correct locations.

Furthermore, I cannot get the text box to line up at all where I want it. In the Preview, a textbox will render higher up than I intend. If I move it downward in the Layout tab, the textbox renders much lower than I intend. I have my choice of rendering above or below where I want it, but I just can't render it at the correct location even using direct manipulation of the location attributes of the textbox properties. If I correct a rendering by 0.01 inches, it moves a half inch downward on the rendering and pdf.

Maybe it's a rounding error in the application with regard to the textbox locations... But I had no problems when I laid out the form with plain text in all textboxes and none of them hidden. All textboxes are set to CanGrow = False

Suggestions are welcome...

|||

Shyam,

I found a workaround to my problem. It fixes the problem of Layout not looking like Preview or Export to PDF. I had to set all textboxes to hidden = false. Then instead of making them conditionally hidden, I made them all visible but made the data conditionally empty.

Moral of the story: don't hide textboxes.

Problem with displaying image on the report

I have a webservice which creates an image returns that image in the form of Byte array. I want to consume this webservice and display that image on my report. When I see my report in the Preview tab of the report designer, it displays that image but when I deploy my report on the report server, image doesn't show up. I have copied app_code.dll (dll for the webservice) to the following locations:

C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies
C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PublicAssemblies
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\bin

Here is the code for the webservice:

Imports System.Web
Imports System.Web.Services
Imports System.Web.Services.Protocols
Imports System.Drawing
Imports System.Diagnostics
Imports System.IO

<WebService(Namespace:="http://tempuri.org/")> _
<WebServiceBinding(ConformsTo:=WsiProfiles.BasicProfile1_1)> _
<Global.Microsoft.VisualBasic.CompilerServices.DesignerGenerated()> _

Public Class Service
Inherits System.Web.Services.WebService

<WebMethod()> _

Public Function HelloWorld() As Byte()

Dim marker As New Bitmap("C:\Inetpub\wwwroot\help.gif")
Dim imageStream As New System.IO.MemoryStream
marker.Save(imageStream, System.Drawing.Imaging.ImageFormat.Jpeg)
Trace.Write("Before Write")
Return imageStream.ToArray()

End Function

End Class

I have referenced this dll saved in public assemblies folder from my report project and wrote a function to call the above mentioned webservice function HelloWorld() and called the function from value property of an image control on the report.

Does anybody has any idea what am I missing?

Thanks

Its probably an authentication problem. Try configuring an Unattended Execution Account for SRS to an account which has permission to access the web service on the remote machine. Also check the IIS logs on the machine serving the image for failed login attempts.

Problem with displaying image from DB

I am able to write an image to a SQL table. The problem I am having is understanding how to retrieve the image from the table AND display it on a page along with the other page content.

Does anyone have an example or an article to help me with this procedure? I have searched the discussion and read a few articles but they all talk about reading an image into a datagrid which isn't what I want.

Dazed & confused!!!If you can display the image in a datagrid, you can display it by itself too.

Try looking at:
http://authors.aspalliance.com/das/readimage.aspx

That has some code (VB.NET) to show images from a SQL database.

I would recommend reading the other articles linked to that page as well.

You'll probably find that datagrid image code you've already seen will have a hint to the answer.|||I think thats the original page where I got the code to write the image to a database.

As you can see from the link, when they retrieve the image, they either retrieve it in a datagarid or on a page by itself.

It must be a lot harder to pull it into a page with existing data than I can figure out. My hands are tied. All examples are limited to either datagrid examples or a page with only the image and nothing else...ugghhhh!

Problem with display of International characters

Hi All,
I have a problem in displaying International characters in SQL Server 2000.
There are a few Russian characters that are displayed as '?' in query
analyzer. i have the database setup on 2 machines. On one DB server the
characters are displayed properly and on other it is shown as '?'.
The machine on which '?' are displayed, i queried syslanguages in which
'Traditional Chinese' and 'Thai' months are displayed incorrectly like small
squares.
Please let me know if there is any SQL server setting required to be changed
or it is some setup issue.
I have also checked 'Ansi to oem conversion' settings.
Thanks
Regards
Sudesh.
This is not a SQL Server problem. Try installing the supplemental language
support files.
1. From Control Panel, select Regional and Language Options
2. From the Languages tab, select the check boxes under Supplement Language
Support.
That should resolve your problem.
Gail Erickson [MS]
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
"Sudesh" <sudesh.h@.polaris.co.in> wrote in message
news:4A4B2435-1E31-4E6A-92BA-835D87E68B13@.microsoft.com...
> Hi All,
> I have a problem in displaying International characters in SQL Server
> 2000.
> There are a few Russian characters that are displayed as '?' in query
> analyzer. i have the database setup on 2 machines. On one DB server the
> characters are displayed properly and on other it is shown as '?'.
> The machine on which '?' are displayed, i queried syslanguages in which
> 'Traditional Chinese' and 'Thai' months are displayed incorrectly like
> small
> squares.
> Please let me know if there is any SQL server setting required to be
> changed
> or it is some setup issue.
> I have also checked 'Ansi to oem conversion' settings.
> --
> Thanks
> Regards
> Sudesh.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

problem with Display databound images in page header

Hi all,
I have a problem in displaying databound images in page header.
I have done as is stated in the below article.
-X-
http://msdn2.microsoft.com/en-us/library/ms159677(en-us,VS.90).aspx
Adding a Databound Image to a Header or Footer
You can use image data stored in a database in a header or footer. However, you cannot reference database fields from the Image control directly. Instead, you must add a text box in the body of the report and then set the text box to the data field that contains the image (note that the value must be base64 encoded). You can hide the text box in the body of the report to avoid showing the base64 encoded image. Then, you can reference the value of the hidden text box from the Image control in the page header or footer.
For example, suppose you have a report that consists of product information pages. In the header of each page, you want to display a photograph of the product. To print a stored image in the report header, define a hidden text box named TXT_Photo in the body of the report that retrieves the image from the database and use an expression to give it a value:
=System.Convert.ToBase64String(Fields!Photo.Value)
In the header, add an Image control which uses the TXT_Photo text box, decoded to show the image:
=System.Convert.FromBase64String(ReportItems!TXT_Photo.Value)

-X-
but I am not getting the image.
The error message displayed is like this
[rsInvalidExpressionDataType] The Value expression used in image ‘image2’ returned a data type that is not valid.
Can any one help me in this regard.
Thanks in advance.
Ramesh

What's the image source type of the image reportitem? Is it set to "Database"?

-- Robert

|||

If the image was saved to the database via MS Access, the solution is here: http://forums.devarticles.com/microsoft-sql-server-5/displaying-image-fields-in-reporting-services-11844.html . In brief, as it states, instead of utilizing the hidden text box and Base64 strings, simply use this piece of code to set the Value property of your RS image control:

=System.Text.Encoding.Default.GetBytes(Mid(System.Text.Encoding.Default.GetString(Fields!Picture.Value),XXX))

For English use XXX = 79
For Spanish use XXX = 89

(The image control should still have its Source property set to Database.)

|||

u can do that with the help of parameters

1) add a calculated field in dataset named imagebase64 in that add the following expression

Convert.ToBase64String(Fields!Photo.value)

2)Add report Parameter named image

set the datatype as string.

problem with DEFAULT (getdate())

Hi all

I create table and set default value detdate(). But after insert record date display ‘1900-01-01 00:00:00’.

Example this,

CREATE TABLE [tblTemp1] (

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 values(0)

select ItemUserDate from dbo.tblTemp1 =‘1900-01-01 00:00:00’

select getdate() =’2006-07-20 15:53:27.820’ I need this answer

Hi,

try this:

CREATE TABLE [tblTemp1] (

part1 char,

ItemUserDate [smalldatetime] NOT NULL CONSTRAINT [DF_tblTemp1_ItemUserDate] DEFAULT (getdate())

)

GO

INSERT INTO dbo.tblTemp1 part1, values('a')

And then do your select. You'll recieve the correct answer.

You have you result because you're putting 0 in your default column, so default has no effect anymore...

Greeting.

|||

Hi

To make Insert work for the table structure with a single datetime column you can use Default VALUES option: INSERT INTO dbo.tblTemp1 DEFAULT VALUES

|||

thankx Stefan Haeck

|||thanks NB2006

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !
http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/inf...asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>
|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.
|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegr oups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/in...e.asp#Searching
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups
.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>

problem with date time field ...

Hi guys,
I have an asp page that needs to show data based on date criteria.
Basically the user selects a date and the asp page should display all
records within that day.
The problem is that this field contains date and time.
My current query is as follows:
RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
Request.Form("date") & "'", dbConn, 1
How should I modify this query so that it ignores the time ?
Thanks in advance !http://www.karaszi.com/SQLServer/info_datetime.asp
http://www.karaszi.com/SQLServer/info_datetime.asp#Searching
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
<zerbie45@.gmail.com> wrote in message news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>|||Hey Tibor,
thanks for your reply; I'm just starting using asp and sql.
Do you know a quick way to modify this query so that it skips the time
? Your links are a bit too advanced for me.
That would be VERY appreciated.
Thanks in advance.|||To add to Tibor's response, you can use a parameterized query to improve
performance, security and mitigate the need for date formatting. Also,
consider using a fast-forward cursor instead of a keyset one unless you have
a specific reason to do otherwise.
Set command = CreateObject("ADODB.Command")
command.ActiveConnection = connection
command.CommandText = _
"Select * from DB1.dbo.logs WHERE Date >= ? AND Date < ? + 1"
Set dateParameter1 = command.CreateParameter( _
"@.dateParameter1", 7, 1)
command.Parameters.Append dateParameter1
dateParameter1.Value = Request.Form("date")
Set dateParameter2 = command.CreateParameter( _
"@.dateParameter2", 7, 1)
command.Parameters.Append dateParameter2
dateParameter2.Value = Request.Form("date")
Set RS = command.Execute()
--
Hope this helps.
Dan Guzman
SQL Server MVP
<zerbie45@.gmail.com> wrote in message
news:1141037700.450486.299340@.p10g2000cwp.googlegroups.com...
> Hi guys,
> I have an asp page that needs to show data based on date criteria.
> Basically the user selects a date and the asp page should display all
> records within that day.
> The problem is that this field contains date and time.
> My current query is as follows:
> RS.Open "Select * from DB1.dbo.logs WHERE Date = '" &
> Request.Form("date") & "'", dbConn, 1
> How should I modify this query so that it ignores the time ?
> Thanks in advance !
>