473,327 Members | 2,094 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

Could not open Connection to SQL Server.

80 64KB
I'm getting this error.

SQLite error
near "16": syntax error

On the code:

Expand|Select|Wrap|Line Numbers
  1.  private void btnUpdate_Click(object sender, EventArgs e)
  2.         {
  3.             for (int i = 0; i < dataGridView1.Rows.Count; i++)
  4.             {
  5.                 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 +");";
  6.  
  7.                 try
  8.                 {
  9.                     SQLiteConnection conn = new SQLiteConnection(connString);
  10.                     conn.Open();
  11.                     using (SQLiteCommand comm = new SQLiteCommand(StrQuery, conn))
  12.                     {
  13.                         comm.ExecuteNonQuery();
  14.                     }
  15.                     conn.Close();
  16.                 }
  17.                 catch (Exception crap)
  18.                 {
  19.                     MessageBox.Show(crap.ToString());
  20.                 }
  21.             }
  22.         }
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?
Feb 19 '13 #1

✓ answered by Mikkeee

See comments in code below:
Expand|Select|Wrap|Line Numbers
  1. using (SQLiteCommand command = new SQLiteCommand())
  2. {
  3.     conn.Open();
  4.  
  5.     command.Connection = conn;
  6.     command.CommandType = CommandType.Text;
  7.     command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  8.  
  9.     // Changed AddWithValue to Add
  10.     command.Parameters.Add("@Date", SqlDbType.VarChar);
  11.     command.Parameters.Add("@LogName", SqlDbType.VarChar);
  12.     command.Parameters.Add("@Channel", SqlDbType.VarChar);
  13.     command.Parameters.Add("@DateRecord", SqlDbType.VarChar);
  14.     command.Parameters.Add("@SizeInBytes", SqlDbType.Float);
  15.  
  16.     foreach (DataGridViewRow row in dataGridView1.Rows)
  17.     {
  18.         if (!row.IsNewRow)
  19.         {
  20.             command.Parameters["@Date"].Value = row.Cells[0].Value.ToString();
  21.             command.Parameters["@LogName"].Value = row.Cells[1].Value.ToString();
  22.             command.Parameters["@Channel"].Value = row.Cells[2].Value.ToString();
  23.             command.Parameters["@DateRecord"].Value = row.Cells[3].Value.ToString();
  24.             // Make sure "row.Cells[4].Value" returns a float or convert it before setting the parameter value
  25.             command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  26.             command.ExecuteNonQuery();
  27.         }
  28.     }
  29.     // Move the following line up where you're assigning the command text 
  30.     //command.CommandType = CommandType.Text;
  31.  
  32.     // This should be in your foreach loop if you want each row in your
  33.     // DataGridView if you want all the items to be inserted
  34.     //command.ExecuteNonQuery();
  35. }
  36. conn.Close();
  37.  
  38.  

50 4141
Mikkeee
94 64KB
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?
Feb 19 '13 #2
M1kkelZU
80 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. 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.
Feb 19 '13 #3
Mikkeee
94 64KB
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.
Feb 19 '13 #4
M1kkelZU
80 64KB
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
Expand|Select|Wrap|Line Numbers
  1. No connection associated with this command
