f



CancelUpdate

My VB6 project is going crazy.  It uses the ADO and M$ Jet.  Currently
there are 2 records in the db.  Let's say I want to add another
record.  So I hit the adatPrimaryRS.Recordset.AddNew and the input
boxes appear.  I changed my mind and want to cancel the new record
before I hit the update.  So I engage the
datPrimaryRS.Recordset.CancelUpdate, which should cancel any data, and
should reset the datPrimaryRS.Recordset.RecordCount back to 2.  But,
it doesn't.  The datPrimaryRS.Recordset.RecordCount remains at 3. This
can be verified by Debug.print datPrimaryRS.Recordset.RecordCount, and
the result is 3.  The real wierd part is that when I try to add
another record by adatPrimaryRS.Recordset.AddNew, a real wierd Error
Message shows the Operation was Canceled.  The
datPrimaryRS.Recordset.AbsolutePosition and the
datPrimaryRS.Recordset.RecordCount each remain at 3.  The Cancel
should return the datPrimaryRS.Recordset.AbsolutePosition and the
datPrimaryRS.Recordset.RecordCount to 2, but doesn't.  Any idea what
is going on?
0
Mike4592 (6)
5/6/2006 7:59:10 PM
comp.lang.basic.visual.misc 10153 articles. 0 followers. Post Follow

10 Replies
2883 Views

Similar Articles

[PageSpeed] 44

Hello Friend,

As You said...
> Let's say I want to add another record.  So I hit the
> adatPrimaryRS.Recordset.AddNew and the input
> boxes appear.  I changed my mind and want to
> cancel the new record before I hit the update.  So I
> engage the datPrimaryRS.Recordset.CancelUpdate,
> which should cancel any data, . . .

> The real wierd part is that when I try to add another
> record by adatPrimaryRS.Recordset.AddNew, a real
> wierd Error Message shows the Operation was
> Canceled.

On Going through your question it seems to be clear that the Recordset
used to add and cancel is different. i.e adatPrimaryRS is used for
append and datPrimaryRS is used for cancel. So it may be the problem,
make neccy. correction to have the operations in the SAME RECORDSET
(ie, either in adatPrimaryRs or datPrimaryRs.) It may Solve the
problem.

On Considering it may the typing mistake. we can make the following
steps to retify the problem.

1.  ADO is used for Tier Arch. in  Client - Server Database
Connectivity, So, in Most cases It will use the Batch Process. So, in
case of Cancelling Try the method CancelUpdateBatch. It may solve the
problem.

2. Even if the Problem continue then you just refresh the recordset
after cancelling the updatation process. ie use the following statement
after to the cancelupdate,

adatPrimaryRS.Recordset.Refresh

I hope it will solve your problem.


By
Vadivel Pichaipillai,
India. Email: vadivelpvs@gmail.com

0
vadivelpvs (24)
5/7/2006 12:05:56 PM
On 7 May 2006 05:05:56 -0700, "vadivelpvs@gmail.com"
<vadivelpvs@gmail.com> wrote:

>Hello Friend,
>
>As You said...
>> Let's say I want to add another record.  So I hit the
>> adatPrimaryRS.Recordset.AddNew and the input
>> boxes appear.  I changed my mind and want to
>> cancel the new record before I hit the update.  So I
>> engage the datPrimaryRS.Recordset.CancelUpdate,
>> which should cancel any data, . . .
>
>> The real wierd part is that when I try to add another
>> record by adatPrimaryRS.Recordset.AddNew, a real
>> wierd Error Message shows the Operation was
>> Canceled.
>
>On Going through your question it seems to be clear that the Recordset
>used to add and cancel is different. i.e adatPrimaryRS is used for
>append and datPrimaryRS is used for cancel. So it may be the problem,
>make neccy. correction to have the operations in the SAME RECORDSET
>(ie, either in adatPrimaryRs or datPrimaryRs.) It may Solve the
>problem.

The 'a' on adatPrimaryRs was a typo.

>
>On Considering it may the typing mistake. we can make the following
>steps to retify the problem.
>
>1.  ADO is used for Tier Arch. in  Client - Server Database
>Connectivity, So, in Most cases It will use the Batch Process. So, in
>case of Cancelling Try the method CancelUpdateBatch. It may solve the
>problem.
>
>2. Even if the Problem continue then you just refresh the recordset
>after cancelling the updatation process. ie use the following statement
>after to the cancelupdate,
>
>adatPrimaryRS.Recordset.Refresh
>

The ADO does not like being used on a application where the location
of the database is not known.  The db will not be located on a server.
The db will be located in the same directory as the app on the user's
pc.  Problem is that I have no idea which directory or drive the user
may install the app.  How do I write the ADO connection string to
allow a variable for the path?  Existing -
PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program
Files\HeadsDown\HeadsUp.mdb;

