Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
 
  HOME    TRACKER    Visual Basic




ADODB Recordset Update


Hi:
I have the following piece of code which works well under SQL Server 6.5. But it gives a "Cursor is Read Only" error at the Update statement under SQL Server 7.0.

Set rsheader = New ADODB.Recordset
rsheader.Open strSQL, cnnODBC, adOpenDynamic, adLockOptimistic
Do While Not rsheader.EOF
rsheader.Update "Status", "P"

' more code here

rsheader.movenext
loop

Could somebody let me know what is wrong in the above code?
Thanks.




View Complete Forum Thread with Replies

See Related Forum Messages: Follow the Links Below to View Complete Thread
Problem With Update Recordset With ADODB
Hi all,

I'm trying to update a field of a recordset object but without results

Here's my code

'############
' Set Variables
Set myConn = New ADODB.Connection
Set myRSCalDate = New ADODB.Recordset

myConn.Provider = "ASAProv"
myConn.ConnectionString = "Data Source=Calibration Data;UID=**;PWD=**;"
myConn.CursorLocation = adUseClient
myConn.Mode = adModeReadWrite
myConn.IsolationLevel = adXactCursorStability
myConn.Open

myRSCalDate.CacheSize = 2000

'Query for the "Kalibrierdaten"
myRSCalDate.Source = _
"SELECT DISTINCT " _
& " Inventory.I4201, Inventory.I4202, Inventory.I4203, CalResults.C2501, CalResults.C2502, " _
& " Inventory.I4204, Calibration.C2339, Calibration.C2341 " & _
"FROM " _
& " (Inventory INNER JOIN Calibration ON Inventory.MTAG=Calibration.MTAG) " _
& " INNER JOIN CalResults ON Calibration.CTAG=CalResults.CTAG " & _
"WHERE " _
& " Inventory.I4201 = '" & TxtKarteinummer.Text & "'" _
& " AND Calibration.C2339 = 1 " & _
" ORDER BY " _
& " CalResults.C2502 ASC"

myRSCalDate.ActiveConnection = myConn
myRSCalDate.CursorType = adOpenDynamic
myRSCalDate.LockType = adLockOptimistic
myRSCalDate.Open

myRSCalDate.MoveFirst

myRSCalDate.Fields("C2501").Value = "test"
myRSCalDate.Update

myRSCalDate.Close
myConn.Close
'##############
i do not get back any kind of error, but the field does not update

Any hints?

Thanks

Update Problem With ADODB Recordset
I am having problem updating ADODB recordset.
Two programs are updating on the same table at the same time on 2 slow (and old) computers. Very frequently an error message appear:

[Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.

Update A Field Size In A Adodb Recordset
I have opened a recordset from a database where I only have read only access to .... but for ease of use I am updating fields in the recordset before printing to the user....

what I am having troubles with is that one of the updates is larger than the defined size of the field... how can I update the size of this field....?

ADODB: Adodc.RecordSet Or Adodc.Command Is More Efficient Way For Update Table
Friends,

My database is working in Corporate office, where more than 100 hundreds of users are simultaneously accessing the database. is it better to use a SQL procedure with Adodb.Command to update table or Adodb.RecordSet.

The problem I'm facing in RecordSet is 'DeadLock' and unexpected result.

My doubt is that " which method is faster and efficient, Command or Recordset".

Difference Between Using Recordset And ADODB.Recordset
Hi,

When accessing a DB using ADO, whats the difference between using the AODB prefix and not using it, for example setting a variable as "Dim rs as Recordset" and "Dim rs as ADODB.Recordset" ?

Thanks,

John

ADODB Delete And Update
Hello....

I use this code to add new record to database:


Code:
Dim Tabela As ADODB.Recordset
Dim SQL As String
Set Tabela = New ADODB.Recordset

SQL = "SELECT * FROM RegPac"
Tabela.Open SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Register.mdb", adOpenKeyset, adLockOptimistic

Tabela.AddNew

Tabela!RegPriimekIme = txtPriimekIme.Text
Tabela!RegNaslov = txtUlicaSt.Text
Tabela!RegKraj = txtKraj.Text
Tabela!RegTelefon = txtTelefon.Text
Tabela!RegRojDatum = txtDatumRojstva.Text


Dim Datum As Date
Datum = txtDatumRojstva.Text

Tabela.Update
Tabela.Close

Set Tabela = Nothing
The recordset is filled in ListView... with this code:


Code:
Dim X As Long

Dim Tabela As ADODB.Recordset
Dim SQL As String
Set Tabela = New ADODB.Recordset

SQL = "SELECT * FROM RegPac"
Tabela.Open SQL, "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "Register.mdb"


X = 0

With Tabela
If .EOF And .BOF Then
Me.MousePointer = vbArrow
Exit Sub
End If
.MoveFirst
Do Until Tabela.EOF

listSeznam.ListItems.Add.Text = !RegPriimekIme
listSeznam.ListItems(X + 1).SubItems(1) = !RegNaslov
listSeznam.ListItems(X + 1).SubItems(2) = !RegKraj
listSeznam.ListItems(X + 1).SubItems(3) = !RegTelefon
listSeznam.ListItems(X + 1).SubItems(4) = Format(!RegRojDatum, "dd.mm.yyyy")
listSeznam.ListItems(X + 1).Tag = !ID

X = X + 1
.MoveNext
Loop
End With
Now I want to select specific record in ListView and delete it with Recordset.Delete method. How should I show Adodb wich record in ListView
has to be deleted. With the same way I will update record...

Thank you for helping...

Cancel DB Update Using ADODB
Hi,
This is what I am using right now:


VB Code:
Private Sub Command2_Click()On Error GoTo ERRoRS.AddNewFor Each Field In oRS.FieldsoRS.Fields(Field.Name) = InputBox("Please give a value for : " & Field.Name, "INSERT A VALUE FOR FIELD")Nextres = MsgBox("Do you want to save this change?", vbYesNo, "Save it?")If res = vbYes ThenoRS.UpdateElseoRS.Cancel 'HERE IS THE PROBLEMEnd IfoRS.RequeryExit SubERR:MsgBox "ERROR ENCOUNTERED!!" & vbCrLf & ERR.Description, vbCriticalEnd Sub


When oRS.Cancel is called the changes are saved in the DB. Have also tried:
oRS.CancelUpdate

Please help. I am giving the user the option to save the changes in the db or not? What am i dong wrong, or how to do it...

Recordset.AddNew Doesnt Clear Datagrid After Recordset.Update
I have a Parent/Child recordset created using the SHAPE construct as the REcordSource property of ADO control. In the Form_LOad event, i have set the datasource of the datagrid to the Child chapter of the recorset

Set DataGrid1.DataSource = adoPrimaryRS.Recordset("ChildCMD").UnderlyingValue
datagrid1.refresh

For simplicity, the form has textboxes with each datasource property set to adoPrimaryRS - the ParentCMD. The form also has an array button named cmdAction with the index 0=Add and index 3=Update button to add new records and update existing records respectively.

Private Sub cmdAction_Click(Index As Integer)
   With adoPrimaryRS
   Select Case Index

      Case 0 'Add new records to the database

         If cmdAction(0).Caption = "&Add" Then
            varBookMark = .Recordset.Bookmark
            .Recordset.AddNew
            cmdAction(0).Caption = "&Cancel"
            txtTransDate(0).SetFocus
         Else
            .Recordset.CancelUpdate
            If varBookMark > 0 Then
               .Recordset.Bookmark = varBookMark
            Else
               .Recordset.MoveFirst
            End If
            cmdAction(0).Caption = "&Add"
            txtTransDate(0).SetFocus
         End If


   Case 3 'Update the record with the changes

         MsgRes = MsgBox("Save changes?", vbQuestion + vbOKOnly, "Save Prompt")
         If MsgRes = vbCancel Then Exit Sub
         .Recordset.Update
         varBookMark = .Recordset.Bookmark
         .Recordset.Requery
         If varBookMark > 0 Then
            .Recordset.Bookmark = varBookMark
         Else
            .Recordset.MoveLast
         End If
         cmdAction(0).Caption = "&Add"

OBSERVATIONS:
1. IF i click on the Add button (invoking Recordset.Addnew method) before i click on the Update button, the textboxes and the datagrid bound with the recordset will clear its contents.
2. If i click on Update button the changes are saved into the database. But, if i click on the Add button, the textboxes contents will be cleared off but the datagrid's contents will not be cleared, instead the contents will remain as if the Addnew method of the recordset was not invoked. I'm sure that the Addnew method was invoked the fact that the textbox's contents was cleared. And, if i do a MoveNext or MovePrevious, the textbox's contents will changed from record to record but the datagrid's is not.

What is the reason behind this recordset behaviour? How do i change my code to correct this problem?

Thanks in advance,


URGENT (srry) ->ADODB Update Many
hi there,

i have a little question..
I have a database with groups..
some contacts are in groups..
now i want.. for example to change..
all the contacts that have group name "group2"
change to group name "group5"

How can this be done?
thnx

Add/Update Records In Datagird Through ADODB
Hi all, I have a query, i have collected records from two table andshown in a datagrid, now I want to Update a particular colum in datagridI'm using ADODB . plz help me in this context .thanx

Oxid Arc,

ADODB How To Find And Update Records
I have a visual basic 6 application that saved data in the wrong Access fields.
Ex. Table: Expenses
    form field "personal" saved in Expenses.Business
    form field "business" saved in Expenses.Personal

I need to create an executable to send to customers to correct this problem.  I read that RecordSets and Cursors shouldn't be used (multi-user concerns).  How can I do this?  (ADODB)

I thought I would do the following but don't know how to code it.
-query table for records where personal or business are not blank
-save data into temporary variable
-update correct field
-loop thru the rest of the records
Thanks for the help.

UPDATE Database Adodb Problem
hie guys, im having trouble with updating an access database. Here is my code below. userAns and QuesNo are fields in the database and userAns and ques are variables that I have declared and will get values from textboxes etc. For some reason the details are not being updated into the database

        Dim objConn As New OleDbConnection _
            ("Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "User ID=Admin;" & _
            "Data Source=db2.mdb")
        objConn.Open()

        Dim objAdapter As New OleDbDataAdapter
        objAdapter.UpdateCommand = New OleDbCommand( _
            "UPDATE Ques_Table SET userAns= '" & userAns & "' Where QuesNo ='" & ques & "'", objConn)

        objConn.Close()

if anyone has a template of the SELECT, INSERT and other syntax for access db's please post as well


ADODB Add/edit/update/delete
hi, i'm new in vb city and i have a question

I'm using adodb connection with MSAccess and i wanna know if I can edit the field and alter without having to delete it

bellow follows how i'm doing it....


Public Sub alteracliente() //' this is called by the press of the button cmd_alterar
If db.State = adStateOpen Then
  db.Close
    db.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=C:db.mdb;Jet OLEDB: Database Password=""
    db.Open
Else
    db.ConnectionString = "Provider=Microsoft.jet.oledb.4.0;data source=C:db.mdb;Jet OLEDB: Database Password=""
    db.Open
End If
If rs.State = adStateOpen Then
  rs.Close
    rs.Open "select * from clientes where codigo_cliente=" & frmClientes.txt_codcliente.Text & "", db, adOpenDynamic, adLockOptimistic
Else
  rs.Open "select * from clientes where codigo_cliente=" & frmClientes.txt_codcliente.Text & "", db, adOpenDynamic, adLockOptimistic
End If
  rs.Delete
    Call verificacliente
Set db = Nothing
Set rs = Nothing
End Sub


the call "verificacliente" checks either the cliente already exists by cnpj or company name and if the textboxes are empty or its value is not correct......


do you have a better solution for this ?
post any code error as well.....thanks

ADODB - Data Update Delay
[RESOLVED]

Well, I think I found out my problem. Since the database was located on a server share (\serversharedbfile.mdb), the server was caching the data that I was writing to the database. I was reading from the database file before the cached data was written to the hard drive.

What I discovered is that I could modify the 'File and Printer Sharing...' settings of the Ethernet Connection to 'Minimize memory used' which would decrease the size of the cache and write data quicker. It still takes an eye-blink or two to update, but it's a whole heck of a lot quicker than it was before. The server is a Windows 2000 server (SP4) which mainly runs our Intranet site. After making this change, I haven't noticed any performance issues on other applications or the Intranet site.

=========================================================

I'm creating a program that will generate a priority list for jobs to run in our machine shop. The database is very small (2 tables) and so far, the program is quite crude (I just started it yesterday).

I'm using an ADODB Connection to connect to an Access Database located on a share on one of our servers. I can read and update the database file quickly with no problems, but there is a delay in the data update in my program.

I'm using the MS ADO Datagrid 6.0 to display information and binding it to a Recordset which is coded (not a control). When I right click on an item in the list, I have options to move it up or down (increase / decrease the priority). My routine does this just fine, but it takes 3 to 4 seconds for the data to actually refresh, even when I click on Refresh List.

I've tried several different ways of updating the data in hopes of finding one that works, but thus far I have found nothing.

This code is what I currently have.
Code:Public Sub PrioSw(UpPrioID As Long, DnPrioID As Long)
  Dim dbConn As New ADODB.Connection
  Dim dbRS As New ADODB.Recordset
  Dim dbRS2 As New ADODB.Recordset
  dbConn.Mode = adModeReadWrite
  dbConn.Open ConnStr
  Dim UpPNum As Long, DnPNum As Long
  
  ' Get the current priority numbers
  dbRS.Open "SELECT PriorityNo FROM Priorities WHERE PID = " & UpPrioID, dbConn, adOpenDynamic, adLockOptimistic
  dbRS2.Open "SELECT PriorityNo FROM Priorities WHERE PID = " & DnPrioID, dbConn, adOpenDynamic, adLockOptimistic
  UpPNum = dbRS("PriorityNo").value
  DnPNum = dbRS2("PriorityNo").value
  
  ' Update the priorities
  dbRS.Update "PriorityNo", DnPNum
  dbRS2.Update "PriorityNo", UpPNum
  
  DoEvents
  
  dbRS.Close
  dbRS2.Close
  
  dbConn.Close
  Set dbRS = Nothing
  Set dbConn = Nothing
End Sub

This code was my original version:
Code:Public Sub PrioSw(UpPrioID As Long, DnPrioID As Long)
  Dim dbConn As New ADODB.Connection
  Dim dbRS As New ADODB.Recordset
  dbConn.Mode = adModeReadWrite
  dbConn.Open ConnStr
  Dim UpPNum As Long, DnPNum As Long
  
  ' Get the current priority numbers
  dbRS.Open "SELECT PriorityNo FROM Priorities WHERE PID = " & UpPrioID, dbConn, adOpenDynamic, adLockReadOnly
  UpPNum = dbRS("PriorityNo").value
  dbRS.Close
  dbRS.Open "SELECT PriorityNo FROM Priorities WHERE PID = " & DnPrioID, dbConn, adOpenDynamic, adLockReadOnly
  DnPNum = dbRS("PriorityNo").value
  dbRS.Close
  
  ' Update the priorities
  dbConn.Execute "UPDATE Priorities SET PriorityNo = " & UpPNum & " WHERE PID = " & DnPrioID, , adExecuteNoRecords
  dbConn.Execute "UPDATE Priorities SET PriorityNo = " & DnPNum & " WHERE PID = " & UpPrioID, , adExecuteNoRecords
  
  dbConn.Close
  Set dbRS = Nothing
  Set dbConn = Nothing
End Sub

Either way, the routine is called as so:
Code:Dim PrioUp as Long, PrioDn as Long
dgPriorities.Col = 0
PrioUp = CLng(dgPriorities.Text)
dgPriorities.Row = dgPriorities.Row - 1
dgPriorities.Col = 0
PrioDn = CLng(dgPriorities.Text)
Call PrioSw(PrioUp, PrioDn)
DoEvents
Call FillTableFromDB ' just refreshes the recordset / datagrid

At this point in time, I'm up for just about any suggestions anyone can offer. I really want the user to be able to right click on the list, select 'Move Up' and have the list refresh immediately, not have it take several seconds to update.

Michael Lackey
Group I.T. Technician
L&P Machine Products
"There are only 10 types of people in this world; those who understand binary and those who don't."
http://www.genieshouse.com

Edited by - LPITGuy on 8/8/2005 11:37:58 AM

ADODB Recordset .. Why Would You
In a different post, the following code is observed. No matter how long I have used and/or played with VB6, I have always wondered why you would use
VB Code:
Dim rsInformation as ADODB.RecordsetSet rsInformation = New ADODB.Recordset
Why wouldn't you just use
VB Code:
dim rsInformation as new ADODB.Recordset
I use it this way all the time and never have a problem! I don't use the Set ....

Is there something going on in the background I should be concerned with?

ADODB.Recordset
I need to create a temporary ADODB recordset in my VB program.
This recordset is in memory only, it is not ties to any external
table. I need to be able to do a keyed read on the contents
and update some of the fields. I cannot find a good example
of the in MSDN. I found some things close to it but not quite.
Is what I am after possible? Can any one point me to a place to
get some examples? thanks
Tom

ADODB.Recordset Help
hello, i was wondering if it was possible to set a field to a specific Word Document from VB using an ADODB recordset. it can be done "manually" using just Access, but i am creating an app that needs to do this behind the scenes.

thanks in advance for any help,
PJ

Help With ADODB.Recordset
hello, i was wondering if it was possible to set a field to a specific Word Document from VB using an ADODB recordset. it can be done "manually" using just Access, but i am creating an app that needs to do this behind the scenes.

thanks in advance for any help,
PJ

0 With ADODB Recordset
I am trying to collect datas from a Sybase DB and to
populate a ADODB Recordset with those datas. I inconter
the following problem : all numbers have theyre zeros at
the right removed. For example, a 1000 in my Sybase DB
will appear in my Recordset as 1.

I precise that I am working with Excel 2002 on Windows XP
and that my spreadsheet worked perfectly with Excel 97
under Windows NT.

Has anyone already incontered this issue ?


Thanks in advance for your help.

Stefen.

ADODB.Recordset
Hi.
I have a program that reads a Database file (*.dbf - dBase IV) to a ADODB.RecordSet Variable and I set the Datasource property of my MSHFlexGrid to this recordset to diplay it.
On this level program works correctly. I know how to open a DB, which connection to set etc.

I have a problem with saving changes. I dont know whow to create a connection and how to perform this save. I want to write a procedure that saves all changes made on the grid to the database that i had opened earlier. I think that there is a simple way to do that but I am struggling with it for over a week. I have been posting about that elsewhere but nooned had helped me.

Here is some of my code
opennig DB:

Private Sub OpenDB(sFile)

Dim conn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rs As New ADODB.Recordset

conn.mode = adModeShareDenyNone
conn.CursorLocation = adUseClient
conn.Open ("Driver={Microsoft dBASE Driver (*.dbf)};" & _
"SourceDB=" & sFile & ";" & _
"SourceType=DBF;Exclusive=No;BackgroundFetch=No;Collate=Machine;")

Dim strSQL As String: strSQL = "SELECT * FROM " & sFile

Set cmd.ActiveConnection = conn
cmd.CommandType = adCmdText
cmd.CommandText = strSQL
rs.LockType = adLockOptimistic
rs.CursorLocation = adUseClient
rs.CursorType = adOpenKeyset
rs.Open cmd

Set MSHFlexGrid.DataSource = rs

End Sub


Please help me!!!
Thanks in advance.

ADODB Recordset
I am belatedly catching up on ADODB recordsets (with A2K) and have hit a speedbump.

I am attempting to open a named query with a field that references a dropdown control (Forms!MyForm!MyCombo) as criteria.  I get an error message advising me to use a Valid SQL statement beginning with SELECT, DELETE, etc.

The query will return a recordset on its own.

If I change the query's control reference to a hardcoded string, my ADODB recordset opens without difficulty.  This obviously lacks flexibility.

I have also run through all the ADODB recordset flavors (except batch) with the same un happy results.

For grins, here is the code snippet

Dim cnn as ADODB.Connection
Dim rs as ADODB.Recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = = adUseServer 'also tried Client side
rs.Open "qrySet", CurrentProject.Connection, adOpenStatic, adLockReadOnly

The code halts on the rs.Open line.  Any suggestions to keep me from retreating to DAO will be appreciated.

Thanks
Bill





ADODB.Recordset
hi i like to how i use the two sql statement in ADODB.Recordset..
this my code

Public Function sqlGetGrpEmailInfo(ByVal grpId As String) As ADODB.Recordset
On Error GoTo SQLERR
     Dim sqlString As String
     Dim sqlString1 As String
    sqlString = "SELECT customer_id FROM customer_group WHERE group_id=1"
    sqlString1 = "SELECT contactPerson & '(' & name & ')' as addresee, email FROM Customer WHERE ????"
 i like to use the retrieve customer_id to place in the condition where customer_id = customer_id in the sqlString1
    Dim rs As ADODB.Recordset
    Set rs = adoLib.getRecordSet(sqlString)
    Set sqlGetGrpEmailInfo = rs
SQLERR:
    If Err.Number <> 0 Then
        sqlErrorLog sqlString, Err.description
    End If
End Function




Edited by - evangeline.nah on 5/28/2004 2:05:27 AM

How Can I Get Recordset [ADODB]
First of all , sorry for my bad English.

Dim RS as Recordset
Dim RSADODB as ADODB.Recordset


How can i get the recordset of RSADODB to RS.

ADODB Recordset
I have got following doubt with the ADODB recordset

Ex:
dim recEx1 as new ADODB.recordset
dim recEx2 as new ADODB.recordset

set recEx1 = funGetRecords() 'Function returns the recordset after querying 'the table

recEx1.Filter = "field1 = 'ABC'" 'filter the records where field1 = 'ABC'

set recEx2 = recEx1

recEx1.filter = adFilterNone 'removes the filter
Msgbox recEx1.recordcount
Msgbox recEx2.recordcount

Suppose for the argument sake recEx1 contains 10 record and after filtering it has got 5 records. But I will get both Output (Message box) as 10 records and not 5 for recEx2.recordcount. Why is this happening ?.
When I specify recEx2 as NEW ADODB.recordset why recEx2 points to recEx1 ?.
What is the difference between 'recEx2 as new ADODB.recordset' and recEx2 as ADODB.recordset (without new)' in this contest ?.

Please let me know if possible, how can I make sure that when filter is removed from recEx1, recEx2 should have 5 records and not 10 (as in recEx1 without filter )?. Also If I make changes to recEx2 (assuming 2 different recordset) how to make sure that when I update in recEx2 will be reflected in recEx1...

thanks in advance....

Need Help With ADODB Recordset.
How hard is this to do?

I have a recordset in memory. I need to execute a SQL statement on it and generate a new recordset in the process. Is this possible WITHOUT having to first save the RS to a database.

what I need to do is create a RS with all records that contain duplicate entries within a particular field.

Any help would be greatly appreciated.
Thanks,
Tony

Question About ADODB.Recordset
dim rs as new ADODB.Recordset
SQL="SELECT *..."
rs.Open SQL,conn,....
temp=rs.GetRows

what is temp? dim temp as ____?
lets say the column "Hour" is empty in the database...when I do the line:
msgbox rs("Hour")
I get an run time error: "Invalid use of null"
I tried also the line:
if rs("Hour")=null then msgbox "empty"
and this line didnt get me a run time error but it didnt open the msgbox!
so, what is the value of rs("Hour") when the cell is empty.
p.s. with cells that are String cell in the database even if they are empty the line works.

Adodb Recordset Recordcount
hello all,

nothing critical, but i cant understand why when i've coded a recordset through an ADODB connection i cant return the number of records in the recordset?

it returns -1 records in the set?? this is oarticularly bizzare since i can page through, update and retrieve values for the entire recordset...

can anyone shed any light?

thanks,
A

"Smoke me a kipper I'll be back for breakfast!"

ADODB.Recordset And Requery
i have a program that i make lot of queries
and beacuse of that i tried to do :

Code:
If rs.State = adStateOpen Then
rs.Requery update
Else
rs.open update
End If
but i recive type mismatch when i do requery (the syntax in the update is ok i chekced it withthe db)
any idea what can be the problem?
thnaks in advance
peleg

Adodb Recordset Recordcount -1
I am a manic of dinamic settings, so I made a dinamic connection based on few varible like:


Code:
Dim cnnTmp as new adodb.connection
dim recTmp as new adodb.recordset

set cnntmp = new adodb.connection
cnntmp.connectionstring ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & " est.mdb;Persist Security Info=False"
rectmp.open mysqlstring, cnntmp
so far the code is just fine but after this code if I try:
msgbox rectmp.recordcount it returns -1 even that I have let say 3 records on the table.
of course I made a little piece like:

Code:
dim i as integer
do until rectmp.eof
i=i+1: rectmp.movenext
loop
but my question is:
How can I pass the do => loop code and simple after I open the recordset to have my real recordcount at the function rectmp.recordcount and not just -1?

Source: ADODB Recordset
In my application when I click on my save button I get the following message:

Error Code: 3265
Description: Item caanot be found in the collection corresponding to the requested name or ordinal.
Source: ADODB Recordset.

I know this is a long shot, but here is the coding for the save button:

Private Sub cmdSave_Click()

On Error GoTo ErrorHandler
If mblnEdit = True Then
mblnEdit = False
If strEditRec <> txtSection.Text Then
LoadDataInControls
End If
End If
If ValidData Then
If mblnAdd Then rsRecordSet.AddNew
WriteData
rsRecordSet.Update
mblnAdd = False
SetButtons True
txtSection.Enabled = True
'txtSection.SetFocus
CmdAdd.SetFocus
LockTbox
'ClearControls
CmdEdit.Enabled = True
CmdDelete.Enabled = True
End If
Exit Sub
rsRecordSet.Close
rsRecordSet.Open

ErrorHandler:
DisplayErrorMsg
If rsRecordSet.EditMode = adEditAdd Then rsRecordSet.CancelUpdate

End Sub

Thanks in advance

Empty ADODB.Recordset
hi, how do i know if the ADODB.Recordset returned by a query is empty?

thanks.

ADODB.Recordset And MSHFlexGrid
im trying to add data from a recordset into my MSHFlexGrid.


Code:
Private Sub cmdOk_Click()
Dim myConn As ADODB.Connection
Dim MyRecSet As ADODB.Recordset
Set myConn = New ADODB.Connection
myConn.ConnectionString = "provider=microsoft.jet.OLEDB.4..0;Data Source=N:db2.mdb;"
myConn.Open

Dim strSQL As String

strSQL = "SELECT * FROM Member"

Set MSHFlexGrid1.Recordset = MyRecSet

End Sub

this doesn't work.

Any ideas?

sam

Sorting ADODB Recordset
Hi everyone,

I have a recordset that consists of the following column, called EarnCode.
rg1
rg1
rg2
rg2
tt1
tt1
tx2
tx2
tx1
tx1

When I try to sorted the recordset using( parsed.Sort = "EarnCode ASC")
The recordset is sorted in the following manner
rg1
rg1
rg2
rg2
tt1
and so. on.... I would like the recordset to be sorted by the number all the rg1,tt1,tx1 at the top, then the rg2,tt2,tx2.

Any Idea on how I can achieve this?

Clearing An ADODB Recordset
How do I clear the data in a recordset.

I'm using one recordset of multiple queries depending on user input but it looks like it appending the result of the query onto the end if any data that already exists?

Cheers.

MSFlexGrid And ADODB Recordset
Hi,

If I use a MsFlexGrid and a Data Object I can assign the DataSource proprty of the flexigrid to the data object and when the app loads up, the data control's recordset is populated and the flexigrid is refreshed, the flexgrid is populated with the data from the recordset.

How do I get this to happen using the MSFlexGrid and an ADODB record set?

I've tried....


Code:
Public DBConn As ADODB.Connection
Public DBRecSet As ADODB.Recordset
.
.
.
Set DBRecSet = DBConn.Execute("select SURNAME,AGE,GEN FROM PEOPLETABLE ORDER BY SURNAME")
Set MSFlexGrid1.DataSource = DBRecSet
but I get the following Error...
Object variable or With block variable not set

when I hit debug the Set "MSFlexGrid1.DataSource = DBRecSet" line is highlighted

I've looked in the DBRecSet and the data is there so the SQL worked, I just can get the link sorted between the recordset and the grid.

Any idea how I get the flexgrid tied to the ADODB recordset?

Keep Track Of An Adodb Recordset
I have an application with a routine that generates a recordset and then passes that to a report window. I would like to close the recordset when the report is closed, but I can't because I lose track of the recordset. How do I identify a distinct recordset so I can tie to a form so it can be closed later. Here's a simplified version of the code:


Code:
sub doReport()
Dim myRecordset AS ADODB.RECORDSET
Dim myReport as reportWindow

SQLQuery = "SELECT * FROM myTable"
DB.QUERY SQLQuery, myRecordset 'This populates my recordset

Set myReport.Recordset = myRecordset
myReport.show
End sub


What I'd like to do is something along the lines of:


Code:
private myRecordsets(10,10)

sub doReport()
Dim myRecordset AS ADODB.RECORDSET
Dim myReport as reportWindow

SQLQuery = "SELECT * FROM myTable"
DB.QUERY SQLQuery, myRecordset 'This populates my recordset

Set myReport.Recordset = myRecordset
addReport myReport.hwnd, myRecordset.SOMEIDENTIFIER
myReport.show
End sub

sub closeRecordset(formID)
'Finds the matching recordset for the given formID and closes it
End sub

sub addReport(formID, recordsetID)
'Add the formID and recordsetID to array
End sub


Hopefully that makes sense, I just don't know what to use to uniquely identify a recordset (like form.hwnd) in memory so I can refer back to it in the close routine.

Any suggestions?

VB6 ADODB.Recordset Question
Hi All,

Just wondering if theres any easy way of checking if a recordset object is already open or not.

Tried IF Not rs.EOF or Not rs.BOF
but this can not be done as the rs might not be opened in the first place.

I want to be able to check if a rs is open and if so close it.
Any ideas ?
Thanks.

Edit ADODB Recordset
Hi,
I am using VB6 and MS Access database. My ADODB recordset is rsInv. now with DAO i used to edit that recordset with rsInv.edit but that does not work with ADODB recordset. how can I edit ADODB recordset????

How To Get Checkbox Value Using ADODB Recordset
Hi All,

am not been able to get the value of Checkbox

chkwet.DataField = rs.Fields("Qty_wet_chk")

.value alos not Working

Which Property i have to use

Thanks

ADODB.Recordset And Requery
i have a program that i make lot of queries
and beacuse of that i tried to do :

VB Code:
If rs.State = adStateOpen Then      rs.Requery update    Else      rs.open update    End If


but i recive type mismatch when i do requery (the syntax in the update is ok i chekced it withthe db)
any idea what can be the problem?
thnaks in advance
peleg

How To Tell If Adodb.recordset Is Open
how can i tell programatically if an adodb.recordset is open?

Thanks!

ADODB Recordset (Easy One)
VB Code:
Private Sub Command1_Click()    Dim results As ADODB.Recordset        Set results = New ADODB.Recordset           ' Create new Recordset    If Not (results.BOF Or results.EOF) Then        MsgBox results.Fields.Count    End IfEnd Sub


Why doesn't the above code work? It gives me an run-time error "operation not allowed when object is closed". However if I add a field to the recordset then it works. I need to create an EMPTY recordset for a function. Whats the problem here?

Udate / Add A ADODB Recordset
Dear Vb users,

Trying to update a table with the help of a .mdw file, we are using the next code:

Dim Arst As ADODB.Recordset
Dim oConn As ADODB.Connection
Set oConn = New ADODB.Connection

oConn.Open "Driver={Microsoft Access Driver (*.mdb)}; Dbq=D:MyDb.mdb; SystemDB=D:MyDbSys.mdw; Mode=ReadWrite"

Set Arst = New ADODB.Recordset
Arst.Open "select * from MyTbl", oConn, adOpenStatic ĎadOpenDynamic / adOpenForwardOnly / adOpenKeyset
Arst.AddNew 'xxxxxgives a problem! ! ! ! ! ! !
Arst.Update

It is possible to read the information from the table but it is not possible to Add or Edit the recordset.
What do we have to do extra to do this.

Nice regards,

Michelle.

Deleting From An ADODB Recordset
I am trying to delete an entire record given a
value selected from a combo box. But I keep getting
the following error "The operation requested by
the application is not supported by the provider"

Any help would be much appreiated.

Regards Sean

Set rst = New ADODB.Recordset
rst.Source = "Select * FROM officer WHERE
officerinitials='" & Combo1.Text & "'"
rst.ActiveConnection = Con
rst.CursorLocation = adUseClient
rst.CursorType = adOpenDynamic
rst.Open
rst.Delete adAffectGroup
rst.Update
rst.Close
Set rst = Nothing

Cannot Load ADODB.recordset
hi, i'm new to VB and tried to run a simple file which accesses a database but got the error missing library. Then on the preferences menu, it says MISSING: Microsoft Activex Data Objects 2.8 Library. What did I missed? Please help....

ADODB Recordset For More Than 1 Subform
Is it possible to open a recordset with multiple subforms
i.e append more than 1 ChildCMD.

CODESet adoPrimaryRS = New Recordset
adoPrimaryRS.Open "SHAPE {" & Sv1 & "} AS ParentCMD APPEND ({" & Sv2 & " } AS ChildCMD RELATE LotID TO LotID) AS ChildCMD", db, adOpenStatic, adLockOptimistic

Problem With ADODB.Recordset
Hi,
       Hi,
    i have encountered a new problem in VB6.0. Actually when i am executing one simple query "select * from table_name order by field_name" in oracle sql editor it is returning 14 rows but when i am executing the same query to open the ADODB.Resultset , it is returning only 4 rows, (means recorcount=4). if anybody have prior exepriance on this please help me to understand why this is happening?.

I am using windows XP Service Pack II, VB6.0, ORACLE 9.2.0.1.0

Note:

That table i have created before going to execute Rs.open statement from VB6.0.

just above Rs.Open statement i have created the table using CN.Execute "Create table....."

then i have inserted some rows, here i have introduced CN.BeginTrans and i have entered CN.ComitTrans all are executed properly.

MSFlexGrid ==&gt; ADODB.Recordset
Please help to dinamycly export data from MSFlexGrid to ADODB.Recordset

THANKS YOU

___________________________
© Spectator
E®!©'s Productions Ltd.
© 1998-2008

ADODB Recordset Open
I'm working on a database project. I have been following the FAQ 'My First Database' and I'm stuck on the recordset open.

I added the Miscrosoft Active X Objects Library 2.6 to the projects references.

In the following code I get a 'Complie Error - Expected: =' on my .open statement.

Code:
Dim strSQL As String
Dim cnConnection As ADODB.Connection
Dim strConnection As String 'Define the connection string for use in the ADODB Connection
Dim rsTotal As ADODB.Recordset

Private Sub Form_Load()

    Set cnConnection = New ADODB.Connection
    With cnConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source= " & App.Path & "Blending.mdb"
        .Open
    End With
    Set rsTotal = New ADODB.Recordset
   
    strSQL = "Select ComponentItem, Description, "
    Select Case frmMain.txtShift
        Case "1st"
            strSQL = strSQL & "Qty1stShift, Wgt1stShift "
        Case "2nd"
            strSQL = strSQL & "Qty2ndShift, Wgt2ndShift "
        Case Else
            strSQL = strSQL & "Qty3rdShift, Wgt3rdShift "
    End Select
    strSQL = strSQL & " FROM Totals WHERE RecType = 'U'"
    strSQL = strSQL & " AND OPItem = '" & frmMain.lvSchedule.SelectedItem
    strSQL = strSQL & "' ORDER BY ComponentItem, Description,"
    Select Case frmMain.txtShift
        Case "1st"
            strSQL = strSQL & " Qty1stShift, Wgt1stShift "
        Case "2nd"
            strSQL = strSQL & " Qty2ndShift, Wgt2ndShift "
        Case Else
            strSQL = strSQL & " Qty3rdShift, Wgt3rdShift "
    End Select
       
   With rsTotal
      .Open(strSQL, strConnection, adOpenStatic, adLockReadOnly, adCmdText)
   End With

Base on some other examples I found I have also tried

Code:
   With rsTotal
       .ActiveConnection = cnConnection ' Attach recordset to connection
       .CursorLocation = adUseClient ' Make sure the data will be Updatable
       .CursorType = adOpenStatic ' Open recordset using SQL SELECT statement to select records
       .LockType = adLockReadOnly
       .Open (strSQL, cnConnection, .CursorType, .LockType, adCmdText)

   End With

I get the same message with either open statements. I don't see what I'm missing.

Thanks

Jules

Copyright © 2005-08 www.BigResource.com, All rights reserved