|
Hi everybody,
I like to asked anyone who is very good at sql to help me display this data in a table format that duplicate headings, subheadings or other columns won't appear twice or more. Can anyone help me? I need to sort them and load them in a Datatable. Loop thorugh all records and display them in a label control created at run time. Thanks in advance.
I have posted this topic on other forums, some solutions given but they are using approach for .Net Framework 2.0, I am using .Net Framework 1.1. I am trying to avoid using a lot of select Distinct sql queries to database and looping the data to be able to remove duplicates. Do anyone knows another approach using less code and less sql access?
Sample Data:
Topics Table
TopicId TopicLevel Topic ParentId
1 1 First 0
2 1 AnotherFirst 0
3 2 Second 1
4 2 AnotherSecond 2
5 3 Third 3
Contents Table
ContentID Heading SubHeading SubSubHeading1 NumberText Names Content1 ParentId
1 How are you? Hello Someone 1 Ben some text 5
2 How are you? Hello Someone 1 John other text 5
3 How are you? Hello Anybody 2 Ben some text 5
4 How are you? Hello Anybody 2 Mike other text 5
5 How are you? Greet Anywhere 1 Ben some text 5
6 How are you? Greet Anywhere 1 Luke some text 5
7 I miss you. Really When null null some text 5
8 I miss you. Really When null null some text 5
Display Format:
How are you?
----------------------------------------------
Hello
-----------------------------------------------
1 Someone
----------------------------------------------
Ben some text
---------------------------------------------
John other text
---------------------------------------------
2 Anybody
---------------------------------------------
Ben some text
---------------------------------------------
Mike other text
--------------------------------------------
Greet
-------------------------------------------------
1 Anywhere
-----------------------------------
Ben some text
-------------------------------------
Luke some text
================================================
I miss you
--------------------------------------------------
Really
------------------------------------------------
When
-----------------------------------------------
some text
----------------------------------------
some text
------------------------------------------
denpsia
-- modified at 19:33 Wednesday 21st December, 2005
|
|
|
|
|
|
Thanks for reply, toxcct. I know that but to do this would requires a lot sql Select Distinct queries to filter each column and loop arround results to get next distinct columns and display them at run time.
denpsia
-- modified at 19:09 Thursday 22nd December, 2005
|
|
|
|
|
if you have a lot of distinct to put, it is because the sql query is not well written...
you have to set to correct restrictions in the where clause.
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
|
|
|
|
|
Toxcct,
I know that, just asking if there are much simpler and shorter way to do this. Thanks for reply.
denpsia
|
|
|
|
|
Hi i need a query that can manipulate i.e count records on hour basis from a table depending upon time_in field....
|
|
|
|
|
You are going to have to be more specific.
You can do a count with something like this:
SELECT COUNT(*) FROM MyTable
My: Blog | Photos
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
|
|
|
|
|
How do you write a query that horizontally merges records from different tables? For example, say I have Person, Phone, and Email tables where Phone and Email have foreign key relationships to the Person table, and store 0 to n email and phone records for each person record.
The result query should include the person's name, all email addresses and all phone numbers, and the email addresses and phone numbers should be listed "side by side". To illustrate in CSV format;
--------------------------------------------------------------
LName, FName, EmailAddress, PhoneNumber
Jones, Sue, sjones@nowhere.com, 123-555-0203
Jones, Sue, sj@somewhere.com, 098-555-2382
Jones, Sue, SueJones@AspenState.com, NULL
Smith, Joe, jsmith@nowhere.com, 123-555-0202
Smith, Joe, js@home.com, 098-555-2382
Smith, Joe, NULL, 230-555-3298
Smith, Joe, NULL, 234-555-0982
Washington, Peter, NULL, NULL
...
--------------------------------------------------------------
In this example Sue has more email records than phone records, so the third record in the result set is NULL for the PhoneNumber field. Similarly, Joe has more email records than phone records so rows 6 and 7 have NULL for the EmailAddress field. Finally Peter has no email or phone records so both PhoneNumber and EmailAddress are NULL.
Any ideas? Thanks
|
|
|
|
|
Hi djsdjsdjsdjs,
Have you tried using DataRelations in DataTable or Joining tables? Just a thought.
denpsia
-- modified at 19:33 Wednesday 21st December, 2005
|
|
|
|
|
While this data will go into an ADO.Net Dataset I'm mainly concerned in how to write the SQL query that gets the data into a single row set that I can use to fill a Dataset.
|
|
|
|
|
if you have this schema :
table1 : table2 :
------------- --------------
col1 <-----\ col1
col2 \ col2
\---- col_tab1_id
then you can "merge horizontally" the two tables like this :
SELECT t1.col1,
t1.col2,
t2.col1,
t2.col2
FROM table1 t1,
table2 t2
WHERE (t1.col1 = t2.col_tab1_id);
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:19 Thursday 22nd December, 2005
|
|
|
|
|
This query produces the Cartisian product of the Email and Phone tables and doesn't have any relation to the Person table. These kinds of joins produce every possible combination of rows from the joined tables, but I don't want every possible combination.
|
|
|
|
|
but you add the where clause that condition your query as needed !
i did not read the first message entierly.
ok, just change the query, adding this :
... were Table1.id = Table2.tab1_id;
(or something like this)... d'you follow me ?
[edit]
i modified my first answer... i think it is now as you want.
[/edit]
TOXCCT >>> GEII power
[toxcct][VisualCalc 2.20][VisualCalc 3.0]
-- modified at 10:22 Thursday 22nd December, 2005
|
|
|
|
|
i've created a new database with 2 table in microsoft access 2003 (.mdb)
I've created a query too, embedded in the .mdb file.
Can i use directly this query in ADO.NET avoiding to write SQL comand in c# code?
|
|
|
|
|
Yes u can. You can refer to this query as the store procedure in C# and call at as you are calling a store procedure.
Akif
|
|
|
|
|
Does anyone know how to use just the time portion of a DateTime object when calling an SQL Query (using SQL Server). I have a field in the database that stores only the Time, and want to do a select based on this field, but it forces me to match the Date portion of the field or it will not work. So, as simple example, if wanted to get all records with time of 9:30am, would use a Command something like:
<br />
DateTime timeToGet = new DateTime(1900,1,1,9,30,0);<br />
SqlCommand command = new SqlCommand();<br />
command.CommandText = "Select * from MyTable where MyTime = @TimeParam";<br />
SqlParameter parameter = new SqlParameter();<br />
parameter.ParameterName = "TimeParam";<br />
parameter.SqlDbType = SqlDbType.DateTime;<br />
parameter.Value = timeToGet;<br />
command.Parameters.Add(parameter);<br />
This will work fine as long as Date portion of field in database = 1/1/1900. But, if date portion is something else then will not find record.
Maybe it would work if could pass it a Time-Only object (instead of DateTime object) but not sure if object like that exists, and if did, not sure parameter object could handle it.
If anyone has any ideas, please let me know.
Thanks,
BK
|
|
|
|
|
SELECT * FROM mytable WHERE CONVERT(CHAR(8), mytime, 8) = CONVERT(CHAR(8), @TimeParam, 8)
|
|
|
|
|
Hi..
I want to put a page in asp ( or if not possible I could also use dot.net) to my server that will report about instant number of
> open database connection object number
> open recordset object number
> connected user number (not by counting from global.asa)
> ram and cpu used by iis...
and any usefull data like above..
where to start
karanba
|
|
|
|
|
can i use truted connection with microsoft access database?
that is avoid to specified password and id value in the connection string?
can i use directive with .mdb?
i use c# language and ado.net
|
|
|
|
|
Ofcourse you can use that !
R A M
|
|
|
|
|
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =MyNothing;"
|
|
|
|
|
sorry i mean connect to a password protected .mdb, but avoid to specified in the connection string the password used.
In SQL server i can do it but in .mdb access?
|
|
|
|
|
Sasuki,
Infact you have to populate the Password screen yourself in Access you can use this
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =" & Text1.Text & ";"
OR
string strConnect = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyNothing.MDB;Jet OLEDB:Database Password =" & InputBox$("Enter password") & ";"
R A M
|
|
|
|
|
I've recently completed a Pocket PC application in VB.NET that works with connections to a local SQL Server 2000 CE DB and a SQL Server 2000.
I'd like to look at doing the same thing using eMbedded Visual C++ 4.0 and OLE DB for a CE.NET device. While there are plenty of resources for SQL CE I can't find any for SQL Server 2000.
Does anyone know of a good sample for eVC++ that hits both SQL CE and SQL Server? No ActiveSync and no IIS for this solution thus RDA and Merge Repl are of no value.
Any input is much appreciated!
|
|
|
|
|
Can SQL Server views be constructed to take parameters?
Some assembly required.
Code-frog System Architects, Inc.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
