CODEWITHSUNDEEP
c#sqlsql-serverinsertsql-server-ce
the1mike1man
the1mike1man

Reputation: 47

INSERT INTO command not working

Before I start, I'll let you know that I tried everything that has already been suggested on previous questions and other websites before I considered posting a question myself. As it happens, nothing seems to work and I'm just about fed up with this.

As some background information, this is for my Computing A2 project, so I'm kind of stuck for time now - i.e. I can't be changing loads of my code ideally.

Anyway, onto the issue... I'm using SQLCe in my code to read from various tables and write to one. So far, the code for reading from the tables works fine, so that's any connection issues out the way first. The piece of code I am struggling with is as follows:

        string connectionString = Properties.Settings.Default.BookingSystemDatabaseConnectionString;
        using (SqlCeConnection myConnection = new SqlCeConnection(connectionString))
        {
            myConnection.Open();
            try
            {
                string commandStr = "INSERT INTO bookings(username, room, time) VALUES(@username, @room, @time)";
                SqlCeCommand myCommand = new SqlCeCommand(commandStr);
                //Passes parameters into SQL command.
                myCommand.Parameters.AddWithValue("username", StaticUser.StudentUser.username);
                myCommand.Parameters.AddWithValue("room", roomBox.Text);
                myCommand.Parameters.AddWithValue("time", timeBox.Text);
                //Executes SQL command. Returns the number of affected rows (unecessary for my purposes; a bi-product if you will).
                myCommand.ExecuteNonQuery();
            }
            catch
            {
                System.Windows.Forms.MessageBox.Show("Could not write new booking to database. This is likely because the database cannot be reached.", "Error");
                Program.AccessError = true;
            }
            myConnection.Close();
        }

This is just one of the many ways I have tried to combat the issue I am having. I have also explored:

myCommand.Parameters.Add(new SqlCeParameter("username", StaticUser.StudentUser.username));

to pass the parameters...and another method which escapes me now (using ".Value = StaticUser.StudentUser.username" I think). Furthermore, I have tried using a 'using' statement for the command to save me closing the connection myself (I will probably end up using a solution that uses 'using'). Finally (albeit this isn't a chronological recollection), I tried:

SqlCeCommand myCommand = new SqlCeCommand("INSERT INTO bookings(username, room, time) VALUES(@username, @room, @time)", myConnection)

Again, of course, to no avail.

To highlight the actual symptoms of the issue I am having: The code appears to run fine; stepping through the full method I have pasted above shows that no error is being caught (of course, the message box does not appear - I realised afterwards that stepping through was arguably an unnecessary procedure) and in the other methods I have touched on, the same thing happens. The issue, then, is that the table 'bookings' is not actually being updated.

So, my question, why?

I didn't do the obvious and check the Debug folder for an updated database.

Upvotes: 1

Views: 1720

Answers (2)

XTGX
XTGX

Reputation: 114

i think you are not defining a connection for the command try 

mycommand.connection = connectiostring;

Upvotes: 0

ErikEJ
ErikEJ

Reputation: 41749

Look for a copy of the database file in your bin/debug folder. Use full path in connection string, and preferably do not include the sdf file in your project (or at least set build action to None)

Upvotes: 1

Related Questions