Need some assistance to properly insert data into Access using OleDB and C#

I need some assistance properly setting up inserting data into an Access database.  I have a Windows App and I am trying to insert data into an access table using C#.  I get an error:  Syntax error in Insert Into statement.  I don't have an Insert Into statement.

If I use a table other than [Import], the data goes into the table.  However, I need this to work with the [Import] table.


private void addMyRow(string DocType)
{
   string doc = DocType.ToString();
   string strSQLSelect = "SELECT * FROM [Import]";

   OleDbConnection myConnection = new OleDbConnection(strConnection);
   OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);
   OleDbDataAdapter daCmd = new OleDbDataAdapter();
   OleDbCommandBuilder importCB = new OleDbCommandBuilder(daCmd);
   DataSet resultDataSet = new DataSet();
   selectCommand.CommandType = CommandType.Text;
     
   bool bIsError=false;
   try
   {
      if( data.m_timeStamp.Length == 0)
          MessageBox.Show("An error occurred while importing this record. There is no timestamp.  Please notify Programmer.");
      else
      {
          switch(doc.ToString())
          {
             case "Acknowledge Transaction":
                 myConnection.Open();
                 daCmd.SelectCommand = selectCommand;
                 int numRows = daCmd.Fill(resultDataSet, "Import");
                 //create a new row
                 DataRow myRow = resultDataSet.Tables["Import"].NewRow();
                 //fill in myRow data
                 myRow["timeStamp"] = data.m_timeStamp.ToString();
                 myRow["DocumentName"] = data.m_DocumentName.ToString();
                 myRow["DocumentType"] = data.m_DocumentType.ToString();
                 myRow["TransactionType"] = data.m_TransactionType.ToString();
                 myRow["TANUM"] = data.m_TANUM.ToString();
               
                 resultDataSet.Tables["Import"].Rows.Add(myRow);
                 daCmd.Update(resultDataSet, "Import");
                 break;
          }
      }
   }
   catch(Exception err)
   {
       string str=err.Message;
       bIsError=true;
   }
   finally
   {
       myConnect.Close();
   }
   if(!bIsError)
      MessageBox.Show("XML Data has been submited!");
   else
      MessageBox.Show("There has been an error with this page.  Please contact the programmer.");
   }      
}

thanks in advance!
ptslvAsked:
Who is Participating?
 
dsaboCommented:
Show me your select query or inthis case strSQLSelect.

I think that's the problem.

You cannot use * on the select query, you must specify each column.

example

NOT =>  SELECT * FROM Clients
USE => SELECT FirstName, LastName FROM Clients

0
 
dsaboCommented:
Hi.

The problem must be that your are not setting the property SelectCommand of the DataAdapter before you create an instance of CommandBuilder.
This should work:

   OleDbConnection myConnection = new OleDbConnection(strConnection);
   OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);
   OleDbDataAdapter daCmd = new OleDbDataAdapter();
   daCmd.SelectCommand = selectCommand;  //*********  line added
   OleDbCommandBuilder importCB = new OleDbCommandBuilder(daCmd);
   DataSet resultDataSet = new DataSet();
   selectCommand.CommandType = CommandType.Text;
   
 bool bIsError=false;
   try
   {
      if( data.m_timeStamp.Length == 0)
          MessageBox.Show("An error occurred while importing this record. There is no timestamp.  Please notify Programmer.");
      else
      {
          switch(doc.ToString())
          {
             case "Acknowledge Transaction":
                 myConnection.Open();
                 //daCmd.SelectCommand = selectCommand;                    *****line removed
                 int numRows = daCmd.Fill(resultDataSet, "Import");
                 //create a new row
                 DataRow myRow = resultDataSet.Tables["Import"].NewRow();
                 //fill in myRow data
                 myRow["timeStamp"] = data.m_timeStamp.ToString();
                 myRow["DocumentName"] = data.m_DocumentName.ToString();
                 myRow["DocumentType"] = data.m_DocumentType.ToString();
                 myRow["TransactionType"] = data.m_TransactionType.ToString();
                 myRow["TANUM"] = data.m_TANUM.ToString();
               
                 resultDataSet.Tables["Import"].Rows.Add(myRow);
                 daCmd.Update(resultDataSet, "Import");
                 break;
          }
      }
   }
   catch(Exception err)
   {
       string str=err.Message;
       bIsError=true;
   }
   finally
   {
       myConnect.Close();
   }
   if(!bIsError)
      MessageBox.Show("XML Data has been submited!");
   else
      MessageBox.Show("There has been an error with this page.  Please contact the programmer.");
   }    
}

I hope this helps,
0
 
ptslvAuthor Commented:
dsabo,

I tried your code and still get a syntax error in the Insert Into query.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
dsaboCommented:
try this...

OleDbConnection myConnection = new OleDbConnection(strConnection);
   OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);
   selectCommand.CommandType = CommandType.Text;
   OleDbDataAdapter daCmd = new OleDbDataAdapter();
   daCmd.SelectCommand = selectCommand;  //*********  line added
   OleDbCommandBuilder importCB = new OleDbCommandBuilder(daCmd);
   DataSet resultDataSet = new DataSet();

