OleDB Update command

[MasterRacker]

The code below is a snippet from a small utility I'm trying to write to load an Excel spreadsheet int oa DataSet, change the "Password" and "ChangeDay" fields in ALL rows with programmatically generated information, then save the whole sheet back out.

Everything works as I want it to except the save. Being new to ADO.NET I'm suffering brain disintegration trying to come up with the UPDATE command necessary to allow the DataAdapter Update method to function.

I expect the command string to be something along the lines of "OleDbCommand cmdUpdate = new OleDbCommand("UPDATE [Sheet1$] SET Password = @Password, ChangeDay = @ChangeDay WHERE UserID = @UserID");" but I'm struggling with getting the parameters into the command object.

private StringBuilder sbConn = new StringBuilder();
private string workBookName = "";  // filled by an OpenFileDialog
private OleDbConnection cnExcel;
private OleDbDataAdapter daWorkBook;

private void LoadSheet()
{
	sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
	sbConn.Append(workBookName);
	sbConn.Append(";Extended Properties=");
	sbConn.Append(Convert.ToChar(34));
	sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
	sbConn.Append(Convert.ToChar(34));

	cnExcel = new OleDbConnection(sbConn.ToString());
	cnExcel.Open();
	OleDbCommand cmdSelect = new OleDbCommand("SELECT UserID, Password, ChangeDay FROM [Sheet1$]", cnExcel); 
	daWorkBook = new OleDbDataAdapter(); 
	daWorkBook.SelectCommand = cmdSelect; 
	daWorkBook.Fill(dsWorkBook); 
	cnExcel.Close(); 
}

private void btnSave_Click(object sender, System.EventArgs e)
{
// Update command generated here....

	cnExcel.Open();
	daWorkBook.Update (dsWorkBook);
	cnExcel.Close();
}

Can anyone suggest an Update command? (I'm sure I'm missing something obvious and I'm too brain damaged from years of beer abuse to intrepret what Dr. Google is showing me, but that's the way it goes some days. )


Jeff
The future is already here - it's just not widely distributed yet...

 

[dalchri]

It might be something along the lines of:

string strPassword;
DateTime dtmChangeDay;
string strUserID;

//Parameter variable assignments here

cmdUpdate.Parameters.Add("@Password", strPassword);
cmdUpdate.Parameters.Add("@ChangeDay", dtmChangeDay);
cmdUpdate.Parameters.Add("@UserID", strUserID);

Also, some data providers are sensitive to the character used to denote parameters so you might need to use a "?" instead of an "@". If I remember right, ODBC doesn't even support named parameters so you have to do something like this:

OdbcCommand cmdUpdate = new OdbcCommand(
   "UPDATE [Sheet1$] " +
   "SET " +
      "Password = ?, " +
      "ChangeDay = ? " +
   "WHERE UserID = ?");

//The parameters are implicitly identified by the order they are added
cmdUpdate.Parameters.Add(strPassword);
cmdUpdate.Parameters.Add(dtmChangeDay);
cmdUpdate.Parameters.Add(strUserID);

 

[MasterRacker]

Pardon the long post...

dalchri is right - OleDB appears to need the ? syntax. After poking around some more in MSDN I've tried a number of different syntaxes. I always get a runtime OleDB exception on the update attempt. I'm beginning to believe there's something wrong with the DataAdapter, but I can't see what it is. This is a very simple little app and the only quirk is that I'm connecting to a spreadsheet instead of an actual DB. I'm posting the entire module minus the form designer stuff (hence the advance apology) for anyone who wants to look at it:

using System;
using System.Drawing;
using System.Collections;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data;
using System.Data.OleDb;
using System.Text;

namespace MassPass
{
  /// <summary>
  /// Summary description for MainForm.
  /// </summary>
  public class MainForm : System.Windows.Forms.Form
  {
    /// <summary>
    /// Required designer variable.
    /// </summary>
    private System.ComponentModel.Container components = null;

    private System.Windows.Forms.DataGrid dataGrid1;
    private System.Windows.Forms.Button btnExit;
    private System.Data.DataView dataView1;
    private System.Windows.Forms.Button btnOpen;

    private System.Windows.Forms.GroupBox groupBox1;
    private System.Windows.Forms.CheckBox chkLower;
    private System.Windows.Forms.CheckBox chkUpper;
    private System.Windows.Forms.CheckBox chkNum;
    private System.Windows.Forms.Label label2;
    private System.Windows.Forms.Label label3;
    private System.Windows.Forms.Label label4;
    private System.Windows.Forms.TrackBar trkSize;
    private System.Windows.Forms.Button btnPasswords; 
    private System.Windows.Forms.Button btnSave;

    private StringBuilder sbConn = new StringBuilder();
    private string workBookName = "";
    private OleDbConnection cnExcel;
    private OleDbDataAdapter daWorkBook;
    private  DataSet dsWorkBook = new DataSet();

    public MainForm()
    {
      //
      // Required for Windows Form Designer support
      //
      InitializeComponent();

      //
      // TODO: Add any constructor code after InitializeComponent call
      //
    }

    /// <summary>
    /// Clean up any resources being used.
    /// </summary>
    protected override void Dispose( bool disposing )
    {
      if( disposing )
      {
        if (components != null) 
        {
          components.Dispose();
        }
      }
      base.Dispose( disposing );
    }

    #region Windows Form Designer generated code

    #endregion

    /// <summary>
    /// The main entry point for the application.
    /// </summary>
    [STAThread]
    static void Main() 
    {
      Application.Run(new MainForm());
    }

