Inserting and updating datetime in SQL database
private void ButtonOk_Click(object sender, EventArgs e)
{
if (txtWedstrijdSchemaID.Text == "")
{
//Insert
string SQL;
SQL = "Insert into Wedstrijdschema (Team1, Team2, Datum)";
SQL += " values (";
SQL += "" + txtTeam1.Text + ",";
SQL += "" + txtTeam2.Text + ",";
SQL += "" + Convert.ToDateTime(txtDatum.Text) + "";
SQL += ")";
clDatabase.ExecuteCommand(SQL);
vulLv();
}
else
{
//Update
string SQL;
SQL = "Update Wedstrijdschema SET ";
SQL += "Team1 = " + txtTeam1.Text + ",";
SQL += "Team2 = " + txtTeam2.Text + ",";
SQL += "Datum = " + Convert.ToDateTime(txtDatum.Text) + "";
SQL += " where SchemaId = " + zoek;
clDatabase.ExecuteCommand(SQL);
vulLv();
}
txtDatum.Enabled = txtTeam2.Enabled = txtTeam1.Enabled = false;
}
This is what I currently have, because of trycatch it won't fail when I try, if I comment out txtDatum.Text from // insert and // upload it works (but explicitly introduces NULL for Datum into the database) can can anyone see where i am going wrong?
EDIT: about using parameters, we need to use a threetier system where all the SQL goes through a class that is the only one that allows you to do anything with the database, this is how the command is executed:
public static bool ExecuteCommand(string SQLInstructie)
{
bool retour = true;
SqlConnection Conn = new SqlConnection(clStam.Connstr);
SqlCommand Cmd = new SqlCommand(SQLInstructie, Conn);
try
{
Cmd.Connection.Open();
Cmd.ExecuteNonQuery();
}
catch
{
retour = false;
}
finally
{
Conn.Close();
}
return retour;
}
It works!! Many thanks for the help:
private void ButtonOk_Click(object sender, EventArgs e)
{
if (txtWedstrijdSchemaID.Text == "")
{
//Insert
string SQL;
SQL = "Insert into Wedstrijdschema (Team1, Team2, Datum)";
SQL += " values (";
SQL += "" + txtTeam1.Text + ",";
SQL += "" + txtTeam2.Text + ",";
SQL += "'" + Convert.ToDateTime(txtDatum.Text) + "'";
SQL += ")";
Debug.WriteLine(SQL);
clDatabase.ExecuteCommand(SQL);
vulLv();
}
else
{
//Update
string SQL;
SQL = "Update Wedstrijdschema SET ";
SQL += "Team1 = " + txtTeam1.Text + ",";
SQL += "Team2 = " + txtTeam2.Text + ",";
SQL += "Datum = '" + Convert.ToDateTime(txtDatum.Text) + "'";
SQL += " where SchemaId = " + zoek;
clDatabase.ExecuteCommand(SQL);
vulLv();
}
txtDatum.Enabled = txtTeam2.Enabled = txtTeam1.Enabled = false;
}
EDIT: I promise to use parameterized SQL from now on!
source to share
You are missing the command ,
from the instructions INSERT
and UPDATE
.
The syntax for inserting data into the database is:
INSERT INTO Table
(Column1, Column2, Column3)
VALUES
('Value 1', 'Value 2', 'Value3')
Also, you are vulnerable to SQL injection
, use SQL-coded queries to prevent this.
First, I'll start by using an object SqlCommand
.
SqlCommand cmd = new SqlCommand("INSERT INTO Wedstrijdschema (Team1, Team2, Datum) VALUES (@V1, @V2, @V3");
cmd.Parameters.AddWithValue("@V1", txtTeam1.Text);
cmd.Parameters.AddWithValue("@V2", txtTeam2.Text);
cmd.Parameters.AddWithValue("@V3", Convert.ToDateTime(txtDatum.Text));
And then execute it with cmd.ExecuteNonQuery();
As a side note, I also ensure that the value in txtDatum
is correctly converted to the correct date format.
source to share
remove single quotes from datetime column. also you missed the column to add to insert statement
private void ButtonOk_Click(object sender, EventArgs e)
{
if (txtWedstrijdSchemaID.Text == "")
{
//Insert
string SQL;
SQL = "Insert into Wedstrijdschema (Team1, Team2,**Datum**)";
SQL += " values (";
SQL += "" + txtTeam1.Text + ",";
SQL += "" + txtTeam2.Text + "";
SQL += "" + Convert.ToDateTime(txtDatum.Text) + "";
SQL += ")";
clDatabase.ExecuteCommand(SQL);
vulLv();
}
else
{
//Update
string SQL;
SQL = "Update Wedstrijdschema SET ";
SQL += "Team1 = " + txtTeam1.Text + ",";
SQL += "Team2 = " + txtTeam2.Text + "";
SQL += "Datum = " + Convert.ToDateTime(txtDatum.Text) + "";
SQL += " where SchemaId = " + zoek;
clDatabase.ExecuteCommand(SQL);
vulLv();
}
txtDatum.Enabled = txtTeam2.Enabled = txtTeam1.Enabled = false;
}
source to share
Always use parameterized . The queries.string concatenations make a way for SQL injection
private void ButtonOk_Click(object sender, EventArgs e)
{
if (txtWedstrijdSchemaID.Text == "")
{
SqlCommand cmd = new SqlCommand("Insert into Wedstrijdschema (Team1, Team2, Datum) values (@Team1,@Team2,@datetime)");
cmd.Parameters.AddWithValue("@Team1",txtTeam1.Text
cmd.Parameters.AddWithValue("@Team2",txtTeam2.Text
cmd.Parameters.AddWithValue("@datetime",Convert.ToDateTime(txtDatum.Text)
clDatabase.ExecuteCommand(SQL);
vulLv();
}
else
{
SqlCommand cmd = new SqlCommand("Update Wedstrijdschema SET Team1=@team1,Team2=@team2,Datum =@Datum where SchemaId=@SchemaId");
cmd.Parameters.AddWithValue("@team1",txtTeam1.Text );
cmd.Parameters.AddWithValue("@team2",txtTeam2.Text);
cmd.Parameters.AddWithValue("@Datum ",Convert.ToDateTime(txtDatum.Text);
cmd.Parameters.AddWithValue("@SchemaId",zoek);
clDatabase.ExecuteCommand(SQL);
vulLv();
}
txtDatum.Enabled = txtTeam2.Enabled = txtTeam1.Enabled = false;
}
source to share
Use ToString
to format the date in an acceptable format (and enclose it in quotes when passed as a string):
string SQL;
SQL = "Insert into Wedstrijdschema (Team1, Team2, Datum)";
SQL += " values (";
SQL += "" + txtTeam1.Text + ",";
SQL += "" + txtTeam2.Text + ",";
SQL += "'" + Convert.ToDateTime(txtDatum.Text).ToString("yyyy-MM-dd HH:mm:ss") + "'";
SQL += ")";
source to share