grid connected to SQL, posible? 2

[ruelmercado]

i am currently developing a web application and the design needs the use of an [bold]UPDATEABLE GRID[/bold] on the web browser, is this possible? what grid control will i use?

i have searched MSDN Online and found an answer but after tryng out their suggerstion, i think it only works for ACCESS database. if you wish to read the article, this is the link

http://support.microsoft.com/support/kb/articles/Q229/6/72.asp?FR=0

 

[MerlinB]

Yes this is possible with the DTC Grid Control with one line of the grid becomming editable. Or you could go-it-alone with some fairly extensive server-side code. There is a sample from Microsoft that shows some of the following, though where I got that code from I cannot remember.

For the DTC Grid Control, you will need to construct DTC text-box / check-box / select list etc. controls - one for each column of the grid. Attach these to the recordset(s) as required, but then make them 'hidden'.

In the Grid, instead of simply outputting the recordset data, you need to call a function that:
1. Checks if you are on the Current Row of the recordset
2. If NOT current row, then output the column data
3. If Current Row, then get the HTML of the editable DTC control (switching it to visible first).
An example Grid Column in the DTC properties could now look like:
=this.editCell(txtFullName, [USR_Full_Name])
Where:
this.editCell is a new method added to the GridDTC. If the code is NOT in the GridDTC, then you cannot use the 'this' bit;
txtFullName is the name of a DTC control, in this case a text INPUT control - but it could be any DTC (even another grid!)
[USR_Full_Name] is the recordset column with data to display on the non-editable rows.

I have extended the DATAGRID.ASP library code for the DTC Grid to include just such a function. You can adapt this code if you want to place it directly in the web page or an INC include file - in which case all references to this must be replaced with the name of your grid.

function _DG_editCell(vCurrentRowItem, vOtherRowItem) {
//
// Allow in-grid editing of the 'current' row
//
// You must have text-box controls already defined on
// the page, and bound to the appropriate recordset columns.
// - The text-box controls will be become hidden except in the grid area.
//
// 19-jan-2001 MBeedell From a sample page developed by Microsoft
// 02-feb-2001 MBeedell Either item can be an object

var vItemToDisplay;

// are we on the 'current' row?
if(this._objDataSource.absolutePosition == this._markPos)
vItemToDisplay = vCurrentRowItem;
else
vItemToDisplay = vOtherRowItem;

//if the item to display is a DTC object (text box / combo etc.)
//then get the HTML for that object...
switch (typeof vItemToDisplay) {
case 'object':
// make current row INPUTs
var oControl = vItemToDisplay;
oControl.show();
//return the HTML as a string
vItemToDisplay = oControl.display(true);
oControl.hide();
break;
case 'undefined':
vItemToDisplay = ' '
break;
}
//Return the HTML to display in the grid cell
return vItemToDisplay;
}

If you extend the DATAGRID.ASP as above, then you will also need to add the following line near the top of function _DG__Prototype() where you see other similar lines:

_DataGrid.prototype.editCell = _DG_editCell; //row edit feature


Unfortunately, extending any DTC in this way will not result in the new method or property showing in the handy pop-up lists in the VI editor.

A useful feature of the Grid that is rarely used is the 'Anchor' method - this re-positions the 'current row' to the one that you click. This is ideal when used with the editing feature. Place the following into an 'Unbound' column of a GridDTC and see what happens...
=this.anchor('Edit')
The text ('edit') could be a complete HTML Image tag, if you want to be a bit more flash.


Good luck!I

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[crystalized]

Merlin,

I appears you have used this functionality have you had any problems with it? Also have you ever used it so that you could have a "new" record added automatically at the bottom of the grid and have it ready to edit. I can see possibilities for doing this by adding a new record to the recordset immediately. Then after you add the new record how do you get it to be the current record.

I am just returning to ASP after a 7 month absence from it so I have a bit of catching up to do. Your previous post will go a long way to helping me get going on my new project.

Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[crystalized]

Merlin,

I am having a little difficulty with the code provided as it seems that the line

vItemToDisplay = oControl.display(true);

is causing vItemToDisplay to resolve to 'undefined'

this is not the result I was expecting. Do you recall if you had any problems such as this and if so how did you resolve it.

