Data Control/SQL Server Problem [AVDF Q&A]

Image of Navigational Panel mapped to Contents / Home / Search

Bound Data Control and SQL Server Problem
Tech Support Question

Q. I have a bound data control (creatively titled Data1) set to an ODBC source (called SOURCE) which in turn references a SQL Server 6 database (CUST) which is all created and a few dummy entries inserted into a table called CUSTOMER. The same form that holds the data control also includes 3 buttons:

ENTER (the code is simply data1.Recordset.Addnew)
SAVE (data1.Recordset.Addnew)
DELETE (data1.Recordset.Delete)

When I run the VB program, I can edit the entries on the fly, and the Enter button works, but if I MoveLast I get a message that says "Record is Deleted." The new record doesn't show up unless I exit and restart, and even then the order is totally weird (if I recall correctly).

Believe it or not, the record within the SQL db correctly inserts the primary key Identity default of an autoincremented value within the CUSTOMER_ID column... so everything is just dandy except I need some help on the code for the form_load and the Enter, Save, and Delete buttons. Do I need to re-set the data source for the data control back and forth, from table to dynaset, each time? It's my hope that this will be easy -- and that I'll be able to add a LINE_ITEM datagrid to the same form which will do a SELECT * FROM LINEITEM WHERE CUSTOMER_CUSTID = LINE_ITEM_CUSTID -- a faint one at best?

Dusting off my apprentice's cap,

From: Eric Saeger
Email: erics@ren.pcdocs.com

A. You have discovered some issues, that's for sure. When you add a record to a SQL server recordset like this, you are putting the recordset as exposed by JET in a temporary state - where it has this extra record just appended to the end of the recordset while JET/ODBC/SQL Server negotiate about where everything goes (more or less). I would recommend that whenever you add a record in this fashion or even if you edit a record you use the Requery method to have JET ask ODBC to refresh the recordset.

This will force all changes and repositionings to be flushed back to the SQL server and leave you with a brand spanking new recordset. Obviously if you are making a number of changes or additions you would leave this until the last has been completed so that it is not being done unnecessarily. This will achieve the effect you are observing in changing the recordset from Table type to Dynaset type.

If you wish to display the records reliably in a reasonable order then you might want to consider using the ORDER BY clause on your SQL statement to specify an order.

What you are trying to do with two grids is not beyond possible at all, you are in fact very close as far as I can see. I am curious, however, you seem to have the same code under two of your buttons based upon what you say below. Is one of these actually an Update method and not an AddNew? Be careful how you handle these. You do not want to let the user hit an Update without the current record being in an Edit or AddNew mode. You can determine this by checking the EditMode property of the recordset.

Otherwise, you seem to be very close. Don't give up now, when it's all working it will all seem worth it. Good Luck.

[TECH SUPPORT TOC]

[HOME] [TABLE OF CONTENTS] [SEARCH]