I've seen this a trillion times in the last week and its annoying me.
Current code:
Expand|Select|Wrap|Line Numbers
  1.     private void btnUpdate_Click(object sender, EventArgs e) // TODO LOLWUT
  2.         {
  3.             SQLiteConnection con = new SQLiteConnection("Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db");
  4.  
  5.             using (SQLiteConnection conn = new SQLiteConnection(con))
  6.             {
  7.                 using (SQLiteCommand command = new SQLiteCommand())
  8.                 {
  9.                     sqliteInsertCommand1.Connection = connect;
  10.                     sqliteInsertCommand1.CommandText = "insert into Test (Date, Log Name, Channel, Date Record, Size in Bytes) values(@Date, @Log Name, @Channel, @Date Record, @Size in Bytes)";
  11.  
  12.                     command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
  13.                     command.Parameters.Add(new SQLiteParameter("@Log Name", SqlDbType.VarChar));
  14.                     command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
  15.                     command.Parameters.Add(new SQLiteParameter("@Date Record", SqlDbType.VarChar));
  16.                     command.Parameters.Add(new SQLiteParameter("@Size in Bytes", SqlDbType.VarChar));
  17.  
  18.                     if (conn.State == ConnectionState.Open)
  19.                     {
  20.                         foreach (DataGridViewRow row in dataGridView1.Rows)
  21.                         {
  22.                             if (!row.IsNewRow)
  23.                             {
  24.                                 command.Parameters["@Date"].Value = row.Cells[0].Value;
  25.                                 command.Parameters["@Log Name"].Value = row.Cells[1].Value;
  26.                                 command.Parameters["@Channel"].Value = row.Cells[2].Value;
  27.                                 command.Parameters["@Date Record"].Value = row.Cells[3].Value;
  28.                                 command.Parameters["@Size in Bytes"].Value = row.Cells[4].Value;
  29.                                 command.ExecuteNonQuery();
  30.                             }
  31.                         }
  32.                     }
  33.                     else
  34.                     {
  35.                         conn.Open();
  36.                         foreach (DataGridViewRow row in dataGridView1.Rows)
  37.                         {
  38.                             if (!row.IsNewRow)
  39.                             {
  40.                                 command.Parameters["@Date"].Value = row.Cells[0].Value;
  41.                                 command.Parameters["@Log Name"].Value = row.Cells[1].Value;
  42.                                 command.Parameters["@Channel"].Value = row.Cells[2].Value;
  43.                                 command.Parameters["@Date Record"].Value = row.Cells[3].Value;
  44.                                 command.Parameters["@Size in Bytes"].Value = row.Cells[4].Value;
  45.                                 command.ExecuteNonQuery(); // Exception here
  46.                             }
  47.                         }
  48.                     }
  49.                 }
  50.             }
Why must C# torture me so? I've been nothing but kind to it.
Feb 20 '13 #5
Mikkeee
94 64KB
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 SQLiteConnection 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 'sqlliteInsertCommand1' 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.

Expand|Select|Wrap|Line Numbers
  1.             string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  2.  
  3.             using (SQLiteConnection conn = new SQLiteConnection(conString))
  4.             {
  5.                 conn.Open();
  6.                 using (SQLiteCommand command = new SQLiteCommand())
  7.                 {
  8.                     command.Connection = conn;
  9.                     command.CommandText = "insert into Test ([Date], [Log Name], [Channel], [Date Record], [Size in Bytes]) values(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  10.  
  11.                     command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
  12.                     command.Parameters.Add(new SQLiteParameter("@LogName", SqlDbType.VarChar));
  13.                     command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
  14.                     command.Parameters.Add(new SQLiteParameter("@DateRecord", SqlDbType.VarChar));
  15.                     command.Parameters.Add(new SQLiteParameter("@SizeInBytes", SqlDbType.VarChar));
  16.  
  17.                     foreach (DataGridViewRow row in dataGridView1.Rows)
  18.                     {
  19.                         if (!row.IsNewRow)
  20.                         {
  21.                             command.Parameters["@Date"].Value = row.Cells[0].Value;
  22.                             command.Parameters["@LogName"].Value = row.Cells[1].Value;
  23.                             command.Parameters["@Channel"].Value = row.Cells[2].Value;
  24.                             command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
  25.                             command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  26.                             command.ExecuteNonQuery();
  27.                         }
  28.                     }
  29.                 }
  30.                 conn.Close();
  31.             }
  32.  
Feb 20 '13 #6
M1kkelZU
80 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. 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
Feb 20 '13 #7
Mikkeee
94 64KB
Yes, a DateTime data type would be correct. You should also be sure that you're putting a DateTime value in your parameter value:
Expand|Select|Wrap|Line Numbers
  1. 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].
Feb 20 '13 #8
M1kkelZU
80 64KB
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
Expand|Select|Wrap|Line Numbers
  1. SQLite error
  2. 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.