Any help is appreciated.
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[crystalized]

I had to install the service pack and then the code works for placing the textfield into the grid. The problem I am having now is that the dtc is also visible above the grid where the dtc control is originally placed. This extra input is only visible after you have moved through the records in some way (either with the anchor code that was suggested or with the grid navigation buttons).

I have tried a number of things to no avail. Has anyone worked with this before? It seems like it will decrease coding time by a significant amount if I can get it to work. I would be very glad to hear any comment on this code.

Thanks in advance
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[ruelmercado]

crystalized,

did you make some additional coding for the suggestion of Merlin to work? i am having a tough time about his solution because i am really a "rookie" in terms of using VI6. can you please relay to me the process that you did step-by-step, thanks!

Merlin thanks man!

 

[MerlinB]

Actually, I have found just the thing on MSDN that describes the Edit Grid, and it states that you need Service Pack 3...

http://msdn.microsoft.com/workshop/languages/vinterdev/bin/updateablegrid/editgrid0.asp

The Code that I constructed required the grid DTC script to be adjusted - unlike the MSDN article. This makes it nice and generic (you can use it on any web page - it uses the this command). The MSDN article code requires you to edit the showCell method for each page that you use it on (assuming you re-name your recordsets to something
sensible, other than Recordset1)

To Answer your queries:

Add the TextboxDTC's and un-tick their 'visible' flag. This prevents a copy showing outside the grid (also try placing the dtc's after the grid). They should be bound to the recordset in the normal way.

In the grid properties, make these dtc's appear using
=this.editCell(txtMyDTCContolName, [recordsetColumnName])

the DTC control name must not be in quotes, and the recordset Column Name must be in square brackets. The square brackets are important as the DTC grid control will convert these to rsMyRecordset.fields.getValue('recordsetColumnName') - try right-clicking the grid and select 'show run-time text' to see for yourself.

===
Most DTC's will generate their HTML via their 'display' method. However, instead of dumping the HTML straight out into the Response stream, you can return it as a text string using the (true) parameter:
strHTML = myDTC.display(true)
This is how the editCell works - as it can now place the HTML for the INPUT control within the Grid.
If this does not work (the strHTML is 'undefined') then look at the display routine in your DTC's ASP code. For the TextBox control, it should look like:
function _TXT_display(bReturnText)
{ ...body of the display...
if (bReturnText != true)
Response.Write(strHTML);//write to browser
else
return strHTML;// return HTML to caller
}
If it is any different, then try installing Visual Studio Service Pack 3 or 4. You may need to create a new project in order to get your hands on the updated DTC scripts.

Also note, the editCell routine will show() the DTC before the display(true) - if not then the DTC could return nothing. Then the DTC is hidden again - so you do not end up with duplicate DTC's on your page. If the DTC is BEFORE your grid, then it will be displayed first - so ensure that you set it to 'not visible' at design time.

Good luck!

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[crystalized]

Merlin you are a magician.

I moved the text area dtc after the grid and it works flawlessly now. I really can not thank you enough for this it is going to be an amazing time saver. Before seeing your code I was somewhat scared to even look at the code behind those DTC's. I have not yet attempted to add a save button or editing the data but I am sure it will work just fine once I do.

Ruelmercado, I used the code exactly like Merlin posted it. It did not work properly without having the service pack installed. So I installed service pack 5 and that resolved part of the problem. Placing the bound dtc's after the grid accomplished the rest. So here are the steps.

1. Install service pack 5 (or 3 or 4) available through microsofts site - use the knowledge base to locate it
2. Create a new project
3. Expand the _ScriptLibrary and right click the datagrid.asp and release working copy
4. Open the datagrid.asp file and add the code exactly as Merlin provided it (the function part) - I added it at the bottom of the functions before the closing script tag just so it was easy to locate.
5. Save the data grid file
6. Create an asp page in the project.
7. Drag a recordset onto the page - set the properties to retrieve the required data.
8. Drag a textbox dtc onto the page set the properties to bind the dtc to one of the recordset fields - set the name to something easy to remember
9. Drag a grid dtc onto the page
10. On the Data tab for the grid properties set the recordset source, add an unbound column, in the Field/Expression enter the code provided by Merlin this.editCell(<use your textbox dtc name here>,[<use a field name from the recordset here>]).
11. On the navigation tab check the enable row navigation checkbox.
12. Just make sure the textbox is after the grid in the page.
13. View the page in your browser and you should see the edit box appear in the current row.

