• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1097
  • Last Modified:

Problem with load data local infile and c#

Im having a problem in c# .net express edition.

the error I get is "Object reference not set to an instance of an object."

heres the code
try {
        string sql = "load data local infile 'I:\\Internet Development\\WebVision\\Contents\\Reporting\\Source           Data\\Donor Vision\\Dailies\\20070520.tab' INTO TABLE azarel_data_donorvision.daily_donorvision";

                MySqlConnection conn = new MySqlConnection("data  source=source;DATABASE=database;User ID=user;PassWord=pw;");
                MySqlCommand comm = new MySqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = sql;
                comm.ExecuteNonQuery();
            }
            catch (Exception ex) {
                throw ex;
            }

problem is that this only happens when I use the load data local infile statement
I can execute any other sql statement with this same code.
Any help fixing this would be appreciated.
0
brian78wa
Asked:
brian78wa
  • 5
  • 4
1 Solution
 
ToFroCommented:
There have been issues with "LOAD DATA LOCAL INFILE..." in some versions of the .NET Connector. Try connection with ODBC instead(you need to install: MySQL ODBC 3.51 Driver). If that works, then there's a problem with the connector: Use the following connection string and the classes in System.Data.Odbc instead of the ones in MySql.Data.MySqlClient:

"Driver={MySQL ODBC 3.51 Driver};Server=source;Port=3306;Database=database;User=user;Password=pw;Option=3;"

If that doesn't solve it, then what version of MySQL are you using? There have also been problems with LOAD DATA... in some versions of MySQL Server.
0
 
ToFroCommented:
You can download the MySQL ODBC 3.51 Driver from here:
http://dev.mysql.com/downloads/connector/odbc/3.51.html
0
 
brian78waAuthor Commented:
I tried that and I get the following error.
Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

not sure if I need to add a refference or not if I am what is the reference?
0
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
brian78waAuthor Commented:
Oh and Im using MySQL 5.0
0
 
ToFroCommented:
You shouldn't have to reference anything. Not if you used the System.Data.Odbc namespace. You need to have the using statement in, of course:
using System.Data.Odbc;
... But you probably had that. Did you change the details(username, password, etc.) in the connection string?

If you can't get it working, you could try adding an ODBC Data Source like this:
- Go to Control Panel >> Administrative Tools >> Data Sources (ODBC)
- Click Add and then select the MySQL ODBC 3.51 Driver
- Fill in the details for your MySQL Server configuration, then click Test and OK if successful.

After you have added the datasource, try connecting with this simple connection string instead of the old one(replace dsnname with the name you specified when creating the Data Source):
"DSN=dsnname"

0
 
brian78waAuthor Commented:
Ive actually already done all that.
Added the using statement.
tried using the dsn that i have set up which connects fine when you test.
Not really sure whats going on.
0
 
ToFroCommented:
Looking again at your error message...

Request for the permission of type 'System.Data.Odbc.OdbcPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.

... this is probably a security issue.

I don't think it's the MySQL Server security settings, since the error message mentions a .NET namespace.
It probably has something to do with the Framework Security Settings or Permissions. Too bad I'm no expert on that.

I don't know where you're from, but I'm sitting here at home in Finland, and will be going to sleep now, and then to work. So you're on your own for a while, unless someone else joins this question.
I suggest you search the web for the error message. You might wan't to limit the search to "Request for the permission of type", to get more hits.

See ya.
0
 
brian78waAuthor Commented:
I just did something stupid when using your solution of DSn=dsnname but I corrected it and it works now so thank you for your help.

Im a web analyst and Im having to pull reports every day and load them to db so this makes my life alot easier so I can automate it :)
0
 
ToFroCommented:
I'm glad I was of some use.
Keep coding.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now