how to insert data into the table after excel import -- please conclude the routine for the insert

Question

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 20-Jul-14 8:39am

Member 10744248

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

strimpf · Answer 1 · 2014-07-22T11:57:00

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