Feb 20 '13 #9
Mikkeee
94 64KB
Please post your revised code.
Feb 20 '13 #10
M1kkelZU
80 64KB
Expand|Select|Wrap|Line Numbers
  1. string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  2.  
  3.             using (SQLiteConnection conn = new SQLiteConnection(conString))
  4.             {
  5.                 conn.Open();
  6.                 using (SQLiteCommand command = new SQLiteCommand())
  7.                 {
  8.                     command.Connection = conn;
  9.                     command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  10.  
  11.                     command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
  12.                     command.Parameters.Add(new SQLiteParameter("@LogName", SqlDbType.VarChar));
  13.                     command.Parameters.Add(new SQLiteParameter("@Channel", SqlDbType.VarChar));
  14.                     command.Parameters.Add(new SQLiteParameter("@DateRecord", SqlDbType.VarChar));
  15.                     command.Parameters.Add(new SQLiteParameter("@SizeInBytes", SqlDbType.Float));
  16.  
  17.                     foreach (DataGridViewRow row in dataGridView1.Rows)
  18.                     {
  19.                         if (!row.IsNewRow)
  20.                         {
  21.                             command.Parameters["@Date"].Value = Convert.ToDateTime(row.Cells[0].Value);
  22.                             command.Parameters["@LogName"].Value = row.Cells[1].Value;
  23.                             command.Parameters["@Channel"].Value = row.Cells[2].Value;
  24.                             command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
  25.                             command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  26.                             command.ExecuteNonQuery();
  27.                         }
  28.                     }
  29.                 }
  30.                 conn.Close();
  31.             }
  32.  
  33.         }
That should be it.
Feb 20 '13 #11
Mikkeee
94 64KB
The only thing I see wrong is:

Expand|Select|Wrap|Line Numbers
  1. command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
Should be
Expand|Select|Wrap|Line Numbers
  1. command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.DateTime));
Feb 20 '13 #12
M1kkelZU
80 64KB
Nope still the same error. It doesn't see my columns in my DataBase
Feb 20 '13 #13
Mikkeee
94 64KB
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).
Feb 20 '13 #14
M1kkelZU
80 64KB
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.
Feb 20 '13 #15
Rabbit
12,516 Expert Mod 8TB
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.
Feb 20 '13 #16
M1kkelZU
80 64KB
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:
Expand|Select|Wrap|Line Numbers
  1.  Input string was not in a correct format. 
No clue why though
Feb 21 '13 #17
Mikkeee
94 64KB
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.
Feb 21 '13 #18
M1kkelZU
80 64KB
Expand|Select|Wrap|Line Numbers
  1. command.ExecuteNonQuery();
Thats the line thats doing the damage :P
Feb 21 '13 #19
Mikkeee
94 64KB
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).
Feb 21 '13 #20
M1kkelZU
80 64KB
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.
Feb 21 '13 #21
M1kkelZU
80 64KB
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:
Expand|Select|Wrap|Line Numbers
  1.  string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  2.  
  3.             using (SQLiteConnection conn = new SQLiteConnection(conString))
  4.             {
  5.                 using (SQLiteCommand command = new SQLiteCommand())
  6.                 {
  7.                     conn.Open();
  8.                     command.Connection = conn;
  9.                     command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  10.                     SQLiteParameter[] param = new SQLiteParameter[5];
  11.  
  12.                     param[0] = new SQLiteParameter("@Date", SqlDbType.VarChar);
  13.                     param[1] = new SQLiteParameter("@LogName", SqlDbType.VarChar);
  14.                     param[2] = new SQLiteParameter("@Channel", SqlDbType.VarChar);
  15.                     param[3] = new SQLiteParameter("@DateRecord", SqlDbType.VarChar);
  16.                     param[4] = new SQLiteParameter("@SizeInBytes", SqlDbType.Float);
  17.  
  18.                     for (int i = 0; i < param.Length; i++)
  19.                     {
  20.                         command.Parameters.Add(param[i]);
  21.                     }
  22.  
  23.                     command.CommandType = CommandType.Text;
  24.                     command.ExecuteNonQuery();  
  25.                }
  26.                 conn.Close();
  27.            } 
Feb 22 '13 #22
Mikkeee
94 64KB
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.AddWithValue' which is nice if you only have to insert a single record.
Feb 22 '13 #23
M1kkelZU
80 64KB
Yeah I can use the Parameters.AddWithValue method. But I wouldnt know where to add it.

Would I have to change:
Expand|Select|Wrap|Line Numbers
  1.  command.Parameters.Add(new SQLiteParameter("@Date", SqlDbType.VarChar));
