SQL Mode Command to update database table

[Former Member]

Hi All,

I have a couple of scenarios which I need help with:

1)

- I have a sql query which pulls two columns lets say Equipment and a val1 from a sql database table displayed in a grid.

- Once the user clicks on a record I have two text boxes which get the value of the selected records on the same page (equipment, val1).

- I then have an update button, wherein they can change the value of the val1 column.

I have created a sql query of mode command with an update statement. My question is, how do I execute this update query from the webpage, I know how to assign the parameters so the get passed to the update query I just am not sure how to kick off the update query. Do I have to have it as part of an applet to even refer to it from within my webpage? If so, don't I have to assign an applet a display template? (this command query does not have an output). Please help...

Thanks,

Mahwish

[Former Member · Accepted solution]

Scenario2:

Instead of having the field values come up in a text box and then updating it. Is there a way I can update the values from within the grid itself? ie have the data show up in a tabular form (similar to a grid) but have one of the columns be updatable?

Thanks,

Mahwish

[Former Member · Accepted solution]

I have the first scenario working using the iCommand display, any insight into scenario 2 will be appreciated.

Thanks!

[Former Member · Accepted solution]

Just for future reference to other users, you can "kick off" the iCommand applet by using the executeCommand() method (document.myApplet.executeCommand()).

I will usually put this in an if statement to check for errors like:

if(document.myApplet.executeCommand()) {
  // update iGrid or display success message.
} else {
  // show some error (document.myApplet.getLastError())
}

For scenario 2, there is not functionality in the iGrid to do this. What you might be able to do is use Javascript to popup an alert to the user allowing them to enter a new value and then save it back to the DB.

[Former Member]

I have done scenario 2, but its not easy. This is how I did it.

Instead of using the iGrid applet, I used an XSLT stylesheet to create a "grid" of input boxes in HTML that allow direct editing of the value. At the end of each row you had an "update" button that passed the value of the updated box, and the corresponding information you need for icommand update. You can either display the information you need for the update query in the "grid" or you can use hidden form objects to store the information. Indexing is pretty easy, you just name the form elements by positions (eg. frmTxtR1C1) for example.

The solution is not for the faint of heart, takes some development time and a fair amount of XSLT knowledge, but if you need this functionality repeatedly in your application, it works great. It looks really slick too because you can do things like change the color of your form after you have updated it so the user knows there has been a change. Of course there are always multiple ways to attack a problem I love hearing other suggestions.

[Former Member]

Hey Doug,

We were able to achieve this by using the icommand query and data retrieval methods, we used the getColumnCount() and getRowCount() methods, once you have these two numbers you can set two for loops and use String getValue(int ColID, int RowID) , within the for loops you can generate the inner html content and write the values either in a html table or html form.

Thanks,

Mahwish

[Former Member]

This works, but why would not want to deliver an HTML data table directly from the server (SERVLET) as opposed to the round trip and then manipulating with JavaScript on the client side. It won't be nearly as quick as with a server delivered method.

[Former Member]

Hi Ryan,

Our requirement was to display the data in text boxes within the table so it can be manipulated and posted back to the database. Is this possible using the Servlet approach, I though with the servlet you can just display.

Thanks,

Mahwish

[Former Member]

Yes, absolutely. The Servlet will spit out any html element. So instead of just the typical <table><tr><td></td></tr></table> stuff, you could easily insert into a table cell an <input type="text" id="someID" value="some value"> You can even have the SERVLET throw a form submit or button on the page.