locked
excel sheet uploading prob. RRS feed

  • Question

  • User1936334964 posted

    there is broblum for uploading excel sheet when my application runing on local host but there is no problum on vs enviroment .

    i am using the following connection string code

    strConn =

    "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";Extended Properties=Excel 8.0;"

    ;

    please tell me whats the problum as son as possible.....m waiting..

    Tuesday, February 8, 2011 3:32 AM

Answers

  • User-109091561 posted
    public void GetDataFromFile(string Filename,string userid)
            {
                OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Filename + ";Extended Properties=Excel 8.0");
                try
                {
                    OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                    oconn.Open();
                    OleDbDataReader odr = ocmd.ExecuteReader();
                    string ArtistName = "";
    
                    while (odr.Read())
                    {
                        ArtistName = valid(odr, 0);
    
                        insertdataintosql(ArtistName, userid);
                    }
                    oconn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
            {
                object val = myreader[stval];
                if (val != DBNull.Value)
                    return val.ToString();
                else
                    return Convert.ToString(0);
            }
    
            public void insertdataintosql(string ArtistName,string UserId)
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    SqlCommand cmdinsert = new SqlCommand("DJ_Insert_Artist", con);
                    SqlCommand cmdinsert1 = new SqlCommand("DJ_Insert_ArtistTemp", con);
    
                    cmdinsert.CommandType = CommandType.StoredProcedure;
                    cmdinsert.Connection = con;
                    cmdinsert.Parameters.Add("@ArtistName", SqlDbType.NVarChar, 50).Value = ArtistName;
                    cmdinsert.Parameters.Add("@Des", SqlDbType.NVarChar, 50).Value = ArtistName;
    
                    cmdinsert.Parameters.Add("@ArtistImage", SqlDbType.NVarChar, 50).Value = "";
    
                    cmdinsert.Parameters.Add("@UserId", SqlDbType.NVarChar, 50).Value = UserId;
                    cmdinsert.ExecuteNonQuery();
                    cmdinsert.Parameters.Clear();
                    cmdinsert.Dispose();
    
                    //cmdinsert1.CommandType = CommandType.StoredProcedure;
                    //cmdinsert1.Connection = con;
                    //cmdinsert1.Parameters.Add("@ArtistName", SqlDbType.NVarChar, 50).Value = ArtistName;
                    //cmdinsert1.Parameters.Add("@Des", SqlDbType.NVarChar, 50).Value = ArtistName;
    
                    //cmdinsert1.Parameters.Add("@ArtistImage", SqlDbType.NVarChar, 50).Value = "";
    
                    //cmdinsert1.Parameters.Add("@UserId", SqlDbType.NVarChar, 50).Value = UserId;
                    //cmdinsert1.ExecuteNonQuery();
                    //cmdinsert1.Parameters.Clear();
                    //cmdinsert1.Dispose();
                    con.Close();
                }
                catch { throw; }
              
            }
    
    here filename is name of file
    change the code as required


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 8, 2011 5:11 AM

All replies

  • User-1161841047 posted

    Kindly share us the error message.

    Tuesday, February 8, 2011 4:12 AM
  • User-886123767 posted

    Can you please specify your error so that we can give you some direction to solve your problem.

    Tuesday, February 8, 2011 4:55 AM
  • User-109091561 posted
    public void GetDataFromFile(string Filename,string userid)
            {
                OleDbConnection oconn = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Filename + ";Extended Properties=Excel 8.0");
                try
                {
                    OleDbCommand ocmd = new OleDbCommand("select * from [Sheet1$]", oconn);
                    oconn.Open();
                    OleDbDataReader odr = ocmd.ExecuteReader();
                    string ArtistName = "";
    
                    while (odr.Read())
                    {
                        ArtistName = valid(odr, 0);
    
                        insertdataintosql(ArtistName, userid);
                    }
                    oconn.Close();
                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
    
            protected string valid(OleDbDataReader myreader, int stval)//if any columns are found null then they are replaced by zero
            {
                object val = myreader[stval];
                if (val != DBNull.Value)
                    return val.ToString();
                else
                    return Convert.ToString(0);
            }
    
            public void insertdataintosql(string ArtistName,string UserId)
            {
                try
                {
                    if (con.State == ConnectionState.Closed)
                        con.Open();
                    SqlCommand cmdinsert = new SqlCommand("DJ_Insert_Artist", con);
                    SqlCommand cmdinsert1 = new SqlCommand("DJ_Insert_ArtistTemp", con);
    
                    cmdinsert.CommandType = CommandType.StoredProcedure;
                    cmdinsert.Connection = con;
                    cmdinsert.Parameters.Add("@ArtistName", SqlDbType.NVarChar, 50).Value = ArtistName;
                    cmdinsert.Parameters.Add("@Des", SqlDbType.NVarChar, 50).Value = ArtistName;
    
                    cmdinsert.Parameters.Add("@ArtistImage", SqlDbType.NVarChar, 50).Value = "";
    
                    cmdinsert.Parameters.Add("@UserId", SqlDbType.NVarChar, 50).Value = UserId;
                    cmdinsert.ExecuteNonQuery();
                    cmdinsert.Parameters.Clear();
                    cmdinsert.Dispose();
    
                    //cmdinsert1.CommandType = CommandType.StoredProcedure;
                    //cmdinsert1.Connection = con;
                    //cmdinsert1.Parameters.Add("@ArtistName", SqlDbType.NVarChar, 50).Value = ArtistName;
                    //cmdinsert1.Parameters.Add("@Des", SqlDbType.NVarChar, 50).Value = ArtistName;
    
                    //cmdinsert1.Parameters.Add("@ArtistImage", SqlDbType.NVarChar, 50).Value = "";
    
                    //cmdinsert1.Parameters.Add("@UserId", SqlDbType.NVarChar, 50).Value = UserId;
                    //cmdinsert1.ExecuteNonQuery();
                    //cmdinsert1.Parameters.Clear();
                    //cmdinsert1.Dispose();
                    con.Close();
                }
                catch { throw; }
              
            }
    
    here filename is name of file
    change the code as required


     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, February 8, 2011 5:11 AM