|
As Jamie said, mistake 1 STORE YOUR DATES AS DATE TIME.
So now that your have screwed your data you have 2 choices.
Convert the dates from varchar to datetime - this is the recommended solution
Make all the date values into datetime in your query.
Select * from table name where convert(datetime,Stupiddate) between thisdate and thathdate
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your response that query is working for my condition.
Select * from tbl_ticketinfo where convert(datetime,issuedate) between '" 02/05/2009' and '02/09/2009'
|
|
|
|
|
select * from table where issueDate BETWEEN CONVERT(varchar, '2009-02-04', 111) AND CONVERT(varchar, '2009-02-11', 111)
|
|
|
|
|
I tried with this query
select * from table where issueDate BETWEEN CONVERT(varchar, '2009-02-04', 111) AND CONVERT(varchar, '2009-02-11', 111)
but when i execute that query i am getting zero results but there are records in my table with that dates. I stored date value in varchar is it correct or i have to use only datetime datatype.
|
|
|
|
|
Rupesh
Wrong way - you are converting to varchar and comparing strings, you need to convert to datetime to use between!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
How to connect to multiple databases (like SQL server, mysql, oracle) through Java code.
Thanks,
Nirmala
|
|
|
|
|
Hell all,
i have developed an application say inventory management in vb.net/ ms sql. now i want to install this application on different machines of my clients. i have written maximum code in Sp's and have fear that some one copy it or edit/delete it.
kindly suggest me a best way in which no one copy my database or edit/copy/delete it.
thanks in advance
rmshah
Developer
|
|
|
|
|
That's a big reason not to use stored procedures.
If it's your client's system, you can't lock them out from doing silly things to it, yet you will likely be responsible for repairing any damage they do.
At best you could have a written agreement that clearly states what happens when they screw up the system.
I've was only required to write stored procedures for one client, and it as dreadful. 
|
|
|
|
|
PIEBALDconsult wrote: That's a big reason not to use stored procedures.
Not really. The user can just as easily go in and update the tables without.
PIEBALDconsult wrote: At best you could have a written agreement that clearly states what happens when they screw up the system.
Thats a more common way.
At the end of the day, if the database is on your clients server, there is very little you can do to stop them screwing it up, so having a good, watertight, written agreement of responsibilities is probably the best way - if they are fully aware of the consequences then they probably won't.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Don't give database to the users. Keep it in a secured server. Create a web service/WCF service which provides access to this database from your application. This helps to keep database isolated and prevent users from modifying the SPs.
Another alternative(worst) what I think is to create the stored procedure with WITH ENCRYPTION clause. This will make the SPs encrypted, but there is no easy way to decrypt it back. Again, this will not prevent users from dropping it.
|
|
|
|
|
Hi evryone
I have some Oracle Question
1. I build DataBase on SQL server (Table's and view's) is it posible
to transfer to Oracle ? if yes - How ?
2. I build C# program that work's whit SQL server, I need to work
whit Oracle, is the code will fix ?
thank's for any help
|
|
|
|
|
E_Gold wrote: I build DataBase on SQL server (Table's and view's) is it posible
to transfer to Oracle ? if yes - How ?
If you use standard SQL format, I think you can copy and paste the queries. But creating views and tables in oracle and SQL server may have slightly different syntaxes. This will not work if your query has SQL server specific keywords (like top). Best way is to try it out.
E_Gold wrote: I build C# program that work's whit SQL server, I need to work
whit Oracle, is the code will fix ?
Program to interfaces, always. It makes database switching easier. Read about the design patterns like provider model, etc.
|
|
|
|
|
I have the perfomance problem with my query, it executes every time I click on grid paging.
Here is my ms sql query(its output of Nhibernate query):
WITH query AS (SELECT TOP 235800 ROW_NUMBER() OVER (order by
cast(upper((employee0_.lastname+' '+employee0_.firstname))as nvarchar)ASC ,
cast(upper((employee0_.lastname+', '+employee0_.firstname))as nvarchar)ASC) as __hibernate_row_nr__,
employee0_.primkey as x0_0_, employee0_.firstname as x1_0_, employee0_.lastname as x2_0_,
department1_.name as x3_0_, jobtitle2_.job_title as x4_0_, workarea3_.name as x5_0_,
position4_.position_ as x6_0_, employee0_.active as x7_0_ from otis.cs_employee_ employee0_
left outer join otis.cs_department department1_ on employee0_.dept_key=department1_.primkey
left outer join otis.cs_jobtitle jobtitle2_ on employee0_.job_key=jobtitle2_.primkey
left outer join otis.cs_workarea workarea3_ on employee0_.work_key=workarea3_.primkey
left outer join otis.cs_position position4_ on employee0_.pos_key=position4_.primkey
where (employee0_.site_code!='00' )
order by cast(upper((employee0_.lastname+' '+employee0_.firstname))as nvarchar)ASC ,
cast(upper((employee0_.lastname+', '+employee0_.firstname))as nvarchar)ASC)
SELECT * FROM query WHERE __hibernate_row_nr__ > 235780 ORDER BY __hibernate_row_nr__
SELECT * FROM query WHERE __hibernate_row_nr__ > 235780 ORDER BY __hibernate_row_nr__
I have many rows in this table( more than 200 000), it executes very slow...
Would it help me if I create a stored procedure for this...or is there any other way?
|
|
|
|
|
Hi,
I'm not an SQL expert, however looking at your code I would add a field that holds
upper((employee0_.lastname+' '+employee0_.firstname))as nvarchar)ASC so it does not need recomputing all the time; and I would index it.
BTW: You may want to add some code to your app to make sure the new field tracks any changes to the fields it depends on.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
|
|
|
|
|
Thanks, I'll try 
|
|
|
|
|
dasha_pl wrote: Would it help me if I create a stored procedure for this
If you can rewrite the SQL (is that permitted?). It seems that you're fetching 20 rows by scanning massive amount of rows.
dasha_pl wrote: is there any other way
As Luc pointed out, indexing will also help. It would require a bit more info about the tables (amount of rows in each table, how many rows in otis.cs_employee_ have site_code != '00' etc.)
You could try adding indexes like:
- otis.cs_employee_ (site_code, dept_key)
- all primkey columns unless they are already created as primary keys or indexed in another way
- otis.cs_employee_ (lastname)
- otis.cs_employee_ (firstname)
But as a personal opinion, the query is so twisted that if you can, you should rewrite it.
|
|
|
|
|
Hi
I have a stored procedure that returns all the docs uploaded ordered by creation date. Now i want to change it like show a document with the id "1234" as always on top and the remaining results as today after to that document.
For example:
Today we have stored procedure that returns the result as:
Id Title CreationDate Lang
1pqr About obama 24/02/2009 en
xyz You and Me 10/01/2009
abc KnowHow 01/12/2008 de
12345 Yes And No 29/10/2008 de
Now I want the result like:
Id Title CreationDate Lang
xyz You and Me 10/01/2009
3prt Stadt 25/02/2009 sr
1pqr About obama 24/02/2009 en
abc KnowHow 01/12/2008 de
12345 Yes And No 29/10/2008 de
Does anyone have any one idea.
Any Help please......!
Thanks in advance
|
|
|
|
|
So, is the document with id '12345' distinct in some way to all the others?
If that's what you mean, you could create a temporary table, insert the 12345 record into it, and then insert the results of the existing query (minus the 12345) into it as well.
Then, just return the temporary table.
Regards,
Rob Philpott.
|
|
|
|
|
This could be done in several ways. One way is that you use a simple union statement. Something like:
SELECT 1, Id, Title, CreationDate, Lang
FROM TableName
WHERE Id = 12345
UNION ALL
SELECT 2, Id, Title, CreationDate, Lang
FROM TableName
WHERE Id <> 12345
ORDER BY 1
The WHERE clause would actually contain a relevant condition. I take it that you don't want to use id as a condition, but for example MAX(CreationDate) or something else. ALso the ORDER BY could have more columns on it.
|
|
|
|
|
SELECT Id, Title, CreationDate, Lang
FROM TableName
order by case when Id = 12345 then 0 else 1 end, CreationDate
Wout Louwers
|
|
|
|
|
Hi Guys 
I am working on a WinForm Project (VSS2008) with SQL Reporting Services (SQL Server 2005). I have multiple reports that need to be printed ad-hoc. So far, I have placed each report into a seperate windows form and render the report within this form. Once referenced, these forms can be called from anywhere in the project using the Form.ShowDialog() method. Once the form is rendered I call ReportViewer.PrintDialog() to pop up the print dialog. This all works fine. 
The Developement Specs asks for the form to be printed automatically.  That means then when the form is called, it must render and bypass the print dialog, and then print the report (.rdlc) automatically. I have been struggling with this for a while now.  I am still new to development and hope that someone can help me.
Basically, is there an easy way to programmatically trigger a Report Print (like clicking print on the Print Dialog) once the report has been rendered? 
Thanks is advance.
|
|
|
|
|
I think you could for example use Render[^] method on the report and export it as PDF-file and then print the file.
|
|
|
|
|
Still to many step. The thing is I'd like to automate as much of the process as possible...
What happens is:
-> the user clicks the SAVE button
----> the report is generated
----> the user clicks the PRINT Icon
----> the print dialog box opens
----> the user clicks the PRINT Button
----> the page is printed
----> the user closes the Invoice / Report
What would ideally happen is:
-> the user clicks the SAVE button
----> the report prints "silently"
-------> the report generates
-------> the report prints
-------> the report closes
I'm still really struggling. Thanks for the attempted help Mika. Any other ideas???
|
|
|
|
|
Seren Singh wrote: Still to many step
I'm not sure what are you referring to. Too many steps for the end-user?
Seren Singh wrote: What would ideally happen is:
-> the user clicks the SAVE button
----> the report prints "silently"
-------> the report generates
-------> the report prints
-------> the report closes
In the ideal situation, couldn't you just wire the Save-button click and then (in code):
- instantiate the report
- set report properties
- set datasources
- render it to (temporary) file
- print it
- (delete temporary file)
This way the user wouldn't have to do anything more than to press the Save-button. No extra UI shown along the way.
|
|
|
|
|
Thanks Mika, that sounds like a possible "alternate" solution. The report is rather detailed and has already been designed and built into a .rdlc file. The datasources and report properties are all included in this.
How would I render it to a temp file, print it and delete then it? I didn't know you could do that with SQL Reporting Services - but that's the point - continuously learn new things.
Thanks again 
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
