|
Without knowing anything about your application, including what language it is in and what type of application it is, how can I? Anyway, 2 minutes on google should give you plenty of examples, its a common enough thing to do.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
i have mention my tech. what i am using for you'r knowledge i am using sql server v2005
and VS 2005 .
|
|
|
|
|
OK, I had forgotten. Anyway, just google for datatable, dataadaptor and filter and you should be sorted, ther are hundreds of examples available.
Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP
|
|
|
|
|
Get all the data from the database once and store into DATASET.
Next by the help of DATAVIEW( http://www.csharp-examples.net/dataview-rowfilter/[^] ) you can filter the records.
You don't have to make N-number of round trips for that.
Hope this helps.
Niladri Biswas
|
|
|
|
|
I'm a bit of an SQL novice, so I have a feeling this is easy, but I can't figure it out and I'm Googled out trying to find the answer.
I have a query:
SELECT CONTACT.FIRSTNAME, CONTACT.LASTNAME, CONTACT.COMPANYNAME, CONTACT.USER2, EMAIL.ADDRESS
FROM dbo.CONTACT CONTACT, dbo.EMAIL EMAIL
WHERE CONTACT.CONTACTID = EMAIL.CONTACTID
ORDER BY CONTACT.LASTNAME DESC
That's fine, but contacts may have more than one email. So I might get, the following records as a result:
Ann Onymous Initech v7.35 ann.onymous@initech.com
Ann Onymous Initech v7.35 a.onymous@initech.com
Ann Onymous Initech v7.35 onymousa@initech.com
If I use Count(EMAIL.ADDRESS) I'd get 3 for Ann.
So how do I tell SQL to just get me the first email record and not the other two? e.g.
Ann Onymous Initech v7.35 ann.onymous@initech.com
I'm pretty sure its simple...but..so am I! 
|
|
|
|
|
Like this:
SELECT CONTACT.FIRSTNAME, CONTACT.LASTNAME, CONTACT.COMPANYNAME, CONTACT.USER2, first(EMAIL.ADDRESS)
FROM dbo.CONTACT CONTACT
inner join dbo.EMAIL EMAIL on CONTACT.CONTACTID = EMAIL.CONTACTID
GROUP BY CONTACT.FIRSTNAME, CONTACT.LASTNAME, CONTACT.COMPANYNAME, CONTACT.USER2
ORDER BY CONTACT.LASTNAME DESC
|
|
|
|
|
I believe First() is an MSAccess function which in such a situation cannot be used when the DB is Sql Server
Niladri Biswas
|
|
|
|
|
You're right I'm querying SQL Server and First() doesn't work.
|
|
|
|
|
I don't know whether you are testing others or not but I guess you are so because you statement reveals that.
Any way, use the Top 1 statement of sql server to obtain the same.
SELECT TOP 1 CONTACT.FIRSTNAME, CONTACT.LASTNAME, CONTACT.COMPANYNAME, CONTACT.USER2, EMAIL.ADDRESS
FROM dbo.CONTACT CONTACT, dbo.EMAIL EMAIL
WHERE CONTACT.CONTACTID = EMAIL.CONTACTID
ORDER BY CONTACT.LASTNAME DESC
Hope this helps
Niladri Biswas
|
|
|
|
|
Thanks for the suggestion but that only gets a single record, I need the record for each customer, but only once with the first email address (which is the primary address).
Min and Max 'work' in that they only return one record per customer, but the result is not reliably the first email address.
|
|
|
|
|
Divide your query into multiple smaller subqueries, and the problem is easier to tackle. Assuming SQL2005;
SELECT *,
(SELECT TOP 1 dbo.EMAIL.Address
FROM dbo.EMAIL
WHERE dbo.EMAIL.ContactId = dbo.CONTACT.ContactId)
FROM dbo.CONTACT
I are troll
|
|
|
|
|
Thanks, that worked.
I knew it would be simple!
|
|
|
|
|
$request2 = mysql_query("SELECT * FROM tablename WHERE status IN('In Progress','Pending') ORDER BY FIND_IN_SET('urgent', priority) DESC, FIND_IN_SET('high', priority) DESC, priority DESC, status DESC") or die(mysql_error());
Above is my query. I want the query to show me all the cases where the status is "In Progress" or "Pending" then order them by the priority (urgent, high, mid, low) in that order, then order them DESC by status.
Example:
Case 1, urgent, Pending
Case 2, urgent, In Progress
Case 3, high, Pending
Case 4, high, In Progress
...
For some reason though, random cases aren't displayed in my table. Everything works fine if I leave out the ORDER BY section, but for some reason when ordering is added it stops working. Also, the mid and low sections always seem to show up, but random high and urgent cases do not. I don't understand what is wrong. Can someone please help? I'll gladly provide more information if needed.
|
|
|
|
|
You could show us an example of some of the records that are not getting displayed.
Check to see if nulls are getting in the way.
Another thing you can do is take the order by clause and turn it into part of your select statement output to see what the computer is seeing for those seemingly random records.
Something like this, although you might have to clean up the syntax to get it to run.
$request2 = mysql_query("SELECT tablename.*, FIND_IN_SET('urgent', priority), FIND_IN_SET('high', priority), priority, status FROM tablename WHERE status IN('In Progress','Pending') ") or die(mysql_error());
This way you might have a clearer view of why a given record gets dropped.
_____________________________
Give a man a mug, he drinks for a day
Teach a man to mug... -Scott M.
|
|
|
|
|
Thanks for the reply, I'll try that today if I have time. None of my fields allow nulls so I don't think that is the problem.
Now here's something interesting. I put the same values in my HTML form on the previous page (the one that enters them into the database) and submitted, then it shows up in the list, where the first one with the same values does not. I went into MySQL itself and can't find anything wrong with the data...it seems exactly the same in both places. Now one was made before the sorting query was setup and one was made after...would that somehow make a difference? I wouldn't think so since it's just a query...but might as well cover all possibilities.
|
|
|
|
|
Hi all,
I've created a website the is using SQL 2000/SQL2005 as the back end and I've been tasked to take the catalog I've build for the website and allow it to be ran on a CD. So With the CD version of the website I'm using a Access 2003 database, but for some reason my T-SQL queries wont work with Access. I have a bit field in my SQL database that I use as an Active flag. Access use a "Yes/No" field for this and I'm not sure why the query isn't working. I did notice that Access uses -1/0 and not 1/0, so I changed my query to reflect this:
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE (Active) = -1)
Is there any reason why this wouldn't work?
|
|
|
|
|
I see two closing parenthesis )) and only 1 open parenthesis. (
|
|
|
|
|
*slaps forehead* I should have seen that. But changing it to this doesn't work either. I tried all of the following and none seem to work.
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE ((Active) = -1)
or
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE ((Active) = TRUE)
or
SELECT *
FROM tblWeb_E_Catalog_Categories
WHERE (Active = TRUE)
|
|
|
|
|
Hi,
... WHERE NOT (Active = 0) might work for both database systems.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
Holy smokes Batman... it worked! Thanks!!!
|
|
|
|
|
You're welcome.
Luc Pattyn [Forum Guidelines] [My Articles]
DISCLAIMER: this message may have been modified by others; it may no longer reflect what I intended, and may contain bad advice; use at your own risk and with extreme care.
|
|
|
|
|
i develop my application backend on ms sql 2008. but my client is using 2000. what is the best may to go.should i script all the database object( tables, storeprocedures .e.t.c)
|
|
|
|
|
Ebube wrote: i develop my application backend on ms sql 2008. but my client is using 2000
This begs the question why you didn't find this out at the start.
Ebube wrote: should i script all the database object( tables, storeprocedures .e.t.c)
You can get scripts generated automatically, however, you'll still have a lot of editing to do because there are lots of minor differences between the SQL used in SQL Server 2000 and the SQL used in SQL Server 2008.
|
|
|
|
|
Hi,
How many primary key constraints can we have in a table of ms-access? Any other constraints available in ms-access other than primary key?
Please share your ideas...
Thanks,
Sivakumar.M.
|
|
|
|
|
One table, one primary key. You can verify this in the documentation[^];
"You can use the PRIMARY KEY reserved words to designate one field or set of fields in a table as a primary key. All values in the primary key must be unique and not Null, and there can be only one primary key for a table."
I are troll
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
