By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,501 Members | 2,817 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,501 IT Pros & Developers. It's quick & easy.

getting INSERT INTO syntax error for this code

P: 3
Can you see where I am going wrong?

Expand|Select|Wrap|Line Numbers
  1.  'SAVE THE RESULTS TO THE DATABASE  
  2.  
  3.        Dim c_strAccessMDBFile As String = CStr(IBM.txtdblocation.Text) & "CrustaceanIBM.mdb"
  4.  
  5.        Dim myConn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & c_strAccessMDBFile & ";")
  6.         Dim myDataAdapter As New OleDb.OleDbDataAdapter()
  7.  
  8.        myDataAdapter.SelectCommand = New OleDbCommand("SELECT * FROM Population_Size", myConn)
  9.  
  10.        Dim myDataRowsCommandBuilder As OleDb.OleDbCommandBuilder = New OleDb.OleDbCommandBuilder(myDataAdapter)
  11.  
  12.         myConn.Open()
  13.  
  14.         Dim ds As DataSet = New DataSet
  15.  
  16.         myDataAdapter.MissingSchemaAction = MissingSchemaAction.AddWithKey
  17.  
  18.         myDataAdapter.Fill(ds, "Population_Size")
  19.         ds.Tables("Population_Size").Clear()
  20.         Dim dtPopSize As DataTable = ds.Tables("Population_Size")
  21.  
  22.         For r = 0 To NumRuns - 1
  23.             For ro = 0 To PROJYR - MINYR
  24.  
  25.                 myDataAdapter.InsertCommand = New OleDbCommand("INSERT INTO Population_Size(year, number, number_in_mpa) VALUES(@Year, @Number, @Number_in_Mpa) WHERE key = @key ", myConn)
  26.  
  27.                 Dim dbParam_key As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
  28.                 dbParam_key.ParameterName = "@key"
  29.                 dbParam_key.Value = ro + r * (PROJYR - MINYR + 1)
  30.                 dbParam_key.DbType = DbType.Int32
  31.                 myDataAdapter.InsertCommand.Parameters.Add(dbParam_key)
  32.  
  33.                 Dim dbParam_year As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
  34.                 dbParam_year.ParameterName = "@year"
  35.                 dbParam_year.Value = MINYR + ro
  36.                 dbParam_year.DbType = DbType.Int32
  37.                 myDataAdapter.InsertCommand.Parameters.Add(dbParam_year)
  38.  
  39.                 Dim dbParam_number As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
  40.                 dbParam_number.ParameterName = "@number"
  41.                 dbParam_number.Value = SimRun(r).Yr(ro).N
  42.                 dbParam_number.DbType = DbType.Double
  43.                 myDataAdapter.InsertCommand.Parameters.Add(dbParam_number)
  44.  
  45.                 Dim dbParam_Number_in_MPA As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
  46.                 dbParam_Number_in_MPA.ParameterName = "@Number_in_MPA"
  47.                 dbParam_Number_in_MPA.Value = SimRun(r).Yr(ro).NinMPA
  48.                 dbParam_Number_in_MPA.DbType = DbType.Double
  49.                 myDataAdapter.InsertCommand.Parameters.Add(dbParam_Number_in_MPA)
  50.  
  51.  
  52.  
  53.                 dtPopSize.Rows.Add(ro + r * (PROJYR - MINYR + 1))
  54.                 dtPopSize.Rows(ro + r * (PROJYR - MINYR + 1)).Item("Year") = MINYR + ro
  55.                 dtPopSize.Rows(ro + r * (PROJYR - MINYR + 1)).Item("Number") = SimRun(r).Yr(ro).N
  56.                 dtPopSize.Rows(ro + r * (PROJYR - MINYR + 1)).Item("Number_in_MPA") = SimRun(r).Yr(ro).NinMPA
  57.  
  58.             Next ro
  59.         Next r
  60.  
  61.         myDataAdapter.Update(ds, "Population_Size")
  62.         myConn.Close()
Sep 10 '07 #1
Share this Question
Share on Google+
1 Reply


Expert 5K+
P: 8,434
I'm no SQL guru, but... um... does a WHERE clause make any sense on an INSERT? I don't get it.
Sep 10 '07 #2

Post your reply

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