How can I insert data from a text field into a foreign key in the database

Question

How can I insert data from a text field into a foreign key in the database. Basically, I have tables named Employee and Role, RoleId is a foreign key in Employee, but the role text field is a string. I have this

String Query = "insert into EMPLOYEE (LastName,FirstName,Manager,RoleId,HireStatus) values('" + this.txtLName.Text + "','" + this.txtFName.Text + "','" + this.txtManager.Text + "','"  + this.txtRoleId.Text + "','" + this.txtHireStatus.Text + "');";

Edited - I have this, but I cannot have select with the following string

string Query = "insert into EMPLOYEE(LastName,FirstName,RoleId,Manager,HireStatus) values('" + this.txtLName.Text + "','" + this.txtFName.Text + "','" + ( SELECT RoleId From Role WHERE Role.RoleId = this.txtRole.Text ) +  "','" + this.txtManager.Text +  "','" + this.cmbHireStatus.Text + "');";

`this.txtRoleId` what value you have in this? I thin you should do `this.txtRoleId.Text`? — Chetan, Jun 09 '20 at 02:55
Actually, that was a mistake. Forgot to add .Text. My RoleId is an int, but in my Role table, I have a role column that is a string, which is what is in the text field. — Hello world, Jun 09 '20 at 03:12
Is that Role text an existing one in the Role table? or is it also a new role you are trying to to add? — Thimmu Lanka, Jun 09 '20 at 03:17
The Role text which is the txtRoleId is the name of my text field on my form. — Hello world, Jun 09 '20 at 03:20

score 0 · Answer 1 · edited Jun 09 '20 at 04:29

0

I think you should do this:

String Query = "insert into EMPLOYEE (LastName,FirstName,Manager,RoleId,HireStatus) values('" + this.txtLName.Text + "','" + this.txtFName.Text + "','" + this.txtManager.Text + "','" + this.txtRoleId.Text + "','" + this.txtHireStatus.Text + "');";

edited Jun 09 '20 at 04:29

gre_gor

6,669
9
47
52

answered Jun 09 '20 at 03:11

Tien Dung Nguyen

1
1

I actually just edited it, I forgot to add .Text. I am looking for a way to add data into my foreign key. – Hello world Jun 09 '20 at 03:16
you should add ' each value is string – Tien Dung Nguyen Jun 09 '20 at 03:19
My bad, I actually do, but getting an error saying the text filed Role is an int in the database. – Hello world Jun 09 '20 at 03:26
if value is an integer string: Int32.Parse(this.txtRoleId.Text) and remove ' – Tien Dung Nguyen Jun 09 '20 at 03:30
if value is string, I think you should change the data type of this field to varchar instead of int – Tien Dung Nguyen Jun 09 '20 at 03:36

Thimmu Lanka · Answer 2 · 2020-06-10T06:25:03.380

If you are trying to add a new Employee assigned to an existing role, then a better design would be have a dropdown with all Roles populated instead of a textbox for user to enter.

The dropdown should get filled with all the Roles from Roles table. (map the SelectedValue attribute of the dropdown to - RoleId and SelectedText to RoleName).

When you submit the form, you will get the SelectedValue (RoleId) which you can directly send as part of the INSERT statement, i.e., dropDownRole.SelectedValue instead of txtRoleId.Text

Note: Your insert query seems to be a classic candidate for SQL Injection. I suggest you transform it to Parameterized query.

Update: Now that I came to know it is a Winforms application, adding more detailed snippets. Here is how you can do it in the Win Forms world (not as intuitive as web app world though :) ) and please bear with me, it has been years since I had written a winforms snippet.

Define your dropdown combo box like this -

cbxRole.DataSource = roles; // data from back end
cbxRole.Name = "Role";
cbxRole.DropDownStyle = ComboBoxStyle.DropDownList;
cbxRole.DisplayMember = "RoleName"; // should match the property name in your roles data table
cbxRole.ValueMember = "RoleId"; // should match the property name in your roles data table
cbxRole.SelectedItem = null;
cbxRole.SelectedText = "Select Role";

Observe the ValueMember and DisplayMember properties. DisplayMember tells what property in the roles data source to be used to display as the dropdown item text. ValueMember specifies the property to be used to identify each of the item behind the scenes.

When you submit the details, access the SelectedValue property to get the RoleId corresponding to the selected role name.

private void btnCreate_Click(object sender, EventArgs e)
{
    var firstName = txtFirstName.Text;
    var lastName = txtLastName.Text;
    var roleId = (int)cbxRole.SelectedValue;
}

The SelectedValue property fetches the value of the column identified by the ValueMember property in the dropdown definition, which is the RoleId value. Now, you can send this 'roleId' variable's value to the insert query.

here is how it looks like when you submit -

here is quick sample UI -

Thank you, this is what I had in mind, but don't really know how to go about it after having a dropdown populated with the roles. How do I identify what role the employee has In the employee table since my RoleId is an int in the employee table — Hello world, Jun 09 '20 at 03:54
Edited - I have this, but I cannot have select with the following string string Query = "insert into EMPLOYEE(LastName,FirstName,RoleId,Manager,HireStatus) values('" + this.txtLName.Text + "','" + this.txtFName.Text + "','" + ( SELECT RoleId From Role WHERE Role.RoleId = this.txtRole.Text ) + "','" + this.txtManager.Text + "','" + this.cmbHireStatus.Text + "');"; — Hello world, Jun 09 '20 at 04:06
If you have a drop down on the UI, you do not have to do the `SELECT RoleId...`. Because the Dropdown's SelectedValue property will have the RoleId which you can directly pass to the query just as I mentioned in my third paragraph above. — Thimmu Lanka, Jun 09 '20 at 04:09
What is your UI, is it ASP.NET or something else? Depending on that I can add the dropdown sample to my answer — Thimmu Lanka, Jun 09 '20 at 04:10
it is windows form app. I added the drop down menu and populate it with my role from the Role table, but it is still saying cannot convert string to int. What do you advice i add in the insert string — Hello world, Jun 09 '20 at 05:32

How can I insert data from a text field into a foreign key in the database

2 Answers2