to ;

Expand|Select|Wrap|Line Numbers
  1. command.Parameters.AddWithValue(new SQLiteParameter("@Date", SqlDbType.VarChar));
But what do i add to the AddWithValue?
Feb 25 '13 #24
Mikkeee
94 64KB
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.

Expand|Select|Wrap|Line Numbers
  1. command.Parameters.AddWithValue("@Date", myDateVar);
  2.  
Feb 25 '13 #25
M1kkelZU
80 64KB
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:
Expand|Select|Wrap|Line Numbers
  1. Abort due to constraint violation
  2. column Date is not unique
I'm crying... how in hell is this not unique... Its torture I tell you!
Feb 25 '13 #26
Mikkeee
94 64KB
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.
Feb 25 '13 #27
M1kkelZU
80 64KB
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.
Feb 25 '13 #28
Mikkeee
94 64KB
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!
Feb 25 '13 #29
M1kkelZU
80 64KB
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.
Feb 25 '13 #30
Mikkeee
94 64KB
Re-post your code again.
Feb 25 '13 #31
M1kkelZU
80 64KB
I'll post the 3 core components, OpenLog, Updateing the DB and getting from the DB.

getting DB info:
Expand|Select|Wrap|Line Numbers
  1.         private void btnGetDB_Click(object sender, EventArgs e)
  2.         {
  3.             string connString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  4.             string query = "SELECT * FROM Test";
  5.             SQLiteDataAdapter dAdapter = new SQLiteDataAdapter(query, connString);
  6.             SQLiteCommandBuilder cBuilder = new SQLiteCommandBuilder(dAdapter);
  7.             DataTable dTable = new DataTable();
  8.             SQLiteConnection connection = new SQLiteConnection(connString);
  9.             connection.Open();
  10.             try
  11.             {
  12.                 dAdapter.Fill(dTable);
  13.             }
  14.             catch (Exception error)
  15.             {
  16.                 MessageBox.Show(error.ToString());
  17.             }
  18.             BindingSource bSource = new BindingSource();
  19.             bSource.DataSource = dTable;
  20.             dataGridView1.DataSource = bSource;
  21.             dAdapter.Update(dTable);
  22.         }
