Showing posts with label input. Show all posts
Showing posts with label input. Show all posts

Sunday, March 11, 2012

Displaying records from today on (filtering out the old)

I am trying to filter out old records from a search. I tried this but
it still seems to display the old records.
gofind is the input box text query and it passes through fine. It
searches perfectly, just doesn't filter out old records.
It should show everything from today and into the future.
dDate = Date()-1
SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes' ORDER BY date ASC
Can someone help please?You need to enclose the two sections of your where clause in
parentheses. Try something like this:
SELECT * FROM events WHERE (title LIKE '%" & gofind & "%' AND date >= "
& dDate & " AND active='yes') OR (comments LIKE '%" & gofind & "%' AND
date >= " & dDate & " AND active='yes') ORDER BY date ASC
As it was written simply a match with the comments LIKE '%" & gofind &
"%' would result in an evaluation of true.
james.shearer@.gmail.com wrote:
> I am trying to filter out old records from a search. I tried this but
> it still seems to display the old records.
> gofind is the input box text query and it passes through fine. It
> searches perfectly, just doesn't filter out old records.
> It should show everything from today and into the future.
> dDate = Date()-1
> SELECT * FROM events WHERE title LIKE '%" & gofind & "%' AND date >= "
> & dDate & " AND active='yes' OR comments LIKE '%" & gofind & "%' AND
> date >= " & dDate & " AND active='yes' ORDER BY date ASC
> Can someone help please?
>|||Thanks Steve but that doesn't seem to make any difference.
The query works fine apart from the part:
date >= " & dDate & "
That doesn't seem to make any difference at all.
It should show records dated from a date greater than yesterday but it
just shows everything that meets the rest of the criteria ignoring the
date request.
Any idea?|||On 8 Nov 2005 20:05:09 -0800, james.shearer@.gmail.com wrote:

>Thanks Steve but that doesn't seem to make any difference.
>The query works fine apart from the part:
>date >= " & dDate & "
>That doesn't seem to make any difference at all.
>It should show records dated from a date greater than yesterday but it
>just shows everything that meets the rest of the criteria ignoring the
>date request.
>Any idea?
Hi James,
I don't know what front end you are using. Best would be to pass the
date as a parameter instead of passing it as text. That would prevent
any possible conversion problems, since native date/time datatypes will
be used.
If you must pass it as text, then the best way to troubleshoot this is
to display the query string instead of executing it. The part with the
date comparison should look like this:
date >= '20051109'
or
date >= '2005-11-09T22:40:34.550'
That is: the date string must be enclosed in single quotes, and it must
adhere to one of the unambigous date formats, which are:
* yyyymmdd for date only
(note: no dashes, slashes, dots, or other punctuation)
* yyyy-mm-ddThh:mm:ss for date and time
(note: dashes in the date, colons in the time and an uppercase T [not
a space!] between the two parts)
* yyyy-mm-ddThh:mm:ss.mmm for date and time with millisecond precision
(note: same as above; add a dot and then the milliseconds)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Friday, March 9, 2012

Displaying Dynamically created columns

Hello,
I have a stored procedure that accepts input parameters and depending
on the input, returns multiple columns. For example depending on the month
range passed in, the stored procedure creates and populates temporary table
with columns for each month and returns the values in the temporary table.
How do I display these dynamically created columns using Reporting Services
(the number of coulmns returned by the stored proc varies between different
runs). Any help is appreciated.Sunil,
You would need to use the matrix control and then edit the
matrix_columngroup to use the data returned from your stored proc.
Hope this helps.
Bill Youngman
Anexinet, Inc.
"Sunil Oliver" <SunilOliver@.discussions.microsoft.com> wrote in message
news:D11DBA4E-C99F-4E37-8C0D-7F9D76192001@.microsoft.com...
> Hello,
> I have a stored procedure that accepts input parameters and
depending
> on the input, returns multiple columns. For example depending on the month
> range passed in, the stored procedure creates and populates temporary
table
> with columns for each month and returns the values in the temporary table.
> How do I display these dynamically created columns using Reporting
Services
> (the number of coulmns returned by the stored proc varies between
different
> runs). Any help is appreciated.

