Could not open Connection to SQL Server.

[Mikkeee]

Re-post your code again.

[M1kkelZU]

I'll post the 3 core components, OpenLog, Updateing the DB and getting from the DB.

getting DB info:

        private void btnGetDB_Click(object sender, EventArgs e)
        {
            string connString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
            string query = "SELECT * FROM Test";
            SQLiteDataAdapter dAdapter = new SQLiteDataAdapter(query, connString);
            SQLiteCommandBuilder cBuilder = new SQLiteCommandBuilder(dAdapter);
            DataTable dTable = new DataTable();
            SQLiteConnection connection = new SQLiteConnection(connString);
            connection.Open();
            try
            {
                dAdapter.Fill(dTable);
            }
            catch (Exception error)
            {
                MessageBox.Show(error.ToString());
            }
            BindingSource bSource = new BindingSource();
            bSource.DataSource = dTable;
            dataGridView1.DataSource = bSource;
            dAdapter.Update(dTable);
        }

Opening log:

 private void btnOpenLog_Click(object sender, EventArgs e)
        {
            OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                try
                {
                    dataGridView1.DataSource = LoadTable(openFileDialog1.FileName);
                    btnUpdate.Enabled = true;
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error:  " + err.Message, "Program Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    btnUpdate.Enabled = false;
                }
            }
        }

        private DataTable LoadTable(string filename)
        {
            dTable.Columns.Add(new DataColumn("Date", typeof(string)));
            dTable.Columns.Add(new DataColumn("LogName", typeof(string)));
            dTable.Columns.Add(new DataColumn("Channel", typeof(string)));
            dTable.Columns.Add(new DataColumn("DateRecord", typeof(string)));
            dTable.Columns.Add(new DataColumn("SizeInBytes", typeof(float)));

            DataRow dr;

            String sLine = new StreamReader(filename).ReadToEnd();
            if (sLine != null)
            {
                foreach (string regel in Regex.Split(sLine, "\r\n"))
                {
                    if (!string.IsNullOrEmpty(regel))
                    {
                        dr = dTable.NewRow();

                        string[] splitregel = Regex.Split(regel.Replace("|", ";"), ";");
                        dr[0] = splitregel[0];
                        dr[1] = splitregel[1];
                        dr[2] = splitregel[2];
                        dr[3] = splitregel[3];
                        dr[4] = splitregel[4];

                        dTable.Rows.Add(dr);
                    }
                }
            }
            return dTable;
        }

And Update DB:

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

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

                    param[0] = new SQLiteParameter("@Date", SqlDbType.VarChar);
                    param[1] = new SQLiteParameter("@LogName", SqlDbType.VarChar);
                    param[2] = new SQLiteParameter("@Channel", SqlDbType.VarChar);
                    param[3] = new SQLiteParameter("@DateRecord", SqlDbType.VarChar);
                    param[4] = new SQLiteParameter("@SizeInBytes", SqlDbType.Float);

                    for (int i = 0; i < param.Length; i++)
                    {
                        command.Parameters.Add(param[i]);
                    }

                    command.CommandType = CommandType.Text;
                    command.ExecuteNonQuery();

                    
                }
                conn.Close();
           } 
        }

[Mikkeee]

I'm looking in your Update and you're still not assigning any values to your parameters.

   for (int i = 0; i < param.Length; i++)
   {
      command.Parameters.Add(param[i]);
      command.Parameters.Value = NEEDVALUEHERE;
   }

[M1kkelZU]

Yeah Problem is I have no clue what the value would have to be. Also I cannot open the .exe file in the debug flder but thats not my problem at the moment lol.

[Mikkeee]

Well, you stated "it doesn't insert what I want to though, same thing 22-22-22-6 for each column.". If 22-22-22-6 is wrong, what is right?

[M1kkelZU]

it should insert, example from the file It reads from:

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

Hence Date, LogName, Channel, DateRecord, SizeInBytes (column names)

[M1kkelZU]

Well, I think I know why its not retrieving what I''m saving into my database. Because its not saving in to my database... I'm on the brink of giving up lol.

[Mikkeee]

Don't give up! As I stated before, you're not saving anything to your database because you're not assigning any values to your parameters. You say you want the values coming from a file you're reading but you're not reading from anything in your update db routine.

[M1kkelZU]

What if I changed it into that when I open the file it updates it straight away in to the database? Would that be a possible fix?

[Mikkeee]

Yes. You need to combine your log file read routine with your database update routine and you should be good to go.

[M1kkelZU]

ok so I've moved the code I had into my file reader part and what not and I ge tthe same exception: Input String was not in correct format.

OpenFileDialog openFileDialog1 = new OpenFileDialog();
            if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
            {
                try
                {
                    dataGridView1.DataSource = LoadTable(openFileDialog1.FileName);
                    string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";

                    using (SQLiteConnection conn = new SQLiteConnection(conString))
                    {
                        using (SQLiteCommand command = new SQLiteCommand())
                        {
                            conn.Open();

                            command.Connection = conn;

                            command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";

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

                            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.CommandType = CommandType.Text;
                            command.ExecuteNonQuery();
                        }
                        conn.Close();
                    } 
                }
                catch (Exception err)
                {
                    MessageBox.Show("Error:  " + err.Message, "Program Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
                    btnUpdate.Enabled = false;
                }
            }

Thats my code. Could you have a look at it when you have time?

[Mikkeee]

See comments in code below:

using (SQLiteCommand command = new SQLiteCommand())
{
    conn.Open();

    command.Connection = conn;
    command.CommandType = CommandType.Text;
    command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
    
    // Changed AddWithValue to Add
    command.Parameters.Add("@Date", SqlDbType.VarChar);
    command.Parameters.Add("@LogName", SqlDbType.VarChar);
    command.Parameters.Add("@Channel", SqlDbType.VarChar);
    command.Parameters.Add("@DateRecord", SqlDbType.VarChar);
    command.Parameters.Add("@SizeInBytes", SqlDbType.Float);

    foreach (DataGridViewRow row in dataGridView1.Rows)
    {
        if (!row.IsNewRow)
        {
            command.Parameters["@Date"].Value = row.Cells[0].Value.ToString();
            command.Parameters["@LogName"].Value = row.Cells[1].Value.ToString();
            command.Parameters["@Channel"].Value = row.Cells[2].Value.ToString();
            command.Parameters["@DateRecord"].Value = row.Cells[3].Value.ToString();
            // Make sure "row.Cells[4].Value" returns a float or convert it before setting the parameter value
            command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
            command.ExecuteNonQuery();
        }
    }
    // Move the following line up where you're assigning the command text 
    //command.CommandType = CommandType.Text;
    
    // This should be in your foreach loop if you want each row in your
    // DataGridView if you want all the items to be inserted
    //command.ExecuteNonQuery();
}
conn.Close();

[M1kkelZU]

Lol by making it Parameters.Add it gives me this error

Error	7	The best overloaded method match for 'System.Data.SQLite.SQLiteParameterCollection.Add(string, System.Data.DbType)' has some invalid arguments.

I forgot what I had to change lol

EDIT:
Had to change the parameter from SqlDbType to DbType. :)
EDIT2
OMGOMGOGMGOMGOG MGM its sort of working :D
It only adds 1 row in but from there I can work forward. Thanks Mikkeee :)

[Mikkeee]

I'm syntax challenged with SQLite. Did this update your DB as expected?

[M1kkelZU]

Sort of, it added the very last record of the Log File into the database, I'm figuring out why it only added the last one and not every record but at the moment, its progress and a load of my chest.