OleDb Exception...

"No value given for one or more required parameters."

Page 1 of 1

7 Replies - 2867 Views - Last Post: 09 April 2010 - 07:59 AM Rate Topic: -----

#1 sallyjenkins  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-February 09

OleDb Exception...

Posted 07 April 2010 - 12:44 PM

The objective: Open and read a CSV file one line at a time using SQL commands.
The problem: I am getting an error that I have a feeling may not point to the "real" problem.
I would like you to take a look at my code and see where I may have syntax or other errors.

stInputConn = "Provider=Microsoft.jet.OLEDB.4.0;Data Source=V:\\IT\\RE\\Exports\\;Extended Properties=\"text;HDR=YES;Format=Delimited(,)\"";
            OleDbConnection cn = new OleDbConnection(stInputConn);
stInputFileName = ConfigurationManager.AppSettings.Get("InputFile");
     // the input file name is CSV_DataExport.CSV
if (cn.State != ConnectionState.Open)
                    cn.Open();
                OleDbCommand aCmd = new OleDbCommand(string.Format("SELECT * FROM [{0}] WHERE CnAttrCat_4_01_Description = 'BLC'", stInputFileName), cn);
OleDbDataReader aReader = aCmd.ExecuteReader();


The last line (ExecuteReader) is where I get the OleDb Exception.
Thank you,

Is This A Good Question/Topic? 0
  • +

Replies To: OleDb Exception...

#2 JackOfAllTrades  Icon User is offline

  • Saucy!
  • member icon

Reputation: 6031
  • View blog
  • Posts: 23,414
  • Joined: 23-August 08

Re: OleDb Exception...

Posted 07 April 2010 - 01:01 PM

And the exception IS???
Was This Post Helpful? 0
  • +
  • -

#3 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OleDb Exception...

Posted 07 April 2010 - 01:30 PM

The exception is: "No value given for one or more required parameters." as stated in the topic title...

OK, as I recall I had the same issue when trying to read the CSV file with OleDb driver... Here's the thing you could try: if the name of your CSV file is more than 8 characters long, rename the file to be less or equal to 8 characters long.

What operating system are you using anyway?
Was This Post Helpful? 1
  • +
  • -

#4 sallyjenkins  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-February 09

Re: OleDb Exception...

Posted 07 April 2010 - 02:53 PM

I renamed the file to CSV_2010.csv and got the same error :-(
I am using XP Pro OS with all the latest updates SP 3.
Was This Post Helpful? 0
  • +
  • -

#5 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OleDb Exception...

Posted 07 April 2010 - 03:20 PM

OK... Another solution would be this:

Open the registry and find the: HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text
Then verify that format is: "Format" = "CSVDelimited"
If it's not, update it to be "Format" = "CSVDelimited"
Then try if it works...
Was This Post Helpful? 0
  • +
  • -

#6 sallyjenkins  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-February 09

Re: OleDb Exception...

Posted 08 April 2010 - 08:49 AM

The format was/is CSVDelimited. I did not need to change it.
Was This Post Helpful? 0
  • +
  • -

#7 FlashM  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 382
  • View blog
  • Posts: 1,195
  • Joined: 03-December 09

Re: OleDb Exception...

Posted 08 April 2010 - 09:25 AM

OK... I guess I really needed to test your code to see what's going on... I believe that a problem is in delimiter. I used your code and made my own test csv file that looks like this:

ID,FirstName,LastName,CnAttrCat_4_01_Description
1,"John","Doe","AAA"
2,"Jane","Doe","BBB"
3,"Someone","Else","BLC"

And your code works perfect for me. If I use the ; as a delimiter, I get the same error as you. Change the delimiter in your text file to be comma insted of ;

You don't have to use quotes for strings in your csv file, but it's nothing wrong if you do... The code should work in both cases.

If you don't want to change your ; delimiter then update the delimiter in the registry at the following location:
HKEY_LOCAL_MACHINE \ SOFTWARE \ Microsoft \ Jet \ 4.0 \ Engines \ Text

You can use the following delimiters:
"Format" = "TabDelimited"
"Format" = "Delimited(;)"
"Format" = "CSVDelimited"

Hope this finally resolves your issue...

This post has been edited by FlashM: 08 April 2010 - 09:26 AM

Was This Post Helpful? 0
  • +
  • -

#8 sallyjenkins  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 33
  • Joined: 26-February 09

Re: OleDb Exception...

Posted 09 April 2010 - 07:59 AM

:online2long:Thanks to all who responded. I have fixed the problem. I was with the input file. Last time I created it I must have changed something because the column headings were missing. I created the file last night with headings and all is well.
Lesson learned.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1