Wednesday, March 7, 2012

Displaying data from a certain date range

Hey all, hopefully this question is in the right spot. I'm writing a .NET app talking to a MS SQL 2000 DB. I have two date range input boxes, and I want to display the data (probably in a dataGrid) from those 2 certain dates. How do I go about this with my SQL server??

My DB table has a date field that I would use to search for the data between those two user specified dates. Any tips, examples, etc. would be greatly appreciated!!!Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0|||Okay, now whats up with this DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

What am I doing with my Date1.text and Date2.text input boxes.. sorry, I'm having trouble converting the code some.|||That's TSQL

The DECALRE and SELECT is to mimic the values in your text box...

Do you have sql server client tools installed?

Query Analyzer?

Your best bet would be to call a stored procedure...|||Yes, I have those tools installed, but I really don't know how to use them to my benefit.... :(|||Puttin this code in the Query analyzer shows the DATEDIFF in pink, and some of the code after that grey... I'm guessing something else needs to be used here?|||Hows about...

USE Northwind
GO

-- Your text boxes
DECLARE @.x datetime, @.y datetime SELECT @.x = '1996-09-01', @.y = '1996-09-30'

SELECT OrderDate, DATEDIFF(d,@.x,OrderDate), DATEDIFF(d,OrderDate,@.y)
FROM Orders
WHERE DATEDIFF(d,@.x,OrderDate) > = 0 AND DATEDIFF(d,OrderDate,@.y) > = 0

Brett, I am having a similar problem...can't seem to use datetime parameters with default values in a stored procedure.

This is the code I used. Basically, trying to default dates on the Begin and End Dates for the query...but when I run it, always get this error
'Syntax error converting datetime from character string.'

Is the problem in the default values or the datediff functions? Then is the solution to format the dates differently or use a cast/convert function?

Thanks for the help

Alex

CREATE PROCEDURE dbo.usp_TempTest
@.BeginDate DateTime = '11/1/2004',
@.EndDate DateTime = getdate

AS

SELECT dbo.vw_BasicAuditDetails_Complete.*
FROM dbo.vw_BasicAuditDetails_Complete
WHERE (DATEDIFF(d, @.BeginDate, Audit_TM) > 0)
AND (DATEDIFF(d, Audit_TM, @.EndDate) > 0)
GO|||Has anyone cut and pasted my code in to query analyzer and executed it?|||That's what I was trying to do earlier.... :confused: I'm not a pro with the analyzer though..|||Well, if you highlighted the text, and pasted it in to a QA (Query Analyzer) window, and the typed [CTRL]+E, the code would execut and give you this

OrderDate
---------------- ---- ----
1996-09-02 00:00:00.000 1 28
1996-09-03 00:00:00.000 2 27
1996-09-04 00:00:00.000 3 26
1996-09-05 00:00:00.000 4 25
1996-09-06 00:00:00.000 5 24
1996-09-09 00:00:00.000 8 21
1996-09-09 00:00:00.000 8 21
1996-09-10 00:00:00.000 9 20
1996-09-11 00:00:00.000 10 19
1996-09-12 00:00:00.000 11 18
1996-09-13 00:00:00.000 12 17
1996-09-16 00:00:00.000 15 14
1996-09-17 00:00:00.000 16 13
1996-09-18 00:00:00.000 17 12
1996-09-19 00:00:00.000 18 11
1996-09-20 00:00:00.000 19 10
1996-09-20 00:00:00.000 19 10
1996-09-23 00:00:00.000 22 7
1996-09-24 00:00:00.000 23 6
1996-09-25 00:00:00.000 24 5
1996-09-26 00:00:00.000 25 4
1996-09-27 00:00:00.000 26 3
1996-09-30 00:00:00.000 29 0

Which is the range of data from the sample database Northwinds Tables Orders...The USE Statement should have brought you there.

Isn't that what you want?|||OK Ok, thanks! It is working now in Northwind... :)

Some suggested I use a Stored Procedure for doing this...

""You'll want to create a Stored Procedure in the SQL Server and then pass it's result into a DataSet to use a the Source for your DataGrid.