If you still get the error, can you copy the exact message?


0
 
ptslvAuthor Commented:
"Syntax error in INSERT INTO query."

StackTrace      "   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)\r\n  
at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable)\r\n  
at WindowsApplication8.Form1.addMyRow2(String DocType) in d:\\my documents\\visual studio projects\\windowsapplication8\\form1.cs:line 674"      string
0
 
ptslvAuthor Commented:
This is what I use:

myConnection.Open();
int numRows = daCmd.Fill(resultDataSet, "Import");
DataRow myRow = resultDataSet.Tables["Import"].NewRow();

myRow["timeStamp"] = data.m_timeStamp.ToString();
myRow["DocumentName"] = data.m_DocumentName.ToString();
myRow["DocumentType"] = data.m_DocumentType.ToString();
myRow["TransactionType"] = data.m_TransactionType.ToString();
myRow["TANUM"] = data.m_TANUM.ToString();

resultDataSet.Tables["Import"].Rows.Add(myRow);
daCmd.Update(resultDataSet, "Import");
myConnection.Close();

I tried placing [] around "Imports" in the daCmd.Update() statement, but that threw a 'looking for ()" error.
0
 
dsaboCommented:
Show me your select query that you are using for the command

   OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);

strSQLSelect ?? show me the string?
0
 
ptslvAuthor Commented:
Sorry.  I gotcha.  I am using *.  I will change to the column names and see what happens.
0
 
ptslvAuthor Commented:
When I replace the * with the column names, I get the following error:

err      {"No value given for one or more required parameters." }      System.Exception


string strSQLSelect = "SELECT [Auto], [timeStamp], [DocumentName], [DocumentType], " +
"[TransactionType], [TANUM], [TransactionID], [TravelerSSN], [InvoiceNumber], [DOVNumber]  FROM [Import]";

I checked the database - no fields are required and each field accepts a zero length string.

I don't get any errors when I import this data into a different table using  * in the Select statement.
0
 
dsaboCommented:
try with out using [ ]

string strSQLSelect = "SELECT Auto, timeStamp, DocumentName, DocumentType, " +
"TransactionType, TANUM, TransactionID, TravelerSSN, InvoiceNumber, DOVNumber FROM Import";
0
 
ptslvAuthor Commented:
I still get the same error: "No value given for one or more required parameters."

0
 
dsaboCommented:
I'm sorry, I missunderstood your post.

The problem appears to be a column that is required, check the Auto Column, try to setting that colmun as a non autoincrement, just set manually the number, an see what happens.

If it work's the problem is that you must set the column Auto at runtime as an AutoIncrement Column.
0
 
ptslvAuthor Commented:
OK, changing the 'auto' field to a number didn't work.  The way that I have my function set up, since I'm not setting paramaters, where would I set that field as a AutoIncrement Column?
0
 
dsaboCommented:
did you set a number to the column auto on the new row???

myRow["auto"] = 1;  // or an available number  just to try....
myRow["timeStamp"] = data.m_timeStamp.ToString();
myRow["DocumentName"] = data.m_DocumentName.ToString();
myRow["DocumentType"] = data.m_DocumentType.ToString();
myRow["TransactionType"] = data.m_TransactionType.ToString();
0
 
ptslvAuthor Commented:
Yes.  When I did I got this error message:

"No value given for one or more required parameters."
0
 
dsaboCommented:
Lets try something.

Set for each column anything, I think the problem it's on the database.

myRow["auto"] = 1;  // or an available number  just to try....
myRow["timeStamp"] = data.m_timeStamp.ToString();
myRow["DocumentName"] = data.m_DocumentName.ToString();
myRow["DocumentType"] = data.m_DocumentType.ToString();
myRow["TransactionType"] = data.m_TransactionType.ToString();
myRow["TANUM"] = "" or 0
myRow["TravelerSSN"] = "" or 0
and so on, for each column....

0
 
ptslvAuthor Commented:
each member variable is initially set = "" in the class, so I don't think that is the problem.

How do I set auto as a AutoIncrement column?
0
 
dsaboCommented:
Yes but your are not setting all the columns y the new row.

after the fill statment

DataTable t = daCmd.Tables["Import"];
t.Columns["auto"].AutoIncrement = true;
t.Columns["auto"].AutoIncrementStep = 1;
0
 
ptslvAuthor Commented:
I get this error when adding that code:

System.Data.OleDb OleDbDataAdapter does not contain a definition for 'DataTable'
0
 
dsaboCommented:
I'm sorry....

DataTable t = resultDataSet.Tables["Import"];
t.Columns["auto"].AutoIncrement = true;
t.Columns["auto"].AutoIncrementStep = 1;
0
 
ptslvAuthor Commented:
Sorry this is becoming such a pain. I appreciate your help.

The code falls out at this line:

int numRows = daCmd.Fill(resultDataSet, "myImport");

which is just before:

DataTable t = resultDataSet.Tables["Import"];
t.Columns["auto"].AutoIncrement = true;
t.Columns["auto"].AutoIncrementStep = 1;

I can't seem to catch the exact error.
0
 
dsaboCommented:
you should use the same table name

daCmd.Fill(resultDataSet, "myImport") here and resultDataSet.Tables["Import"];

resultDataSet.Tables["myImport"];

try clearing the dataset before the fill statment

resultDataSet.Tables.Clear();
0
 
ptslvAuthor Commented:
I still get this error:  "No value given for one or more required parameters."

Here is the updated code:

private void addMyRow2(string DocType)
{
      string doc = DocType.ToString();
      string strSQLSelect = "SELECT Auto,HeaderInfo, timeStamp, DocumentName, DocumentType, " +
            "TransactionType, TANUM, TransactionID  FROM [myImport]";
      OleDbConnection myConnection = new OleDbConnection(strConnection);
      OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);
      selectCommand.CommandType = CommandType.Text;
      OleDbDataAdapter daCmd = new OleDbDataAdapter();
      daCmd.SelectCommand = selectCommand;
      OleDbCommandBuilder importCB = new OleDbCommandBuilder(daCmd);
      DataSet resultDataSet = new DataSet();

   
      bool bIsError=false;
      try
      {
          switch(doc.ToString())
          {
            case "Acknowledge Transaction":
                 myConnection.Open();
                 resultDataSet.Tables.Clear();
                 int numRows = daCmd.Fill(resultDataSet, "myImport");
                 DataTable t = resultDataSet.Tables["myImport"];
                 t.Columns["auto"].AutoIncrement = true;
                 t.Columns["auto"].AutoIncrementStep = 1;
                 DataRow myRow = resultDataSet.Tables["myImport"].NewRow();
                 myRow["auto"] = "";  // or an available number  just to try....
                 myRow["timeStamp"] = data.m_timeStamp.ToString();
                 myRow["HeaderInfo"] = data.m_HeaderInfo.ToString();
                 myRow["DocumentName"] = data.m_DocumentName.ToString();
                 myRow["DocumentType"] = data.m_DocumentType.ToString();
                 myRow["TransactionType"] = data.m_TransactionType.ToString();
                 myRow["TANUM"] = data.m_TANUM.ToString();
                  
                 resultDataSet.Tables["myImport"].Rows.Add(myRow);
                 daCmd.Update(resultDataSet, ("myImport"));
                 break;
            }
        }
        }
      catch(Exception err)
      {
            string str=err.Message;
            bIsError=true;
      }
      finally
      {
            if(!bIsError)
                 MessageBox.Show("XML Data has been submited!");
            else
            {
                 MessageBox.Show("There has been an error.  Please contact the programmer.");
                 this.Close();
            }
      }
}
0
 
dsaboCommented:
update this part

                       DataRow myRow = resultDataSet.Tables["myImport"].NewRow();
//remove         myRow["auto"] = "";  // or an available number  just to try....
                       myRow["timeStamp"] = data.m_timeStamp.ToString();
                      myRow["HeaderInfo"] = data.m_HeaderInfo.ToString();
                      myRow["DocumentName"] = data.m_DocumentName.ToString();
                      myRow["DocumentType"] = data.m_DocumentType.ToString();
                     myRow["TransactionType"] = data.m_TransactionType.ToString();
                     myRow["TANUM"] = data.m_TANUM.ToString();

could you set also transaction id?
0
 
ptslvAuthor Commented:
It's still falling out at the fill line.
Do you think I need to rebuild that particular function and use parameters?   I'm going to take a break from this and hit it again in the morning. My brain is starting to hurt!
0
 
dsaboCommented:
I think you need to set all the colmuns manually, I mean ALL. Including auto, disabeling autoincrement on the code and on the Database. But be sure to include all.

You also could try to set the key of the table

DataTable t = resultDataSet.Tables["myImport"];
DataColumn[] key = new DataColumn[1];
key[0] = t.Columns[0];
t.PrimaryKey = key;

paste the above code after the fill statment

0
 
KelmenCommented:
Seems to me you are having an invalid SQL statement for Access.

Have you try direct exec the SQL in Access to comfirm its validity?

Try remove the INTO. INSERT tblX ....
0
 
ptslvAuthor Commented:
Kelman,

The SQL statement that I have (Select * from tablename)  is good - it works in Access.  I have the program running fine when I split the data into different tables.  I have 7 SQL statements in my switch working with 7 different tables.  It's when I try to put all the data into one table that it breaks.  The customer wants the data to go into the Import table.  

ptslv
0
 
ptslvAuthor Commented:
dsabo,

I finally got this working.  By adding one field at a time, i found some fields were missing from the Import table.  That was throwing the "No value given for one or more required parameters." error.  If I used the individual tables, I was able to use SELECT * because I was already using all the fields in addrow().  I couldn't use it when dealing with the Import table because not all fields from the Import table were in each file.

Thanks for all the help.  I am going to give you the points.

ptslv
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.