OleDb Exception...

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

Page 1 of 1

9 Replies - 3183 Views - Last Post: 14 August 2014 - 05:24 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: 6062
  • View blog
  • Posts: 23,513
  • 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
  • +
  • -

#9 magesh  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 16-January 06

Re: OleDb Exception...

Posted 14 August 2014 - 03:58 AM

View Postsallyjenkins, on 07 April 2010 - 12:44 PM, said:

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,




Use this

Provider=Microsoft.Jet.OleDb.4.0; Data Source = '[Your File directory]'; Extended Properties = 'Text; HDR=YES; FMT=CsvDelimited; ISAM=2'"


OleDbCommand cmd = new OleDbCommand("SELECT * FROM [" + csv file name + "]", conn);
Was This Post Helpful? 0
  • +
  • -

#10 Skydiver  Icon User is offline

  • Code herder
  • member icon

Reputation: 3566
  • View blog
  • Posts: 11,079
  • Joined: 05-May 12

Re: OleDb Exception...

Posted 14 August 2014 - 05:24 AM

Please do not revive old threads... specially if the person asking the question posted the question 4 years ago and hasn't logged on since.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1