Input string error on Table.AddRow

[ohkovar]

I receive this error when adding a row to a Table control:

"Input string was not in a correct format."

This occurs when I add a row that is a string, but not when I add a number. However, my table column is set as a string data type. I created the table control on a form, then dynamically add columns to it at runtime. Then, I populate the table with rows dynamically.

Here is my code to create the table:
tableMedsSummary.AddCol( cString, "id", 50 )
tableMedsSummary.AddCol( cString, "ptid", 50 )
tableMedsSummary.AddCol( cString, "NDC", 50 )
tableMedsSummary.AddCol( cString, "Drug Name", 50 )
tableMedsSummary.AddCol( cString, "Change", 50 )
tableMedsSummary.AddCol( cString, "Dose", 50 )
tableMedsSummary.AddCol( cString, "Route", 50 )
tableMedsSummary.AddCol( cString, "Frequency", 50 )
tableMedsSummary.AddCol( cString, "Start Date", 50 )
tableMedsSummary.AddCol( cString, "D/C Date", 50 )
tableMedsSummary.AddCol( cString, "dxHOLD", 50 )
tableMedsSummary.AddCol( cString, "Comment", 50 )


Here is the code to add a row to the table:
tableMedsSummary.AddRow( id, ptid, ndc, drugname, change, dose, route, freq, startdate, dcdate, dx, comment )

The problem is with the dxHOLD column. If the "dx" variable is a number or null, I receive no error. But, if it is a string, I receive the error. You can see that I create the dxHOLD column as a string data type.

What gives? Any help would be appreciated.

 

[Erel]

Can you upload your code?

 

[ohkovar]

I would, but it contains over 20K lines of code, most of which have nothing to do with this problem.

Could you tell me if there is something in the B4P structure that causes a table control to automatically change its column data type?

 

[ohkovar]

Okay, I think I finally have this one resolved. Here is what happened, so maybe it will save someone else some trouble:

I ran a sqlite query with the concatentate option, such as:

"Select field1, ('THIS IS FIELD 2' || field2) 'CONCATENATEDFIELD' FROM TABLE"

Then, I ran an ExecuteTable command using that query.

If the query does not return any rows (i.e. - RowCount = 0), then somehow the table expects the concatenated column to be a number, even if it was created as a string (AddColumn("CONCATENATEDFIELD', cString). I assume that ExecuteTable overrides any previous AddColumn commands and uses the data type from the query. However, the sqlite documentation states that concatenated fields are returned as strings, so I don't understand where the number data type comes from. The data type of the field I called is "text" and I concatenated it with the letters "dx", so it should have returned a data type of string regardless.

Bottom line, you can solve the problem by reading through each row returned by the query and setting the columns manually, rather than running ExecuteTable. That way, if no records are returned by the query, no data types are assigned to the columns and any previous data types assigned to the column are not overridden by the ExecuteTable call.

Hope that makes sense. If anyone has an explanation for why that happened, I would love to hear it.

 

[Erel]

ExecuteTable does remove the existing columns and create new ones.

Another possible solution is to first make a query (with ExecuteReader) using Select Count and check that there are more than 0 rows.