Its Visual Studio 2013. How do I convert string to DateTime?

Question

I have created a database and a windows form application in C# on Visual Studio 2013. I get an error "Cannot convert datetimepicker from character string" How can I convert the birthdateDateTimePicker from character string in my code? do I have to use a cast operater?
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Practice_3
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void patientBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.patientBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this.medicaldblDataSet);
}
private void MainForm_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the 'medicaldblDataSet.Patient' table. You can move, or remove it, as needed.
this.patientTableAdapter.Fill(this.medicaldblDataSet.Patient);
}
private void btnSave_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(global::Practice_3.Properties.Settings.Default.MedicaldblConnectionString);
try
{
string sql = "Insert into Patient (PatientID, PatientName, PatientLastName, Birthdate, Alias) Values (" + patientIDTextBox.Text + ",'"+ patientNameTextBox.Text + "','" + patientLastNameTextBox.Text + "','" + birthdateDateTimePicker + "','" + aliasTextBox.Text + "')";
SqlCommand pro = new SqlCommand(sql, conn);
conn.Open(); //open the database
pro.ExecuteNonQuery();
MessageBox.Show("The data was saved successfully","", MessageBoxButtons.OK, MessageBoxIcon.Information);
this.patientTableAdapter.Fill(this.medicaldblDataSet.Patient);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message,"Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
conn.Close();
}
}
private void btnRefresh_Click(object sender, EventArgs e)
{
this.patientTableAdapter.Fill(this.medicaldblDataSet.Patient);
}
}
}

A A · Answer

Hello again thank you for all of your help I have learned a lot. I asked a classmate of mine about my code and he said that I made it more complicated than what it was supposed to be but, I don't mind because it helps me practice. To make it a little less complicated I deleted the the checkbox and 1 datetimepicker from the code as well as in the designer. my code runs without any build errors but, now I am having two errors when I execute the application.
1.) The first error is when I am filling out the fields i use the TAB key to move from one textbox to the next but when I hit the TAB key to go to the last textbox all of my data diappears from all the fields and a -1 appears in my DataGridView (and non of the data is saved in any of the columns)
2.)My second error is when I hit the save button an error pops up and says:
INCORRECT SYNTAX NEAR ".
I have looked for information but i cannot find any real solution to these problems.
here is my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.Sql;
namespace database_project
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void tblPatientBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.tblPatientBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this._DoctorRecords_SQLServer_DataSet);
}
private void MainForm_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the '_DoctorRecords_SQLServer_DataSet.tblPatient' table. You can move, or remove it, as needed.
this.tblPatientTableAdapter.Fill(this._DoctorRecords_SQLServer_DataSet.tblPatient);
}
private void btnSave_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=MainUser;Initial Catalog=DoctorRecords(SQLServer);Integrated Security=True");
try
{
string say = ("insert into tblPatient(PatientID, Name, LastName, RAddress, PAddress, " +
" Alias, HomeTel, Cell, Email, Birthdate, [Where do you spend most of your time?], [Friends and Family], " +
"[Homeless or Sheltered], [Where do you sleep]) Values('" +
patientIDTextBox.Text + "','" + nameTextBox.Text + "','" + lastNameTextBox.Text + "','" + rAddressTextBox.Text +
"','" + pAddressTextBox.Text + "','" + aliasTextBox.Text + "','" + homeTelTextBox.Text + "','" + cellTextBox.Text + "','" + emailTextBox.Text +
"','" + timeSpentTextBox.Text + "','" + fFamilyTextBox.Text + "','" + homeless_ShelteredTextBox.Text + "','" + sleepTextBox.Text + "'");
SqlCommand yay = new SqlCommand(say, conn);
conn.Open();
yay.ExecuteNonQuery();
MessageBox.Show("Data successfully saved", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
patientIDTextBox.Clear();
nameTextBox.Clear();
lastNameTextBox.Clear();
rAddressTextBox.Clear();
pAddressTextBox.Clear();
aliasTextBox.Clear();
homeTelTextBox.Clear();
cellTextBox.Clear();
emailTextBox.Clear();
timeSpentTextBox.Clear();
fFamilyTextBox.Clear();
homeless_ShelteredTextBox.Clear();
sleepTextBox.Clear();
conn.Close();
}
}
}
}

Vulpes · Answer

Am I right in thinking that the columns whose values you're trying to insert using checkbox values are of the BIT type in SQL Server?
If they are, then you need to insert 1 if the checkbox is checked or 0 if it isn't. Also there shouldn't be any single quotes around these values.
So instead of "'" + sexCheckBox + "'"
you want to include the following expression in your string:
(sexCheckBox.Checked ? 1 : 0)

A A · Answer

