Could not open Connection to SQL Server.

[M1kkelZU]

I'm getting this error.

SQLite error
near "16": syntax error

On the code:

 private void btnUpdate_Click(object sender, EventArgs e)
        {
            for (int i = 0; i < dataGridView1.Rows.Count; i++)
            {
                string StrQuery = @"INSERT INTO Test VALUES (" + dataGridView1.Rows[i].Cells["Column1"].Value + ", " + dataGridView1.Rows[i].Cells["Column2"].Value + ", " + dataGridView1.Rows[i].Cells["Column3"].Value + ", " + dataGridView1.Rows[i].Cells["Column4"].Value + ", " + dataGridView1.Rows[i].Cells["Column5"].Value +");";

                try
                {
                    SQLiteConnection conn = new SQLiteConnection(connString);
                    conn.Open();
                    using (SQLiteCommand comm = new SQLiteCommand(StrQuery, conn))
                    {
                        comm.ExecuteNonQuery();
                    }
                    conn.Close();
                }
                catch (Exception crap)
                {
                    MessageBox.Show(crap.ToString());
                }
            }
        }

With breapoints etc it does show the values it has to insert into my database. But it just doesn't and gives me the Near "16" syntax error. Am I just beng stupid and overlooking something or is my code just totally wrong?

[Mikkeee]

Your title indicates that you cant open a connection but you didn't supply your connection string. What are you holding in your 'connString' variable?

[M1kkelZU]

I found out what was wrong first, I mistyped my connection string in connString. Now I can't change the title to the error I now have. I've constated that I get the error because the value from column1 is:

1-2-2013 16:58|H00002|NL-Radio 2|2013-01-03T00:00:00.0000000Z|172806528

And for somereason it doesn't like the space in between 2013 and 16. I'll most probably get it for NL-Radio 2 as well.

[Mikkeee]

This is happening due to the way you're performing your update query. A better technique would be to add parameters to your command and update the parameter values. This is also better because it eliminates the possibility of any type of SQL injection.

[M1kkelZU]

hmm okay, I'll look into that. Otherwise I'll post in this thread to find out what I'm doing wrong.

EDIT:
Ok so after debugging now my connection state is open (lolwat, like 10 seconds ago it was closed :P )
but now its giving me the nicest error ever

No connection associated with this command

I've seen this a trillion times in the last week and its annoying me.
Current code:

    private void btnUpdate_Click(object sender, EventArgs e) // TODO LOLWUT
        {
            SQLiteConnection con = new SQLiteConnection("Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db");

            using (SQLiteConnection conn = new SQLiteConnection(con))
            {
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    sqliteInsertCommand1.Connection = connect;
                    sqliteInsertCommand1.CommandText = "insert into Test (Date, Log Name, Channel, Date Record, Size in Bytes) values(@Date, @Log Name, @Channel, @Date Record, @Size in Bytes)";
                    
                    command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Log Name", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Date Record", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Size in Bytes", SqlDbType.VarChar));

                    if (conn.State == ConnectionState.Open)
                    {
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            if (!row.IsNewRow)
                            {
                                command.Parameters["@Date"].Value = row.Cells[0].Value;
                                command.Parameters["@Log Name"].Value = row.Cells[1].Value;
                                command.Parameters["@Channel"].Value = row.Cells[2].Value;
                                command.Parameters["@Date Record"].Value = row.Cells[3].Value;
                                command.Parameters["@Size in Bytes"].Value = row.Cells[4].Value;
                                command.ExecuteNonQuery();
                            }
                        }
                    }
                    else
                    {
                        conn.Open();
                        foreach (DataGridViewRow row in dataGridView1.Rows)
                        {
                            if (!row.IsNewRow)
                            {
                                command.Parameters["@Date"].Value = row.Cells[0].Value;
                                command.Parameters["@Log Name"].Value = row.Cells[1].Value;
                                command.Parameters["@Channel"].Value = row.Cells[2].Value;
                                command.Parameters["@Date Record"].Value = row.Cells[3].Value;
                                command.Parameters["@Size in Bytes"].Value = row.Cells[4].Value;
                                command.ExecuteNonQuery(); // Exception here
                            }
                        }
                    }
                }
            }

Why must C# torture me so? I've been nothing but kind to it.

[Mikkeee]

LOL! What seems to be torture now will turn into quick and easy code over time. The way you're now using parameters is much better than dynamic sql but it looks like you just made a couple of mistakes on your variables. The first thing I notice is that you create your SQLiteConnectio n and name it 'con' yet you're trying to use this connection to seed the connection you're defining in your first 'using' statement. You also create your SQLiteCommand with the name 'command' but you call it 'sqlliteInsertC ommand1' in your code. These must be the same. I see that you have spaces and reserved words as your field names in your database. Place brackets around these as you could have some issues down the line with these. You might want to also remove the spaces from your named parameter. You also are checking for the connection state when you really don't need to. Just open the connection the first time and you're good.