Try a Stored Procedure like this, Changing table and field names appropriately:
Code:
------------------------

CREATE PROCEDURE sp_BetweenDates ( @.StartDate DATETIME, @.EndDate DATETIME ) AS SELECT * FROM YOURTABLENAMEHERE WHERE [YOURDATEFIELDNAMEHERE] Between @.StartDate And @.EndDate;GO
------------------------

Then you would just pass the two dates as parameters to the Stored Procedure.""

What do you think of something like this?|||Okay, i have contrusted this query that gives me what I want...

USE billing1SQL2
GO

SELECT Hours.Employ#, Hours.Purchord, Hours.Datewrk, Hours.Hourswrk, Hours.typewrk, Hours.formwrk, Hours.class, Hours.brate, PurchaseOrder.Descr, Employee.Lastname, Employee.Firstname, Employee.[Employ#] AS Expr1, Hours.[Ticket#], PurchaseOrder.Purchord AS Expr2 FROM Hours As Hours INNER JOIN PurchaseOrder As PurchaseOrder ON Hours.Purchord = PurchaseOrder.Purchord INNER JOIN Employee As Employee ON Hours.[Employ#] = Employee.[Employ#] WHERE Hours.Datewrk between '6/15/2004' And '6/28/2004' ORDER BY Hours.Datewrk, Employee.Lastname

I just need to figure out how to use it with VB.Net the best way. Suggestions?|||Long answer:
I would start from:
System.Data.SqlClient Namespace (help is available with VB.Net)
Then I would take a class or read a book about T-SQL
Same thing for SQL server.

Short answer:
Public Sub ReadMyData(myConnString As String)
Dim mySelectQuery As String = "SELECT OrderID, Customer FROM Orders"
Dim myConnection As New SqlConnection(myConnString)
Dim myCommand As New SqlCommand(mySelectQuery, myConnection)
myConnection.Open()
Dim myReader As SqlDataReader = myCommand.ExecuteReader()
Try
While myReader.Read()
Console.WriteLine((myReader.GetInt32(0).ToString & ", " & myReader.GetString(1)))
End While
Finally
' always call Close when done reading.
myReader.Close()
' always call Close when done reading.
myConnection.Close()
End Try
End Sub 'ReadMyData
(source: VB.Net help)

good luck!|||In your VB code, you can create your query like this

dim str1 as string

str1 = "exec sp_between_dates '" & Format(Text1.text, "mm/dd/yy") & _
"','" & Format(Text2.text, "mm/dd/yy") & "'"

Roshmi Choudhury|||Okay, thank you! soo I have this stored procedure:

CREATE PROCEDURE sp_BetweenDates (@.StartDate DATETIME, @.EndDate DATETIME)
AS
SELECT *
FROM Hours WHERE [DateWrk] Between @.StartDate And @.EndDate;
GO

..and this in VB.net...

Dim str1 As String

str1 = "exec sp_BetweenDates '" & Format(txtDate1.Text, "mm/dd/yy") & _
"','" & Format(txtDate2.Text, "mm/dd/yy") & "'"

Do I need to put my txtDates.text into a variable/string "StartDate" and "EndDate" and put that in my str call, like this... ??

str1 = "exec sp_BetweenDates '" & Format(StartDate, "mm/dd/yy")

I'm just unclear how the best way to get the actual date from my text box, to the stored procedure works...

Sunday, February 19, 2012

Display output parameters from stored procedure

Hi,

I have a report that uses a stored procedure. The stored procedure has 3 parameters 1 input and two output and also returns a resultset.

I have managed to hide the user prompt by changing the prompt for the two output parameters to an empty string.

The report runs fine and the user is not prompted for these two values, however the report does not show the values that are returned by these output parameters.

I could add extra fields to the resultset and not bother with the ouput parameters but this seems extremely inefficient as the resultset could contain several thousand rows and the fields would be the same for every single row.

Is it possible to display output paramater values?

Thanks In Advance

Chris

Chris,

have you thought of redesigning your stored procedure?

split it into two sp's, one containg the resultset, the second containg your output parameters, which should be redefined as "normal" parameters and brought back as a resultset via select @.parm1, @.parm2.

cheers,
Markus