I dont know why but, my data would not save to the table. Because I am practicing I decided to start fresh and created tables using MSSQL instead of Visual Studio. I am simply practicing because I am trying to better my coding skills. I looked for more info and found an easier way to connect my database to my C# windows form. At the moment I am trying to run the program but, there is an error that I keep getting: "INCORRECT SYNTAX NEAR 'SYSTEM.WINDOWS.FORMS.CHECKBOX, CHECK STATE: 0'". I know that checkboxes are empty by default but, I want one of them to be checked for my program (male or female and yes or no). Do I have to convert it like the date and if so how? Do I even have to include my checkbox in the Insert statement? I am still practicing at becoming better at coding so if there is anything else that anyone thinks I should change in my code to make it better I am open to suggestions. here is my newly created code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
namespace Practice
{
public partial class MainForm : Form
{
public MainForm()
{
InitializeComponent();
}
private void MainForm_Load(object sender, EventArgs e)
{
// TODO: This line of code loads data into the '_DoctorRecords_SQLServer_DataSet.tblPatient' table. You can move, or remove it, as needed.
this.tblPatientTableAdapter.Fill(this._DoctorRecords_SQLServer_DataSet.tblPatient);
SqlConnection conn = new SqlConnection("Data Source=MainUser;Initial Catalog=DoctorRecords(SQLServer);Integrated Security=True");
try
{
string say = ("Insert into tblPatient(PatientID, Name, LastName, RAddress, PAddress, Birthdate, Alias, HomeTel, Cell, Email, DateAttended, Sex, [Where do you spend most of your time?], [Friends and Family], [Homeless or Sheltered], [Where do you sleep]) Values('" +
patientIDTextBox.Text + "','" + nameTextBox.Text + "','" + lastNameTextBox.Text + "','" + rAddressTextBox.Text +
"','" + pAddressTextBox.Text + "','" + birthdateDateTimePicker.Value.ToString("yyyy, MM, dd") + "','" + aliasTextBox.Text + "','" + homeTelTextBox.Text + "','" + cellTextBox.Text + "','" + emailTextBox.Text +
"','" + dateAttendedDateTimePicker.Value.ToString("yyyy, MM, dd") + "','" + sexCheckBox + "','" + timeSpentTextBox.Text + "','" + fFamilyTextBox.Text + "','" + homeless_ShelteredTextBox.Text + "','"
+ sleepTextBox.Text + "','" + yesPicCheckBox.ToString() + "','" + noPicCheckBox.ToString() + "'");
SqlCommand yay = new SqlCommand(say, conn);
conn.Open();
yay.ExecuteNonQuery();
if (sexCheckBox == null && femaleCheckBox == null)
{
MessageBox.Show("Please Enter your sex", "Data Missing", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
if (yesPicCheckBox == null)
{
noPicCheckBox.Checked = true;
}
MessageBox.Show("Data saved successfully", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
finally
{
patientIDTextBox.Clear();
nameTextBox.Clear();
lastNameTextBox.Clear();
rAddressTextBox.Clear();
pAddressTextBox.Clear();
aliasTextBox.Clear();
homeTelTextBox.Clear();
cellTextBox.Clear();
emailTextBox.Clear();
timeSpentTextBox.Clear();
fFamilyTextBox.Clear();
homeless_ShelteredTextBox.Clear();
sleepTextBox.Clear();
conn.Close();
}
}
private void tblPatientBindingNavigatorSaveItem_Click(object sender, EventArgs e)
{
this.Validate();
this.tblPatientBindingSource.EndEdit();
this.tableAdapterManager.UpdateAll(this._DoctorRecords_SQLServer_DataSet);
}
}
}

Vulpes · Answer

I can't see any obvious reason why it wouldn't insert the record.
Are you sure that your connection string is OK and that in particular your userid and password are correct?
The only other (remote) possibility is that a trigger is replacing the insert command with something else.

A A · Answer

Thank you for your help it worked but, I have another question? I insert data into my table and it said "data saved successfully" but, when I check my Table I dont have any Data saved in my database. Do you know why this might happen?

Vulpes · Answer

Try:
string sql = "Insert into Patient (PatientID, PatientName, PatientLastName, Birthdate, Alias) Values (" + patientIDTextBox.Text + ",'"+ patientNameTextBox.Text + "','" + patientLastNameTextBox.Text + "','" + birthdateDateTimePicker.Value.ToString("yyyy-MM-dd") + "','" + aliasTextBox.Text + "')";

Sibeesh Venu · Answer

Try this : http://stackoverflow.com//questions/6530536/date-time-conversion-problem-when-inserting-data-into-sql-database
Find out more here : http://vb.net.bigresource.com/SQL-Exception-on-Insert-of-dateandtimepicker-value-date-into-date-datatype-Conversion-failed-when-converting-date-and-or-time-from-character-string--3MIjsqgCt.html