How to add new record to Access database? 3

[Frank123]

I'm trying make a button on a page that would add a new row on my DTC Grid with ? marks in all fields. It is an Access 98 database. So far I tried the following:

recordset1.addnew
recordset1("FirstName&quot = "?"
recordset1("LastName&quot = "?"
recordset1.update

I was reading somewhere that Access doesn't support adding new records, but I find that somewhat hard to believe. If someone could give me some tips I'd be very happy.

 

[dvrobin]

Shooting from the hip, I'd say that you should make sure you specify the cursortype when you open the Recordset object. The default is ForwardOnly. A ForwardOnly cursor does not allow adding new records. Also, the default Locktype is ReadOnly. The MSDN topic "Recordset Object Basics" might be helpful.

Dave Robinder, MCSD

 

[Frank123]

Thanks for the info dvrobin, but it didn't solve the problem. My settings are the following:
Cursor type = 3-Static
Cursor location = 3-Use client-side cursors
Locktype = 3-Optimistic

This is the only cursor type that it allows me to choose because of the client-side cursor location. When I do change my cursor location to server side and change the cursor type to dynamic (or any) my table doesn't show up and I get a error '800a0cc1' Item cannot be found in the collection corresponding to the requested name or ordinal.
If anyone could give me any more help that would be great!

 

[MerlinB]

There are a couple of possible reasons that spring to mind.

* The SELECT query should not contain any joins - ie, base it on a single table. While this is not an absolute requirement - it sure simplifies the work that ODBC needs to do to figure out what an INSERT/UPDATE and DELETE actually should do.

* The table MUST have a unique (primary) key. Try including this column(s) in your SELECT clause, even if not displayed.

Try using the DataEnvironment:- Right click the Global.asa, add a connection, then add a command with the required SQL - double-click this command and an editable grid will pop-up. If this grid is ReadOnly then no amount of code will solve the problem!

You still have a problem though. For the new row to appear in the Data Grid, you must REQUERY the recordset. This will place the new row (potentially) anywhere in the grid (not always at the end, depending on the Sort order in the SQL).

The easiest solution is to provide a separate set of data-entry text boxes for the ADD function, and a bundle of code to position the grid recordset at this row after the INSERT and REQUERY has completed.

If you are feeling brave, you could alter the Recordset and DataGrid DTC javascript code (in _ScriptLibaray) to provide an Access Sytle add facility - ie a blank row at the bottom of the grid.

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[Frank123]

Thanks for the help Merlinb, however, I still can't get my page to work right. And I forgot to mention before that my DTC Grid is editable already (input boxes in the grid) so all I really need to do is add a line to the grid with question marks in all the fields. I thought it would be an easy task, but Interdev proved me wrong.
To answer Merlins questions:
My select statement has no joins.
The table does have a primary key.
When I double click the command in the global.asa folder the grid does pop up and is editable.

My most recent code is the following:
Sub btnAdd_onclick()
Recordset1.addrecord
Recordset1.fields.setValue "Software", "new"
Recordset1.updateRecord
End Sub

This code doesn't give me any errors but it overwrites the first record. The bad thing is I can't find any useful information on this subject. I appreciate your help.

 

[Metka]

I belive that addrecord method does not work properly (check out thread117-50932 )
you can add new record also with addImmediate method or by executing an SQL statement.

 

[Frank123]

Thanks for the info Metka. I already was in the process of adding a record by executing an SQL statement but couldn't get it to work right. I know it should work so I keep trying. Maybe one of you could help me out. This is what I have so far..

strSQL = "Insert into software (software) values ('software')"
dim conn
conn = 'dont know what to put here..
conn.execute strSQL

I know I have to set conn equal to some connection but have no clue what since the recordset and connection were made through InterDev. Thanks..

 

[MerlinB]

Well, there are many ways to obtain this. As its christmas, and you have plenty of time for reading, try the following!...

Have a look in the GLOBAL.ASA file in the root of your app. You will (should) see a range of connection strings added to the APPLICATION collection.

Application("cnCON_User_ConnectionString&quot = "Provider=MSDASQL.1;User ID= ..... etc...

If you expose (the code of) one of your recordset DTC's, then you may see these settings being used - something like this:

var DBConn = Server.CreateObject('ADODB.Connection');
DBConn.ConnectionTimeout
= Application('cnCON_User_ConnectionTimeout');
DBConn.CommandTimeout
= Application('cn_User_CommandTimeout');
DBConn.CursorLocation
= Application('cnCON_User_CursorLocation');
DBConn.Open(Application('cnCON_User_ConnectionString'),
Application('cnCON_User_RuntimeUserName'),
Application('cnCON_User_RuntimePassword'));
var cmdTmp = Server.CreateObject('ADODB.Command');
var rsTmp = Server.CreateObject('ADODB.Recordset');
cmdTmp.ActiveConnection = DBConn;
rsTmp.Source = cmdTmp;
cmdTmp.CommandType = 1;
cmdTmp.CommandTimeout = 10;
cmdTmp.CommandText = 'SELECT * FROM tblTable';
...etc... [put your INSERT command above]

You may be able to add a RECORDSET DTC, that includes the INSERT clause - this has the benefit of wrapping any PARAMETERS, so you can insert like this:

(DTC called rsInsert)
INSERT (c1, c2, c3) VALUES (?, ?, ?)

make sure you un-tick the AutoOpen option for this recordset!
Then set the column values via parameters...
rsInsert.setParameter 0, txtValue1
rsInsert.setParameter 1, txtValue2
..etc..
rs.Open
rs.Close
(now refresh the grid recordset to see the new row - assuming the Recordset Query does not Filter it out!!!)

this has the benefit that you do not need to care about single/double quotes within the txtValue's.

FINALLY: The above method is OK, but if you want to do the same function on other web pages, then you could consider using the DataEnvironment. Just add the INSERT as a Command - locate the GLOBAL.ASA, right click a connection and add a command - call it insMyInsert. Type in the INSERT clause. Then in your web page:

thisPage.createDE
intResult = DE.insMyInsert txtValue1, txtValue2, txtValue3

Again, refresh the Grid Recordset - if its WHERE clause allows, then the new row will show somewhere in the list.

***New Year Teaser...***
If you feel up to it, you could add an extra 'row' to the Grid via an event for an ADD function:

I have added a total-row event - so I can add sum totals to the bottom of a grid, you could adjust it for an ADD row:

in the middle of the function _DG_display(bReturnText)
if (objRS.EOF) {
@if (@trace_events)
thisPage._traceEvent(this.name,GRID_ONTOTALROW);
Response.Write ('<br>EVENT TRACE: <b>' + this.name + '</b> fired <b>' + GRID_ONTOTALROW + '</b> event.');
@end
this._objEventManager.fireEvent(GRID_ONTOTALROW);
}

and in the function _DataGrid(strName,objParent)
add:
// advise for eventhandlers
this._objEventManager = CreateEventManager();
this._objEventManager.adviseDefaultHandler(this.name,GRID_ONDISPLAY);
this._objEventManager.adviseDefaultHandler(this.name,GRID_ONTOTALROW);

and in your web page code:

Function grdGrid_ontotalrow()
dim strHTML

strHTML = &quot;<TR>&quot;
strHTML = strHTML & &quot;<TD><Font Size=2><B>**Add New**</TD>&quot;
/*add INPUT text boxes in the HTML string..*/
strHTML = strHTML & &quot;</TR>&quot;

grdGrid.insertRow strHTML
End Function

HAPPY NEW YEAR

http://www.websitedesign.co.uk

http://www.zentext.com

(Content Management)

 

[Frank123]

Thanks for the info MerlinB. I got it to work using the Date Environment way. I've got a small problem though, my grid doesn't refresh after the first time I use it. The second time I click my add button the first addition appears, the third click the second appears and so on... I assume it's something to do with the grids recordset DTC. If I click the refresh button after the first click the record appears in the grid (although after I hit the refresh key it asks me to resend data, which I hit ok to). Once again thanks a lot!!

 

[Frank123]

Just found out that when I hit my &quot;add&quot; record button and then hit refresh it adds 2 records and only displays the first one on the grid. ???? Don't know why..