| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 1906 人关注过本帖
标题:excel导入sq程序l提示“from字句语法错误”
只看楼主 加入收藏
daylightme
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2010-11-14
结帖率:0
收藏
已结贴  问题点数:20 回复次数:5 
excel导入sq程序l提示“from字句语法错误”
版主好,各位高手好,初入论坛,还请大家多关照,这两天因公司需要,要把一些EXCEL表格导入SQL数据库,下面的代码在执行时一直提示“from子句语法错误”,不知道是何原因!另外,有谁可以提供下比较好的excel导入sql的代码么,初学c#,不胜感激!
using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;
using System.Data.SqlClient;
using
using System.Data.OleDb;


public partial class inputexcel : System.Web.UI.Page
{
    protected static string getErrMsg = "";
    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {
        string filePath = "";
        if (!FileUpload1.HasFile)
        {
            Response.Write("<script language=javascript>alert('请选择要上传的文件!');</script>");
            return;
        }
        else
        {
            //获取文件路径
            filePath = FileUpload1.PostedFile.FileName;
            string excel_sql = "select [文件编号],[目录],[文件名],[文件路径],[上传时间],[状态],[上传人] from OPENROWSET('MICROSOFT.JET.OLEDB.4.0','Excel 8.0;HDR=YES;DATABASE=d:\\exceltosql.xls',sheet1$)";
            //把excel看成数据库
            string mystring = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\exceltosql.xls;Extended Properties=Excel 8.0;";
            OleDbConnection conn = new OleDbConnection(mystring);
            OleDbDataAdapter myda = new OleDbDataAdapter(excel_sql,conn);
            DataSet myds = new DataSet();
            myda.Fill(myds);
            DataTable dt = new DataTable();
            dt = myds.Tables[0];
            GridView1.DataSource = dt;
            GridView1.DataBind();
            if (myds.Tables[0].Rows.Count != 0)
            {
                SqlConnection myconn = new SqlConnection();
                string strConn = ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
                myconn.ConnectionString = strConn;
                SqlCommand cmd = new SqlCommand();
                try
                {
                    for (int i = 0; i < myds.Tables[0].Rows.Count; i++)
                    {
                        string sql = "insert into filedetail(filenumber,catalogueid,filename,filepath,updatetime,state,funame) values('" + myds.Tables[0].Rows[i]["文件编号"] + "','" + myds.Tables[0].Rows[i]["目录"] + "','" + myds.Tables[0].Rows[i]["文件名"] + "','" + myds.Tables[0].Rows[i]["文件路径"] + "','" + myds.Tables[0].Rows[i]["上传时间"] + "','" + myds.Tables[0].Rows[i]["状态"] + "','" + myds.Tables[0].Rows[i]["上传人"] + "')";
                        cmd = new SqlCommand(sql,myconn);
                        if (cmd.Connection.ToString()=="Closed")
                        {
                            cmd.Connection.Open();
                        }
                        cmd.ExecuteNonQuery();
                    }
                }
                catch (Exception ex)
                {

                    getErrMsg = ex.Message.ToString();
                    Response.Write(getErrMsg);
                }
                finally
                {
                    cmd.Connection.Close();
                    cmd = null;
                }
                if (getErrMsg =="")
                {
                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script language='Javascript'>alert('导入成功!')</script>");
                    return;
                }
                else
                {
                    Page.ClientScript.RegisterStartupScript(Page.GetType(), "", "<script language='Javascript'>alert('导入失败!')</script>");
                    return;
                }
            }
        }
    }

}


[ 本帖最后由 daylightme 于 2010-11-14 11:14 编辑 ]
搜索更多相关主题的帖子: 不胜感激 excel 数据库 EXCEL 
2010-11-14 10:07
c1_wangyf
Rank: 11Rank: 11Rank: 11Rank: 11
等 级:小飞侠
威 望:7
帖 子:665
专家分:2832
注 册:2010-5-24
收藏
得分:7 
个人建议你可以先把excel文件中的各个sheet都读到一个listbox里面,然后根据需要把那个需要的sheet导入到数据库!!

