Cannot convert to a numeric

Classic Servoy
1

Dear all

I have been fighting a problem with my solution for 2 days. Someone please tell me where I’m screwing it up… It worked fine when I first made it, but it began failing with an error every now and again, and now it simply does not run at all…

I have a valuelist that is populated by this method:

function set_valuelists()
{
	databaseManager.saveData()
	var sql_query = "SELECT DISTINCT invoice_number_text, sales_id FROM line_items WHERE sales_id = " + forms.sale_master.salesid + "ORDER BY invoice_number_text asc";
	var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), sql_query, null, 300);

	var maingroupArray = new Array();

	var index = 0;
	maingroupArray[0] = 'new invoice';
	maingroupArray[1] = 'new credit';
var	index = 2 
	for (var i = 0; i <= dataset.getMaxRowIndex(); i++)
	{
		dataset.rowIndex = i;
		maingroupArray[index] = dataset.invoice_number_text;
		index = index + 1;
	}
	 application.setValueListItems('invoice_credit_chooser', maingroupArray);
}

This works fine, and gives me a dropdown with ‘new invoice’, new credit, and a list of all previous invoice numbers so that the current line can be applied to a previous invoice number. So far so good…

I then have a method to figure out what to do depending on the choice above. It goes like this:

function choose_invoice_credit()
{
	if (invoice_number_text == 'new invoice')
	{
		var vnew_invoice_number = 6
			//plugins.dialogs.showInputDialog('Invoice number','Please provide an invoice number');
		if (vnew_invoice_number)
			{
			var sql_query = "SELECT invoice_number_text FROM line_items WHERE invoice_number_text = " + vnew_invoice_number;
			var dataset = databaseManager.getDataSetByQuery(controller.getServerName(), sql_query , null,-1)
			}


	}
}

There will be more than that, but it fails at the var dataset point. The console error I get is: SQL Anywhere Error -157: Cannot convert new invoice to a numeric(30,6)
at /Applications/Servoy/servoy_workspace/interiors/forms/line_items_complex_master/line_items_complex_master_methods.js:1279 (choose_invoice_credit)

I don’t have anything called ‘new invoice’ apart from the text in the dropdown. The sql query is not setting anythingThe field behind the combobox is a text field, and has been populated by text and numbers.

I’m stumped. Someone please tell me what I’m doing wrong…

Thanks

Bevil

2

I have apparently solved the problem by giving the valuelist some random text in order to be set as a text valuelist to begin with…

This seems like a workaround and there is no documentation that I can find to say that a valuelist must be set as text / integer / number or it will fail. This is most annoying because it was not the method to set the valuelist which was suspending, it was the sql query - which was ONLY dealing with text. Also, the valuelist worked fine. It showed all the text I needed it to.

Am I missing something obvious here? If so, I feel like a mug as it has cost me 2 days of very precious development time.

Thanks

Bevil

3

Aha, Brilliant… I have managed to get my problem back.

Of course I have no idea what I changed (nothing to do with any of these methods…)

Servoy has a mind of its-own. It worked perfectly after I gave the valuelist some random text. I managed to put 10 or 15 invoice numbers in and was testing it complaining about numbers already existing and suddenly I have my familiar Cannot convert new invoice to a numeric(30,6) again…

Be very grateful for any pointers from anyone.

Bevil

4

Okay, some progress…

So, if the method fails and ends up with ‘new invoice’ in the field called ‘invoice_number_text’ - which is a text field, on any record, the method fails. So if you accidentally put text into the text field which is then checked against a number with the sql statement, there is an error. To solve this I think I am going to have to check that any input in this field is an integer using Marcel’s tools plugin, that should prevent it allowing text into the field. Very annoying though.

5

As far as I can see, your query: ```
"SELECT invoice_number_text FROM line_items WHERE invoice_number_text = " + vnew_invoice_number;

is going to compare a **invoice_number_text**, which is text I presume, with 6 because you set **vnew_invoice_number** to 6 (integer). That's most probably where it breaks.
I guess you would want to convert quote this **vnew_invoice_number** in your query like:

“SELECT invoice_number_text FROM line_items WHERE invoice_number_text = '” + vnew_invoice_number + “'”;

6

Hi Jeroen

Yeah, I set it to compare to 6 as a test. Ordinarily it would compare to a number which had been typed into my inputDialog. I left it comparing to 6 in order to not have to input a number every time while testing…

The problem is that it will fail on the SQL query while comparing against the database if there is text in the field in any record. If the user managed to type in 123a for example, it would cause all future SQL queries to fail. The strange thing though is that invoice_number_text in Sybase is a varchar (see attached). Therefore I am surprised that there is an error when it compares a number to this column. Perhaps I need to convert the number to text before doing the sql search?

I have got around the problem by checking that the inputDialog value is an integer (with Marcel’s excellent tools plugin), but it did cause me a lot of grief (particularly as I didn’t originally see the pattern of it failing only when there was text in the field. I was working on a record which has many line items as my test, but there was a different record which had only 3 line items, and one of them had text in the invoice_number_text field, without me ever visiting this parent record, it was causing the sql query to fail across all records (because my check is to make sure that the manually typed invoice number was not already used anywhere else in the database))

So, why it doesn’t work comparing text to text still confuses me, and I think that this is really a workaround rather than a solution.

Thank you for the message.

Bevil

Screen shot 2009-10-05 at 08.46.17.png
Screen shot 2009-10-05 at 08.46.17.png1284×807 49.1 KB

7

dont construct sql statements like that. Never just add the values directly in the sql string
use ? and the parameter array, then you also wont have that kind of a problem that you have now.