How to update the date in C# through SQL Server

Question

I'm making a sports club management system for my intro to software development course. I am using SQL Server in the backend. I add a member and also update a member.

In C# I have used the datetimepicker and it works fine for adding a new member. It gives the exact date selected. However when I want to update a member information it gives some weird errors like 'wrong input near nvarchar' when I haven't even used nvarchar anywhere in the SQL Server table.

Here is my code. Can anyone just tell where I'm wrong.

string connectionstring = "Server=Momal-PC\\MOMAL;Database=Project;Trusted_Connection=True;";

SqlConnection conn = new SqlConnection();
conn.ConnectionString = connectionstring;
conn.Open();

string sql = "UPDATE Members SET Type = '" + comboBox1.Text + "' , FirstName = '" + FNTxt.Text + "', Address = '" + ATxt.Text + "' , [D.O.B] = '" + dateTimePicker1.Value + "' Where Number  = '" + comboBox1.Text + "'";

SqlCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = sql;

cmd.Parameters.Add(new SqlParameter("@Type", comboBox1.Text));
cmd.Parameters.Add(new SqlParameter("@FirstName", FNTxt.Text));
cmd.Parameters.Add(new SqlParameter("@Address", ATxt.Text));
cmd.Parameters.Add(new SqlParameter("@[D.O.B]", dateTimePicker1.Value));

int rowsInserted = cmd.ExecuteNonQuery();
MessageBox.Show("Done!");

conn.Close();

Answer 1

Firstly, you need to correct your code as follows, so that you make use of the parameters. Secondly, Can you tell us the type of the [D.O.B] field?

string connectionstring = "Server=Momal-PC\\MOMAL;Database=Project;Trusted_Connection=True;";
using(SqlConnection conn = new SqlConnection(connectionstring ))
{
    conn.Open();

    string sql = "UPDATE Members SET Type = @Type , FirstName = @FirstName, Address = @Address , [D.O.B] = @[D.O.B] Where Number  = '" + comboBox1.Text + "'";


    using(SqlCommand cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = sql;

        cmd.Parameters.Add(new SqlParameter("@Type", comboBox1.Text));
        cmd.Parameters.Add(new SqlParameter("@FirstName", FNTxt.Text));
        cmd.Parameters.Add(new SqlParameter("@Address", ATxt.Text));
        cmd.Parameters.Add(new SqlParameter("@[D.O.B]", SqlDbType.DateTime));
        cmd.Parameters["@[D.O.B]"].Value = dateTimePicker1.Value;

        int rowsInserted = cmd.ExecuteNonQuery();
        MessageBox.Show("Done!");
    }
   conn.Close()
}

EDIT:

To do it using a stored procedure, first create one:

Then add/copy/paste the parameters and UPDATE command and press F5 to "execute" the command and create the stored procedure:

CREATE PROCEDURE UpdateMemberByID
@MemberID int,
@Type varchar(MAX),
@FirstName varchar(MAX),
@Address varchar(MAX),
@DOB smalldatetime
AS
BEGIN
SET NOCOUNT ON;
UPDATE Members SET 
[Type] = @Type
[FirstName] = @FirstName,
[Address] = @Address,
[DOB] = @DOB 
WHERE Number  = @MemberID
END
GO

Finally change this:

    cmd.CommandType = CommandType.Text;

To:

    cmd.CommandType = CommandType.StoredPrcedure;