And after I posted the adjusted code I noticed the data types you have for your parameters. You should be a bit more strict here. For example, the Date field in your database should be a Date data type and this should match in your parameter. You might also want to change the SizeInBytes to a Float or whatever numeric data type can hold the expected data.

            string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
 
            using (SQLiteConnection conn = new SQLiteConnection(conString))
            {
                conn.Open();
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "insert into Test ([Date], [Log Name], [Channel], [Date Record], [Size in Bytes]) values(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
 
                    command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@LogName", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@DateRecord", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@SizeInBytes", SqlDbType.VarChar));
 
                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        if (!row.IsNewRow)
                        {
                            command.Parameters["@Date"].Value = row.Cells[0].Value;
                            command.Parameters["@LogName"].Value = row.Cells[1].Value;
                            command.Parameters["@Channel"].Value = row.Cells[2].Value;
                            command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
                            command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                conn.Close();
            }

[M1kkelZU]

Ah ok Thanks! It was torture to think of this all and made some small mistakes while at it.

Now about the datatypes. Well I have a few things I have trouble defining.
Here's an example of what I read into my datagridView from the logfile I have to insert in the database:

1-2-2013 16:59|H00002|NL-Radio 5|2013-01-03T00:00:00.0000000Z|172811136|

I replace the '|' with '##' in the code and split each "record". Now the first part: 1-2-2013 16:59 is the first record. I called it Date because of the date the recording started. I wouldn't know what to define it as in a database, so I decided as a temporary solution to use varChar until I knew which definition to use for a date and time section. come to think of it, I'm guessing its somewhere near DateTime (LOL).

Now by using the newly edited code, I keep getting an exception: SQLite error Near "Name": syntax error.
this at

command.ExecuteNonQuery();

After using a breakpoint(I learned something about debugging after 3 yeras of noobish programming lol) I have a feeling I have to rebuild the table and rid of the space for each column that has a space (i.e. Log Name, Date Record, Size in Bytes) Am I correct?

EDIT: No I was not correct, after editing the columns in my database Table it still gives me the same exceoption.

EDIT2: Now I fixed the Near "Name": syntax error. But Now I get some dumb stuff saying there is no Column named LogName eventhough I just renamed every column to the right name without a space etc. I'm stumped again

[Mikkeee]

Yes, a DateTime data type would be correct. You should also be sure that you're putting a DateTime value in your parameter value:

command.Parameters["@Date"].Value = Convert.ToDateTime(row.Cells[0].Value);

And yes, you should remove the spaces from your database and also be sure that's reflected in your CommandText. Anywhere you see [Log Name] should be replaced by [LogName].

[M1kkelZU]

I used the Replace All Function as I am that smart to use that instead of seraching through everything for Log Name etc.

Now I still get the nice error saying this

SQLite error
table Test has no column named LogName

Now, the only theory I have at the moment is that C# hates me and doesn't like it that I'm trying to make an application.
I have proof of this:
The table LogName Exists c#, don't be silly.

[Mikkeee]

Please post your revised code.

[M1kkelZU]

string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";

            using (SQLiteConnection conn = new SQLiteConnection(conString))
            {
                conn.Open();
                using (SQLiteCommand command = new SQLiteCommand())
                {
                    command.Connection = conn;
                    command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";

                    command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@LogName", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@DateRecord", SqlDbType.VarChar));
                    command.Parameters.Add(new SQLiteParameter("@SizeInBytes", SqlDbType.Float));

                    foreach (DataGridViewRow row in dataGridView1.Rows)
                    {
                        if (!row.IsNewRow)
                        {
                            command.Parameters["@Date"].Value = Convert.ToDateTime(row.Cells[0].Value);
                            command.Parameters["@LogName"].Value = row.Cells[1].Value;
                            command.Parameters["@Channel"].Value = row.Cells[2].Value;
                            command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
                            command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
                            command.ExecuteNonQuery();
                        }
                    }
                }
                conn.Close();
            }
            
        }

That should be it.

[Mikkeee]

The only thing I see wrong is:

command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));

Should be

command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.DateTime));

[M1kkelZU]

Nope still the same error. It doesn't see my columns in my DataBase

[Mikkeee]

Well, the code looks good and should work. The only thing left is the db and connection. Are you SURE that the database and path in your connection string is the database that you updated? (I've made this mistake before and made modifications to a copy I had floating around).

[M1kkelZU]

No I'm 100% sure, I only have 1 version and its uploaded to Subversion too. The only thing I can think of is that my *.s3db isnt supported, which I highly doubt.