I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
app.path & "\" & "HeadsUp.mdb";

Since the app.path is a variable, it bombs.  How do I fix it?

0
Mike4592 (6)
5/9/2006 4:47:35 PM
On Tue, 09 May 2006 11:47:35 -0500, Mike@Donald.com wrote:

>I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
>app.path & "\" & "HeadsUp.mdb";
>
>Since the app.path is a variable, it bombs.  How do I fix it?

<code>
Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As String) _
As String
Attribute ADO_BuildMSAccessConnectString.VB_Description = _
"Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51"
' Listing C: Setting the ADO connection string using Microsoft Access OLEDB 
' Provider 3.51
' Source: MSDN - Using ADO GetRows...
    ADO_BuildMSAccessConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
Source = " & sUNCPathOfMDB
ExitThis:
    Exit Function
End Function
</code>

Dim s As String

s = ADO_BuildMSAccessConnectString( App.Path & "\databasename.mdb" )
Debug.Print s
'Note:  Substitute Microsoft.Jet.OLEDB.4.0 for 3.51.

Hope this helps!
_______________________
Michael B. Johnson
0
mjohnson (626)
5/9/2006 6:01:33 PM
On Tue, 09 May 2006 13:01:33 -0500, Michael B. Johnson
<mjohnson@veribox.net> wrote:

>On Tue, 09 May 2006 11:47:35 -0500, Mike@Donald.com wrote:
>
>>I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
>>app.path & "\" & "HeadsUp.mdb";
>>
>>Since the app.path is a variable, it bombs.  How do I fix it?
>
><code>
>Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As String) _
>As String
>Attribute ADO_BuildMSAccessConnectString.VB_Description = _
>"Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51"
>' Listing C: Setting the ADO connection string using Microsoft Access OLEDB 
>' Provider 3.51
>' Source: MSDN - Using ADO GetRows...
>    ADO_BuildMSAccessConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
>Source = " & sUNCPathOfMDB
>ExitThis:
>    Exit Function
>End Function
></code>
>
>Dim s As String
>
>s = ADO_BuildMSAccessConnectString( App.Path & "\databasename.mdb" )
>Debug.Print s
>'Note:  Substitute Microsoft.Jet.OLEDB.4.0 for 3.51.
>
>Hope this helps!
>_______________________
>Michael B. Johnson

Is it necessary to Bind the ADO to the database at design time?
0
Mike4592 (6)
5/10/2006 10:44:02 AM
On Tue, 09 May 2006 13:01:33 -0500, Michael B. Johnson
<mjohnson@veribox.net> wrote:

>On Tue, 09 May 2006 11:47:35 -0500, Mike@Donald.com wrote:
>
>>I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
>>app.path & "\" & "HeadsUp.mdb";
>>
>>Since the app.path is a variable, it bombs.  How do I fix it?
>
><code>
>Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As String) _
>As String
>Attribute ADO_BuildMSAccessConnectString.VB_Description = _
>"Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51"
>' Listing C: Setting the ADO connection string using Microsoft Access OLEDB 
>' Provider 3.51
>' Source: MSDN - Using ADO GetRows...
>    ADO_BuildMSAccessConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
>Source = " & sUNCPathOfMDB
>ExitThis:
>    Exit Function
>End Function
></code>
>
>Dim s As String
>
>s = ADO_BuildMSAccessConnectString( App.Path & "\databasename.mdb" )
>Debug.Print s
>'Note:  Substitute Microsoft.Jet.OLEDB.4.0 for 3.51.
>
>Hope this helps!
>_______________________
>Michael B. Johnson

Boy, did it ever help.  You hit the nail on the head.  The property
table for the ADO still has its RecordSource as "C:\Program
Files\HeadsUp\HeadsUp.mdb", but there is no file called HeadsUp.mdb at
that location.  The app and HeadsUp.mdb file are located at c:\Program
Files\Stupid\.  When I launch the app, its default ADO connection
string gets overwritten by the sConnect string in Sub Form_Load.  The
app, ADO, and db load properly.  You have no idea how much help you
have been.  Thank you very much.

Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As
String) As String

    ADO_BuildMSAccessConnectString =
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & sUNCPathOfMDB

ExitThis:
    Exit Function

End Function


Private Sub Form_Load()
Const HorizontalSpacing = 100
Dim sConnect As String
Dim sSQL As String


With frmMeMeMe
    .Width = 11950
    '.Height = 9000
End With

sSQL = "select [Day/Time of Appt],Appt_Date,Appt_Time,[Medical
Person],[Medical Facility],[Purpose of Visit],[Rating of
Service],[Questions Answered],Comments from Bubbles Order by [Day/Time
of Appt]"