I hope this helps.
Crystal
crystalized_s@yahoo.com

--------------------------------------------------

Experience is one thing you can't get for nothing.

-Oscar Wilde

 

[MerlinB]

Thanks Crystal!

Although adding a 'save' button is helpful, if you always update the recordset during the <page>_onenter, then any changes are automatically saved.

Sub mypage_onenter()
if thisPage.firstEntered = false then
'only update if second server sound-trip
rsMyRecordset.Update
end if
end sub

You can even adjust the RECORSET.ASP to check each column during the UPDATE processing to see if it has actually changed - and only perform a real update if changes have been found.

PS this article (updateable grid) has been added to the FAQ secion of Tek-Tips.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[Guest_imported]

In the Recodset1_onbeforeopen function, (in my asp file), I use multiple Request.Form(&quot;value&quot to get the values posted from my htm file. I store these Request.Form values in values I create using Dim(ie. Dim hi = Request.Form(&quot;value&quot).

Before the asp file is displayed I assign the values using getparameter into a stored procedure, and then I output the results onto a grid! I also enable row navigation and use the =Grid1.anchor[(&quot;ssn&quot] method, so that the row is highlighted.

My problem:
Everytime I select the anchor from the grid, the page breaks, and an error reveals that setparameter was expecting a value (ie @ssn) in my stored procedure. Is there anway to prevent this error from showing everytime I navigate through a row to select my information?

I tried setting the recordset implementation tab to Preserv in session state, but I get the same error message all the time, expecting a value to be passed to the stred procedure!

What can I do to allow me to make selections on the grid, and not have it quit on me with errors? Thank You!

 

[MerlinB]

I am not too sure what you mean. However, whenever a form does a Server round-trip any result sets will need to be re-opened, and the results re-fetched. This would imply that the rs1_onbeforeopen will be re-executed. If the Request.Form values have changed, then the result set will be different - or it could error.
Variables that you DIM only last for the time that the page is being created on the server. On each server round-trip they need to be re-created - so I am not sure why you need to store the form values in variables.
Try turning on the trace feature:

<script id=&quot;DebugDirectives&quot; runat=&quot;server&quot; language=&quot;javascript&quot;>
// Set these to true to enable debugging or tracing
@set @debug=false
@set @trace=true
</script>

to see the flow of events.
I am not sure if the problem is to do with the anchor tag, other than it causes a server round-trip.
I assume that the
=Grid1.anchor[(&quot;ssn&quot]
was actually entered as
=Grid1.anchor([ssn])
and could be further simplified to:
=this.anchor([ssn])

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[peggasus88]

Hi,

Before changing my UnitPrice column to:
=this.editCell(txtUnit, [UnitPrice])
as described in the posts above, I had:
=formatColumn([UnitPrice]).

My formatColumn function is as follows:

<script ID=&quot;serverEventHandlersVBS&quot; LANGUAGE=&quot;vbscript&quot; RUNAT=&quot;Server&quot;>

'== formatColumn uses FormatCurrency to display 2 decimal places and a dollar sign
' (Right Alignment)
Function formatColumn(strInput)
formatColumn = &quot;<P align=right>&quot; & FormatCurrency (strInput, 2) & &quot; &quot; & &quot;</P>&quot;
End Function

</script>

So 130 would look like $130.00. =) Since it's more important for me to be able to edit my grid, I am using:
=this.editCell(txtUnit, [UnitPrice])
so it looks like just plain old 130.
Is there a way to have both? =)

Thank you for any you can give me.

Peggy


P.S.
To Merlin: Thank you for yet another helpful post (and FAQ)!

To Crystal: Thank you for your input! The strHTML was 'undefined' for me also and I would never have thought to install a Service Pack!

To Others: It seems like a Service Pack higher than 3 is needed (I had Service Pack 3 and the strHTML was 'undefined' and the textbox DTCs were visible even though I made them hidden at design time). Installing Service 5 and creating a new project, as Merlin suggested, fixed those problems. =)

 

[MerlinB]

try

=this.editCell(txtUnit, formatColumn([UnitPrice]))

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[peggasus88]

Hi Merlin,

Thank you! Thank you!


I would like to do some field validation before the recordset is updated (updated using your thisPage_onenter() code above) to check that the txtProductName textbox has something in it and that the txtUnitPrice textbox has something in it and is a number:

function thisPage_onbeforeserverevent() {
if (isNaN (document.thisForm.txtUnitPrice.value) == true || document.thisForm.txtUnitPrice.value == &quot;&quot {
alert (&quot;Enter a valid Unit Price. \n Example: 1300&quot;
document.thisForm.txtUnitPrice.focus();
thisPage.cancelEvent = true;
}
if (document.thisForm.txtProductName.value == &quot;&quot {
alert (&quot;Product Name is a required field.&quot;
document.thisForm.txtProductName.focus();
thisPage.cancelEvent = true;
}
}


To test my function above, I input nothing in the txtUnitPrice textbox and I get this error:

Microsoft VBScript runtime error '800a000d'
Type mismatch: 'FormatCurrency'
/Administrator/ViewProducts.asp, line 89

FormatCurrency is called from my FormatColumn(strInput) function. Since this function is in the <script ID=&quot;serverEventHandlersVBS&quot; LANGUAGE=&quot;vbscript&quot; RUNAT=&quot;Server&quot;> block of code that is RUNAT=&quot;Server&quot;, it seems like my thisPage_onbeforeserverevent() function is skipped and NOT executed.


I also tried inputting a non-numeric in the txtUnitPrice textbox, and I get an error that I am unfamiliar with:

Microsoft Cursor Engine error '80040e21'
Multiple-step operation generated errors. Check each status value.
/Administrator/_ScriptLibrary/Recordset.ASP, line 1235


Before I learned about the editable grid, I had a page that added, deleted, and updated information using a FormManager DTC (no grid on this page - only 3 Textbox DTCs and many Button DTCs). This page used a similar (working) thisPage_onbeforeserverevent() function to do field validation. The only difference was that the function took in 2 parameters, obj & evnt. For the editable grid, I was thinking that my thisPage_onebeforeserverevent() function above would not need any parameters and would execute, well, before a server event. Am I very wrong? How to I get my thisPage_onbeforeserverevent() field validation function to execute?


Peggy

 

[MerlinB]

The onbeforeserverevent occurs whenever a DTC control causes a submit/server-round-trip - typically a button click. You could use the more standard HTML approach and use the onsubmit event of the FORM control - in which case your javascript function returns true to submit, or false to halt the submit. So if you have an ordinary HTML submit button on the form, this will skip the onbeforeserverevent.

The onbeforeserverevent always accepts 2 parameters - the control name and the event name.

The multiple-step operation error is returned when you have attempted an sql insert/update, but it had a field validation error (non-numeric value in this case - but typically when a null is supplied for a non-null column). It may be returned for other reasons - you just have to guess what they may be! The ADO connection object collects an array of errors - if you could get to it you may be able to unwind the real error message(s).

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[peggasus88]

Hi Merlin,

Are you suggesting I change:
=this.anchor('Edit')
to an HTML submit button, so I can write an onsubmit event handler that does field validation?
I changed it to:
=this.anchor('<INPUT id=sbmtEdit name=sbmtEdit type=submit value=Edit>')
It looks really cool, but when I click on any 'Edit' below the first row, the editable textboxes don't show up for that row; they remain on the first row.

...Overall, I am confused because I don't have a FORM on this page, only a grid and the hidden Textbox DTCs below it to make the grid editable. I apologize for not stating my question clearly...I used your thisPage_onenter() code (from a post above) to automatically save changes to the recordset when a different 'Edit' is clicked. When I click 'Edit' on a row that is NOT the 'current row', I would like to do some field validation before invalid changes are saved to the recordset and the new row is made the 'current row'.

Aside: I added an 'Add New Product' Button DTC below the grid to allow for the addition of a new editable row to the grid. If I don't have field validation, and a user inputs invalid data and then clicks on a different 'Edit', my page always gives an error message because the invalid data has already been saved to the database! The editable grid is so convenient. I really want to use it! =)

Thanks again for your help,

Peggy