Could not open Connection to SQL Server.

[Rabbit]

This is from the SQLite documentation:

1.2 Date and Time Datatype

SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC.
Applications can chose to store dates and times in any of these formats and freely convert between formats using the built-in date and time functions.

So you need to put your data into the format that you chose for your date.

[M1kkelZU]

Ok I'll do that as soon as I can fix the issue that it can't find the column, would you have an idea on why its doing that?

Ok I fixed the column issue, I didn't think to edit the *.s3db file in SQLiteAdmin so I did that and now I just get the error:

 Input string was not in a correct format.

No clue why though

[Mikkeee]

Which line of code is generating that error? And looking at your error and Rabbit's post makes me think it might be the data type issue with the Date field. I'm not familiar with SQLite and didn't know there was no Date field type.

[M1kkelZU]

command.ExecuteNonQuery();

Thats the line thats doing the damage :P

[Mikkeee]

Make sure that the fields in your db match the parameter data types. Also, don't convert your Date field to a DateTime (back to Rabbit's post).

[M1kkelZU]

No I didn't change anything, I kept everything the way they I had them first (temp varChar solution lol)

The only thing I can think of is that the insert command is not correct for the way I'm trying to insert it, but I highly doubt that is the case as I've seen tons of tutorials & examples (Like yours Mikkeee) that use the same kind of Database and same method to insert.

I'll puzzle some more with it, but any help is much appreciated.

[M1kkelZU]

Ok so I changed my code a bit, and Now I don't get an input string error, actually I dont get an error at all. I only get this:



So it save something in my database but as soon as I click on Get DataBase it receives these numbers and not the actual records.

Modified code:

 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]

The values you see must be default values because nowhere in your code are you setting the Parameter.Value . You're now creating an array of parameters but they really aren't necessary. I'm not sure about the SQLite db but most db providers have the method 'Parameter.AddW ithValue' which is nice if you only have to insert a single record.

[M1kkelZU]

Yeah I can use the Parameters.AddW ithValue method. But I wouldnt know where to add it.

Would I have to change:

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

to ;

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

But what do i add to the AddWithValue?

[Mikkeee]

Look up your help file for AddwithValue to be sure but the line below works for all the data providers that I use. Also make sure to actually place the value you want stored in the parameter.

command.Parameters.AddWithValue("@Date", myDateVar);

[M1kkelZU]

Alright, well I'll get to that later. now I get some dumb happening lol. When I import my file it doesnt fill any column in the DataGridview itself other than Date and Channel. For the rest it says <Columnname> isn't unique.

EDIT: Never mind, just a small mistake I made the other day. I'm still trying to get the values added in to the database and be able to pick up the current value of the Database. Still playing with that at the moment.

EDIT 2:

Abort due to constraint violation
column Date is not unique

I'm crying... how in hell is this not unique... Its torture I tell you!

[Mikkeee]

You must have some type of constraint setup on your Date field. Is it a primary key, unique value, or a foreign key? If so, the value you're putting in the Date field must be unique.

[M1kkelZU]

So basically make it not unique in the database itself? I can do that even though I am feeling less compitent by the minute with this.
Even by making it not unique I get the exact same error... I don't like using database connections lol.

LOL I am amazing, I keep forgetting I have to edit the initial Database in SQLiteAdmin... Now I'm going to try to finish this and then win at being me.

[Mikkeee]

Good Luck! And just remember that this stuff is like anything else in life. The more experience you have the easier it will be and you're definitely giving the SQLite data provider a good work out!

[M1kkelZU]

Exactly, I'm as far as I can insert into the database and get from the database. it doesnt insert what I want to though, same thing 22-22-22-6 for each column. Still having a think on how I can fix that.