|
The 'EventDate' data must be of type date.
Then use an order by that converts to a formatted date truncated to the year and month. In Oracle I'd do it like so;
<br />
order by to_char(event_date,'YYYYMM')<br />
Chris Meech
I am Canadian. [heard in a local bar]
|
|
|
|
|
You need to create a datetime column and insert example: CAST('Nov 1 2007' as datetime) then order by that column
Dave Evans
|
|
|
|
|
I had experienced the same kind of problem in SQL Server 2000 too... do u mean that we need to insert a new datetime colum? I'm sorry I didnt quiet get what u said. can u plz give a more elaborate example on this.
thanks
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
I would make the EventDate a datetime datatype (You may already be doing this but I couldn't tell). You can then SELECT * From [table] ORDER BY EventDate DESC
Dave Evans
|
|
|
|
|
No no that would just sort the data w.r.t date. What I want is to sort the data w.r.t the month and year, ignoring the day part.
My idea now is to do something like that in oracle
select col1, to_char(mydate,'Month, YYYY') AS Month
FROM Tab1
Order by to_char(mydate,'mm'),to_char(mydate,'YYYY')
but is this expression a valid one in Oracle
"Order by to_char(mydate,'mm'),to_char(mydate,'YYYY')"
and how should we do it in SQL Server 2000
and what if we need to group according to the months and year as well
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Same thing just use ORDER BY DATEPART(mm,mydate), DATEPART(yyyy,mydate)
Dave Evans
|
|
|
|
|
OK Thats gr8 thanksa bundle 
Cheers...
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
I'm unable to open a recordset to an access 97 database. I can't even get an error to tell me why this is happening.
I have my connection object defined as follows
IMCDSN = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IMC\mdb\IMC.mdb;"
set session("IMCConn")=server.CreateObject("ADOdb.connection")
session("IMCConn").open IMCDSN
AnalysisDSN= "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\IMC\mdb\Analysis.mdb;"
set session("AnalysisConn")=server.CreateObject("ADOdb.connection")
session("AnalysisConn").open AnalysisDSN
I then have some code that calls ClientAddEdit as follows:
sub ClientAddEdit()
'The first two lines creates the recordset and creates the beginning line
'of the SQL statement.
set rsClient = server.CreateObject ("adodb.recordset")
sqlClient = "Select * from [Client Table]"
sqlClient = sqlClient & " Where [ClientID] = " & varClientID
rsClient.open sqlClient, session("IMCConn"), adOpenDynamic, adLockPessimistic, adCmdText
'Modify the data in the database.
rsClient("ClientDescription") = request.form("txtClientDescription")
rsClient ("ClientContact") = request.form("txtClientContact")
rsClient ("ClientPhone") = request.form("txtPhone")
rsClient ("ClientComments") = request.form("txtClientComments")
rsClient.Update
rsClient.close
End sub
I tried putting error handlers but I cant even get the error to display. I tried "on error goto somelabel" as well as on error resume next and including logic as follows
if Err.number <> 0 then
msgbox Err.description
end if
immediately after the rsClient.open statement. I get nothing. I'm started to believe that I should upgrade the database to access 2000 for it work.
This code currently works in a windows nt box that i assume has access 97 installed. Could it be the version of access that may be possible causing the problem here?
Any experience anybody has had with this would be of great help.
Senior .Net Engineer @ Miami, FL
|
|
|
|
|
If using Access 97-format, use Jet 3.51. Access 2000 and later use Jet 4.0.
|
|
|
|
|
Then this must be an access 2000 database. However it may be, the code works in an NT workstation. However in my windows 2000 box, it doesn't seem to work.
I keep getting:
The page cannot be displayed
There is a problem with the page you are trying to reach and it cannot be displayed.
Are there any special dll's that must be registered in order for ADO to properly work?
Anybody who's had any experience with getting ado recordsets to open against a conection with a state of 1 which indicates open. Please help.
Thanks.
Senior .Net Engineer @ Miami, FL
|
|
|
|
|
Hey guys, first post ever.
I have a module which is event driven which is an adapter to a biometrics device. Essentially it only reads the database, never writes.
Because of this, when I have to enroll a new ID, I must check first that the ID has not previously been used. So basically the class holds a datatable of all users and their biometric key.
My GUI which utilises this component requests the class perform an enrollment, and then returns the biometric information plus any anciliaries to a business logic class and updates the database. Now the quirk is this.
SQL update command to enter the biometric data is run.
Then, the biometric class performs a "fill" from the tableadapter AFTER the data is updated.
This "fill" does NOT contain the biometric data sent in the update that was run previously.
Does this sound like a bug? The biometric class, and the business logic in the GUI share nothing more than the parameters for the SQLConnection, nothing else. If I close the GUI and then reinitialise the biometric class, it then recieves the updated biometric information.
SQL Server 2005 c#. I know the next question is to see code, but I'm afraid that is something I'm not able to do. Perhaps it will make it clear that if the user attempts an identification check and fails, this triggers the biometric class to run a "fill" on the data.
Thanks in advance guys, honestly any bone you can throw me will be worth it if it helps.
|
|
|
|
|
Weird!! thats really weird!
SInce you can't show the code, its really difficult to say anything for sure. but may be there is some problem with ur code when it refreshes the data in whatever control your using to display the data. Perhaps try emptying the datatable and then filling it again after an update opration and see what it does
Rocky
You can't climb up a ladder with your hands in your pockets.
|
|
|
|
|
Hi
I have to upload PDF files in database. I am uploading PDFs as stream and inserting into a column of varbinary(max) type. A column also to store file type.
So my database Table looks like
docs(PDFStream,filetype).
Here "PDFStream" is varbinary(max)
"filetype" is varchar(10)
I have the Full Text Index created on the table "docs".
my problem is when I execute the following
SELECT PDFStream
FROM Docs
WHERE FREETEXT (PDFStream, 'EQUIPMENT')
Does not fetch any result. But I have uploaded the PDF file with a line '........EQUIPMENT SALE AGREEMENT ......' ( dots here are just indicates other texts )
Any idea where I am going wrong ???
Any help will be appreciated.
Thanks,
Arindam D Tewary
|
|
|
|
|
I would guess that WHERE FREETEXT (PDFStream, 'EQUIPMENT') does not work because of the encoding maybe. Does WHERE FREETEXT (CAST(PDFStream as nvarchar(max)), 'EQUIPMENT') work.
//I have never used varbinary and freetext so this advice comes with no warranty.
Dave Evans
|
|
|
|
|
Hi,
I am updating (not inserting) certain Excel sheet data to my SQL Server database. So I have Excel as source file & SQLServer 2000 as my destiination.
In the Transformation tab of "Transform Data Task" properties window I have written my customised logic to update excel sheet data to a particular table.
When I test my logic (by clicking the Test button of ActiveXScript window of "Transform Data Task"), the data gets updated without any error. But when I try executing this package, it gives me following error : "Cannot insert NULL value in column 'TestID', table 'TestTable'; column doesnot allow NULLS. Insert fails".
I really cant get hold of this weird DTS error as I am just updating a particular column of a particular table.
Additional Information:
1) 'TestID' column's Identity property has been set to No
2) There is no trigger written for that particular table
Kindly let me wat might be the reason behind this problem
Regards,
Vipul Mehta
Sr. Software Engineer
Accenture Services Pvt Ltd
|
|
|
|
|
Hi,
Further to my issue I found out that if I update 100 records from excel sheet to SQL DB table (by writing customise logic in ActiveXScript), after updating the proper fields it then inserts that 100 records in to that table.
Why does that try to insert again all the 100 records of my excel sheet to the SQL table?
Regards,
Vipul Mehta
Sr. Software Engineer
Accenture Services Pvt Ltd
|
|
|
|
|
i have username and password fields in my table,
i jus want to write a query to check whether the given details of user matches with the database.
please gimme the query
|
|
|
|
|
Something like (this may not be the best way, but a quick answer to say the least):
SELECT * FROM <TABLENAME> WHERE USER = 'JOHN' AND PASS = 'MYPASS'
Regards,
The only programmers that are better that C programmers are those who code in 1's and 0's ![Green Alien | [Alien]](https://www.codeproject.com/script/Forums/Images/alien.gif)
Programm3r
My Blog: ^_^
|
|
|
|
|
not like that........
wat i mean to say is.......
i have alogin page where user enters his/her username and password
i have to check the data with database and redirect the user to corresponding next page for the i need a query.
|
|
|
|
|
bhattiprolu wrote: i have to check the data with database and redirect the user to corresponding next page for the i need a query
I cant see what the problem is, because after the select statement you will have all data specific to that user, so redirecting the user to the correct page should be a breeze. Just pass the information of the user to the next page. Unless I'm totlally mis-understanding you.
Regards,
The only programmers that are better that C programmers are those who code in 1's and 0's
Programm3r
My Blog: ^_^
|
|
|
|
|
can u gimme the query.....
|
|
|
|
|
HE DID!!
He gave you this:
SELECT * FROM <TABLENAME> WHERE USER = 'JOHN' AND PASS = 'MYPASS'
But since you've told us nothing about your database structure it may not be correct.
If you want more help you'll have to help us understand your problem. Repeating the same statements over and over again do not help. You need to give us more information.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
bhattiprolu wrote: not like that........
Your original post said:
i have username and password fields in my table,
i jus want to write a query to check whether the given details of user matches with the database.
please gimme the query
What other answer could you expect from that?!
bhattiprolu wrote: i have to check the data with database and redirect the user to corresponding next page for the i need a query.
You mentioned nothing about login pages or redirecting users. How were we supposed to know, especially since you posted this in the "SQL" forum?
If you've got the data and youre question is about ASP.NET post in the appropriate forum.
-- Always write code as if the maintenance programmer were an axe murderer who knows where you live.
Upcoming FREE developer events:
* Glasgow: Agile in the Enterprise Vs. ISVs, Mock Objects, SQL Server CLR Integration, Reporting Services, db4o ...
* Reading: SQL Bits
My website
|
|
|
|
|
SELECT * FROM <tablename> WHERE USER = 'JOHN' AND PASS = 'MYPASS'
this is the write query...
now if u r using c# or vb.net
get result of this query in datareader and dataadaptor..
if any row returns that means username and password is correct... then redirect to the other page..
|
|
|
|
|
They Already gave u the right Query..if u're try to implement that query in your
apps just change the where value, ex :
<br />
Select * From [my_table] WHERE username = '" +UserText.Text+ "' AND password = '" +PasswdText.Text+ "'<br />
wat else do u need they gave u the right query u need
Regards,
Tomi
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