sConnect = ADO_BuildMSAccessConnectString(App.Path & "\HeadsUp.mdb")

datPrimaryRS.RecordSource = sSQL
datPrimaryRS.ConnectionString = sConnect

Set txtDateAndTime.DataSource = datPrimaryRS
txtDateAndTime.DataField = "Day/Time of Appt"

Set txtDate.DataSource = datPrimaryRS
txtDate.DataField = "Appt_Date"

Set txtTime.DataSource = datPrimaryRS
txtTime.DataField = "Appt_Time"

Set txtMedicalPerson.DataSource = datPrimaryRS
txtMedicalPerson.DataField = "Medical Person"

Set txtMedicalFacility.DataSource = datPrimaryRS
txtMedicalFacility.DataField = "Medical Facility"

Set txtPurposeOfVisit.DataSource = datPrimaryRS
txtPurposeOfVisit.DataField = "Purpose of Visit"

Set lblRatingOfServiceValue.DataSource = datPrimaryRS
lblRatingOfServiceValue.DataField = "Rating of Service"

Set chkQuestionsAnswered.DataSource = datPrimaryRS
chkQuestionsAnswered.DataField = "Questions Answered"

Set txtComments.DataSource = datPrimaryRS
txtComments.DataField = "Comments"


End Sub
0
Mike4592 (6)
5/10/2006 7:24:24 PM
On Wed, 10 May 2006 14:24:24 -0500, Mike@Donald.com wrote:

>app, ADO, and db load properly.  You have no idea how much help you
>have been.  Thank you very much.

You're welcome. Sometimes it helps just to have a sounding board...
_______________________
Michael B. Johnson
0
mjohnson (626)
5/11/2006 4:29:11 PM
On Tue, 09 May 2006 13:01:33 -0500, Michael B. Johnson
<mjohnson@veribox.net> wrote:

>On Tue, 09 May 2006 11:47:35 -0500, Mike@Donald.com wrote:
>
>>I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
>>app.path & "\" & "HeadsUp.mdb";
>>
>>Since the app.path is a variable, it bombs.  How do I fix it?
>
><code>
>Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As String) _
>As String
>Attribute ADO_BuildMSAccessConnectString.VB_Description = _
>"Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51"
>' Listing C: Setting the ADO connection string using Microsoft Access OLEDB 
>' Provider 3.51
>' Source: MSDN - Using ADO GetRows...
>    ADO_BuildMSAccessConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
>Source = " & sUNCPathOfMDB
>ExitThis:
>    Exit Function
>End Function
></code>
>
>Dim s As String
>
>s = ADO_BuildMSAccessConnectString( App.Path & "\databasename.mdb" )
>Debug.Print s
>'Note:  Substitute Microsoft.Jet.OLEDB.4.0 for 3.51.
>
>Hope this helps!
>_______________________
>Michael B. Johnson

Got another question for you.

Why doesn't the ADO control have a handle - ADO.hwnd does not exist?  
0
Mike4592 (6)
5/12/2006 2:00:34 PM
<Mike@Donald.com> skrev i melding
<snipped/>
> Got another question for you.
>
> Why doesn't the ADO control have a handle - ADO.hwnd does not exist?

Because it is not a window!???

If you're talking about the visual control, one of two reasons:

    1. if it is a "windowed" (aka. heavyweight) contol, the designers
       have decided not to expose it.
    2. It is not a windowed control (like Label), the window-handle
       doesn't exist

-- 
Dag.


0
me3469 (798)
5/12/2006 2:07:16 PM
On Wed, 10 May 2006 14:24:24 -0500, Mike@Donald.com wrote:

