|
I have a table i am querying, and a prety odd set i need to return.
the columns i am interested in are the following types:
int, nvarchar, datetime,datetime.
the int has a pretty normal progression, 1,2,3,4,5. the nvarchar contains only 2 possible values, we'll say red and blue. now, the data lines up something like this:
1 blue
2 blue
1 red
3 blue
2 red
3 red
ie, no particular order, but every number listed in the int coulmn has a red and a blue row, so a normal query would return
1 blue
2 blue
3 blue
1 red
2 red
3 red
if ordering by the colors
now, the two date times are a strat date and end date, and they line up weird. the blue and red of each int do not have the same range, but are considered related. however, all of the dates for a color tie together, so if the enddate for 1 blue is 4/25/2008, then the startdate for 2 blue is 4/26/2008. same goes for red, except what i end up with is something like this
1 blue 2/23/2008 3/12/2008
1 red 2/25/2008 3/20/2008
what i need to do is write a query that returns only 2 values, where they are both less than todays date, one is blue and one is red, and they both have the same int value.
now, the query i am using looks like this:
select top 2 * <br />
from [table] where enddate < cast('4/15/2008 12:59:59.999' as datetime)<br />
order by enddate desc
this particular one is right on the cusp of one of the transition dates, and the result i am getting is:
1 red
2 blue
when i need
1 red
1 blue
or
2 red
2 blue.
the int must always be the same and the nvarchar must always be different. is there a way to force this in a query?
what i end up needing is a query that returns
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
Change your order by to include the number field as the first portion before the enddate, e.g. order by id, enddate.
|
|
|
|
|
Didn't actually try running this, but you can give it a whirl:
select top 2 a.* from [table] a
INNER JOIN [table] b ON a.int = b.int
AND a.nvarchar <> b.nvarchar
AND b.enddate < cast('4/15/2008 12:59:59.999' as datetime)
where a.enddate < cast('4/15/2008 12:59:59.999' as datetime)
order by enddate desc
|
|
|
|
|
your code is a thing of beauty 
i modified it to run against the table i was using and was getting the same results i was getting before, whihc confused me because looking at it, it definatley said exaclty what i needed. so i opened a new view and plugged it in and started playing with it. the first thing i did was change it to show the results from a and b, and bingo, there it was. with this, i dont need the top 2 rows, just the top 1 with the nd date set to order by desc, and tada, i had the exact results i was looking for. so here is what i used:
SELECT TOP (1) a.nvarchar, b.nvarchar AS nvarchar2, b.int, a.int AS int2, a.StartDate, b.StartDate AS StartDate2, <br />
a.EndDate, b.EndDate AS EndDate2<br />
FROM [table] AS a INNER JOIN<br />
[table] AS b ON a.int = b.int AND a.nvarchar <> b.nvarchar AND <br />
b.EndDate < getdate() AND a.EndDate < GETDATE() <br />
ORDER BY int2 DESC
Thank you very much for this, you saved me a massive headache. And thank you to everyone that replied, i appreciate any input 
______________________
Mr Griffin, eleventy billion is not a number...
|
|
|
|
|
How can I avoid the error:?
-------------------------------------------------------------------------------------------------------
Run-time error '2749':
There isn't enough memory to complete the Automation object operation in the OLE object.
--------------------------------------------------------------------------------------------------------
Here is the code:
--------------------------------------------------------------------------------------------------------
Private Sub cmdLoadOLE_Click()<br />
<br />
Dim MyFolder As String<br />
Dim MyExt As String<br />
Dim MyPath As String<br />
Dim MyFile As String<br />
<br />
MyFolder = Me.SearchFolder__<br />
' Get the search path.<br />
MyPath = MyFolder & "\" & "*.jpg"<br />
' Get the first file in the path containing the file extension.<br />
MyFile = Dir(MyPath, vbNormal)<br />
<br />
Do While Len(MyFile) <> 0<br />
<br />
'[fldID] = CInt("4869")<br />
[fldID] = CInt(Replace(MyFile, ".jpg", ""))<br />
[fldOLE].Class = "MSPhotoEd.3"<br />
[fldOLE].OLETypeAllowed = acOLEEmbedded<br />
[fldOLE].SourceDoc = MyFolder & "\" & MyFile<br />
[fldOLE].Action = acOLECreateEmbed<br />
[fldOLE].Action = acOLEClose<br />
<br />
' Check for next OLE file in the folder.<br />
MyFile = Dir<br />
<br />
' For Access 97 only, use the following line of code:<br />
DoCmd.RunCommand acCmdRecordsGoToNew<br />
<br />
Loop<br />
<br />
End Sub
--------------------------------------------------------------------------------------------------------
Thanks in advance
|
|
|
|
|
Hi i am from the field of java and new in asp.net, can anybody explain me how can i execute multiple queries , means i am executing 5 queries in a line if any of query fails the transaction should rollback and if all 5 queries executed successfully then commit action should perform.
|
|
|
|
|
First of all, I suggest you read what the forum guidelines say about post titles. 'newbee ado.net' gives us no idea what your question is. Your question relates to ado.net database transactions and your title should reflect this.
Have a look at this CodeProject article about ADO.NET Transactions[^].
Paul Marfleet
"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush
|
|
|
|
|
I am working on SQL Server 2005 and I have a doubt. Clinet application is in VC ++ and access SQL Server through ODBC API. If client application starts a transaction and locks some rows in a table and is terminated abnormally (crashed). Will SQL Server automatically rollback all those updates made by the client and release the locks on rows? If SQL Server is not handling this situation, how to handle it? .Please respond to my query.
Thanks in advance
modified on Tuesday, March 25, 2008 12:04 AM
|
|
|
|
|
|
thanx Mark, for the reply.
|
|
|
|
|
A few points.
1. It's very, very rude to state that it's urgent. It is up to us to decide if and when we want to reply.
2. You could have tested this yourself with a minimum of effort. Why didn't you? Don't expect others to do your thinking for you - you should actually try and solve problems for yourself.
3. Putting your email address in a post is just asking to be spammed.
|
|
|
|
|
I m sorry for that,actually i am new to codeproject so i dont have much awareness regarding the rules and regulation.
In future,I will take care of the things.
anyways thanks for replying...
|
|
|
|
|
Fair enough. Since you are new to the boards, this[^] article might help you get to know the niceties of posting on Code Project.
|
|
|
|
|
HI,
In my database i am storing the date of birth of each user, but how can i create a query which give date of births of all user whose birthday month
falls in the current month.
that means if current month is march then the query should fetch the records of all users which are born in march.
|
|
|
|
|
Suppose
create table TestBirth
(
UserId int identity(1,1)
,UserName varchar(50)
,birthday datetime
)
is the table which contains the records
UserId UserName birthday
1 Manoj Singh 2008-02-02 00:00:00.000
2 Manoj singh2 1894-07-03 00:00:00.000
4 Anshu singh 1894-07-04 00:00:00.000
5 Garima singh 1894-07-13 00:00:00.000
6 Rajan singh 1894-07-14 00:00:00.000
7 Garima chaudhary 1894-07-03 00:00:00.000
8 Pankaj chaudhary 2008-03-03 00:00:00.000
Below query will help you
Select UserName from testbirth where Month(birthday) = month(getdate())
Sarvesh Upadhyay
Senior Software Engineer
Birlasoft India Ltd.
Microsoft Certified Professional Developer in Dotnet 2.0 Enterprise Application
|
|
|
|
|
SELECT * from tablename where (@Month = month(Date) and @year = year(Date))
try this and give me feedback
Ahmed hassan
|
|
|
|
|
Hi
I have 2 tables:
1. Physicians, consisting on two columns:
primery_specialty_id, sub_specialty_id
2. Specialties, consisting on two columns:
specialty_id (primary key and the foreign key for both Physicians table columns above), specialty_name
Physicians.primery_specialty_id is nn but sub_specialty_id can be null and in my db there are a lot of physicians that have 1 specialty but not a second.
I want to create a view that will retrieve all data showing the specialty_name1 for all primery_specialty_id and specialty_name2 for all sub_specialty_id.
I try to create another view that will hold the specialty_name for the sub_specialty but I don’t know how can I show all data containing the sub_specialty which is null.
if anyone can help me on this it will be more than appreciated
Thanks
Yohay
|
|
|
|
|
I am using SQL Server EXPRESS Edition for my C# client. I have written an SP InsertData for inserting data into a table. Another SP GETDATA fetch all the data from the table.
Now the problem is with my C# client application. On the main Form I have placed a grid which is filled by calling GETDATA stored procedure. There are text fields and an ADD button on the main form. When I click add button I call InsertData SP and then in the same event handler I call GETDATA SP again to refresh my grid. It works well and show data in grid but later when I check the database no entry is inserted in the database. Again I run the application and database is in the previous state (i.e. no data is found that was inserted in the previous run). In other words data is shown to be inserted but is not actually inserted in the database.
What could be the problem?
For refernce I have checked msdn where someone has posted the same problem here. http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2888935&SiteID=1[^]
but no satisfactory answer found up till now.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Have you tried to run InserData via query analyzer ? Put break points and step into the code.
|
|
|
|
|
Yes I have tried the procedure in query analyser and it inserts data successfully. I have added 4 records in tabale using this SP but and everytime I start application, it starts from ID = 5 (ID is IDENTITY column).
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Just a lil doubt, are you explicitly opening transactions?
If yes, you'll have to commit your transactions by using the COMMIT keyword everytime you insert a row. By default AUTO COMMIT is ON in SQL Server. Check your connection properties to see if its turned off somewhere.
SG
Cause is effect concealed. Effect is cause revealed.
modified on Monday, March 24, 2008 5:39 AM
|
|
|
|
|
No I am not using this statment and I have also checked SqlCommand object's propertys using it like this com.Transaction.Commit(); function but it is not working. 
I think it is the problem of SQLEXPRESS that may does not allow inserting or updating by default. If anyone give any idea about it, I shall be thankful.
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Check your connection string/properties. Are you connecting to SQL Express as 'User Instance = True' ?
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
|
Yes this property is set to true as 'User Instance = True'.
I think it might be some limitation of SQL EXpress but uptil now I haven't come to know about any such limitation.
Same problem also posted here by someone.
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=2555908&SiteID=17[^]
Mujtaba
"If both of us are having one apple each and we exchange it, at the end we both will have one apple each. BUT if both of us are having one idea each and we exchange it, at the end both of us will be having two ideas each."
|
|
|
|
|
Make sure your connections string says something like:
Database='Path&FileName';...;User Instance = False
I don't speak Idiot - please talk slowly and clearly
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
