7 Replies - 783 Views - Last Post: 14 September 2015 - 07:04 AM Rate Topic: -----

#1 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

OleDb Insert Issue

Posted 11 September 2015 - 09:31 AM

So first let me start off with the error I'm getting
Dynamic SQL generation for the UpdateCommand is not supported against a SelectCommand that does not return any key column information.
The error occurs on this line adapter.UpdateCommand = cb.GetUpdateCommand();

Now What I'm trying to do is insert rows into an excel sheet via OleDbDataAdapter. I can't figure out how to set the PrimaryKey on the Select command as the error suggests. Below is my code, I've tried setting the PrimaryKey Property on the DataTable variable I created as well as the DataSet.Tables["Table1"] that I've filled with the adapter. Neither of those seem to work and I'm at a loss. I've also tried specifying "Order Number" in the select statement as well. This isn't connecting directly to a database currently, it's just data in a file loaded into a dataset. Below is my code, I'm only posting the code relevant to the problem. The TestOrder class is just a class with a method that generates test orders.

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ExcelUpdater
{
	class ExcelGenerator
	{
		private string FilePath;
		private string SheetName;

		public ExcelGenerator(string filePath, string sheetName)
		{
			FilePath = filePath;
			SheetName = sheetName;
		}

		public bool Update()
		{
			OleDbConnection con = new OleDbConnection();
			con.ConnectionString = string.Format("Provider=Microsoft.ACE.OLEDB.12.0;data source={0};Extended Properties=\"Excel 12.0;HDR=YES\";", FilePath);
			OleDbDataAdapter adapter = new OleDbDataAdapter(String.Format("Select * from {0}", SheetName), con);
			List<TestOrder> testOrders = TestOrder.GenerateTestOrders();
			DataSet ds = new DataSet("Table1");
			adapter.Fill(ds, "Table1");
			DataTable dt = ds.Tables["Table1"];
			dt.PrimaryKey = new[] {dt.Columns["Order Number"]};
			testOrders.ForEach(x =>
			{
				DataRow row = dt.NewRow();
				row["Order Date"] = x.OrderDate;
				row["Order Number"] = x.OrderNumber;
				row["PO"] = x.PO;
				row["Requisitioner"] = x.RequisitionerID;
				row["SKU"] = x.SKU;
				row["Qty Ordered"] = x.QtyOrdered;
				row["Cost"] = x.Cost;
				row["Ship Attn"] = x.Attention;
				row["Ship Addr1"] = x.ShipAddress1;
				dt.Rows.Add(row);
			});
			string s = adapter.SelectCommand.CommandText;
			OleDbCommandBuilder cb = new OleDbCommandBuilder(adapter);
			adapter.UpdateCommand = cb.GetUpdateCommand();
			return Convert.ToBoolean(adapter.Update(ds));
		}
	}
}



-Edit had to remove some client data...>_>

This post has been edited by ybadragon: 11 September 2015 - 09:39 AM


Is This A Good Question/Topic? 0
  • +

Replies To: OleDb Insert Issue

#2 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6560
  • View blog
  • Posts: 26,597
  • Joined: 12-December 12

Re: OleDb Insert Issue

Posted 11 September 2015 - 09:52 AM

Did you try it without the CommandBuilder?
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6560
  • View blog
  • Posts: 26,597
  • Joined: 12-December 12

Re: OleDb Insert Issue

Posted 11 September 2015 - 10:04 AM

Quote

This isn't connecting directly to a database currently..

If your longer term goal is not with Excel then I wouldn't use Excel as a test data-source, particularly for inserts.
Was This Post Helpful? 0
  • +
  • -

#4 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: OleDb Insert Issue

Posted 11 September 2015 - 10:14 AM

I did try without the command builder, but it didn't work either. The long term goal is to generate the excel reports, but there is some calculating I have to do in the program for some of the columns that isn't possible in the sql query(s) I've made to generate the views I need. What I generate doesn't need to be pushed back into the database, so that is why I'm keeping it in excel instead of just updating the database and then reflecting the updates in the report.

I have made it a bit further though. I've created my own update command, but now am getting the following error
Update unable to find TableMapping['Table'] or DataTable 'Table'.

below is my new code for the parameter adding. The weird part about the error is it is saying 'Table' when I don't have a table called "Table" it is actually called the clients name in my dataset so I figured the error if anything would have returned 'Client'

string updateCommand = String.Format(
					"Update {0} set [Order Date] = ?, [Order Number] = ?, PO = ?, Requisitioner = ?, SKU = ?, [Qty Ordered] = ?, Cost = ?, [Ship Attn] = ?, [Ship Addr1] = ? Where [Order Number] = ?",
					SheetName);
			adapter.UpdateCommand = new OleDbCommand(updateCommand);
				
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Date]",
				SourceColumn = "Order Date",
				OleDbType = OleDbType.Date
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Number]",
				SourceColumn = "Order Number",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "PO",
				SourceColumn = "PO",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Requisitioner",
				SourceColumn = "Requisitioner",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "SKU",
				SourceColumn = "SKU",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Qty Ordered]",
				SourceColumn = "Qty Ordered",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Cost",
				SourceColumn = "Cost",
				OleDbType = OleDbType.Decimal
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Attn]",
				SourceColumn = "Ship Attn",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Addr1]",
				SourceColumn = "Ship Addr1",
				OleDbType = OleDbType.Date
			});
			string s = adapter.UpdateCommand.CommandText;