下面是一段实例:
string openfileName = null;
            OpenFileDialog openfd = new OpenFileDialog();
            openfd.Filter = "All Excel file (*.xls)|*.xls|All files (*.*)|*.*";
            if (openfd.ShowDialog() == DialogResult.OK)
            {
                openfileName = openfd.FileName;
                OpenFileName = openfileName;
                if (openfd.FileName != "")
                {
                    string excelfilename = openfileName;
                    OleDbConnection conn = new OleDbConnection();
                    conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0';Data Source=" + excelfilename;

                    if (conn.State == ConnectionState.Closed)
                    {
                        conn.Open();
                        DataTable tables = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
                        listBox_sheet_Name.Items.Clear();
                        for (int i = 0; i < tables.Rows.Count; i++)
                        {
                            listBox_sheet_Name.Items.Add(tables.Rows[i]["Table_Name"].ToString());//把文件里面的sheet都放到listbox里面
                        }
                    }
                    conn.Close();
                    conn.Dispose();
                }
                else
                {
                    MessageBox.Show("The file name which you want to import is EMPTY!!!", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                }
            }
            else
            {
                MessageBox.Show("Please select a data file to import...", "Tips", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }

下面是导入到datagridview显示一下
private void listBox_sheet_Name_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox_sheet_Name.SelectedIndex != -1 && listBox_sheet_Name.SelectedIndex < listBox_sheet_Name.Items.Count)
            {
                string excelfilename = OpenFileName;
                OleDbConnection conn = new OleDbConnection();
                dataGridView_run_parameter_nameUnit.ColumnHeadersVisible = true;
                dataGridView_run_parameter_nameUnit.ColumnHeadersHeight = 120;
                conn.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties='Excel 8.0';Data Source=" + excelfilename;
                comboBox_run_parameter.Visible = false;
                comboBox_runparameter_unit.Visible = false;
                if (conn.State == ConnectionState.Closed)
                {
                    conn.Open();
                    DataSet daset = new DataSet();
                    string query = "SELECT  * from [" + listBox_sheet_Name.SelectedItem + "]";//相当于你的select语句
                    OleDbDataAdapter oledd = new OleDbDataAdapter(query, conn);
                    tabControl_right.SelectedTab = tabPage_preview; //在最前面显示该tabpage
                    tabPage_preview.Show();
                    dataGridView_preview.DataSource = null; //断开数据源,清空datagridview,为重新载入数据做准备
                    dataGridView_preview.Columns.Clear();
                    dataGridView_preview.Update();
                    oledd.Fill(daset, "Preview");
                    dataGridView_preview.AllowUserToAddRows = false;
                    dataGridView_preview.DataSource = daset.Tables["Preview"].DefaultView;
                    dataGridView_preview.ColumnHeadersVisible = true;
                    dataGridView_preview.RowHeadersVisible = false;
                }
                conn.Close();
                conn.Dispose();
            }
        }
仅供参考!!
2010-11-14 14:42
daylightme
Rank: 1
等 级:新手上路
帖 子:2
专家分:0
注 册:2010-11-14
收藏
得分:0 
谢谢版主,我去试一下这个方法!
2010-11-14 15:27
梦灵儿
Rank: 3Rank: 3
等 级:新手上路
威 望:8
帖 子:260
专家分:9
注 册:2007-8-7
收藏
得分:7 
调试就能出来。或者把这条语句输出来!看看哪个地方搞错了!

2010-11-14 16:34
梦灵儿
Rank: 3Rank: 3
等 级:新手上路
威 望:8
帖 子:260
专家分:9
注 册:2007-8-7
收藏
得分:0 
myds.Tables[0].Rows[i]["文件名"].toString() 应该是这样的!如果是字符串就要这样写。如果是数字要转换一哈!不然会报错!

2010-11-14 16:36
欲与天斗
Rank: 4
等 级:业余侠客
帖 子:88
专家分:270
注 册:2010-10-6
收藏
得分:7 
路过
2010-11-21 18:02
快速回复:excel导入sq程序l提示“from字句语法错误”
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.033036 second(s), 9 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved