How Can I Update Two Tables Data With Same Column Name..?

Question

0.00/5 (No votes)

See more:

Can any body Help me in below code in which when i update Dept_Name column of Dept
table in sql database it will also update the record of same column Dept_Name in Staffs
table.

Below is the Update Code for Dept Table:-

VB

Dim obj As New ConStr
            Dim sql As String
            sql = "update Dept set Dept_Name='" & TextBox2.Text & "' where Dept_No=" & TextBox1.Text
            obj.RunInsertDeleteUpdateQry(sql)
            'RefreshGrid()
            TextBox1.Clear()
            TextBox2.Clear()
            MsgBox("Data Updated Successfully")

Below is the Update Code for Staffs Table:-

VB

Dim obj As New ConStr
            Dim sql As String
            sql = "update Staffs set Dept_Name='" & TextBox2.Text & "' where Dept_No=" & TextBox1.Text
            obj.RunInsertDeleteUpdateQry(sql)
            'RefreshGrid()
            TextBox1.Clear()
            TextBox2.Clear()
            MsgBox("Data Updated Successfully")

But Staffs table was not updated.

Please help me.........

Posted 16-Aug-15 21:06pm

hspl

Updated 16-Aug-15 21:23pm

CPallini

v2

Add a Solution

3 solutions

Solution 2

I would suggest not to repeat the data on several tables. If the staff is linked to a department, define a foreign key relation from Staff to Department and have the department name only in the department table. When fetching the data, join both department and staff in order to get data rom both tables.

However, if you need to update both tables, a very traditional approach is to create a stored procedure which would contain the updates. The stored procedure would have the input values as parameters.

Have a look at CREATE PROCEDURE[^]

Posted 16-Aug-15 21:22pm

Wendelius

Updated 16-Aug-15 21:23pm

v2

Solution 3

Just to add to what the others have said, don't do it like that!
Do not concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

Posted 16-Aug-15 21:37pm

OriginalGriff

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Abhinav S · Accepted Answer · 2015-08-16T21:17:00

Solution 1

Check whether staffs table contains data with this dept number and name.

As an aside, you should use command parameters to avoid SQL Injection[^].

Posted 16-Aug-15 21:17pm

Abhinav S

Comments

hspl 17-Aug-15 3:21am

Staffs table does not contain dept number.

Abhinav S 17-Aug-15 3:40am

Then obviously your query is wrong.
sql = "update Staffs set Dept_Name='" & TextBox2.Text & "' where Dept_No=" & TextBox1.Text

hspl 17-Aug-15 3:35am

can you please help me