• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

SQL and deleted recordset makes error ....

Hi!

I am using following code:

Dim Datenbank As Database
Dim DatenRecordSet As Recordset
Dim SQLDef As QueryDef
Dim Temp$

Set Datenbank4 = OpenDatabase(App.Path & "\data.mdb")
Temp$ = "SELECT * FROM [Test] "
Set SQLDef = PDatenbank.CreateQueryDef("", Temp$)
Set DatenRecordSet = SQLDef.OpenRecordset()


'*****

if i use it twice at the same time and one of the 2 delete one recordset. How can i know if a recordset is deleted? Is there a easy if then or can i only look for an error (not so good)?

Falke
0
Falke
Asked:
Falke
  • 7
  • 5
  • 4
  • +2
1 Solution
 
Anthony PerkinsCommented:
Please maintain your open questions:
terminate a programm and send vbreturn? Date: 09/23/2001 03:14AM PST  
http://www.experts-exchange.com/visualbasic/Q_20186489.html

The following should be deleted as no solutions have been contributed:
Resize Taskbar and set the desktop font smaller Date: 04/14/2002 02:27AM PST
http://www.experts-exchange.com/visualbasic/Q_20288984.html
DHTMLeditor and helpfile available? Date: 03/20/2002 01:33AM PST
http://www.experts-exchange.com/visualbasic/Q_20279191.html
Embedding Internet Explorer Component into a frame -> how can i retrieve links? Date: 03/11/2002 11:53PM PST
http://www.experts-exchange.com/visualbasic/Q_20275703.html
mediaplayer and disable hardware-acceleration? Date: 10/26/2001 01:51PM PST
http://www.experts-exchange.com/visualbasic/Q_20218046.html

Thanks,
Anthony
0
 
FalkeAuthor Commented:
@acperkins
I like your Answers! But thats no answer to my question! To your, how can i delete a question?

Falke
0
 
Anthony PerkinsCommented:
Post a message to that effect in Community Support (http://www.experts-exchange.com/commspt/).

In general, you will find Experts more receptive to your questions when you take the time to maintain them.

Anthony
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
FalkeAuthor Commented:
ok done
0
 
aikimarkCommented:
This should be:

Set Datenbank4 = OpenDatabase(App.Path & "\data.mdb")
Temp$ = "SELECT * FROM [Test] "
Set DatenRecordSet = Datenbank4.OpenRecordset(Temp$)
0
 
Ryan ChongCommented:
> How can i know if a recordset is deleted?

Are you mean a recordset object (example DAO.recordset) or a Query object in database? Do you want to check the existence of a Query in database?

regards
0
 
FalkeAuthor Commented:
@ aikimark
you are right .. but that's not the problem
and to correct all it must be:
Set Datenbank = OpenDatabase(App.Path & "\data.mdb")


@ryancys
DAO.Recordset (datenrecordset is a dao.recordset (Dao 3.6)

Falke
0
 
aikimarkCommented:
Falke ,

My example code does not use a querydef object.  The recordset object is created directly from the database object.  I appologize for the typo.  I had copied code from your question entry.  The Datenbank was spelled Datenbank4 on one of the lines.
0
 
FalkeAuthor Commented:
thanks for the improve ;)

but my problem is, if i use 2 routines at the same time (same SQL) and i delete in one a recordset, in the other i get an error. How can i ask if a recordset has been deleted? I would like to have something like if datenrecordset.isdeleted = True or so.

Falke
0
 
aikimarkCommented:
do the recordsets have the same name?  If so, they can't be simultaneously addressed by some code.

If DatenRecordSet Is Nothing Then

End If
0
 
Anthony PerkinsCommented:
You cannot delete a recordset.  You can close it and set it to Nothing (In which case you can can conpare to Nothing).  But I do not believe this is what you mean.  I think you are opening two recordsets with the same SQL and one of them is deleting a record.  Your question is then how do you "alert" the other recordset that the record is deleted.

The short answer is you cannot, without requerying the database.  You may want to do one of the following:
1. Check fr this possibility with Error Handling
2. Re-query.
3. Re-think your strategy.

Anthony
0
 
FalkeAuthor Commented:
@acperkins
you are right

i am using it for a login procedure. I give at the first time a "rang order" to the customer, on which queue rang he is. If he terminate before he enter, the datarecordset SQL has been deleted. Every time one has beend entered the other will be informed about the new "rang in the queue", that's the idea.

@acperkins
i do it with an error Handling but i would like to know, if there is a "better way" to know, if this recordset has been deleted.

Falke
0
 
Anthony PerkinsCommented:
>>i do it with an error Handling but i would like to know, if there is a "better way" to know, if this recordset has been deleted.<<

I think that may be your best bet.

Anthony
0
 
FalkeAuthor Commented:
your best bet?
0
 
TigerZhaoCommented:
why not use SAME recordset object between tow form(or somewhere you operate data)?
0
 
aikimarkCommented:
Falke,

After re-reading your question and all the comments, it appears that you ARE NOT deleting the recordset.  Rather, you are rebuilding the recordset when you execute the VB code the second time.

Since you are using the same SQL to build the recordset, this should not cause a problem, other than flicker on bound list, combobox and grid controls.

===========================================
We (both you and us experts) need to step back from the code a moment and understand what you WANT your code to do.  In other words, we experts need to understand the context in which this code is run and why you are running it twice.
0
 
FalkeAuthor Commented:
@aikimark
you are right but i whill show you a partial of my code :

...
Temp2$ = "SELECT * FROM [Login]"
Set SQLDef2 = PDatenbank.CreateQueryDef("", Temp2$)
Set DatenRecordSet2 = SQLDef2.OpenRecordset()
   
Dim bOKNeu As Boolean
While Not DatenRecordSet2.EOF
   Temp$ = "SELECT * FROM [Login]
   Set SQLDef = PDatenbank.CreateQueryDef("", Temp$)
   Set DatenRecordSet = SQLDef.OpenRecordset()
       
        while not datenrecrdset.eof
          'gives the user the actuall queue number
          'if user has gone before entering into theprogramm datenrecordset must be deleted but
            DatenRecordSet.MoveNext
            Wend
        End If

 .....

Falke
0
 
aikimarkCommented:
Falke,

I have two recommendations for you:
1. Stop using the temporary querydefs to open your recordsets.  Instead, use the Openrecordset method of the database object. (see the 07/14/2002 04:59AM PST example)

2. If you want to see the position of the user's row in the LOGIN table, then position to their row and display that row's AbsolutePosition property.  If you want to display the relative position then display AbsolutPosition/RecordCount.   Please be aware that the Absoluteposition property is zero based.  Also be aware that you might have to MoveLast and MoveFirst in order to set the RecordCount property to accurately reflect the number of rows in the recordset.  You don't need a second recordset.

Hint: A .FindFirst method for the user id is faster than a .MoveNext inside a loop.

Note: The While...Wend construct is going away.  In VB.Net, this isn't supported.  There is a While...End While construct.  I don't know if it is still true, but the While...Wend used to be less efficient than the Do...Loop construct.  Two other advantages of the Do...Loop are the ability to use UNTIL or WHILE and the ability to check the condition after the first (and subsequent) iterations.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 7
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now