locked
Importing Data to Excel 2007 RRS feed

  • Question

  • User1902406554 posted

    I have came across the funniest thing today.  I am working with Visual Web Developer using Visual C#.  I am using a Update statement while connected to the .xlsx file and my code is as follows.

    string ColumnName = Convert.ToString(Data.Text);

    string ColumnName1 = Convert.ToString(ID.Text); //INSERT STATEMENT ----- INSERT INTO [Sheet1$] ([change]) values (@ID)

    string ColumnName2 = Convert.ToString(Value1.Text);

    //string ColumnName3 = Convert.ToString(Value2.Text);

    OleDbConnection con1 = new OleDbConnection(System.Configuration.ConfigurationManager.ConnectionStrings["ExcelConnectionInfo"].ToString());OleDbCommand cmd1 = new OleDbCommand("UPDATE [Sheet1$] SET col2=@Data, col3=@Value1 WHERE col1=@ID");

     

    cmd1.Parameters.AddWithValue("@ID", ColumnName1);

    cmd1.Parameters.AddWithValue("@Data", ColumnName);

    cmd1.Parameters.AddWithValue("@Value1", ColumnName2);

    //cmd1.Parameters.AddWithValue("@Value2", ColumnName3);

    cmd1.Connection = con1;

    con1.Open();

    Response.Write(
    "Connected");

    cmd1.ExecuteNonQuery();

    con1.Close();

    Response.Write("Closed");

     In my Excel File my format is as follows

    col1         col2     col3     col4
    1              a         1a                                 
    2              b         2b                          
    3              c         3c                            
    4              e         4e

    When I run the query it throws an exception: Data type mismatch in criteria expression. Is there something wrong with my query syntex or is there something wrong with the excel file or Code?  Any suggestions, My head is hurting!!!!

     - NU STUDENT -

    Wednesday, April 8, 2009 11:05 AM

All replies

  • User1563393710 posted

    Give it a try -:

    OleDbCommand("UPDATE [Sheet1$] SET col2='" + ColumnName1 + "', col3='" + ColumnName2 + "' WHERE col1='" + ColumnName1 + "'");

    Thursday, April 9, 2009 7:12 AM
  • User1902406554 posted

    It keeps throwing the error: Data type mismatch in criteria.  Does that mean that i need to change the settings of the variable that is thrown in the update statement from string to something else? if so what else do I change it to?  Still can't figure this out

     - Nu Student -

    Monday, April 13, 2009 9:53 AM