- Query Syntax
- SQL
How can I dynamically change all the Names in one column , throughout a Table?
Hi,
I'm looking for some help on a MariaDB (MySQL) project using c#.
I have two tables, one Customers (ID, Name, Email, etc), the other Agreements (ID, Customer Name, AgreementName, ExpiryDate).
My code works pretty good, I have a listbox that loads, and when the end user picks a customer from it, several text boxes populate the info where Customer records can be acted upon (changed, deleted etc).
Also, a second table is automatically accessed, keying off of the customers name selected from the first listbox, this displays all that customers agreements, if any, in a listView.
Everything works great, until I change/update the name of the customer. The change goes through just fine on the first table, but then the second table loses the references because the name has changed.
If I change the name back, everything is fixed.
So I need a way to change all the names in the second table to the proper values and there can be 0 changes (no agreements), or hundreds of agreements. In other words, some customers are not in the second table at all, and other customers are there hundreds of times.
So just wondering do I just need to get my second UPDATE statement correct?
I know there are Join (inner, outer ) but I'm not sure how to use them properly.
Here is my first SQL statement that updates the original customer record (this is working great):
string Query = "update bbb.jurisdiction set JurName='" + this.jurTxtBoxName.Text + "',JurNumber='" + this.jurTxtBoxNumber.Text + "',JurContactPhoneNumber='
And here is the SQL statement where I try and update the second table (does not work at all, but no error in code, or run time errors):
string Query2 = "update bbb.jurisdictionrua set JName='" + this.jurTxtBoxName.Text + "' where JName='" + this.jurTextBoxID.Text + "' ; ";
JName is the name of Column where the Customers Name is, that I need to change throughout the table.
So any help would be appreciated.
Thank you in Advance.
G
I'm looking for some help on a MariaDB (MySQL) project using c#.
I have two tables, one Customers (ID, Name, Email, etc), the other Agreements (ID, Customer Name, AgreementName, ExpiryDate).
My code works pretty good, I have a listbox that loads, and when the end user picks a customer from it, several text boxes populate the info where Customer records can be acted upon (changed, deleted etc).
Also, a second table is automatically accessed, keying off of the customers name selected from the first listbox, this displays all that customers agreements, if any, in a listView.
Everything works great, until I change/update the name of the customer. The change goes through just fine on the first table, but then the second table loses the references because the name has changed.
If I change the name back, everything is fixed.
So I need a way to change all the names in the second table to the proper values and there can be 0 changes (no agreements), or hundreds of agreements. In other words, some customers are not in the second table at all, and other customers are there hundreds of times.
So just wondering do I just need to get my second UPDATE statement correct?
I know there are Join (inner, outer ) but I'm not sure how to use them properly.
Here is my first SQL statement that updates the original customer record (this is working great):
string Query = "update bbb.jurisdiction set JurName='" + this.jurTxtBoxName.Text + "',JurNumber='" + this.jurTxtBoxNumber.Text + "',JurContactPhoneNumber='
And here is the SQL statement where I try and update the second table (does not work at all, but no error in code, or run time errors):
string Query2 = "update bbb.jurisdictionrua set JName='" + this.jurTxtBoxName.Text + "' where JName='" + this.jurTextBoxID.Text + "' ; ";
JName is the name of Column where the Customers Name is, that I need to change throughout the table.
So any help would be appreciated.
Thank you in Advance.
G
then the second table loses the references because the name has changed.
That's one of the big reasons why it's best not to duplicate data in multiple tables. Instead, normalize the tables and store the unique Customer ID (PK) in the Agreements table as a foreign key - not "name". Then there's no need to update the Agreements table when the Customer name is changed.
Thanks _agx_,
Do you recommend I delete the 'Name' column in the agreements table and then create a new column for the FK? So just call it CustID and make it INT/11?
The first thing HeidiSQL asks when I'm creating a FK in the table is which column is going to be the FK and then the reference Table and column. The reference column is going to be customer.id.
Do you recommend I delete the 'Name' column in the agreements table and then create a new column for the FK? So just call it CustID and make it INT/11?
The first thing HeidiSQL asks when I'm creating a FK in the table is which column is going to be the FK and then the reference Table and column. The reference column is going to be customer.id.
The FK in the agreements table is customer_id not customer_name.
You also should NEVER use string concatenation in SQL. Use BIND variables.
You are open to SQL injection using concatenation .
Quick example: What if I enter "1 or 1=1" in this.jurTextBoxID.Text
Updated
@G F - Sorry, I haven't used HeidiSQL. I usually just write the sql to create FK's myself.
>> Do you recommend I delete the 'Name' column in the agreements table
Not unless you intend to drop the table and start from scratch with an empty table. Are you? If not, you'd want to add the new column first, populate it, then finally create the FK.
1. If the data is important, always backup the table first
2. Add the new column CustomerID type integer
3. Update the new CustomerID using a JOIN between Customer and Agreement on "name"
4. Finally, mark CustomerID as a FK referencing the Customer.ID column
>> ... and make it INT/11?
If that's the data type of the Customer.ID column, yes.
@G F - Sorry, I haven't used HeidiSQL. I usually just write the sql to create FK's myself.
>> Do you recommend I delete the 'Name' column in the agreements table
Not unless you intend to drop the table and start from scratch with an empty table. Are you? If not, you'd want to add the new column first, populate it, then finally create the FK.
1. If the data is important, always backup the table first
2. Add the new column CustomerID type integer
3. Update the new CustomerID using a JOIN between Customer and Agreement on "name"
4. Finally, mark CustomerID as a FK referencing the Customer.ID column
>> ... and make it INT/11?
If that's the data type of the Customer.ID column, yes.
Thanks slightwv, I just want to get the code going first, understand what is going on , then I can make it better.
I'm obviously new to this, so maybe a future question you can explain about Bind vars and sql injection.
Right now, i'm just spinning my tires trying to get this right.
I'm obviously new to this, so maybe a future question you can explain about Bind vars and sql injection.
Right now, i'm just spinning my tires trying to get this right.
Thanks _agx_
I'm still early in the development of this, so I deleted the two tables and created them. I decided to keep the name column (called JName in the agreements tbl).
This is my customer table (also called jurisdiction):
![Customer Tbl]()
and this is my agreements table (also called jurisdictionrua):
![agreements tbl]()
I created the foreign key in the agreements table, which references the Customer.Id column (green key icon)
However, just looking at your notes, I'm not sure what you mean by Join comment in step 3. Are you saying change my c# code? or is this something at the Database level?
I'm still early in the development of this, so I deleted the two tables and created them. I decided to keep the name column (called JName in the agreements tbl).
This is my customer table (also called jurisdiction):
and this is my agreements table (also called jurisdictionrua):
I created the foreign key in the agreements table, which references the Customer.Id column (green key icon)
However, just looking at your notes, I'm not sure what you mean by Join comment in step 3. Are you saying change my c# code? or is this something at the Database level?
I'm not sure what you mean by Join comment in step 3.
You can Ignore that now. Since you recreated the tables, it doesn't apply.
I decided to keep the name column (called JName in the agreements tbl).
No, it should be deleted. When you need customer "name", use a JOIN between the two tables on the Customer ID.
I created the foreign key in the agreements table, which references the Customer.Id column (green key icon)
Ohh.. looking at the actual tables, turns out "JurID" or "JurisdictionID" would be a better choice for the column name than "CustomerID" :-) Best to use names that (intuitively) describe the relationships/contents (and use the same naming convention throughout).
Ok sorry, yes I meant the Customer ID Foreign Key references jurisdiction.id, or column 1 in jurisdiction table, I've changed the name.
I've deleted the Jname Column, since it's not needed.
So here is the new agreements table:
![Updated Table]()
So when I run the program, the first part still runs great. I can add and delete customers/jurisdictions. But obviously it crashes out where it references the old name, or JName, which is when it attempts to fill the second listbox.
So how should I structure the query? It currently looks like:
string Query = "SELECT * FROM bbb.jurisdictionrua WHERE JName= '" + textBox3.Text + "' ;";
Which worked, before the changes...
I'm thinking something like
string Query = "SELECT * FROM bbb.jurisdictionrua WHERE JurID = jurisdiction.ID";
Am I on the right track?...basically I want to pass all the names of the agreements from the agreements/jurisdictionrua
I'm also finding out Youtube for Joins (inner/outer) is horrible as is Foreign Keys vids. Especially unhelpful is one titled "How to Insert Data Into Multiple Table Using Foreign Key", where all the guy does is create two small tables and none of his keys are Foreign Keys. under 3 minute run time too.
Thanks for all your help so far!
G
I've deleted the Jname Column, since it's not needed.
So here is the new agreements table:
So when I run the program, the first part still runs great. I can add and delete customers/jurisdictions. But obviously it crashes out where it references the old name, or JName, which is when it attempts to fill the second listbox.
So how should I structure the query? It currently looks like:
string Query = "SELECT * FROM bbb.jurisdictionrua WHERE JName= '" + textBox3.Text + "' ;";
Which worked, before the changes...
I'm thinking something like
string Query = "SELECT * FROM bbb.jurisdictionrua WHERE JurID = jurisdiction.ID";
Am I on the right track?...basically I want to pass all the names of the agreements from the agreements/jurisdictionrua
I'm also finding out Youtube for Joins (inner/outer) is horrible as is Foreign Keys vids. Especially unhelpful is one titled "How to Insert Data Into Multiple Table Using Foreign Key", where all the guy does is create two small tables and none of his keys are Foreign Keys. under 3 minute run time too.
Thanks for all your help so far!
G
Updated
If you already know the selected Jurisdiction ID, then just filter on that ID in the query, i.e WHERE JurID = 5. However, if all you have is the Jurisdiction name, you'll need a JOIN. This page has some nice visual illustrations and simple examples of JOINs, so does SQL Joins on wiki. Anyway, the basic JOIN on the JurID would look something like this
SELECT jurisdictionrua.RUAName
, jurisdictionrua.RUANumber
FROM bbb.jurisdictionrua INNER JOIN bbb.jurisdiction ON jurisdiction
WHERE jurisdiction.ID = jurisdictionrua.JurID
AND jurisdition.JName = 'Some Literal Value To Find Here'
Tip - a slightly shorter way to write that query is using aliases. I find that style much more readable than spelling out full table names everywhere, but .. technically either works.
--- "rua" alias (or short for writing table name "jurisdictionrua")
--- "jur" alias (or short for writing the table name "jurisdiction")
SELECT rua.RUAName
, rua.RUANumber
FROM bbb.jurisdictionrua rua INNER JOIN bbb.jurisdiction jur
ON jur.ID = rua.JurID
WHERE jur.JName = 'The Value To Find Here'
Anyway, once you've verified the query works in HeidiSQL, you should "parameterize" your application query by using bind variables. There are three advantages:
- performance
- protection against sql injection attacks
- you don't have to quote strings manually
Not sure what connection method your app uses, but using SQLCommand, you just add a @parameter for the name and populate it with whatever was entered in the textbox. Not tested but something like this...
If you already know the selected Jurisdiction ID, then just filter on that ID in the query, i.e WHERE JurID = 5. However, if all you have is the Jurisdiction name, you'll need a JOIN. This page has some nice visual illustrations and simple examples of JOINs, so does SQL Joins on wiki. Anyway, the basic JOIN on the JurID would look something like this
SELECT jurisdictionrua.RUAName
, jurisdictionrua.RUANumber
FROM bbb.jurisdictionrua INNER JOIN bbb.jurisdiction ON jurisdiction
WHERE jurisdiction.ID = jurisdictionrua.JurID
AND jurisdition.JName = 'Some Literal Value To Find Here'
Tip - a slightly shorter way to write that query is using aliases. I find that style much more readable than spelling out full table names everywhere, but .. technically either works.
--- "rua" alias (or short for writing table name "jurisdictionrua")
--- "jur" alias (or short for writing the table name "jurisdiction")
SELECT rua.RUAName
, rua.RUANumber
FROM bbb.jurisdictionrua rua INNER JOIN bbb.jurisdiction jur
ON jur.ID = rua.JurID
WHERE jur.JName = 'The Value To Find Here'
Anyway, once you've verified the query works in HeidiSQL, you should "parameterize" your application query by using bind variables. There are three advantages:
- performance
- protection against sql injection attacks
- you don't have to quote strings manually
Not sure what connection method your app uses, but using SQLCommand, you just add a @parameter for the name and populate it with whatever was entered in the textbox. Not tested but something like this...
string sql = "SELECT rua.RUAName, rua.RUANumber
FROM bbb.jurisdictionrua rua INNER JOIN bbb.jurisdiction jur
ON jur.ID = rua.JurID
WHERE jur.JName = @Name";
using (SqlCommand command = new SqlCommand( sql, connection))
{
command.Parameters.Add(new SqlParameter("@Name", SqlDbType.VarChar).Value = textBox3.Text;
// ... rest of code
}
Hi again _agx_,
So I read your comments above and I'm working on it now, but I decided I needed to populate the agreements table, since this is the genesis of my problem. Then I can refer to your info to read it back into my second listbox.
I looked at the agreements table, and I assumed I had to now populate the JurID column, so I took the value of the Selected Customer ID from the first table and tried to jam it into the agreements table, but I'm not having any luck. I'm not sure if the problem is strictly formatting or if I'm just angering the Database Gods...
So, I convert to an Int the value of the customer ID:
{
string temp = listBox1.GetItemText(listB
textBox3.Text = temp;
int newIDtemp;
bool isParsable = Int32.TryParse(temp, out newIDtemp);
I then define my Database Connection:
string constring = "datasource=localhost;port
And My definition string query which should contain my Variable for JurID:
string Query = "insert into bbb.jurisdictionrua (JurID,RUAName,RUANumber,R
this.textBox3.Text + "','" + this.textBox2.Text + "','" + this.dateTimePicker1.Text + "') ;";
And the code continues, but I don't get this far...:
MySqlConnection conDatabase9 = new MySqlConnection(constring)
MySqlCommand cmdDatabase = new MySqlCommand(Query, conDatabase9);
MySqlDataReader myReader;
try
I've tried a few different variations on the string query, and adapting it to the newIDtemp variable, but not having much success. Many examples on the net are doing the SQLCommand format, which I'm guessing what slightwv was talking about, but was hoping I can get it working, then switch it later to the better format.
I then decided to try and jam the JurID value in manually and it seemed to go in okay:
string Query = "insert into bbb.jurisdictionrua (JurID,RUAName,RUANumber,R
this.textBox2.Text + .......
So I read your comments above and I'm working on it now, but I decided I needed to populate the agreements table, since this is the genesis of my problem. Then I can refer to your info to read it back into my second listbox.
I looked at the agreements table, and I assumed I had to now populate the JurID column, so I took the value of the Selected Customer ID from the first table and tried to jam it into the agreements table, but I'm not having any luck. I'm not sure if the problem is strictly formatting or if I'm just angering the Database Gods...
So, I convert to an Int the value of the customer ID:
{
string temp = listBox1.GetItemText(listB
textBox3.Text = temp;
int newIDtemp;
bool isParsable = Int32.TryParse(temp, out newIDtemp);
I then define my Database Connection:
string constring = "datasource=localhost;port
And My definition string query which should contain my Variable for JurID:
string Query = "insert into bbb.jurisdictionrua (JurID,RUAName,RUANumber,R
this.textBox3.Text + "','" + this.textBox2.Text + "','" + this.dateTimePicker1.Text + "') ;";
And the code continues, but I don't get this far...:
MySqlConnection conDatabase9 = new MySqlConnection(constring)
MySqlCommand cmdDatabase = new MySqlCommand(Query, conDatabase9);
MySqlDataReader myReader;
try
I've tried a few different variations on the string query, and adapting it to the newIDtemp variable, but not having much success. Many examples on the net are doing the SQLCommand format, which I'm guessing what slightwv was talking about, but was hoping I can get it working, then switch it later to the better format.
I then decided to try and jam the JurID value in manually and it seemed to go in okay:
string Query = "insert into bbb.jurisdictionrua (JurID,RUAName,RUANumber,R
this.textBox2.Text + .......
>> get it working, then switch it later to the better format.
Speaking from personal experience, do it sooner rather than later, as it's very easy to forget and unknowingly leave a big sql vulnerabilty in your application.
>> , but I don't get this far...:
Do you mean you're getting an error when trying to execute it? Could you post the error message? Also, print out the string containing the insert query - right before you try and execute it - so we can figure out the problem with the sql statement.
Speaking from personal experience, do it sooner rather than later, as it's very easy to forget and unknowingly leave a big sql vulnerabilty in your application.
>> , but I don't get this far...:
Do you mean you're getting an error when trying to execute it? Could you post the error message? Also, print out the string containing the insert query - right before you try and execute it - so we can figure out the problem with the sql statement.
Thanks for the quick reply, I actually got the code to work, I had a typo I totally missed. Sorry bout that!
I'm just entering more test info for customers and agreements before moving on to the big test for the agreement updates!
I'm just entering more test info for customers and agreements before moving on to the big test for the agreement updates!
Ok, so I'm trying to delete Customers from the jurisdiction table, and I get the following error:
![Error deleting jurisdiction]()
I noticed it's referencing the other Table, and by the text of the error it's a problem with the FK.
Here is the code i'm trying to use, I'm assuming the issue is in the SQL statement:
private void button7_Click(object sender, EventArgs e)
string constring = "datasource=localhost;port
string Query = "DELETE FROM bbb.jurisdiction WHERE JurName = '" + this.jurTxtBoxName.Text + "';";
Is it because I'm not deleting the record from the Id column in jurisdiction?
If I put single quotes around JurName, everything seems to go through, but does not actually delete anything.
I noticed it's referencing the other Table, and by the text of the error it's a problem with the FK.
Here is the code i'm trying to use, I'm assuming the issue is in the SQL statement:
private void button7_Click(object sender, EventArgs e)
string constring = "datasource=localhost;port
string Query = "DELETE FROM bbb.jurisdiction WHERE JurName = '" + this.jurTxtBoxName.Text + "';";
Is it because I'm not deleting the record from the Id column in jurisdiction?
If I put single quotes around JurName, everything seems to go through, but does not actually delete anything.
>> Is it because I'm not deleting the record from the Id column in jurisdiction?
Since JurID is a FK, you cannot delete an ID from the Jurisdictions table as long as any record in JurisdictionsRUA references that ID. So if you REALLY want to permanently delete the records, you must delete the associated records in the JurisdictionRUA table first. Once the id is no longer in use, you'll be able to delete the record from Jurisdictions.
Another option is to set up a CASCADE DELETE, which will automatically delete any associated records from JurisdictionRUA whenever you delete a record from Jurisdictions. You don't need to do it yourself. Just be sure you understand how CASCADE works, so you don't inadvertently delete the entire contents of the agreements table!
Personally, I am not a fan of "hard deletes", i.e. permanent. I usually create a column like "IsDeleted" and toggle it on or off. However, it all depends on the app.
Just laying out the options.
Since JurID is a FK, you cannot delete an ID from the Jurisdictions table as long as any record in JurisdictionsRUA references that ID. So if you REALLY want to permanently delete the records, you must delete the associated records in the JurisdictionRUA table first. Once the id is no longer in use, you'll be able to delete the record from Jurisdictions.
Another option is to set up a CASCADE DELETE, which will automatically delete any associated records from JurisdictionRUA whenever you delete a record from Jurisdictions. You don't need to do it yourself. Just be sure you understand how CASCADE works, so you don't inadvertently delete the entire contents of the agreements table!
Personally, I am not a fan of "hard deletes", i.e. permanent. I usually create a column like "IsDeleted" and toggle it on or off. However, it all depends on the app.
Just laying out the options.
Ah ok, well just tried it again and sure enough you are right, it's deleting just fine with the jurisdictions with no agreements, and crashing out where there are.
I think I'll dig more on the CASCADE DELETE, sounds promising. Also I noticed in HeidiSQL I have an option for a CASCADE UPDATE for the FK, just wondering how would that work?
Thanks!
G
I think I'll dig more on the CASCADE DELETE, sounds promising. Also I noticed in HeidiSQL I have an option for a CASCADE UPDATE for the FK, just wondering how would that work?
Thanks!
G
CASCADE UPDATE wouldn't apply unless you are planning on changing the value of JurID. As an autonumber column, it is unlikely you'd be changing the value once it is generated by MariaDb.
https://mariadb.com/kb/en/foreign-keys/
https://mariadb.com/kb/en/foreign-keys/
CASCADE: The change is allowed and propagates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row's ID changes, the child row's ID will also change.
Now that you have a basic query working, it would be a good time to go back and add bind @variables. Not only will they improve performance and protect against sql injection, the sql will be easier to debug. You won't have to manually add single quotes around any string values inserted and sql statements will be much more readble without all the extra con+caten+ation ;-)
Hi _agx_,
Thanks again for all the help, it's very much appreciated it.
I've gone old school and ordered two C# Database books and they are helping a lot as well. There is just something about peer reviewed publications.
I'll go through the code and start fixing the SQL statements.
Have a good one!
G
Thanks again for all the help, it's very much appreciated it.
I've gone old school and ordered two C# Database books and they are helping a lot as well. There is just something about peer reviewed publications.
I'll go through the code and start fixing the SQL statements.
Have a good one!
G
-
![]()
received a Solution
How can I create a query to count the blank fields in the table ? -
![]()
wrote an Article
How to dynamically return a table structure using a Stored Procedure -
![]()
created a Video
![]()
Synchronize List Box in Access using SQL and VBA
-
Explore Cloud Class® ![]()
Microsoft SQL Server and Database Concepts
Premium Content
You need an Expert Office subscription to comment.Start Free Trial