Repeating Section SQL updates

[CharlesParker]

WOW, can't even wrap my mind around this but it seems possible...with xbasic and I am again trying to learn.

SO heres my scenario and it would be like an inventory issue.

I have a repeating section bound to a table.
One of the fields is names Is_deployed
and one is named Equipment_ID and it is not the PK
this table has its own.

There is another table that lists this equipment and its PK is equipment_id


for every row where Is_deployed = "Yes" I want to update the equipment table
where the equipment_id's are equal

This effectively takes the equipment out of inventory as select-able so when an item is chosen I want this "Is_Deployed" field to update immediately on submit.
Here's the function I created:


function change_deployed as c (e as p)
dim cn as sql::Connection
cn.open("::Name::Connection1")
dim args as sql::arguments
dim logid as c
dim status as c
logid = e._currentRowDataNew.equipment_id
status = e._currentRowDataNew.Is_Deployed
sqlCommand = "UPDATE alphanwcopro.company_equipment SET Is_Deployed = 'status' WHERE Equipment_id = 'logid'";
end function


What say you guys? I don't see that it works...advice?

[CharlesParker]

Re: Repeating Section SQL updates

So with some help I got this somewhat working!

function change_deployed as c (e as p)
dim cn as sql::Connection
dim args as sql::arguments
sqlUpdate = "UPDATE alphanwcopro.company_equipment SET Is_Deployed = :deployed WHERE Equipment_id = :whattrap"
vRowCount = e.repeatingSectionInfo[1].rowCount
cn.open("::Name::Connection1")
for i = 1 to vRowCount
	if alltrim(e.dataSubmitted.Equipment_id[i]) <> ""
		args.set("whattrap", val(e.dataSubmitted.Equipment_id[i]))
		args.set("deployed",val(e.datasubmitted.Is_Deployed[i]))
		cn.execute(sqlUpdate, args)
	end if
next i

cn.close()
end function

The equipment table is NOW updating the is_deployed field as per the rows equipment_id BUT the argument :deployed is returning a zero instead of the value of the Is_Deployed field...anyone want to take a stab as to why?

[Steve Workings]

Re: Repeating Section SQL updates

What is e.dataSubmitted.Is_Deployed returning? If it's returning a "Yes" then converting that to its val() is likely the problem. So, key questions:

1. What does Is_Deployed returning on your component and
2. What kind of field is Is_Deployed in your table?

[CharlesParker]

Re: Repeating Section SQL updates

Without answering the questions I want to say that I did get this working with couple edits But there is a problem with the results. I will post the code and the results in a bit when I get back. I am getting a yes an no response now updating the other table...:-D

[CharlesParker]

Re: Repeating Section SQL updates

Ok so I am using this code and I am getting some odd results, when I run the code the equipment table updates teh wrong yes and no responses per the rows in the repeating section or something. Anyone care to trouble shoot why that is? lol
I gave up on trying to set the e.datasubmitted.Is_Deployed field as an arg because all I could get returned was a zero...crap this is frustrating

function change_deployed as c (e as p)
dim cn as sql::Connection
dim args as sql::arguments
sqlUpdate = "UPDATE alphanwcopro.company_equipment SET Is_Deployed = 'Yes' WHERE Equipment_id = :whattrap"
sqlUpdate2 = "UPDATE alphanwcopro.company_equipment SET Is_Deployed = 'No' WHERE Equipment_id = :whattrap"
vRowCount = e.repeatingSectionInfo[1].rowCount
cn.open("::Name::Connection1")
for i = 1 to vRowCount
	if alltrim(e.dataSubmitted.Is_Deployed[i]) = "Yes"
		args.set("whattrap", val(e.dataSubmitted.Equipment_id[i]))

		cn.execute(sqlUpdate, args) 
		else
		cn.execute(sqlUpdate2,args)
	end if
next i

cn.close()
end function

[CharlesParker]

Re: Repeating Section SQL updates

I am also reverting back to the previous code to look at the e.object and see whats in there and myabe why I am getting a zero it is supposed to be a char field!

[CharlesParker]

Re: Repeating Section SQL updates

What is e.dataSubmitted.Is_Deployed returning? If it's returning a "Yes" then converting that to its val() is likely the problem. So, key questions:

1. What does Is_Deployed returning on your component and
2. What kind of field is Is_Deployed in your table?

Ok e.dataSubmitted.Is_Deployed is returning as Yes in the debugger

and I think I see what your saying about val...that means N right?
my field Is_Deployed is a C field
and I saw in the debugger that the arg is an N and I am assuming that the val() means its converting it to a number?

if so that is the issue...
I think I got it now!
I just test it in the debugger and it worked! I will do a bit more testing and then once I am sure it is working I will post the code that worked. I removed the val() part btw as that was teh issue and why I kept getting a zero!

[Steve Workings]

Re: Repeating Section SQL updates

The val() function basically converts a character-type value to a numeric-type value. As all values on an HTML page are actually character-type, you might use val() to get the proper type before setting as an argument.

So, for instance, you have an HTML control where the user has typed in, say "20". But if you try to use the character-string "20" as the insert or update value to your numeric-type SQL field, you'll have a problem. This is a case then, where you would use the val() function, e.g.

args.set("Amount", val(e.dataSubmitted.Amount))

[CharlesParker]

Re: Repeating Section SQL updates

Success! Finally!

So the issue was in fact the Val() part that was converting the result (if I am saying that right) into a numeric value.

function change_deployed as c (e as p)
	debug(1)
dim cn as sql::Connection
dim args as sql::arguments
sqlUpdate = "UPDATE alphanwcopro.company_equipment SET Is_Deployed = :whatis WHERE Equipment_id = :whattrap"
vRowCount = e.repeatingSectionInfo[1].rowCount
cn.open("::Name::Connection1")
for i = 1 to vRowCount
	if alltrim(e.dataSubmitted.Equipment_id[i]) <>""
		args.set("whattrap",e.dataSubmitted.Equipment_id[i])
		args.set("whatis",e.dataSubmitted.Is_Deployed[i])
		cn.execute(sqlUpdate, args) 
	end if
next i

cn.close()
end function

So that's the working and correct code for me!

This sets the corresponding table information based on the arguments. I have been wanting to do this for awhile but I just wasn't sure of the proper way to go about it. Thanks Steve for the pointers and the example from which to work - I felt like I learned something today about xbasic and the debugger and put together alot of the pieces I have been learning over the past year to actually see what was going on and be able to correct the code to suit what I needed.

[CharlesParker]

Re: Repeating Section SQL updates

Thanks again Steve! I see your point and it is well taken. Luckily the Equipment_id is pretty much a read only field so even that is unnecessary in this case to convert. How can one tell the difference between a char "20" and a numeric "20" ?
interesting food for thought, I did not know there was a difference...looks like 20 either way.

Like how can you tell if fish is bad - it smells like fish either way, lol

[Steve Workings]

Re: Repeating Section SQL updates

You just need to know this:

1. All data returned from the component is character-type.
2. If the field in the table that holds the data is not character type, you need to convert it when rolling your own Xbasic (if you're using data binding, Alpha takes care of this for you).

And, there's a bit of a universal function for this besides. See Convert_Type() in the help.