Click here to Skip to main content
15,917,926 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
string query = "SELECT [USERNAME], [PASSWORD], [FULLNAME] FROM [Users$]";
      OleDbCommand cmd = new OleDbCommand(query, oleCn);

      OleDbDataAdapter da = new OleDbDataAdapter(cmd);
      DataSet ds = new DataSet();

      da.Fill(ds);

      DataTable dt = new DataTable();
      dt = ds.Tables[0];

      grvExcelData.DataSource = dt;
      grvExcelData.DataBind();

      //saved into the table
      string sqlConnectionString = "Data Source=HP-PC\\SQLEXPRESS;Initial Catalog=SAMMY;User ID=benefit";
      SqlConnection sqlCon = new SqlConnection(sqlConnectionString);

      sqlCon.Open();

      SqlCommand sqlCmd = new SqlCommand();
      sqlCmd.Connection = sqlCon;
      sqlCmd.CommandType = CommandType.Text;

      for (int i = 0; i <= grvExcelData.Rows.Count - 1; i++)
      {
          string commandText = "INSERT INTO Users(USERNAME, PASSWORD, FULLNAME)" +
              "VALUES()";
      }
Posted
Comments
[no name] 20-Jul-14 15:34pm    
And the question or problem is....?
Member 10744248 21-Jul-14 9:58am    
How do you complete the the following with the values

for (int i = 0; i <= grvExcelData.Rows.Count - 1; i++)
{
string commandText = "INSERT INTO Users(USERNAME, PASSWORD, FULLNAME)" +
"VALUES()";
}
[no name] 21-Jul-14 12:56pm    
Well if you insist on doing it this way, you would concatenate the values from your datatable (I assume that is where you are getting your values from) together inside the (). Not sure exactly why this would be a "problem". Do you not know how to concatenate strings? A StringBuilder would be a better choice. Even String.Format. Why are you not using command parameters?
Member 10744248 21-Jul-14 13:23pm    
please re-organise it.

New to c#
Member 10744248 21-Jul-14 14:03pm    
I am not insisting This is my attempt as a beginner, please give the writr way thanks

1 solution

I did this using a SQL query to get the values to insert instead of using Excel, but I think it shows what you need. I had to add the MultipleActiverResultSets=true to the connection string to make it work, but it did.

C#
SqlConnection conn;
SqlCommand comm;
SqlDataReader sdr;
string connstring = "Data Source=YourServer\\SQLEXPRESS;Initial Catalog=YourDatabase;Integrated Security=False;MultipleActiveResultSets=true;User Id=YourUsername;Password=YourPassword;Connect Timeout=0";


public void populateUsers()
{
    try
    {

        conn = new SqlConnection(connstring);
        conn.Open();
        string query = "SELECT SSN, Password, LastName FROM Person";
        comm = new SqlCommand(query, conn);
        sdr = comm.ExecuteReader();

        while(sdr.Read())
        {
            string sql = "INSERT into Users VALUES (@un, @pw, @fn)";
            comm = new SqlCommand(sql, conn);
            SqlParameter uname = new SqlParameter("@un", SqlDbType.Text);
            uname.Direction = ParameterDirection.Input;

            SqlParameter pword = new SqlParameter("@pw", SqlDbType.Text);
            pword.Direction = ParameterDirection.Input;

            SqlParameter fname = new SqlParameter("@fn", SqlDbType.Text);
            fname.Direction = ParameterDirection.Input;

            comm.Parameters.Add(uname);
            comm.Parameters.Add(pword);
            comm.Parameters.Add(fname);

            uname.Value = sdr["SSN"].ToString();
            pword.Value = sdr["Password"].ToString();
            fname.Value = sdr["LastName"].ToString();

            comm.ExecuteNonQuery();

        }
    }
    catch(Exception exc)
    {
        //log or display or ignore error
    }
    finally
    {
        sdr.Dispose();
        comm.Dispose();
        conn.Close();
    }


Hope it helps.

-strimpf
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900