Avatar of G F
G F
Flag for Canada asked on

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
C#SQL* MariaDB* visual studio 2019

Avatar of undefined
Last Comment
_agx_

8/22/2022 - Mon
_agx_

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 F

ASKER
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.
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 

I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
_agx_

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

ASKER
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 F

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
_agx_

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 F

ASKER
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
ASKER CERTIFIED SOLUTION
_agx_

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
G F

ASKER
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 + .......
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
_agx_

>> 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 F

ASKER
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 F

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
_agx_

>> 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 F

ASKER
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
_agx_

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
_agx_

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 F

ASKER
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
_agx_

You're very welcome. Glad I could help

⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.