|
I have trouble with deleted row in my database program. When my program starts, I start another program too (MySQLBrowser) and from MySQLBrowser, I delete one row. After that in my Program, I refresh (re-fill) my relevant DataTable but the deleted row still exist in DataTable.
I found out a solution by clearing DataTable first, then re-fill it again. It Works.
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.
|
|
|
|
|
Are you deleting the data from the table in your DATA set or sending a SQL command to your db.
If you are deleting a record from a datatable within a dataadapter your have to issue an update commaand to your dataAdapter.
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
I deleted row from MySQLBrowser (or inside MySQL itself) not from my Program. I didn't delete from DataTable because it's inside my Program.
If I add or modify row from MySQLBrowser than in my Program I re-fill DataTable again, the modified or added row is exist on my DataTable. but the problem is when I delete row from MySQLBrowser, then in my program I refresh(re-fill) DataTable, DataTable still contain the deleted row.
|
|
|
|
|
I've been quoting string parameters using single qoutes as follows:
QueryString = "Select * " & _
"From Employees " & _
"Where LastName = '" & EmpLastName & "'"
This works well unless the last name includes a quote, i.e., "Mc'Donald"
So how do you quote the string in visual basic so that Oracle won't hang up on the interior quote?
The same thing happens when you need to query or store feet and inches values ( 9'4" ).
So how would I quote that?
Thanks
P.S., this is for Oracle 8i, if it makes a difference.
|
|
|
|
|
howardjr wrote: This works well unless the last name includes a quote, i.e., "Mc'Donald"
The name McDonald has no apostrophe in it. You may be thinking of a name such as O'Leary.
The simple answer is that you don't inject values in to SQL. It is a security risk and leads to SQL Injection Attacks.
Read this article[^] and it will tell you how to prevent SQL Injection Attacks. And by closing that security hole you solve your current problem.
howardjr wrote: The same thing happens when you need to query or store feet and inches values ( 9'4" ).
You are kidding me when you say you store feet and inches like that?! How do you compare them? These should be stored as a number (probably inches) and the presentation layer of the application reformats the stored information to display as you need it. (Actually, I'm more surprised that people still use feet and inches and not SI units)
|
|
|
|
|
I understand your comment about not normalizing the feet and inches, but the value is stored in a text field as part of an inspection comment, which later I was asked to retrieve. I don't know how the comment was put in so, but the value is in the database, so I'd like to know how to quote the search value.
Comments aside, how do you quote strings that already have quotes in them, either a single or a single and a double quote?
|
|
|
|
|
Perhaps this may help you getting some idea.
SqlCommand cmd = con.CreateCommand();
cmd.CommandText = "SELECT f1 from t2 WHERE upper(f1) LIKE @criteria";
cmd.Parameters.Add("@criteria", SqlDbType.VarChar).Value = "%'MAIRAJ%";
Where f1 is a varchar type field in table t2. I tested this against SQL Server 2000, and it works fine.
Parameters in command object provides you a greater flexibility in working with databases (either you want to update, delete, insert, or get data). Plus it also help in preventing SQL Injection Attacks [^]. I will strongly recommend, go for that what Colin Angus Mackay is suggested.
Moreover, .NET provide following types of parameters (among them one may serve you).
OleDbParameter[^]
SqlParameter[^]
OdbcParameter[^]
Regards
________________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
howardjr wrote: how do you quote strings that already have quotes in them, either a single or a single and a double quote?
Read the article I linked to.
|
|
|
|
|
Thank you for your comments and the links.
It looks like the article on protecting against SQLInjection Attacks covers what I need very nicely. In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system  , I don't have to worry about this.
Thanks again.
|
|
|
|
|
howardjr wrote: In my case I'm lucky in that I'm the one writing the select statement and dealing with the parameter values, so since I'm not going to Attack my own system , I don't have to worry about this.
Statistically 90% of all attacks are insider jobs. You might need protection from yourself. 
|
|
|
|
|
Colin Angus Mackay wrote: You might need protection from yourself.
Don't you also mean from employees within an organization?
Some people have a memory and an attention span, you should try them out one day. - Jeremy Falcon
|
|
|
|
|
PaulC1972 wrote: Don't you also mean from employees within an organization?
Never underestimate the cunning of a master hacker. 
|
|
|
|
|
True enough -- I never know what I'm going to do when I'm sleep-computing.
|
|
|
|
|
Hi ..
when I build a table and indicate the size of all fields like
Name char(30)
Address char(100)
when I enter the data on the table I found that the program is complete the lengn of filed to equal it's size like 30 or 100 as shown above with spaces ...
then when I deal with the table with vb.net items like TextBox this spaces is appear and it's makeing a problem for me
please ... any one come to aid me , i searched alot without any result
 ( (
jooooo
|
|
|
|
|
kindman_nb wrote: please ... any one come to aid me , i searched alot without any result
Use a varchar and not a char. Only use char if the value is always a fixed length or you are using it as a key. (Not that it is a good idea to key on a char column)
|
|
|
|
|
|
Left Trim LTRIM(field)
Right Trim RTRIM(field)
Both LTRIM(RTRIM(field))
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
|
Hi All,
I am looking to use DTS to manipulate data from a number of sources (access, oracle, etc.) and am looking for some direction as to best practices and gotchas that I should worry about. Do people recommend using DTS or is there a better way of doing this type of data import/manipulation? What would I use Data Link for when using DTS?
Thanks in advance,
---Jay
|
|
|
|
|
|
oh there is .NET 2.0 version as well.
Which is a bit nicer than the .NET 1.1 version
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I am doing some research about External Linked Servers and am hoping that someone can point me towards some best practices information and let me know about any gotchas that I should look out for when using this capability in applications.
Thanks in advance,
---Jay
|
|
|
|
|
I've quite a lot of experience in this in a few companies and this is how it generally work
Between SQL server no issues
Between other servers (Progress, Oracle, SyBase etc.....) issues are
* Timeouts in SQL command
* Large data migration or copy of large data chunks fails due to ODBC time outs.
* SQL92 is not a standard standard (work different on different boxes)
* You tend to go for extracts to text files for large data volumes and using dts, as it tends to work
Blog Have I http:\\www.frankkerrigan.com
|
|
|
|
|
Hi All,
I am doing some research on the use of ODBC descriptors and hope that the community might have some opinions:
Is anybody out there using ODBC descriptors in their applications?
What are you using them for and why?
Are their alternatives to using them?
Thanks in advance,
---Jay
|
|
|
|
|
I want to implement ajax controls in web application using .net i dont have any idea about this control n i am not getting anythg into my head abt ajax controls pls help me in this matter n mail me to priya.idiol@gmail.com
regards priya
priya fernandes
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)