database writing

[vochito]

Can somebody help me?

I am sending the following SQL to my database. The VI creates the sql update command string and feed it to a EXE SQL block. I'm using LV 8.2

update wk_valid_states set P_F_Criteria =  'Failed' ,Resulting_State = ' RESULTADO '  where  Trim( state_machine ) =' All ' AND Trim( current_state ) =' Brown_Out_Reset ' AND Trim( event ) =' UP_LIMIT_STATE ' AND Trim( flag1 ) ='  ' AND Trim( flag2 ) ='  ' AND Trim( flag3 ) ='  ' AND Trim( next_state ) =' Up_limit '

Nothing seems to be written into the table  nor any errors reported.

[smercurio_fc]

What is your database server? I know that in SQL Server (2000 at least), Trim is not a valid function name.

Edit: Also not in SQL Server 2005.


Message Edited by smercurio_fc on 04-23-2008 04:34 PM

[vochito]

Thanks for anwering

I am using MS Access and I tried without Trim before with the same results

[mikeporter]

The reason nothing is changing is that there are no records meeting the selection criteria you have specified.

One thing I have noticed is that there seems to be a lot of spaces in the values you are comparing. For example the All, Brown_Out_Reset, UP_LIMIT_STATE, and Up_Limit values all have leading and trailing spaces. Likewise, the flag1, flag2 and flag3 comparison values all consist of two spaces. Moreover, the trim function removes leading and trailing spaces, so the comparisons (by definition) are guaranteed to fail.

Mike...

[Deepu]

I suggest you to try the following command first and see if there is any record retrieved:

select * from wk_valid_states where  Trim( state_machine ) =' All ' AND Trim( current_state ) =' Brown_Out_Reset ' AND Trim( event ) =' UP_LIMIT_STATE ' AND Trim( flag1 ) ='  ' AND Trim( flag2 ) ='  ' AND Trim( flag3 ) ='  ' AND Trim( next_state ) =' Up_limit ' ;

This select statement is constructed with the same WHERE conditions as in your update statement. So you will know if there is any record that would satisfy this condition. If no record was returned, that tells you clearly why no record was updated. In that case you need to check your condition clearly one by one for any mistakes. Please also note that the string values within '' are case sensitive.

As Mike mentioned, you are checking a column or field after Trim() function, but with a value that is not trimmed:

eg> Trim(state_machine) = ' All '. Here even if the column value is 'All', it will not be equal to ' All '.

Hope these tips helps.

[vochito]

Thanks for your help.

 You were right, there were spaces, however I am trimming on both sides of the expresions, I found that by using

Format  into string adds a blank to every element previously trimmed as seen on this SQL string. 

update wk_valid_states set P_F_Criteria =  'Failed' ,Resulting_State = ' RESULTADO '  where  Trim( state_machine ) =' All ' AND Trim( current_state ) =' Traveling_Up ' AND Trim( event ) =' Radio_Command ' AND Trim( flag1 ) ='  ' AND Trim( flag2 ) ='  ' AND Trim( flag3 ) ='  ' AND Trim( next_state ) =' Stop_From_Up '

Concatenate strings does not add blanks to the previously trimmed elements as seen below.

update wk_valid_states set P_F_Criteria =  'Failed' ,Resulting_State = ' RESULTADO '  where ( Trim(state_machine)='All' AND Trim(current_state)='Traveling_Up' AND Trim(event)='WC_Command' AND Trim(flag1)='' AND Trim(flag2)='' AND Trim(flag3)='' AND Trim(next_state)='Stop_From_Up' )

See attached files

[vochito]

My code is workinf fine now, I posted both versions for reference. Thanks again

[mikeporter]

The reason that format into strings was adding spaces is that you weren't specifying a format string. If you used a format string that included the string constants that need to be included in the output, the code would have be much more readable.

Mike...

[vochito]

You're right It looks much cleaner and it also works. thanks!