Strah Behry Strah Behry - 7 months ago 30
SQL Question

Insert and update a datetime into 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;
}


That is what I currently have, because of a trycatch it won't crash when I try, if I comment the txtDatum.Text out on the //insert and //upload it works (but obviously enters NULL for Datum in the Database) does anyone perhaps see where I'm going wrong?

EDIT: About the use of parameters, we need to use a threetier system where all SQL goes through a class which is the only one allowed 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;
}


This works!! Thanks a lot 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'll promise to use parameterized SQL from now on!

Answer

You are missing a command , from the INSERT and UPDATE statement.

The syntax to insert data into the database is:

 INSERT INTO Table 
        (Column1, Column2, Column3) 
 VALUES
        ('Value 1', 'Value 2', 'Value3')

Aside that, you are vulnerable to SQL injection, use SQL paramerterised queries to prevent this.

I would first start off by using a SqlCommand object.

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 using cmd.ExecuteNonQuery();

As an additional note I would also ensure that the value in txtDatum is converted correctly to the desired date format.

Comments