We help IT Professionals succeed at work.

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='" + 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.

G
Comment
Watch Question

Most Valuable Expert 2015

Commented:
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.
G FGeneral Manager

Author

Commented:
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.
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:

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  

Most Valuable Expert 2015

Commented:
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 FGeneral Manager

Author

Commented:
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.
G FGeneral Manager

Author

Commented:
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?
Most Valuable Expert 2015

Commented:
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).
G FGeneral Manager

Author

Commented:
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 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!

G
Most Valuable Expert 2015
Commented:
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...

    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 
     }                                 

Open in new window

G FGeneral Manager

Author

Commented:
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;

            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,RUAExpiryDate) values ('2','" + this.textBox3.Text + "','" +
                          this.textBox2.Text + .......
Most Valuable Expert 2015

Commented:
>> 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.
G FGeneral Manager

Author

Commented:
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!
G FGeneral Manager

Author

Commented:
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=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.
Most Valuable Expert 2015

Commented:
>> 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.
G FGeneral Manager

Author

Commented:
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
Most Valuable Expert 2015

Commented:
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/

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.
Most Valuable Expert 2015

Commented:
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 ;-)
G FGeneral Manager

Author

Commented:
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
Most Valuable Expert 2015

Commented:

You're very welcome. Glad I could help