csv上传数据库中的问题 [英] problem in csv upload in database

问题描述

错误：您的SQL语法出错;查看与您的MySQL服务器版本对应的手册，以便在D：\ Book1111.csv附近使用正确的语法INSERT INTO TABLE sla1 FIELDS TERMINATED BY''，''LINES TERMINAT''在第1行

我的代码.........

string path = FileUpload1.PostedFile.FileName;

string ext = Path.GetExtension（path）;

string contenttype = string.Empty;

if（！FileUpload1.HasFile）

{



ClientScript.RegisterStartupScript（Page.GetType（），validation，< script language =''javascript''> alert（''请选择一个文件''）< / script>）; //如果文件上传者没有选择文件



}

else if（FileUpload1.HasFile）

{

试试

{

开关（分机）

{

case。 csv：



contenttype =application / vnd.ms-excel;



break; < br $>
}

string year = ddlyear.SelectedItem.ToString（）;

string month = ddlmonth.SelectedItem.ToString（）;

MySqlConnection exclecon = new MySqlConnection（Server = Localhost; DataBase = password1; user = root; password = nectar）;

string insquery =INSERT INTO sla（month，year，contenttype） ）VALUES（@month，@ year，@ content类型）;

string uploadQry =LOAD DATA INFILE+ path +INSERT INTO TABLE sla1 FIELDS TERMINATED BY''，''终止于''\\'n''IGNORE 1 LINES;

MySqlCommand mycom =新的MySqlCommand（insquery，exclecon）;

mycom.Parameters.AddWithValue（@ month，month）;

mycom.Parameters.AddWithValue（@ year，year ）;

mycom.Parameters.Add（@ contenttype，MySqlDbType.VarChar）.Value = contenttype;



MySqlCommand myCUpload = new MySqlCommand（uploadQry，exclecon）;

exclecon.Open（）;

mycom.ExecuteNonQuery（）;

myCUpload.ExecuteNonQuery（）;



ClientScript.RegisterStartupScript（Page.GetType（），validation，< script language =''javascript''> alert（''File uploaded成功''）< / script>）;



}



catch（Exception ex）

{

Label4.Text =错误：+ ex.Message.ToString（）;



}

}

}

Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''D:\Book1111.csv INSERT INTO TABLE sla1 FIELDS TERMINATED BY '','' LINES TERMINAT'' at line 1
my code.........
string path = FileUpload1.PostedFile.FileName;
string ext = Path.GetExtension(path);
string contenttype = string.Empty;
if (!FileUpload1.HasFile)
{

ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language=''javascript''>alert(''Please select a file'')</script>"); //if file uploader has no file selected

}
else if (FileUpload1.HasFile)
{
try
{
switch (ext)
{
case ".csv":

contenttype = "application/vnd.ms-excel";

break;
}
string year = ddlyear.SelectedItem.ToString();
string month = ddlmonth.SelectedItem.ToString();
MySqlConnection exclecon = new MySqlConnection("Server=Localhost;DataBase=password1;user=root;password=nectar");
string insquery = "INSERT INTO sla (month, year, contenttype) VALUES(@month, @year, @contenttype)";
string uploadQry = "LOAD DATA INFILE " + path + " INSERT INTO TABLE sla1 FIELDS TERMINATED BY '','' LINES TERMINATED BY ''\n'' IGNORE 1 LINES";
MySqlCommand mycom = new MySqlCommand(insquery, exclecon);
mycom.Parameters.AddWithValue("@month", month);
mycom.Parameters.AddWithValue("@year", year);
mycom.Parameters.Add("@contenttype", MySqlDbType.VarChar).Value = contenttype;

MySqlCommand myCUpload = new MySqlCommand(uploadQry, exclecon);
exclecon.Open();
mycom.ExecuteNonQuery();
myCUpload.ExecuteNonQuery();

ClientScript.RegisterStartupScript(Page.GetType(), "validation", "<script language=''javascript''>alert(''File uploaded Successfully '')</script>");

}

catch (Exception ex)
{
Label4.Text = "Error: " + ex.Message.ToString();

}
}
}

推荐答案

1）用单引号括起查询中的路径。 />

1) Enclose the path in your query with single quotes.

string uploadQry = "LOAD DATA INFILE '" + path + "' INSERT INTO TABLE sla1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES";

2）从查询中删除INSERT命令。

2) Remove INSERT command from your Query.

string uploadQry = "LOAD DATA INFILE '" + path + "' INTO TABLE sla1 FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' IGNORE 1 LINES";

Hi ..



也可以在mysql命令promt窗口写下打击代码。

一旦尝试它。



>> LOAD DATA LOCAL INFILEfilepathINTO TABLE databasename.tablename FIELDS TERMINATED BY''，''LINESATED BY''\ n'';



它可能对你有帮助！

Hi..

Write the blow code on mysql command promt window also.
once try it.

>>LOAD DATA LOCAL INFILE "filepath" INTO TABLE databasename.tablename FIELDS TERMINATED BY '','' LINES TERMINATED BY ''\n'';

its may helpful for u!

这篇关于csv上传数据库中的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！