Assign two inputs to a single column of SQL table

Question

I'm trying to collect data of a user using a small form and insert those to columns of the Student table (db). I was able to insert all information except the user's gender, for which I used two radio buttons (radioButton_male and radioButton_female).

Here is the C# code I used: Dashboard form:

private void Btn_Register_Click(object sender, EventArgs e)
{
    Obj.StudentName = textBox_studentName.Text;
    Obj.Age = int.Parse(textBox_age.Text);
    Obj.Gender_m = radioButton_male.Text;
    Obj.Gender_f = radioButton_female.Text;
    Obj.Contact = int.Parse(textBox_contact.Text);

    bool success = Obj.studentAdd(Obj);
    if (success)
    {
        MessageBox.Show("Saved");
    }
}

Student class

public string StudentName { get; set; }
public int Age { get; set; }
public string Gender_m { get; set; }
public string Gender_f { get; set; }
public int Contact { get; set; }

_

public bool studentAdd(Student obj)
        {
            SqlConnection conn = new SqlConnection(myconnstring);
            bool success = false;
            
            try
            {
                string sql = "INSERT INTO Students(FullName, Gender, ContactNo, Age) VALUES (@FullName, @Gender, @ContactNo, @Age)";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.Parameters.AddWithValue("@FullName", StudentName);
                cmd.Parameters.AddWithValue("@Gender", Gender_m);
                cmd.Parameters.AddWithValue("@Gender", Gender_f);
                cmd.Parameters.AddWithValue("@ContactNo", Contact);
                cmd.Parameters.AddWithValue("@Age", Age);
                conn.Open();
                int row = cmd.ExecuteNonQuery();

                if (row > 0)
                {
                    success = true;
                }
                else
                {
                    success = false;
                }


            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            } finally
            {
                conn.Close();
            }
            
            return success;
        }

I understand that I can't simply assign both entries to single column, but I'm unable to set it right using If Else or any other way. Hope you can assist. Thank you.

Tip 1: [Stop using AddWithValue](https://www.dbdelta.com/addwithvalue-is-evil/) — Dale K, Sep 04 '21 at 05:54
Tip 2: Gender should be a single column/property, not multiple. If you correctly use your radio buttons they will bind to a single property. — Dale K, Sep 04 '21 at 05:55
1. It seems SqlParameterCollection.Add method is depreciated now 2. What is the correct way to use it? — mante, Sep 04 '21 at 06:17
`cmd.Parameters.Add("@FullName", SqlDbType.VarChar, 100).Value = StudentName;` - this overload is **NOT** deprecated and it's the preferred best practice way to set a parameter and its value — marc_s, Sep 04 '21 at 07:54
More tips: connection and command objects should be in `using` blocks. Don;t block the thread with a MessageBox while the connection is open. Don't store age in a database, you would have to update it every year, instead store date of birth and calculate age from that — Charlieface, Sep 05 '21 at 00:18

score 2 · Answer 1 · answered Sep 04 '21 at 05:54

Your design should just maintain a single field recording the student gender:

public string StudentName { get; set; }
public int Age { get; set; }
public string Gender { get; set; }
public int Contact { get; set; }

When you collect the form data from your UI, you should assign either 'M' or 'F' depending on the radio button chosen.

score 2 · Accepted Answer · answered Sep 04 '21 at 11:08

You might consider changing the table design to have a first and last name, set gender as a bit which can be expressed as a string and perhaps calculate age from collecting their birth date.

Person class

public class Person
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public bool? Gender { get; set; }
    public string GenderType => Gender != null && Gender.Value ? "Male" : "Female";
    public DateTime? BirthDay { get; set; }
    public string PhoneNumber { get; set; }
    public override string ToString() => $"{FirstName} {LastName}";
}

To view data (normally we format in the app). Note that for calculating age in this case may not be exact, it's good enough to get an idea about obtaining age at runtime rather than stored in a database table as with time the age will be incorrect. Also, phone number assumes a specific format which can fail easily if that format is not given.

SELECT Id, 
       FirstName, 
       LastName,
       CASE
           WHEN Gender = 1
           THEN 'Male'
           ELSE 'Female'
       END AS Gender, 
       FORMAT(BirthDay, 'MM/dd/yyyy') AS BirthDay, 
       CONVERT(int,ROUND(DATEDIFF(hour,BirthDay,GETDATE())/8766.0,0)) AS Age,
       SUBSTRING(phoneNumber, 1, 3) + '-' + 
       SUBSTRING(phoneNumber, 4, 3) + '-' + 
       SUBSTRING(phoneNumber, 7, 4) AS PhoneNumber
FROM dbo.People;

Table design (column size can be less than NVARCHAR(MAX))

CREATE TABLE dbo.People
(Id          INT IDENTITY(1, 1) NOT NULL, 
 FirstName   NVARCHAR(MAX) NULL, 
 LastName    NVARCHAR(MAX) NULL, 
 Gender      BIT NULL, 
 BirthDay    DATETIME2(7) NULL, 
 PhoneNumber NVARCHAR(MAX) NULL, 
 CONSTRAINT PK_People PRIMARY KEY CLUSTERED(Id ASC)
)

Thank you very much. Can you pls tell me, in that case, how would the Gender and Birthday look like on the Register function? — mante, Sep 05 '21 at 01:45
See the following [project](https://github.com/karenpayneoregon/windows-forms-csharp/tree/Version1/StudentRegistration) — Karen Payne, Sep 05 '21 at 01:50

Assign two inputs to a single column of SQL table

2 Answers2