How to update the date in C# through SQL Server

c#, database, sql-server

Solution

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;

Problem

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(); ```

Original source