AutoCAD P&ID General Discussion

AutoCAD P&ID General Discussion

Reply
Contributor
jbrumbau
Posts: 16
Registered: ‎07-05-2012
Message 1 of 1 (289 Views)

Issue casting DOUBLE DataType in PnPRow Column Object

289 Views, 0 Replies
07-20-2012 04:08 PM

I'm have problems casting a PnPRow column object to type double. I get a "Specified cast is not valid." error message thrown. However, I have no problems casting a PnPRow to type long (for PnPID) or string (for String data types in P&ID Class Definitions). Could the casting error be due to the value being empty? I do a check for (row[c] == null) but it is never null. Here are examples that work:

PnPRow row;
//After obtaining PnPRow...
int pnpid = (int)row[0]; //PnPID column, works fine
string tempstr = (string)row[c];
double num = (double)row[c]; //Error!

 As background, here is the full code for anybody on here to test and hopefully answer my question:

PlantProject proj = PlantApplication.CurrentProject;
ProjectPartCollection projParts = proj.ProjectParts;
PnIdProject pnidProj = (PnIdProject)projParts["PnId"];
DataLinksManager dlm = pnidProj.DataLinksManager;
PnPDatabase db = dlm.GetPnPDatabase();
PnPTables pnpTbls = db.Tables;
PnPTable aTbl = pnpTbls["Instrumentation"];

PnPColumns cols = aTbl.AllColumns;
List<DataType> dataTypes = new List<DataType>(50);
List<string> colNames = new List<string>(50);
List<int> colNums = new List<int>(50);
string cmdVals, cmdFlds, tempstr, typestr;
string cmdText = "CREATE TABLE [INSTRUMENT LIST] (";

int j = 0;
foreach (PnPColumn col in cols)
{
	tempstr = col.Name.Replace(' ', '_') + '_'; //Replace spaces with '_' and append '_' to make field names unique
	typestr = col.ColumnType.Name;
	switch (typestr[0])
	{
		case 'I':
			cmdText += tempstr + " LONG,";
			dataTypes.Add(DataType.Long);
			break;
		case 'D':
			if (typestr[1] == 'a')
			{
				cmdText += tempstr + " DATETIME,";
				dataTypes.Add(DataType.Date);
			}
			else
			{
				cmdText += tempstr + " DOUBLE,";
				dataTypes.Add(DataType.Double);
			}
			break;
		case 'S':
			cmdText += tempstr + " CHAR(255),";
			dataTypes.Add(DataType.String);
			break;
		default:
			txtFeedback.Text += "Unknown data type '" + typestr + "' found with column " + col.Name + "; defaulting to string.";
			cmdText += tempstr + " CHAR(255),";
			dataTypes.Add(DataType.String);
		break;
	}
	cmdFlds += tempstr + ',';
	colNames.Add(tempstr);
	colNums.Add(j);
	cmdVals += '@' + tempstr + ',';
	j++;
}
cmdVals = cmdVals.Substring(0, cmdVals.Length - 1);
cmdFlds = cmdFlds.Substring(0, cmdFlds.Length - 1);

string xlFile = "C:\\temp\\output.xlsx";
if (File.Exists(xlFile))
	File.Delete(xlFile);
if (File.Exists("C:\\temp\\blank.xlsx"))
	File.Copy("C:\\temp\\blank.xlsx", xlFile);
using (OleDbConnection xlCon = new OleDbConnection("Provider = Microsoft.ACE.OLEDB.12.0; " +
				"Data Source = " + xlFile + ";Mode=ReadWrite;" +
				"Extended Properties='Excel 12.0;HDR=YES;'"))
{
	xlCon.Open();
	OleDbCommand xlCmd = new OleDbCommand(cmdText.Substring(0, cmdText.Length - 1) + ')', xlCon);
	xlCmd.ExecuteNonQuery(); //Create [INSTRUMENT LIST] table
	xlCmd = null;

	PnPRow[] rows = aTbl.Select();
	foreach (PnPRow row in rows)
	{
		xlCmd = new OleDbCommand("INSERT INTO [INSTRUMENT LIST](" + cmdFlds + ") values(" + cmdVals + ')', xlCon);
		j = 0;
		foreach(int c in colNums)
		{
			if (row[c] == null)
				xlCmd.Parameters.AddWithValue("@" + colNames[j], DBNull.Value);
			else
			{
				switch (dataTypes[j])
				{
					case DataType.Date:
						DataType tempDT = (DateTime)row[c];
						xlCmd.Parameters.AddWithValue("@" + colNames[j], tempDT);
						break;
					case DataType.Double:
						double tempDbl = (double)row[c];	// ***THIS CRASHES***
						xlCmd.Parameters.AddWithValue("@" + colNames[j], tempDbl);
						break;
					case DataType.Long:
						tempLong = (long)row[c];
						xlCmd.Parameters.AddWithValue("@" + colNames[j], tempLong);
						break;
					//case DataType.General:
					//case DataType.String:
					default:
						tempstr = row[c].ToString();
						if (tempstr.Length > 0)
							xlCmd.Parameters.AddWithValue("@" + colNames[j], tempstr);
						else
							xlCmd.Parameters.AddWithValue("@" + colNames[j], DBNull.Value);
						break;
				}
			}
			j++;
		}
		xlCmd.ExecuteNonQuery();	//Insert a row of data into the [INSTRUMENT LIST] table
	}
	xlCon.Close();
}

Is it failing because the double value is empty? If so, how would I check for an empty value before assigning to [double num]?

 

Thanks in advance.

Post to the Community

Have questions about Autodesk products? Ask the community.

New Post
Need installation help?

Start with some of our most frequented solutions to get help installing your software.

New AutoCAD P&ID Category!

The AutoCAD P&ID forum has moved into it's very own category page, and can no longer be found within the Additional Product Forums.

Announcements
Do you have 60 seconds to spare? The Autodesk Community Team is revamping our site ranking system and we want your feedback! Please click here to launch the 5 question survey. As always your input is greatly appreciated.