Link to home
Create AccountLog in
Avatar of G F
G FFlag for Canada

asked on

How can I dynamically change all the Names in one column , throughout a Table?

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='" + this.jurTxtBoxContactnumber.Text + "',JurEmail='" + this.jurTxtBoxEmail.Text + "',JurWebsite='" + this.jurTxtBoxWebsite.Text + "',JurNotes='" + this.jurTxtBoxNotes.Text + "' where id='" + this.jurTextBoxID.Text + "' ; ";

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.

Avatar of _agx_
Flag of United States of America image

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.
Avatar of G F


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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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 


@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.
Avatar of G F


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.
Avatar of G F


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):

User generated image

and this is my agreements table (also called jurisdictionrua):

User generated image

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).
Avatar of G F


Ok sorry, yes I meant the Customer ID Foreign Key references, 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:

User generated image

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 table over to listbox2 from the customer name selected in listbox1, or also listed in textBox3.Text.

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!

Avatar of _agx_
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Avatar of G F


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(listBox1.SelectedItem);
            textBox3.Text = temp;

            int newIDtemp;
            bool isParsable = Int32.TryParse(temp, out newIDtemp);

I then define my Database Connection:

string constring = "datasource=localhost;port=3306;username=root;pwd=****;";

And My definition string query  which should contain my Variable for JurID:

string Query = "insert into bbb.jurisdictionrua (JurID,RUAName,RUANumber,RUAExpiryDate) values ('" + newIDtemp + "','" +
                           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;


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,RUAExpiryDate) values ('2','" + this.textBox3.Text + "','" +
                          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.
Avatar of G F


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!
Avatar of G F


Ok, so I'm trying to delete Customers from the jurisdiction table, and I get the following error:

User generated image

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=3306;username=root;pwd=****;";
            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.
Avatar of G F


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?


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.

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 ;-)
Avatar of G F


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!


You're very welcome. Glad I could help