Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2004-10-26
29
Medium Priority
?
187 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
Comment
Question by:ptslv
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 15
  • 13
29 Comments
 
LVL 2

Expert Comment

by:dsabo
ID: 12417124
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
ID: 12421711
dsabo,

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

Expert Comment

by:dsabo
ID: 12421770
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:ptslv
ID: 12421932
"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:
dsabo earned 750 total points
ID: 12421999
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
ID: 12422270
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
ID: 12422313
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
ID: 12422337
Sorry.  I gotcha.  I am using *.  I will change to the column names and see what happens.
0
 

Author Comment

by:ptslv
ID: 12422584
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
ID: 12422631
try with out using [ ]

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

Author Comment

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

0
 
LVL 2

Expert Comment

by:dsabo
ID: 12422705
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
ID: 12422829
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
ID: 12424271
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
ID: 12424672
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
ID: 12424712
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
ID: 12424859
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
ID: 12425017
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
ID: 12425595
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
ID: 12426129
I'm sorry....

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

Author Comment

by:ptslv
ID: 12426403
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
ID: 12426610
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
ID: 12427016
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
ID: 12427198
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
ID: 12427409
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
ID: 12427909
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
ID: 12442313
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
ID: 12443671
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
ID: 12497209
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
Introduction Hi all and welcome to my first article on Experts Exchange. A while ago, someone asked me if i could do some tutorials on object oriented programming. I decided to do them on C#. Now you may ask me, why's that? Well, one of the re…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

618 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