AutoCAD P&ID General Discussion

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

Issue casting DOUBLE DataType in PnPRow Column Object

278 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.

You are not logged in.

Log into access your profile, ask and answer questions, share ideas and more. Haven't signed up yet? Register

Announcements
Are you familiar with the Autodesk Expert Elites? The Expert Elite program is made up of customers that help other customers by sharing knowledge and exemplifying an engaging style of collaboration. To learn more, please visit our Expert Elite website.

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.

Ask the Community