>On Tue, 09 May 2006 13:01:33 -0500, Michael B. Johnson
><mjohnson@veribox.net> wrote:
>
>>On Tue, 09 May 2006 11:47:35 -0500, Mike@Donald.com wrote:
>>
>>>I want it to read PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=
>>>app.path & "\" & "HeadsUp.mdb";
>>>
>>>Since the app.path is a variable, it bombs.  How do I fix it?
>>
>><code>
>>Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As String) _
>>As String
>>Attribute ADO_BuildMSAccessConnectString.VB_Description = _
>>"Setting the ADO connection string using Microsoft Access OLEDB Provider 3.51"
>>' Listing C: Setting the ADO connection string using Microsoft Access OLEDB 
>>' Provider 3.51
>>' Source: MSDN - Using ADO GetRows...
>>    ADO_BuildMSAccessConnectString = "Provider=Microsoft.Jet.OLEDB.3.51;Data
>>Source = " & sUNCPathOfMDB
>>ExitThis:
>>    Exit Function
>>End Function
>></code>
>>
>>Dim s As String
>>
>>s = ADO_BuildMSAccessConnectString( App.Path & "\databasename.mdb" )
>>Debug.Print s
>>'Note:  Substitute Microsoft.Jet.OLEDB.4.0 for 3.51.
>>
>>Hope this helps!
>>_______________________
>>Michael B. Johnson
>
>Boy, did it ever help.  You hit the nail on the head.  The property
>table for the ADO still has its RecordSource as "C:\Program
>Files\HeadsUp\HeadsUp.mdb", but there is no file called HeadsUp.mdb at
>that location.  The app and HeadsUp.mdb file are located at c:\Program
>Files\Stupid\.  When I launch the app, its default ADO connection
>string gets overwritten by the sConnect string in Sub Form_Load.  The
>app, ADO, and db load properly.  You have no idea how much help you
>have been.  Thank you very much.
>
>Public Function ADO_BuildMSAccessConnectString(sUNCPathOfMDB As
>String) As String
>
>    ADO_BuildMSAccessConnectString =
>"Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " & sUNCPathOfMDB
>
>ExitThis:
>    Exit Function
>
>End Function
>
>
>Private Sub Form_Load()
>Const HorizontalSpacing = 100
>Dim sConnect As String
>Dim sSQL As String
>
>
>With frmMeMeMe
>    .Width = 11950
>    '.Height = 9000
>End With
>
>sSQL = "select [Day/Time of Appt],Appt_Date,Appt_Time,[Medical
>Person],[Medical Facility],[Purpose of Visit],[Rating of
>Service],[Questions Answered],Comments from Bubbles Order by [Day/Time
>of Appt]"
>
>sConnect = ADO_BuildMSAccessConnectString(App.Path & "\HeadsUp.mdb")
>
>datPrimaryRS.RecordSource = sSQL
>datPrimaryRS.ConnectionString = sConnect
>
>Set txtDateAndTime.DataSource = datPrimaryRS
>txtDateAndTime.DataField = "Day/Time of Appt"
>
>Set txtDate.DataSource = datPrimaryRS
>txtDate.DataField = "Appt_Date"
>
>Set txtTime.DataSource = datPrimaryRS
>txtTime.DataField = "Appt_Time"
>
>Set txtMedicalPerson.DataSource = datPrimaryRS
>txtMedicalPerson.DataField = "Medical Person"
>
>Set txtMedicalFacility.DataSource = datPrimaryRS
>txtMedicalFacility.DataField = "Medical Facility"
>
>Set txtPurposeOfVisit.DataSource = datPrimaryRS
>txtPurposeOfVisit.DataField = "Purpose of Visit"
>
>Set lblRatingOfServiceValue.DataSource = datPrimaryRS
>lblRatingOfServiceValue.DataField = "Rating of Service"
>
>Set chkQuestionsAnswered.DataSource = datPrimaryRS
>chkQuestionsAnswered.DataField = "Questions Answered"
>
>Set txtComments.DataSource = datPrimaryRS
>txtComments.DataField = "Comments"
>
>
>End Sub

Got another question for you experts -

I want to search the above recordset.  The user wants to search the
recordset for a guy named - Gopher.  So I created a sub called
mnuFind.  The user will be asked to input the name of the guy in an
Input Box.  I want an SQL statement to search the recordset.  The
actual name of the Table is Bogus.  VB6 does not lik the * after
select.  VB6 wants to see Select Case.  What is wrong with my SQL
statement>

select  * from Bogus where [Medical Person]='Gopher'
0
Mike4592 (6)
5/15/2006 7:53:22 PM
On Mon, 15 May 2006 14:53:22 -0500, Mike@Donald.com wrote:

>Got another question for you experts -

I recommend that when you have another question that you post it in a new
thread.

>I want to search the above recordset.  The user wants to search the
>recordset for a guy named - Gopher.  So I created a sub called

Look into the .Find methods or the .Filter property or just re-open the
recordset with the new SQL statement.

>mnuFind.  The user will be asked to input the name of the guy in an
>Input Box.  I want an SQL statement to search the recordset.  The
>actual name of the Table is Bogus.  VB6 does not lik the * after
>select.  VB6 wants to see Select Case.  What is wrong with my SQL
>statement
>
>select  * from Bogus where [Medical Person]='Gopher'

You'll need to read up on ADO or DAO. Then you'll need to place your query
within a string variable and pass that variable to a recordset open statement.

sMyString = "SELECT  *  " & _
  "FROM Bogus  " & _
  "WHERE [Medical Person]='Gopher'"

Or 

sMyString = "select  * from Bogus where [Medical Person]='Gopher'"

....hope this helps!
_______________________
Michael B. Johnson
0
mjohnson (626)
5/15/2006 10:37:33 PM
Reply: