Mohamed Ahmed Mohamed Ahmed - 3 months ago 26
SQL Question

Inserting date from DataGridView into database?

In my application, I have a dataGridview that has a column that gets its data from the database, and it represents a column of type date, there is a button when the user click it, it should insert the current select value of that column in another table that has a column of type date.
I tried doing this that way:

private void btnInesrtDate_Click(object sender, EventArgs e)
{
string date = dataGridView1.CurrentRow.Cells[2].ToString();
string sql = $"Insert Into BirthDates (Name, DateOfBirth)Values('{txtName.Text}','{date}')";
conn.Open();
SqlCommand command = new SqlCommand(sql, conn);
command.ExecuteNonQuery();
conn.Close();
MessageBox.Show("Success");
}


but I got an error says:


Conversion failed when converting date and/or time from character
string.

Answer

I'm not sure what that syntax is that you're using, but you need to fix it. What you've got right now is just specifying some hard-coded values... it can't parse the string literal '{date}' to a DateTime.

Use parameters to safely pass values to your query. This is untested, but should work (read the comments too):

private void btnInesrtDate_Click(object sender, EventArgs e)
{
    string sql = "Insert Into BirthDates (Name, DateOfBirth) Values (@Name, @BirthDate)";

    // Always parameterize your query... it's more secure and less prone to errors
    DateTime birthday = Convert.ToDateTime(dataGridView1.CurrentRow.Cells[2].Value);
    command.Parameters.AddWithValue("@Name", txtName.Text);
    command.Parameters.AddWithValue("@BirthDate", birthday);

    conn.Open();

    // Use a Try/Finally block to so the connection is closed even if an exception occurs
    try
    {
        using (var command = new SqlCommand(sql, conn))
        {
            command.ExecuteNonQuery();
            MessageBox.Show("Success");
        }
    }
    finally
    {
        conn.Close();
    }
}