Problems with ADD and DELETE records with ADO Controls
I'm a newbie using VB6 e SQL Server 2000.
I have several problems in the application that I'm doing using VB6 which
access a SQL Server 2000 database designed by me.
I use ADO Data Controls for access the data from the database. Some forms
are header/details (sometimes with more than one child). I did these forms
using SHAPE-APPEND and put the details records on DataGrid Controls.
The problems follows:
I want to verify, on INSERT, if the record that I'm trying to insert is
not already in the database (with the only difference in the primary key).
The problem is that, suppose that the tblTable has 3 fields (ID, field1
and field2), if I use a trigger ON INSERT in Transact-SQL like this:
CREATE TRIGGER ForInsertRecord
IF ((SELECT count(*)
FROM tblTable, INSERTED
WHERE tblTable.field1 = INSERTED.field1
AND tblTable.field2 = INSERTED.field2) > 0)
PRINT 'Cannot Insert the record.'
the record that I want to insert is always found because comes out in the
count(*) (I think that happens in this way). The result is that with this
trigger never inserts records.
What I'm doing wrong?
In general, how to use triggers?
When is better use a trigger, when is better use a stored procedure, when
is better use another methods?
2) ADDING RECORDS:
Which is the right method to add records? How implement correctly both
(I don't know if there are others methods) of them?
The problems happens in a single record form and in a header/detail form.
I know two ways to Add recors:
I can set the EOFAction property of the ADO Data Control to: 2-doAddNew
In this way I can add a record using the ADO Data Control.
The problem in this case is:
If I arrive to this new record and I decide that I don't want really insert
any record and I want to turn back, I cannot do that because it raise an
Empty row cannot be inserted
Row must have at least one columns value set.
If I try to catch this error the messagge comes anyway and I cannot turn
A second way is set the EOFAction of the ADO Data Control to: 0-doMoveLast
and use a button "ADD" to Add records.
In this case I have to click "ADD" button in order to fill the fields.
The problem in this case is:
How can I have the changes effettive in the database? I have to move to
another record always or there is a more natural way to proceed?
In this second way is even a problem insert records in the Details DataGrid
because I cannot move between records.
3) DELETE RECORDS WITH A FOREIGN KEY COSTRAINT:
If I catch the foreign key error: Error -2147217873, I don't eliminate
the record and that is good. The problem is that if I try to move to another
record comes out an error message:
Row handle referred to a deleted row or a row marked for deletion.
What should I do?
4) DELETE RECORDS IN A HEADER/DETAIL FORM:
If I delete a parent record in a header/detail form comes out an error
even if there is no child for this record.
The error is not the foreign key message but this one:
Multiple step operation generated errors.
Check each status value.
What can I do? My header/details are fields for the header and DataGrid
Control(s) for the Detail(s).
5) INSERT RECORDS IN A HEADER/DETAIL FORM:
When I insert a new parent record and then I want insert a child for it,
the child doesn't accept the insertion because is empty the field of the
foreign key (because the parent is not in the database yet).
I patch the problem in this way:
Private Sub GridControl_GotFocus()
This is awful! but what is worse, if it is the first record?
What is the right way to do?. Or maybe something else is wrong?
6) DATA GRID:
How can I do for use the ENTER key in the same way I use the TAB key in
a DataGrid? This means I want to navigate between fields and in the last
field move to the next record.
Thank in advance for your kind attention and scuse me for my English