CODEWITHSUNDEEP
c#sqlbooleansql-updatesql-insert
Brad
Brad

Reputation: 7

C# Update Bool value to Database

I have two radio boxes. One true, the other false. I have a flag set up, but it didn't work. Here is my code right now 

string sqlStatement;
SqlConnection cnn = new SqlConnection(Properties.Settings.Default.cnnString);

cnn.Open();
SqlCommand cmd = cnn.CreateCommand(); 
cmd.CommandType = CommandType.Text;
sqlStatement = string.Format("UPDATE Results SET Finish = '{0}', Place = '{1}', FinishTime = {2}, Winnings = '{3}' Where ResultsId = '{4}' " + (rdoDidFinish.Checked ? 1 : 0), txtPlace.Text, txtTime.Text, txtWinnings.Text);

cmd.CommandText = sqlStatement;
cmd.ExecuteNonQuery();
cnn.Close();

The error I am getting is:

"Index (zero based) must be greater than or equal to zero and less than the size of the argument list."

Upvotes: 0

Views: 1997

Answers (3)

Chris
Chris

Reputation: 79

Your Where ResultsId = '{4}' does not currently have any values being inserted into it.

Add another parameter after txtWinnings.txt providing the ResultsId and replace the + with a , before (rdoDidFinish.Checked ? 1 : 0) so that it is recognized as its own parameter.

The correct statement should look something like this, where resultId is whatever id you want to search for:

sqlStatement = string.Format("UPDATE Results SET Finish = '{0}', Place = '{1}', FinishTime = {2}, Winnings = '{3}' Where ResultsId = '{4}' ", (rdoDidFinish.Checked ? 1 : 0), txtPlace.Text, txtTime.Text, txtWinnings.Text, resultId);

One final note, in line with the comments on your question, you should always prepare your parameters. So your final result should be something along the lines of:

cmd.CommandText = @"UPDATE Results SET Finish = @finished, Place = @place, FinishTime = @time, Winnings = @winnings Where ResultsId = @resultId ";
cmd.Parameters.Add(new SqlParameter("@finished", System.Data.SqlDbType.Bit).Value = rdoDidFinish.Checked);
cmd.Parameters.Add(new SqlParameter("@place", System.Data.SqlDbType.VarChar).Value = txtPlace.Text);
cmd.Parameters.Add(new SqlParameter("@time", System.Data.SqlDbType.VarChar).Value = txtTime.Text);
cmd.Parameters.Add(new SqlParameter("@winnings", System.Data.SqlDbType.VarChar).Value = txtWinnings.Text);
cmd.Parameters.Add(new SqlParameter("@resultId", System.Data.SqlDbType.Int).Value = resultId);

Upvotes: 0

Mad Myche
Mad Myche

Reputation: 1075

I see a few problems here.

NEVER EVER build a SQL Statement by piecing together a string and values, doesn't matter if it is stringbuilder or string.format, or what not. This is language agnostic; DON'T do it in any language. For more information on the dangers, please Google "SQL Injection" The best thing to do is use paramaterized queries. Sample will follow.

Your problem is actually in your string.Format line. There are 2 errors here

Error 1: I believe the + should be a , here: '{4}' " + (rdoDidFinish.Checked ? 1 : 0)

Error 2 You have 5 placeholders, and only 3 values. 4 if the above (Err 1) statement is corrected

Here is a simple mock up assuming Err 1 fixed.. Need to know what the ResultID value is.

string sqlStatement = "UPDATE Results SET Finish = @Finish, Place = @Place, FinishTime = @FinishTime, Winnings = @Winnings WHERE ResultsId = @ResultID";

using (SqlConnection cnn = new SqlConnection(Properties.Settings.Default.cnnString)) {
    SqlCommand cmd = cnn.CreateCommand(sqlStatement, cnn);
    cmd.CommandType = CommandType.Text;

    cmd.Parameters.AddWithValue("@Finish", (rdoDidFinish.Checked ? 1 : 0));     // I think this belongs here.
    cmd.Parameters.AddWithValue("@Place", txtPlace.Text);
    cmd.Parameters.AddWithValue("@FinishTime", txtTime.Text);
    cmd.Parameters.AddWithValue("@Winnings", txtWinnings.Text);
    // cmd.Parameters.AddWithValue("@ResultID", );                                ? What belongs here ?

    cnn.Open();
    cmd.ExecuteNonQuery();
    cnn.Close();

}

Upvotes: 2

JanMer
JanMer

Reputation: 1266

Please use parameters in a prepared statement!

Example:

var myParam = new SqlParameter();
myParam.ParameterName = "@myParam";
myParam.Value = 1;
comm.Parameters.Add(myParam);

You can use the parameter in your SQL-Statement like

Select * from table where abc = @myparam

Upvotes: 0

Related Questions