|
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]
|
|
|
|
|
hi to all,
i have a problem. i have "users" table with Id. now i have a function which acceprt user_id range and give output of all "Name" in one row.
ie if select * from users where iser_id in (1,2,3)
user_id Name
1 A
2 B
15 D
45 X
3 W ...
now i want query which give me output like
"A,B,w"
i.e coma seperated output.
can anybody help me for this?
p.s Not use cusor for this (to combine string)
thanks in advance
|
|
|
|
|
hi,
getting "unsupported data" in the column when selected Price from table field .
This field is defined to have "money" type in table using
Sql server 2005
Please guide.
yog
hui gfgh kgdgrt njjn hjgkn
|
|
|
|
|
Use a cast to get it into a different format.
CAST(Price as REAL)
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I need to execute a command line executable from a stored procedure, any ideas on how I would do this?
Thanks
Kevin
|
|
|
|
|
It is an unwise thing to do because it is a security risk but you can use: xp_cmdshell[^]
|
|
|
|
|
Thanks, works great. Whats the security risk?
|
|
|
|
|
A sql injection attack may use that to run any code on your server. Read Colin's article on SQL Injection attacks.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Kevin Nicol wrote: Whats the security risk?
Allowing external commands to function outside of SQL Server opens some holes in a data based application. If a poorly written front appliction is attacked, or a firewall misconfigured, it is possible for an attacker to gain access to the SQL server and then from there launch commands on the server.
The problem is exacerbated by the fact that many SQL Server installations are runing in the System Account. (If I remember correctly, this was the default before Service Pack 3. After that Full SQL Server installations suggested you create a specific account for SQL Server process to run in, however they still kept a nice handy radio button to swap it back to the system account - many lazy DBAs install SQL Server to use the system account).
The system account has greater authority than the Admin account on the box. For example, did you ever notice in RegEdit that there are apparently empty folders called SECURITY in various locations? If you launch RegEdit in the system account you can see what's in there. But try it with an admin account and they look empty again.
In my SQL Injection Attack presentations I use xp_cmdshell to show how an attacker can go in through a web application and rip a full directory listing off the hard disk. I could continue the presentation and actually have it stream a file that has been routed through SQL Server into the web application then over HTTP. I don't go that far because the directory listing trick is enough to get people to sit up and consider security more seriously.
|
|
|
|