SQL insert syntax, database not updating

Question

EDIT

i changed the code so i am using:[" + Time1 + "] instead of the parameter. This works for the first time, but when the time increases by 0.5, it stays false. The for loop is working as i tried a MessageBox.Show("" + Time1 + ""); inside the for loop.

for (double Time = time_began_5; Time < time_finished_5; Time = Time + 0.5)
        {
            string Time1 = Time.ToString("0.00");


            try
            {
                SqlConnection cn = new SqlConnection("Data Source=.\\SqlExpress;Initial Catalog=AllensCroft;Integrated Security=True;MultipleActiveResultSets=True;Application Name=EntityFramework;");

                cn.Open();
                SqlCommand Command = new SqlCommand("INSERT INTO Slots ([Date],[RoomID],[" + Time1 + "]) Values (@date,@room,1)", cn);
                Command.Parameters.AddWithValue("date", date);
                Command.Parameters.AddWithValue("room", rooms_combo.SelectedValue);


                Command.ExecuteNonQuery();


                try
                {
                    cn.Close();
                }
                catch (Exception e)
                {
                    Console.WriteLine(e.ToString());
                }

            }
            catch (Exception e)
            {
                Console.WriteLine(e.ToString());
            }

        }

First of all, is the loop even executing? P.S. It's generally considered bad practice to use a Double as an indexer variable in a for loop! — Mike Dinescu
– Mike Dinescu, Commented Mar 13, 2013 at 19:38
is @time your column name if not then why did you use there? — Sachin
– Sachin, Commented Mar 13, 2013 at 19:39
Time is a column and a variable and i wanted it to be true, is that possible — Hamoudy
– Hamoudy, Commented Mar 13, 2013 at 19:41

Jack Pettinger · Accepted Answer · 2013-03-13 20:14:13Z

2

You have a @ before your 3rd column (time).
When you add a parameter you need to add the @.
On your insert statement you are trying to insert true as a boolean into the time column.

SqlCommand Command = new SqlCommand("INSERT INTO Slots (Date,RoomID,time) " + "Values (@date,@room,@time)", cn);

Command.Parameters.AddWithValue("@date", date);
Command.Parameters.AddWithValue("@room", rooms_combo.SelectedValue);
Command.Parameters.AddWithValue("@time", Time);

EDIT After comments. Try this:

SqlCommand Command = new SqlCommand("INSERT INTO Slots (Date,RoomID,[" + Time1 + "]) " + "Values (@date,@room,@time)", cn);

Command.Parameters.AddWithValue("@date", date);
Command.Parameters.AddWithValue("@room", rooms_combo.SelectedValue);
Command.Parameters.AddWithValue("@time", true);

edited Mar 13, 2013 at 20:14

answered Mar 13, 2013 at 19:42

Jack Pettinger

2,7652 gold badges25 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hamoudy Over a year ago

Time is a column and it is boolean and i wanted it to be true, is that possible, e.g. the column 8.5, i wanted that to be true

Jack Pettinger Over a year ago

If you want to insert true into the time column, then the SQL datatype needs to be a bit. Is this is the case then there is no need to the @time parameter, what is it for?

Hamoudy Over a year ago

it is just to reference the time column, e.g.8.50, 8.00

Jack Pettinger Over a year ago

Are you saying you have different columns for different times? ie, a column for 8.00, 8.15, 8.30 etc.

Chains · Accepted Answer · 2013-03-13 19:58:19Z

0

You're listing a variable as a column name (@time)

EDIT
Right here:

INSERT INTO Slots (Date, RoomID, --->>> @time <<<--- DANGER WILL ROBINSON, DANGER

To fix it, you need to either change it to a column name from your table, or else get rid of it.

So something like this, for example:

INSERT INTO Slots (Date, RoomID, time)

edited Mar 13, 2013 at 19:58

answered Mar 13, 2013 at 19:39

Chains

13.2k8 gold badges48 silver badges62 bronze badges

Comments

MattW · Accepted Answer · 2013-03-13 20:10:16Z

0

You can't include a variable in your column list, I presume you wanted that @Time to be in the value list about where that true is.

EDIT: To inject the time as a column name, do some C# string manipulation:

SqlCommand Command = new SqlCommand("INSERT INTO Slots (Date, RoomID, [" + time + "]) Values (@date, @room, 1)", cn);

EDIT again: true is not a T-SQL keyword, T-SQL bit columns have values 1 or 0

Now you don't want to add time as a parameter to the query.

edited Mar 13, 2013 at 20:10

answered Mar 13, 2013 at 19:38

MattW

4,6581 gold badge14 silver badges11 bronze badges

6 Comments

Hamoudy Over a year ago

Time is a column and a variable and i wanted it to be true, is that possible, e.g. the column 8.5, i wanted that to be true

MattW Over a year ago

@Hamoudy Answer updated with what I think you you're asking for

Hamoudy Over a year ago

i thied this, but same problem SqlCommand Command = new SqlCommand("INSERT INTO Slots (Date,RoomID,8.00) Values (@date,@room,true)", cn);

MattW Over a year ago

@Hamoudy The square brackets I included are vital, 8.00 requires escaping to be used as a column name. Also, I just updated the answer to reflect that you don't use true as a value in SQL Server, you use 1.

MattW Over a year ago

Also, what you're trying to do with those time columns isn't really in line with what relational database theory suggests. You're breaking the third normal form because your time columns aren't independent.

|

Collectives™ on Stack Overflow

SQL insert syntax, database not updating

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

Comments

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related