    private void LoadSheet()
    {
      sbConn.Append(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=");
      sbConn.Append(workBookName);
      sbConn.Append(";Extended Properties=");
      sbConn.Append(Convert.ToChar(34));
      sbConn.Append("Excel 8.0;HDR=Yes;IMEX=2");
      sbConn.Append(Convert.ToChar(34));

      cnExcel = new OleDbConnection(sbConn.ToString());
      cnExcel.Open();
// do a try/catch for invalid file (wrong columns, etc.)
      daWorkBook = new OleDbDataAdapter(); 
      OleDbCommand cmdSelect = new OleDbCommand("SELECT UserID, Password, ChangeDate FROM [Sheet1$]", cnExcel); 
      daWorkBook.SelectCommand = cmdSelect; 
      daWorkBook.Fill(dsWorkBook); 
// catch
      cnExcel.Close(); 
    }

    private void btnExit_Click(object sender, System.EventArgs e)
    {
      this.Close();
    }

    private void btnOpen_Click(object sender, System.EventArgs e)
    {
      OpenFileDialog dlg = new OpenFileDialog();
      dlg.Title = "Open Spreadsheet";
      dlg.InitialDirectory = @"C:\";
      dlg.Filter = "Excel files (*.xls)|*.xls";
      dlg.FilterIndex = 1;
      dlg.RestoreDirectory = true;
      dlg.Multiselect = false;
      if (dlg.ShowDialog() == DialogResult.OK)
      {
        workBookName = dlg.FileName;
        LoadSheet();
        dataGrid1.DataSource = dsWorkBook.Tables["Table"];
      }
    }

    private void btnPasswords_Click(object sender, System.EventArgs e)
    {
      DateTime today=DateTime.Now;
      RandomPW rp = new RandomPW(chkLower.Checked, chkUpper.Checked, chkNum.Checked);
      
      foreach (DataRow drow in dsWorkBook.Tables["Table"].Rows)
      {
        drow[dsWorkBook.Tables["Table"].Columns.IndexOf("Password")] = rp.Generate(trkSize.Value);
        drow[dsWorkBook.Tables["Table"].Columns.IndexOf("ChangeDate")] = today.ToLongDateString();
      }
    }

    private void btnSave_Click(object sender, System.EventArgs e)
    {
//      OleDbCommand cmdUpdate = new OleDbCommand("UPDATE [Sheet1$] SET Password = @Password, ChangeDate = @ChangeDate WHERE UserID = @UserID");
////      cmdUpdate.Parameters.Add("@Password",OleDbType.Char, 0, "Password");
////      cmdUpdate.Parameters.Add("@ChangeDate",OleDbType.Char, 0, "ChangeDate");
////      cmdUpdate.Parameters.Add("@UserID",OleDbType.Char, 0, "UserID");
//
//      cmdUpdate.Parameters.Add("@Password",OleDbType.Char);
//      cmdUpdate.Parameters.Add("@ChangeDate",OleDbType.Char);
//      cmdUpdate.Parameters.Add("@UserID",OleDbType.Char);
//
////      cmdUpdate.Parameters.Add("@Password", "Password");
////      cmdUpdate.Parameters.Add("@ChangeDate", "ChangeDate");
////      cmdUpdate.Parameters.Add("@UserID", "UserID");

      OleDbCommand cmdUpdate = new OleDbCommand("UPDATE [Sheet1$] SET Password = ?, ChangeDate = ? WHERE UserID = ?", cnExcel);

      //The parameters are implicitly identified by the order they are added
//      cmdUpdate.Parameters.Add(new OleDbParameter("Password", OleDbType.Char, 20));
//      cmdUpdate.Parameters.Add(new OleDbParameter("ChangeDate", OleDbType.Char, 50));
//      cmdUpdate.Parameters.Add(new OleDbParameter("UserID", OleDbType.Char, 5));

      OleDbParameter pwParm = new OleDbParameter("@Password", OleDbType.Char, 20, "Password");
      pwParm.Direction = ParameterDirection.InputOutput;
      cmdUpdate.Parameters.Add(pwParm);

      OleDbParameter pwChg = new OleDbParameter("@ChangeDate", OleDbType.Char, 50, "ChangeDate");
      pwChg.Direction = ParameterDirection.InputOutput;
      cmdUpdate.Parameters.Add(pwChg);

      OleDbParameter pwID = new OleDbParameter("@UserID", OleDbType.Char, 5, "UserID");
      pwID.Direction = ParameterDirection.InputOutput;
      cmdUpdate.Parameters.Add(pwID);

      daWorkBook.UpdateCommand = cmdUpdate;
//      daWorkBook.UpdateCommand.Connection = cnExcel;

      cnExcel.Open();
      daWorkBook.Update (dsWorkBook);
//      daWorkBook.Update (dsWorkBook, "Table");
//      daWorkBook.Update (dsWorkBook.Tables["Table"]);
      cnExcel.Close();
    }

  }
}

Jeff
The future is already here - it's just not widely distributed yet...

 

[MasterRacker]

Forgot to mention that I can put a breakpoint on the update call and immediately after I try to F11 into it the exception throws.


Jeff
The future is already here - it's just not widely distributed yet...

 

[cappmgr]

See if you can find out the exception.

try 
{
      cnExcel.Open();
      daWorkBook.Update (dsWorkBook);
}	
catch (System.Data.OleDb.OleDbException OleDBEx) 
{
    //put the OleDBEx.Message to a file log or label
}
catch (System.Exception SysEx) 
{
    //put the SysEx.Messageto a file log or label
finally 
{
    cnExcel.Close();
}

Marty

 

[obislavu]

Try to mopdify the UPDATE command to refer to a named range of cells. A range of cells is viewed as a table.
Example:
Assume there is a named range of cells named FactorData in file C:\temp\myfile.xls. This range contains three columns: F1, F2 and F3.
To select all FactorData table:
select * from EXCEL...FactorData

To update:

UPDATE EXCEL...FactorData 
SET F1=.99, F2=.893 WHERE F3=30

-obislavu-

 

[MasterRacker]

The original error was [red]"Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done."[/red] After a LOT of Googling, it appears that Excel has a lot of issues with data type conversions when accessed this way (especially since Excel can contain multiple data types in one column). Setting IMEX=1 in the connection strings extended properties forces everything to read in as strings. I also am now examining each column to get the system determined data tye so I'm not inadvertantly trying to change something. Also, from what I found, OleDB does seem to need the ? form of parameters instead of the @named ones. I seem to have eliminated the data problems but am now getting a syntax error in the update statement. My update statement is essentially identical to one in an example I found on The Code Project with the exception that theirs named a range. I suppose I'll have to calculate the size of the whole sheet and try specifying that explicitly to update the whole sheet.


Jeff
The future is already here - it's just not widely distributed yet...

 

[MasterRacker]

Well, I've added a cell range to the sheet reference and appear to have resolved the data type problems by looking at the type read in originally and reassigning that type specifically to the parameters. The eror message is now "Syntax error in UPDATE statement.". The update command being generated is "UPDATE [Sheet1$A1:F4] SET Password=?, ChangeDate=? WHERE UserID=?", which I don't see anything wrong with. The save function in it's current incarnation is as follows:

private void btnSave_Click(object sender, System.EventArgs e)
{
	string cmdStr = "UPDATE [Sheet1$A1:";
	cmdStr += buildEndCellStr(dsWorkBook.Tables["Table"].Rows.Count, dsWorkBook.Tables["Table"].Columns.Count);
	cmdStr += "] SET Password=?, ChangeDate=? WHERE UserID=?";

	OleDbCommand cmdUpdate = new OleDbCommand(cmdStr, cnExcel);

	int idx;	
	DataColumn col = new DataColumn();
		
	idx = dsWorkBook.Tables["Table"].Columns.IndexOf("Password");
	col = dsWorkBook.Tables["Table"].Columns[idx];
			
	OleDbParameter pwParm = new OleDbParameter("@Password", col.ToString());
	pwParm.SourceColumn = col.ColumnName;
	pwParm.SourceVersion = DataRowVersion.Original;
	cmdUpdate.Parameters.Add(pwParm);

//	MessageBox.Show(idx.ToString() + " " + col.ColumnName.ToString());

	idx = dsWorkBook.Tables["Table"].Columns.IndexOf("ChangeDate");
	col = dsWorkBook.Tables["Table"].Columns[idx];
	OleDbParameter chParm = new OleDbParameter("@ChangeDate", col.ToString());
	chParm.SourceColumn = col.ColumnName;
	chParm.SourceVersion = DataRowVersion.Original;
	cmdUpdate.Parameters.Add(chParm);

	idx = dsWorkBook.Tables["Table"].Columns.IndexOf("UserID");
	col = dsWorkBook.Tables["Table"].Columns[idx];
	OleDbParameter idParm = new OleDbParameter("@UserID", col.ToString());
	idParm.SourceColumn = col.ColumnName;
	idParm.SourceVersion = DataRowVersion.Original;
	cmdUpdate.Parameters.Add(idParm);

	daWorkBook.UpdateCommand = cmdUpdate;

	txtSysMsg.Visible = true;
	txtSysMsg.Text = daWorkBook.UpdateCommand.CommandText.ToString();

	try 
	{
		cnExcel.Open();
		daWorkBook.Update (dsWorkBook);
	}    
	catch (System.Data.OleDb.OleDbException OleDBEx) 
	{
		txtOleMsg.Visible = true;
		txtOleMsg.Text = OleDBEx.Message;
	}
	catch (System.Exception SysEx) 
	{
		txtSysMsg.Visible = true;
		txtSysMsg.Text = SysEx.Message;
	}
	finally 
	{
		cnExcel.Close();
	}
}

Any other suggestions? I'm becoming amazed at how difficult this is turning out to be.


Jeff
The future is already here - it's just not widely distributed yet...

 

[MasterRacker]

I've stepped back and simplified my code using code lifted directly from the MS KB. I did find a reference in an article here that suggests I need to "provide primary key information through the UpdateCommand member". Unfortunately I can't seem to find an example of how to do this. Anyone?

I defined a primary key for the DataTable but that doesn't seem to help. I still get a syntax error on what should be perfectly a fine and dandy Update command. I'd also appreciate any suggestions on how to get any more detailed information out of the error.

The Current Save function:

private void btnSave_Click(object sender, System.EventArgs e)
{
	daWorkBook.UpdateCommand = new OleDbCommand("UPDATE [Sheet1$] SET Password=?, ChangeDate=? WHERE UserID=?", cnExcel);
	daWorkBook.UpdateCommand.Parameters.Add("@Password", OleDbType.VarChar, 255, "Password");
	daWorkBook.UpdateCommand.Parameters.Add("@ChangeDate", OleDbType.VarChar, 255, "ChangeDate");
	daWorkBook.UpdateCommand.Parameters.Add("@UserID", OleDbType.VarChar, 255, "UserID");

	dsWorkBook.Tables["Table"].PrimaryKey = new DataColumn[] {dsWorkBook.Tables["Table"].Columns["UserID"]} ;

	statMsg1.Text = daWorkBook.UpdateCommand.CommandText.ToString();

	try 
	{
		cnExcel.Open();
		daWorkBook.Update (dsWorkBook);
	}    
	catch (System.Data.OleDb.OleDbException OleDBEx) 
	{
		txtErrMsg.Visible = true;
		txtErrMsg.Text = "OLE: ";
		txtErrMsg.Text += OleDBEx.Message;
	}
	catch (System.Exception SysEx) 
	{
		txtErrMsg.Visible = true;
		txtErrMsg.Text = "SYSTEM: ";
		txtErrMsg.Text += SysEx.Message;
	}
	finally 
	{
		cnExcel.Close();
	}
}

Jeff
The future is already here - it's just not widely distributed yet...

 

[Terpsfan]

If you want to only update records, your best shot is to use an update command. You don't need to use a DataSet for this.

Look at the following code and see if this helps you. I created two columns in this spreadsheet and named the first row ChangeDay and the second Password. It updated all the records in each column.

private void button2_Click(object sender, System.EventArgs e)
		{
			string strSQL = "UPDATE [Sheet1$] SET ChangeDay = @ChangeDay, [Password] = @Pwd";
			OleDbConnection MyConnection = new OleDbConnection(
				@"provider=Microsoft.Jet.OLEDB.4.0; data source=C:\Passwords.XLS;Extended Properties=Excel 8.0;");
			
			OleDbCommand objCmd = new OleDbCommand(strSQL,MyConnection);
			OleDbParameter pChangeDay = new OleDbParameter();
			pChangeDay.Direction = ParameterDirection.Input;
			pChangeDay.DbType = DbType.String;
			pChangeDay.Value = "test";
			pChangeDay.ParameterName = "@ChangeDay";
			objCmd.Parameters.Add(pChangeDay);
			objCmd.CommandType = CommandType.Text;

			OleDbParameter pwd = new OleDbParameter();
			pwd.Direction = ParameterDirection.Input;
			pwd.DbType = DbType.String;
			pwd.Value = "another test";
			pwd.ParameterName = "@Pwd";
			objCmd.Parameters.Add(pwd);
			
			try
			{
				MyConnection.Open();
				objCmd.ExecuteNonQuery();
				MyConnection.Close();

				MessageBox.Show("Update successful");
			}
			catch(OleDbException ex)
			{
				MessageBox.Show(ex.Message.ToString());
			}
			finally
			{
				//Cleanup code
				pwd = null;
				pChangeDay = null;
				objCmd = null;
				MyConnection = null;
			}
		}

 

[MasterRacker]

Doesn't that change ALL passwords to the same value? I need each password to be a unique value. The only way I can think of to do that is to do a table based update of some sort.


Jeff
The future is already here - it's just not widely distributed yet...

 

[Terpsfan]

My mistake. I misread what you meant. In the first post I thought you said you wanted to change all the passwords, and I thought you meant all to the same value.

In that case you need to use a Data Set.