|
Hierarchical queries I guess is supported by Oracle and MS SQL 2005, I am not sure if MS SQL 2000 supports it. You could just google for hierarchical queries and you have lot of articles explaining how to use it.
Tarakeshwar Reddy
MCP, CCIE Q(R&S)
Experience is like a comb that life gives you when you are bald - Navjot Singh Sidhu
|
|
|
|
|
Hi,
I have a DataGrid which display a DataTable from MySQL Table source.
in the DataGrid, there are these columns (from table ItemList):
ItemID, PeriodID, Debits, Credits
column PeriodID is a smallint foreign key which connect to table PeriodList.
these are PeriodList's column:
PeriodID, PeriodName
my question is simple, in DataGrid, how can I display PeriodName instead of PeriodID ? so my Datagrid columns will be like this:
ItemID, PeriodName, Debits, Credits
Thx.
|
|
|
|
|
You can simply not select the PeriodID when you are filling your grid by using a SELECT statement like this:
SELECT ItemID, PeriodName, Debits, Credits From ItemList, PeriodList WHERE ItemList.PeriodID = PeriodList.PeriodID
But since you may need to reference the PeriodID from the column, you may want to keep selecting it but hide it. To do this you need to set the visiblity of the column to false. If you are in a PC project you can just do it like this:
dgMyDataGrid.Columns("PeriodID").Visible = False
If you are in a web project you would probably want put code like this in the grid's RowDataBound event:
e.Row.Cells(1).Visible = False
(Where 1 is the index of the PeriodID column)
Hope this helps.
|
|
|
|
|
I can't use your solution. Because it means I have to query MySQL Database. My program has been designed to only query MySQL once every 15 minutes. And every process (select, insert, update, delete) will be involving DataTable in DataSet and minimum query to MySQL.
But, if there's a way to query DataTable instead of MySQL maybe it works.
thanks for ur help.
|
|
|
|
|
You should be able to simply add Bound Columns to your DataGrid control, specify which field in the DataTable each column is bound to, and then hide the other columns by setting the visibility to false.
|
|
|
|
|
|
I have 1 more question but not related.
In my program I use DataGrid to show the content of DataTable
and it shows the data nicely. But, once I tried to delete one row from MySQLBrowser while my program runs. What happen was, the deleted row still exist in my DataTable even though I pressed Refresh to fill DataTable again with new Data from MySQL.
that row is gone when I start over my program.
But, if I add new row from MySQLBrowser, and I pressed Refresh in my program, that new row is added to my DataTable ?
thx for the help
|
|
|
|
|
That does seem a little odd. Is this a Windows PC Project that you are writing or a Web Project? My first thought is to make sure that you are clearing the DataTable before you re-fill it again with data. And if that isn't the problem you can try to debug what happens when you press the Refresh button to make sure it is doing what it is supposed to. (If you have Visual Studio 2005 you can highlight your DataTable variable when in debug mode, hover the mouse cursor over it, then click on the magnifying glass icon that appears and a popup window will show you exactly what is in the DataTable. It's a very handy debug feature in VS2005)
Hope this helps.
|
|
|
|
|
This is a Windows PC Project.
If I fill DataTable with same data while DataTable still consist data, the deleted row still exists in DataTable, and the new row (also added from MySQLBrowser) is added to my DataTable.
If I clear DataSet first, then fill it again, everything works fine!. I clear DataSet because if I clear DataTable, there's and constrain error.
But if you read the procedure Fill() description, it says that Fill() can be used to refresh DataTable too, so it means, no need to clear DataTable right?
-----
I decided to set DataSet constrain to false then clear DataTable then set DataSet constrain to true then Fill DataTable again.
is this effective? or are the another more effective way?
-- modified at 22:46 Wednesday 20th December, 2006
|
|
|
|
|
With the projects I do, it is easier to just use DataTables. I don't use DataSets very much. But if what you are doing works, then it is effective.
About your problem with the added records refreshing but the deleted records not...maybe it has something to do with how you are deleting the record. If you post some of your code it would help. (And you might want to post it as a new question on the forum so it will get more attention)
|
|
|
|
|
Clearing DataTable really works. But, what if there are thousands rows?
Anywayay, here's the code:
I have a Static Class which holds the only DataSet in my program. All DataTables created are stored in that DataSet.
/*******************************************************************/
public static DataSet dsObj = new DataSet(); //the one and only DataSet in my program
public static MySqlConnection connObj = new MySqlConnection(connStr);
//method to add Table or Tables to DataSet
public static void addTblsToDsObj(String tblName)
{
String[] tblArr = tblName.Split(',');
dsObj.EnforceConstraints = false; //I remove constrain before clearing DataTable, because of ForeignKey problem
for (int i = 0; i < tblArr.Length; i++ )
{
if(dsObj.Tables.Contains(tblArr[i])){ //If DataSet has same Table, I clear that table before fill it again
dsObj.Tables[tblArr[i]].Clear();
}
}
dsObj.EnforceConstraints = true; //after needed tables are cleared, I set it to true because I want to load the primary key too.
for (int i = 0; i < tblArr.Length; i++)
{
MySqlDataAdapter daObj = new MySqlDataAdapter("SELECT * FROM " + tblArr[i], connObj);
MySqlCommandBuilder cbObj = new MySqlCommandBuilder(daObj);
daObj.MissingSchemaAction = MissingSchemaAction.AddWithKey;
daObj.Fill(dsObj, tblArr[i]);
}
}
/*******************************************************************/
Now, everytime my Form load a Table from MySQL, I call method addTblsToDsObj(String tblName).
Note that code above works fine with deleted data outside my Program environtment (because if Clear method).
I repeat my question above, what if I have thousand of rows? it means I will clear a lot of unnecessary rows right? so I guess it's not an optimal performance.
Thx for any help. I'll try to post this as new message too.
|
|
|
|
|
hi
well I wanted to write a query that deletes a specific no of rows from a table wwhich are specified by the user
is there a way that u can do that..
plz help
thanks in advance
Rocky
|
|
|
|
|
DELETE Table T1
WHERE T1.ID IN (SELECT TOP @NoOfRecords T1.ID
FROM Table T1 ORDER BY T1.ID)
the last thing I want to see is some pasty-faced geek with skin so pale that it's almost translucent trying to bump parts with a partner - John Simmons / outlaw programmer
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
yea that looks pretty helpful I'll try that in a llittle while
thanks a lot Pete
Rocky
|
|
|
|
|
 Hl, installed SRSS 2005 on my PC and every thing works fine exept when I try to deploy my report locally into my machine I get the error bellow
Reporting Services Error
--------------------------------------------------------------------------------
The report server has encountered a configuration error. See the report server log files for more information. (rsServerConfigurationError)
Access to the path 'C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer\RSReportServer.config' is denied.
Any ideas how to fix this problem?
Thanks in advance
Jimbo25
|
|
|
|
|
Hi all,
I've just installed the express edition of Sql Server 2005 + Management Studio Express and I can't get how to import data from a MS Access file. From what I've read is not possible to do through the Management Studio Express Edition so is there anu other tool to do it?
Thanks in advance,
Marc Soleda
... she said you are the perfect stranger she said baby let's keep it like this... Dire Straits
|
|
|
|
|
Marc Soleda wrote: I've just installed the express edition of Sql Server 2005 + Management Studio Express and I can't get how to import data from a MS Access file. From what I've read is not possible to do through the Management Studio Express Edition so is there anu other tool to do it?
You could write your own tool. The one I was going to suggest (SSIS) is only available with a full SQL Server edition.
|
|
|
|
|
Is there a way to change a TextBox Value in the MAIN REPORT, from a SubReport?
Thanks.
|
|
|
|
|
Hello all!
I'm trying to do a web application using open database (MS Access alternative provided by OpenOffice.org which uses the HSQL engine) but I can't find any info on it on the web... 
Can you help me by telling me which driver and connection string should I use?
Thanks a lot,
b4silence
|
|
|
|
|
Is there a stored procedure that I can use to check if a database exist before I create it using C#?
|
|
|
|
|
If you are using SQL Server 2005 you can write a proc to use
select * from sys.databases.
|
|
|
|
|
I am using SQL Server 2005 Express, is there something similar that I can use to check if a table exists in the database? Thanks.
|
|
|
|
|
This will return all of the tables from the db the query is running under...
select * from sysobjects
where xtype = 'u'
|
|
|
|
|
Given a query like the following
select open_date, close_date
from view
where ( open_date between D1 and D2 or close_date between D1 and D2 )
and knowing that close_date may be NULL sometimes and that open_date is always <= to close_date, what kind of date manipulation could I do, so that I can add another field to the select list so that when both open_date and close_date are between D1 and D2, the value for the field would be 1, other wise it would be zero.
Thanks.
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
|
I finally figured out to add two fields and the query looks like this
select open_date, close_date,
decode(sign(open_date-D1),-1,0,1) open_date_in_range,
decode(sign(nvl(close_date-D1,-1)),-1,0,1) close_date_not_null
...
Chris Meech
I am Canadian. [heard in a local bar]
I agree with you that my argument is useless. [Red Stateler]
Hey, I am part of a special bread, we are called smart people [Captain See Sharp]
The zen of the soapbox is hard to attain...[Jörgen Sigvardsson]
I wish I could remember what it was like to only have a short term memory.[David Kentley]
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
