0

I'm trying to use a while loop to insert into a table from a List. I want to loop through and write each item from the list by it's index. I'm getting an error with the values I'm trying to insert.

"SQL logic error or missing database near "[y]": syntax error"

while (y < Name.Count)
{
   cmd.CommandText = "INSERT INTO Mytable(Column1,Column2) values(Column1[y], Column2[y])";
   cmd.ExecuteNonQuery();
   y++;
}
B Minster
  • 331
  • 3
  • 16
  • That's because you're using the string literal character 'y', rather than your variable y. You will need to use string formatting to insert it at those locations. – Erik Uggeldahl Feb 09 '17 at 23:10

2 Answers2

1

Your query is not correct. You need to pass parameters to the query:

"INSERT INTO Mytable(Column1,Column2) values(Column1[@Column1], Column2[@Column1])"

command.Parameters.Add( new SqlParameter( "@Column1", y ) );

Having said that, if I were you, I would use Bulk Insert (or something similar) for this and transfer all the data to the database in one trip.

CodingYoshi
  • 25,467
  • 4
  • 62
  • 64
-1

Your parameters, Column1[y] and Column2[y], are not handled as a index to a data structure but rather as plain text.

   cmd.CommandText = "INSERT INTO Mytable(Column1,Column2) values(" + Column1[y] + ", " + Column2[y] + ")";
Bernhard Schneider
  • 1
  • 4
  • Note that the method `String.Format` is available and may be preferable: https://msdn.microsoft.com/en-us/library/system.string.format(v=vs.110).aspx http://stackoverflow.com/questions/4671610/why-use-string-format – Erik Uggeldahl Feb 09 '17 at 23:15
  • Ok, this got me closer. Now I'm getting an error "System.Data.SQLite.SQLiteException: SQL logic error or missing database no such column: Name" – B Minster Feb 09 '17 at 23:19
  • replace Column1 and Column2 with the exact column names of your table where you want to set values in...You seem try to map a column "Name" which does not exist...here are some examples: https://www.tutorialspoint.com/sqlite/sqlite_insert_query.htm – Bernhard Schneider Feb 09 '17 at 23:24