Solved

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

Posted on 2004-10-26
181 Views
Last Modified: 2010-08-05
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!
0
Question by:ptslv
    29 Comments
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    dsabo,

    I tried your code and still get a syntax error in the Insert Into query.
    0
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    "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
     
    LVL 2

    Accepted Solution

    by:
    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
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    Show me your select query that you are using for the command

       OleDbCommand selectCommand = new OleDbCommand(strSQLSelect,myConnection);

    strSQLSelect ?? show me the string?
    0
     

    Author Comment

    by:ptslv
    Sorry.  I gotcha.  I am using *.  I will change to the column names and see what happens.
    0
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    try with out using [ ]

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

    Author Comment

    by:ptslv
    I still get the same error: "No value given for one or more required parameters."

    0
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    Yes.  When I did I got this error message:

    "No value given for one or more required parameters."
    0
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    I get this error when adding that code:

    System.Data.OleDb OleDbDataAdapter does not contain a definition for 'DataTable'
    0
     
    LVL 2

    Expert Comment

    by:dsabo
    I'm sorry....

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

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     

    Author Comment

    by:ptslv
    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
     
    LVL 2

    Expert Comment

    by:dsabo
    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
     
    LVL 5

    Expert Comment

    by:Kelmen
    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
     

    Author Comment

    by:ptslv
    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
     

    Author Comment

    by:ptslv
    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

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
    We all know that functional code is the leg that any good program stands on when it comes right down to it, however, if your program lacks a good user interface your product may not have the appeal needed to keep your customers happy. This issue can…
    In this Experts Exchange video Micro Tutorial, I'm going to show how small business owners who use Google Apps can save money by setting up what is called a catch-all email address in their Gmail accounts. By using the catch-all feature, small busin…
    Need more eyes on your posted question? Go ahead and follow the quick steps in this video to learn how to Request Attention to your question. *Log into your Experts Exchange account *Find the question you want to Request Attention for *Go to the e…

    884 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    17 Experts available now in Live!

    Get 1:1 Help Now