Oh I found that error, I didn't specify the table name in the update statement. now I hit a third error saying "Error in INSERT STATEMENT". I have a feeling it is because I didn't supply parameters dirrectly for the insert statement so I'm going to try that and see if it works.
Was This Post Helpful? 0
  • +
  • -

#5 andrewsw   User is offline

  • RequestedRangeNotSatisfiable
  • member icon

Reputation: 6560
  • View blog
  • Posts: 26,597
  • Joined: 12-December 12

Re: OleDb Insert Issue

Posted 11 September 2015 - 10:25 AM

You don't need Order Number as part of the Update. If you remove it then move this parameter to the end as OleDb takes the parameters in order, not by name.

I also prefer AddWithValue rather than trying to be explicit about the data-types.

(I'm also curious why you were trying to get the UpdateCommand when you were (earlier) attempting an insert..)

This post has been edited by andrewsw: 11 September 2015 - 10:23 AM

Was This Post Helpful? 0
  • +
  • -

#6 Curtis Rutland   User is offline

  • (╯□)╯︵ (~ .o.)~
  • member icon


Reputation: 5104
  • View blog
  • Posts: 9,283
  • Joined: 08-June 10

Re: OleDb Insert Issue

Posted 11 September 2015 - 10:28 AM

By the way, I've ditched using OleDB for Excel entirely, as well as Interop. I use a component available via Nuget called "EPPlus". Check it out, it's a lot easier to manipulate Excel files that way.

Also, if you're interested in F#, you should check out Excel Type Provider.
Was This Post Helpful? 1
  • +
  • -

#7 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: OleDb Insert Issue

Posted 11 September 2015 - 10:32 AM

I prefer AddWithValue as well, I didn't try it, but I also didn't realize that is doesn't require a datatype to be passed in. I got my code to work like I said above, by adding parameters to the insert statement as well.


string updateCommand = String.Format(
					"Update {0} set [Order Date] = ?, [Order Number] = ?, PO = ?, Requisitioner = ?, SKU = ?, [Qty Ordered] = ?, Cost = ?, [Ship Attn] = ?, [Ship Addr1] = ? Where [Order Number] = ?",
					SheetName);
			string insertCommand =
				String.Format(
					"Insert into {0} ([Order Date], [Order Number], PO, Requisitioner, SKU, [Qty Ordered], Cost, [Ship Attn], [Ship Addr1]) values (?, ?, ?, ?, ?, ?, ?, ?, ?)",
					SheetName);
			adapter.UpdateCommand = new OleDbCommand(updateCommand);
			adapter.InsertCommand = new OleDbCommand(insertCommand);
				
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Date]",
				SourceColumn = "Order Date",
				OleDbType = OleDbType.Date
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Number]",
				SourceColumn = "Order Number",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "PO",
				SourceColumn = "PO",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Requisitioner",
				SourceColumn = "Requisitioner",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "SKU",
				SourceColumn = "SKU",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Qty Ordered]",
				SourceColumn = "Qty Ordered",
				OleDbType = OleDbType.Integer
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Cost",
				SourceColumn = "Cost",
				OleDbType = OleDbType.Decimal
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Attn]",
				SourceColumn = "Ship Attn",
				OleDbType = OleDbType.VarChar
			});
			adapter.UpdateCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Addr1]",
				SourceColumn = "Ship Addr1",
				OleDbType = OleDbType.VarChar
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Date]",
				SourceColumn = "Order Date",
				OleDbType = OleDbType.Date
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Order Number]",
				SourceColumn = "Order Number",
				OleDbType = OleDbType.Integer
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "PO",
				SourceColumn = "PO",
				OleDbType = OleDbType.VarChar
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Requisitioner",
				SourceColumn = "Requisitioner",
				OleDbType = OleDbType.Integer
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "SKU",
				SourceColumn = "SKU",
				OleDbType = OleDbType.VarChar
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Qty Ordered]",
				SourceColumn = "Qty Ordered",
				OleDbType = OleDbType.Integer
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "Cost",
				SourceColumn = "Cost",
				OleDbType = OleDbType.Decimal
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Attn]",
				SourceColumn = "Ship Attn",
				OleDbType = OleDbType.VarChar
			});
			adapter.InsertCommand.Parameters.Add(new OleDbParameter()
			{
				ParameterName = "[Ship Addr1]",
				SourceColumn = "Ship Addr1",
				OleDbType = OleDbType.VarChar
			});
			string s = adapter.UpdateCommand.CommandText;
			return Convert.ToBoolean(adapter.Update(ds, "Table1"));



I will do what you said an use the addwith value. I am getting a weird thing on the actual excel file now though that says there is unreadable data, and that is has to recover the file and then I click "Yes" to allow that and it shows the rest of the data I entered with a weird formating. Note, the yellow is by client request.....

Attached Image

I will definitely look into that Curtis!

@Andrewsw, How else do I call an insert with oledbdataadapter? from eveything I've read they use the update command which calls the insert command for new rows.
Was This Post Helpful? 0
  • +
  • -

#8 ybadragon   User is offline

  • Home Owner
  • member icon

Reputation: 571
  • View blog
  • Posts: 2,647
  • Joined: 11-May 12

Re: OleDb Insert Issue

Posted 14 September 2015 - 07:04 AM

Curtis, I took a look at EPPlus and while their documentation is....lacking, I do really enjoy the library and there are plenty of questions people have asked to get what I needed done! So anyone else who is trying to do this, use EPPlus. Period. I am through with OleDb as well now.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1