Opening log:
Expand|Select|Wrap|Line Numbers
  1.  private void btnOpenLog_Click(object sender, EventArgs e)
  2.         {
  3.             OpenFileDialog openFileDialog1 = new OpenFileDialog();
  4.             if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
  5.             {
  6.                 try
  7.                 {
  8.                     dataGridView1.DataSource = LoadTable(openFileDialog1.FileName);
  9.                     btnUpdate.Enabled = true;
  10.                 }
  11.                 catch (Exception err)
  12.                 {
  13.                     MessageBox.Show("Error:  " + err.Message, "Program Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  14.                     btnUpdate.Enabled = false;
  15.                 }
  16.             }
  17.         }
  18.  
  19.         private DataTable LoadTable(string filename)
  20.         {
  21.             dTable.Columns.Add(new DataColumn("Date", typeof(string)));
  22.             dTable.Columns.Add(new DataColumn("LogName", typeof(string)));
  23.             dTable.Columns.Add(new DataColumn("Channel", typeof(string)));
  24.             dTable.Columns.Add(new DataColumn("DateRecord", typeof(string)));
  25.             dTable.Columns.Add(new DataColumn("SizeInBytes", typeof(float)));
  26.  
  27.             DataRow dr;
  28.  
  29.             String sLine = new StreamReader(filename).ReadToEnd();
  30.             if (sLine != null)
  31.             {
  32.                 foreach (string regel in Regex.Split(sLine, "\r\n"))
  33.                 {
  34.                     if (!string.IsNullOrEmpty(regel))
  35.                     {
  36.                         dr = dTable.NewRow();
  37.  
  38.                         string[] splitregel = Regex.Split(regel.Replace("|", ";"), ";");
  39.                         dr[0] = splitregel[0];
  40.                         dr[1] = splitregel[1];
  41.                         dr[2] = splitregel[2];
  42.                         dr[3] = splitregel[3];
  43.                         dr[4] = splitregel[4];
  44.  
  45.                         dTable.Rows.Add(dr);
  46.                     }
  47.                 }
  48.             }
  49.             return dTable;
  50.         }
And Update DB:
Expand|Select|Wrap|Line Numbers
  1.         private void btnUpdate_Click(object sender, EventArgs e) // TODO LOLWUT
  2.         {
  3.             string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  4.  
  5.             using (SQLiteConnection conn = new SQLiteConnection(conString))
  6.             {
  7.                 using (SQLiteCommand command = new SQLiteCommand())
  8.                 {
  9.                     conn.Open();
  10.                     command.Connection = conn;
  11.                     command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  12.                     SQLiteParameter[] param = new SQLiteParameter[5];
  13.  
  14.                     param[0] = new SQLiteParameter("@Date", SqlDbType.VarChar);
  15.                     param[1] = new SQLiteParameter("@LogName", SqlDbType.VarChar);
  16.                     param[2] = new SQLiteParameter("@Channel", SqlDbType.VarChar);
  17.                     param[3] = new SQLiteParameter("@DateRecord", SqlDbType.VarChar);
  18.                     param[4] = new SQLiteParameter("@SizeInBytes", SqlDbType.Float);
  19.  
  20.                     for (int i = 0; i < param.Length; i++)
  21.                     {
  22.                         command.Parameters.Add(param[i]);
  23.                     }
  24.  
  25.                     command.CommandType = CommandType.Text;
  26.                     command.ExecuteNonQuery();
  27.  
  28.  
  29.                 }
  30.                 conn.Close();
  31.            } 
  32.         }
Feb 26 '13 #32
Mikkeee
94 64KB
I'm looking in your Update and you're still not assigning any values to your parameters.
Expand|Select|Wrap|Line Numbers
  1.    for (int i = 0; i < param.Length; i++)
  2.    {
  3.       command.Parameters.Add(param[i]);
  4.       command.Parameters.Value = NEEDVALUEHERE;
  5.    }
  6.  
Feb 26 '13 #33
M1kkelZU
80 64KB
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.
Feb 26 '13 #34
Mikkeee
94 64KB
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?
Feb 26 '13 #35
M1kkelZU
80 64KB
it should insert, example from the file It reads from:
Expand|Select|Wrap|Line Numbers
  1. 1-2-2013 16:59|H00002|NL-Radio 5|2013-01-03T00:00:00.0000000Z|172811136
Hence Date, LogName, Channel, DateRecord, SizeInBytes (column names)
Feb 26 '13 #36
M1kkelZU
80 64KB
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.
Mar 4 '13 #37
Mikkeee
94 64KB
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.
Mar 4 '13 #38
M1kkelZU
80 64KB
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?
Mar 4 '13 #39
Mikkeee
94 64KB
Yes. You need to combine your log file read routine with your database update routine and you should be good to go.
Mar 4 '13 #40
M1kkelZU
80 64KB
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.

Expand|Select|Wrap|Line Numbers
  1. OpenFileDialog openFileDialog1 = new OpenFileDialog();
  2.             if (openFileDialog1.ShowDialog() != DialogResult.Cancel)
  3.             {
  4.                 try
  5.                 {
  6.                     dataGridView1.DataSource = LoadTable(openFileDialog1.FileName);
  7.                     string conString = "Data Source=D:/swdev/tapearchiver/Src/Tests Voor SQLdbs/TestingSQL3/test.s3db";
  8.  
  9.                     using (SQLiteConnection conn = new SQLiteConnection(conString))
  10.                     {
  11.                         using (SQLiteCommand command = new SQLiteCommand())
  12.                         {
  13.                             conn.Open();
  14.  
  15.                             command.Connection = conn;
  16.  
  17.                             command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  18.  
  19.                             command.Parameters.AddWithValue("@Date", SqlDbType.VarChar);
  20.                             command.Parameters.AddWithValue("@LogName", SqlDbType.VarChar);
  21.                             command.Parameters.AddWithValue("@Channel", SqlDbType.VarChar);
  22.                             command.Parameters.AddWithValue("@DateRecord", SqlDbType.VarChar);
  23.                             command.Parameters.AddWithValue("@SizeInBytes", SqlDbType.Float);
  24.  
  25.                             foreach (DataGridViewRow row in dataGridView1.Rows)
  26.                             {
  27.                                 if (!row.IsNewRow)
  28.                                 {
  29.                                     command.Parameters["@Date"].Value = row.Cells[0].Value;
  30.                                     command.Parameters["@LogName"].Value = row.Cells[1].Value;
  31.                                     command.Parameters["@Channel"].Value = row.Cells[2].Value;
  32.                                     command.Parameters["@DateRecord"].Value = row.Cells[3].Value;
  33.                                     command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  34.                                 }
  35.                             }
  36.  
  37.                             command.CommandType = CommandType.Text;
  38.                             command.ExecuteNonQuery();
  39.                         }
  40.                         conn.Close();
  41.                     } 
  42.                 }
  43.                 catch (Exception err)
  44.                 {
  45.                     MessageBox.Show("Error:  " + err.Message, "Program Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
  46.                     btnUpdate.Enabled = false;
  47.                 }
  48.             }
Thats my code. Could you have a look at it when you have time?
Mar 5 '13 #41
Mikkeee
94 64KB
See comments in code below:
Expand|Select|Wrap|Line Numbers
  1. using (SQLiteCommand command = new SQLiteCommand())
  2. {
  3.     conn.Open();
  4.  
  5.     command.Connection = conn;
  6.     command.CommandType = CommandType.Text;
  7.     command.CommandText = "insert into Test ([Date], [LogName], [Channel], [DateRecord], [SizeInBytes]) values" + "(@Date, @LogName, @Channel, @DateRecord, @SizeInBytes)";
  8.  
  9.     // Changed AddWithValue to Add
  10.     command.Parameters.Add("@Date", SqlDbType.VarChar);
  11.     command.Parameters.Add("@LogName", SqlDbType.VarChar);
  12.     command.Parameters.Add("@Channel", SqlDbType.VarChar);
  13.     command.Parameters.Add("@DateRecord", SqlDbType.VarChar);
  14.     command.Parameters.Add("@SizeInBytes", SqlDbType.Float);
  15.  
  16.     foreach (DataGridViewRow row in dataGridView1.Rows)
  17.     {
  18.         if (!row.IsNewRow)
  19.         {
  20.             command.Parameters["@Date"].Value = row.Cells[0].Value.ToString();
  21.             command.Parameters["@LogName"].Value = row.Cells[1].Value.ToString();
  22.             command.Parameters["@Channel"].Value = row.Cells[2].Value.ToString();
  23.             command.Parameters["@DateRecord"].Value = row.Cells[3].Value.ToString();
  24.             // Make sure "row.Cells[4].Value" returns a float or convert it before setting the parameter value
  25.             command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  26.             command.ExecuteNonQuery();
  27.         }
  28.     }
  29.     // Move the following line up where you're assigning the command text 
  30.     //command.CommandType = CommandType.Text;
  31.  
  32.     // This should be in your foreach loop if you want each row in your
  33.     // DataGridView if you want all the items to be inserted
  34.     //command.ExecuteNonQuery();
  35. }
  36. conn.Close();
  37.  
  38.  
Mar 5 '13 #42
M1kkelZU
80 64KB
Lol by making it Parameters.Add it gives me this error
Expand|Select|Wrap|Line Numbers
  1. 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
OMGOMGOGMGOMGOGMGM its sort of working :D
It only adds 1 row in but from there I can work forward. Thanks Mikkeee :)
Mar 5 '13 #43
Mikkeee
94 64KB
I'm syntax challenged with SQLite. Did this update your DB as expected?
Mar 5 '13 #44
M1kkelZU
80 64KB
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.
Mar 5 '13 #45
Mikkeee
94 64KB
This tells me that it's only dropping into your 'if' condition 1 time. Put a breakpoint in your foreach to see why.
Expand|Select|Wrap|Line Numbers
  1.  
  2.     foreach (DataGridViewRow row in dataGridView1.Rows)
  3.     {
  4.         // Put breakpoint on line below
  5.         Console.WriteLine("");
  6.         if (!row.IsNewRow)
  7.         {
  8.             command.Parameters["@Date"].Value = row.Cells[0].Value.ToString();
  9.             command.Parameters["@LogName"].Value = row.Cells[1].Value.ToString();
  10.             command.Parameters["@Channel"].Value = row.Cells[2].Value.ToString();
  11.             command.Parameters["@DateRecord"].Value = row.Cells[3].Value.ToString();
  12.             // Make sure "row.Cells[4].Value" returns a float or convert it before setting the parameter value
  13.             command.Parameters["@SizeInBytes"].Value = row.Cells[4].Value;
  14.             command.ExecuteNonQuery();
  15.         }
  16.     }
Mar 5 '13 #46
M1kkelZU
80 64KB
Ok I know what I did wrong, When editing the code I left command.ExecuteNonQuery(); out side of my foreach loop, I added this into the 'if' condidtion and now its adding everything, except 1 thing which is SizInBytes, it only places the value 1. Maybe because I can't use the DbType.Float statement. At the moment I have DbType.String on all of them as the DbType doesn't use the Values I've declared in my database, Maybe I need to use int64 or something with the DbType part, not sure though.

I had to change:
Expand|Select|Wrap|Line Numbers
  1. command.Parameters.Add("@SizeInBytes", DbType.String);
to

Expand|Select|Wrap|Line Numbers
  1. command.Parameters.Add("@SizeInBytes", DbType.Int64);
and in SQLite Admin the Database property from
Float

to

NUMERIC.

So All I have to do now is hope that it doesn't break when I have to showcase this thing lol.

Thanks alot Mikkeee, I know it must've been annoying with my noob way of coding but it was a nice introduction to SQL/SQLite and Database management of a program.
Mar 6 '13 #47
HI ! I didn't get your connection string and it's field plz be specify it.
Mar 6 '13 #48
M1kkelZU
80 64KB
Connection String
Expand|Select|Wrap|Line Numbers
  1. string conString = "Data Source=<PATH TO DATABASE>"
to use the string then just use this:
Expand|Select|Wrap|Line Numbers
  1. SQLiteConnection conn = new SQLiteConnection(constring)
but you can go the long way around and use this:

Expand|Select|Wrap|Line Numbers
  1. SQLiteConnection conn = new SQLiteConnection("Data Source=<PATH TO DATABASE>");
Mar 6 '13 #49
Mikkeee
94 64KB
No problem M1kkelZU... we were all noobs at one point. Glad you stuck with it and got it figured out!
Mar 6 '13 #50

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: PAM | last post by:
Hello I'm a newbie trying to open a connection to an Excel file in a deployed ASP .NET app and get the error Dim MyCommand As New OleDbCommand(mySelectQuery, MyConnection MyConnection.Open(
5
by: Owen | last post by:
Hello: How know how many open connection have my application made in asp.net?, To know if a open a new connection and not close? Best regards. Owen.
2
by: Rafi B. | last post by:
Sorry for the English. I mean, when building my applications, I often need to do several database actions, and I'm thinking, what's better? 1) Open connection with DB, for instance mySQL, at...
1
by: Michael Swinarski | last post by:
I am working on setting up our new SQL Server 2005 servers. One of them will eventually be our Replication Distributor. However when I attempt to set this up within the wizard, I get the...
12
idsanjeev
by: idsanjeev | last post by:
how to open connection in oracle for displying and inserting data in asp.net with vb
1
by: Bruno Marinho | last post by:
Hi, I'm having a problem creating an OU entry on a Open LDAP Server. I'm using the code below. $ad = ldap_connect("ldap://srvldap"); ldap_set_option($ad, LDAP_OPT_PROTOCOL_VERSION, 3); $bd =...
1
by: Noorain | last post by:
hi, i create a php file. which function is: i)to refresh a certain time ii)create txt file with some query. i want to this php file always open in server and refresh after 5 minutes. i know...
2
by: KKAIYER | last post by:
I am using VB6,SQL Server,Crystall reports. when I run vb to print report crystal repots giving error 20599 cannot open SQL Server. what is the reason and what is the solution. Please help
1
by: vj4u | last post by:
i want to open sql server on button click in asp.net
1
by: Vinayak Pangam | last post by:
When I open the server it shows a duplicate name exists on the network. It affects the workstation also. I also check in all pc no duplicate name exits. Please give me a solution what is the exact...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.