Help, Unable to add Records into Database

[CountPuchi]

Im using VB6 & Microsoft Access 2003.
I cant seem to add records it says Error "3251" Current Recordset does not support updating. This may be limitation from the provider, or of the selected Locktype"

Here is the code that's troubling me.

 Dim rsDataCust As ADODB.Recordset
 Dim DBConnectionCust As ADODB.Connection
 
 
Private Sub CmdMain_Click()
Unload Me
FrmMain.Show
End Sub

Private Sub CmdNext_Click()
        WriteRecord
         
        MsgBox "Patient successfully added !", vbOKOnly
        txtCustId.Text = " "
    TxtCustName.Text = " "
    TxtCustPhone.Text = " "
    TxtCustAdd.Text = " "
    TxtCustLID.Text = " "
    TxtCustIC.Text = " "
    TxtCustAge.Text = " "
     
End Sub

Private Sub Form_Load()
Dim rsData As ADODB.Recordset

Set DBConnectionCust = LoadDatabase(App.Path & "\LoginTest.mdb")
     
Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

End Sub

Public Sub WriteRecord()
    With rsDataCust
    .AddNew
    .Fields("cust_id") = txtCustId
    .Fields("cust_name") = TxtCustName
    .Fields("cust_phone") = TxtCustPhone
    .Fields("cust_add") = TxtCustAdd
    .Fields("cust_license") = TxtCustLID
    .Fields("cust_ic") = TxtCustIC
    .Fields("cust_age") = TxtCustAge
    
      
         
    .Update
        
    End With
    End Sub

And this is the module

Public DBConnectionCust As ADODB.Connection

Public Function LoadDatabase_Cust(ByVal DatabaseName As String, ByVal cust_id As Integer, ByVal cust_name As String, ByVal cust_add As String, ByVal cust_phone As Integer, ByVal cust_ic As Integer, ByVal cust_license As Integer, ByVal cust_age As Integer) As ADODB.Connection

Dim ConnectionData_Cust As ADODB.Connection
Set ConnectionData_Cust = New ADODB.Connection

ConnectionData_Cust.Provider = "Microsoft.Jet.OLEDB.4.0"
ConnectionData_Cust.ConnectionString = "Data Source = " & DatabaseName
ConnectionData_Cust.CursorLocation = adUseClient
ConnectionData_Cust.Open , cust_id, cust_name, cust_add, cust_phone, cust_ic, cust_license, cust_age, conn, adOpenDynamic, adLockOptimistic

Set LoadDatabase_Cust = ConnectionData

End Function

[Hack]

Moved from the CodeBank

[zeezee]

I haven't used a record set for insertion or updation. I always use SQLs to do that. (May be older method, but at least I know what I'm doing).

Public Sub WriteRecord()
    Dim STRSQL As String

    STRSQL = "INSERT INTO Customer(Cust_ID,cust_name,cust_phone,cust_add,cust_license,cust_ic,cust_age) VALUES (" & _
    "'" & txtCustId.Text & "','" & TxtCustName.Text & "','" & TxtCustPhone.Text & _
    "','" & TxtCustAdd.Text & "','" & TxtCustLID.Text & "','" & TxtCustIC.Text & "'," & TxtCustAge.Text & ")"
    
    DBConnectionCust.Execute STRSQL
 
End Sub

this does the same (I think) as the your previous method.

And you havent given the code for LoadDatabase , you have given LoadDatabase_Cust (It may not be important if its same as LoadDatabase_Cust)

BTW, try changing cursor type to adUserServer
like

ConnectionData_Cust.CursorLocation = adUseServer

(this shoud be changed in LoadDatabase accordingly)

[ThorSubak]

instead:

Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

use this:

rsDataCust .open "SELECT * FROM CUSTOMER",DBConnectionCust,adopenstatic,adopenoptimistic

[CountPuchi]

Sorry my bad!! I posted the wrong Module. Its suppose to be LoadDatabase_Cust


Anyway ive been tinkering for awhile and now i got *Type MisMatch* from

Dim rsDataCust As DAO.Recordset
 Dim DBConnectionCust As ADODB.Connection
--------------------------
  
Private Sub CmdMain_Click()
Unload Me
FrmMain.Show
End Sub
--------------------


Private Sub CmdNext_Click()
        WriteRecord
        rsDataCust.Update
        MsgBox "Patient successfully added !", vbOKOnly
        txtCustId.Text = " "
    TxtCustName.Text = " "
    TxtCustPhone.Text = " "
    TxtCustAdd.Text = " "
    TxtCustLID.Text = " "
    TxtCustIC.Text = " "
    TxtCustAge.Text = " "
     
End Sub
-------------------
Private Sub Form_Load()

 
Set DBConnectionCust = LoadDatabase_Cust(App.Path & "\LoginTest.mdb")
     

	
	
Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")
^                            ^                              ^                    ^
This line here is the mismatch

End Sub
-------------------------
Public Sub WriteRecord()
    With rsDataCust
     
        .Edit
      
        !cust_id = txtCustId
        !cust_name = TxtCustName
        !cust_phone = TxtCustPhone
        !cust_add = TxtCustAdd
        !cust_license = TxtCustLID
        !cust_ic = TxtCustIC
        !cust_age = TxtCustAge
         
     
    End With
    End Sub

The LoadDatabase_Cust Module

Public DBConnectionCust As ADODB.Connection

Public Function LoadDatabase_Cust(ByVal DatabaseName As String, Optional ByVal cust_id As Integer, Optional ByVal cust_name As String, Optional ByVal cust_add As String, Optional ByVal cust_phone As String, Optional ByVal cust_ic As String, Optional ByVal cust_license As String, Optional ByVal cust_age As String) As ADODB.Connection

Dim ConnectionData_Cust As ADODB.Connection
Set ConnectionData_Cust = New ADODB.Connection

ConnectionData_Cust.Provider = "Microsoft.Jet.OLEDB.4.0"
ConnectionData_Cust.ConnectionString = "Data Source = " & DatabaseName
ConnectionData_Cust.CursorLocation = adUseClient
ConnectionData_Cust.Open = ("Select * FROM CUSTOMER")

Set LoadDatabase_Cust = ConnectionData_Cust

End Function

For a better view ive included the forms etc in this rar.
Username : Admin
(Case sensitive) Password : Test
Login Test.rar

[ThorSubak]

Did you try to put some breakpoints here?

ConnectionData_Cust.Open = ("Select * FROM CUSTOMER")

just take that point.
Guess this help.

[zeezee]

ConnectionData_Cust.Open = ("Select * FROM CUSTOMER")

I think this is wrong.
open method requires a connection string as a parameter.
to use a SELECT querry, first you have to open the connection to the database.
Then use Connection.Execute, to execute any SQL querry (or any DB commands)

it should be just ConnectionData_Cust.Open

As for the Type mismatch, you are using a ADODB connection to populate a DAO recordset.
ADODB connection returns a ADODB record set. change the Recordset type or connection type

[CountPuchi]

ok i changed all the mismatched adodb with dao

however, now it says

Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

Where DBConnectionCust.Execute it highlighted execute as the missing function or variable

The module

Public DBConnectionCust As DAO.Connection

Public Function LoadDatabase_Cust(ByVal DatabaseName As String, Optional ByVal cust_id As String, Optional ByVal cust_name As String, Optional ByVal cust_add As String, Optional ByVal cust_phone As String, Optional ByVal cust_ic As String, Optional ByVal cust_license As String, Optional ByVal cust_age As String) As ADODB.Connection

Dim ConnectionData_Cust As DAO.Connection
Set ConnectionData_Cust = New DAO.Connection

ConnectionData_Cust.Provider = "Microsoft.Jet.OLEDB.4.0"
ConnectionData_Cust.ConnectionString = "Data Source = " & DatabaseName
ConnectionData_Cust.CursorLocation = adUseServer
ConnectionData_Cust.Open

Set LoadDatabase_Cust = ConnectionData_Cust

End Function

So i guess the problem is not in the module right? but what kind of variable is missing now.

[zeezee]

hmm, not sure why.
Can you post the Full code like in Post #5,
Cant be sure without seeing the declarations also.

[CountPuchi]

Okay here's the full code for it

Dim rsDataCust As DAO.Recordset
 Dim DBConnectionCust As DAO.Connection
 
  
  
  
Private Sub CmdMain_Click()
Unload Me
FrmMain.Show
End Sub

-------------------

Private Sub CmdNext_Click()
        WriteRecord
        rsDataCust.Update
        MsgBox "Patient successfully added !", vbOKOnly
        txtCustId.Text = " "
    TxtCustName.Text = " "
    TxtCustPhone.Text = " "
    TxtCustAdd.Text = " "
    TxtCustLID.Text = " "
    TxtCustIC.Text = " "
    TxtCustAge.Text = " "
     
End Sub
---------------------
Private Sub Form_Load()


Set DBConnectionCust = LoadDatabase_Cust(App.Path & "\LoginTest.mdb")
     
Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")  'DBConnectionCust,adopenstatic,adopenoptimistic



End Sub
-----------------------
Public Sub WriteRecord()
    With rsDataCust
     
        .Edit
      
        !cust_id = txtCustId
        !cust_name = TxtCustName
        !cust_phone = TxtCustPhone
        !cust_add = TxtCustAdd
        !cust_license = TxtCustLID
        !cust_ic = TxtCustIC
        !cust_age = TxtCustAge
         
     
    End With
    End Sub

[anhn]

That is a big messed up between DAO and ADODB.

Public Function LoadDatabase_Cust(ByVal DatabaseName As String, _
                                  Optional ByVal cust_id As String, _
                                  Optional ByVal cust_name As String, _
                                  Optional ByVal cust_add As String, _
                                  Optional ByVal cust_phone As String, _
                                  Optional ByVal cust_ic As String, _
                                  Optional ByVal cust_license As String, _
                                  Optional ByVal cust_age As String) _
                                  As ADODB.Connection

    Dim ConnectionData_Cust As DAO.Connection
    Set ConnectionData_Cust = New DAO.Connection
    
    ConnectionData_Cust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionData_Cust.ConnectionString = "Data Source = " & DatabaseName
    ConnectionData_Cust.CursorLocation = adUseServer
    ConnectionData_Cust.Open
    
    Set LoadDatabase_Cust = ConnectionData_Cust

End Function

All Optional parameters are never used in the function. Why are they there?

Have you tried to compile the code? It may prompt errors at hundreds lines.

A DAO.Connection Execute method does not return a DAO.Recordset so this line fails:

Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

[zeezee]

Oh dear.
Thats it.
I havent used DAO. Only ADO.

Adding to anhn, it seems DAO has a method called OpenRecordset to return a record set. If you are going to use DAO, that might help.
BTW, I always use rs.Open to open a record set.

[anhn]

Yes. For this case, using DAO.Database
dbs.OpenRecordset(..., dbOpenDynaset)
is simpler.

[CountPuchi]

Hmm..

A DAO.Connection Execute method does not return a DAO.Recordset so this line fails:
Code:

Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

So what your saying is, rsDataCust which is a DAO Record set gets nothing from
ConnectionData_Cust As DAO.Connection??

Yes i did compile the code but it only shows DBConnectionCust.Execute as the problem(Expected function or Variable)

I removed all those parameters @ LoadDatabase_Cust.. ("Gomen... Im really trying hard to understand ")

[randem]

BTW: That connection string looks suspect...

look here http://www.connectionstrings.com

[anhn]

Yes i did compile the code but it only shows DBConnectionCust.Execute as the problem(Expected function or Variable)

Yes, it shows you only one error at a time.
If you comment out that line you will see another line, and so on...

[zeezee]

OK, one question
You used ADO in the beginning, now you are using DAO
Why?

BTW, there is no property called Provider for DAO (3.51)

[CountPuchi]

Well i guess my head is still too full trying to figure things out. While doing the coding i open the object library thingy full with codes then i saw the DAO.Edit line and so i thought i needed to use DAO instead of ADO. Oh well i scrap these and redo again.

ill Update AsAp ^_^

[CountPuchi]

Okay, after redoing everything and making it more simple (And compile check it)

Private Sub CmdMain_Click()
Unload Me
FrmMain.Show
End Sub



Private Sub CmdNext_Click()
        WriteRecord
        rsDataCust.Update
        MsgBox "Patient successfully added !", vbOKOnly
        txtCustId.Text = " "
    TxtCustName.Text = " "
    TxtCustPhone.Text = " "
    TxtCustAdd.Text = " "
    TxtCustLID.Text = " "
    TxtCustIC.Text = " "
    TxtCustAge.Text = " "
     
End Sub

 

 

Public Sub WriteRecord()
    With rsDataCust
     'With test
        .Edit
      
        !cust_id = txtCustId
        !cust_name = TxtCustName
        !cust_phone = TxtCustPhone
        !cust_add = TxtCustAdd
        !cust_license = TxtCustLID
        !cust_ic = TxtCustIC
        !cust_age = TxtCustAge
         
     
    End With
    End Sub

Private Sub Form_Load()
    Dim LoadDataCust As Database
    Dim rsDataCust As ADODB.Recordset
    Dim ConnectionDataCust As ADODB.Connection
    Set ConnectionDataCust = New ADODB.Connection
    Set LoadDataCust = OpenDatabase(App.Path & "\LoginTest.Mdb")
    
    ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
   
    Set rsDataCust = ConnectionDataCust.Execute("SELECT * FROM CUSTOMER")

        'Driver= (Microsoft Access Driver (*.mdb));Dbq=C:\Documents and Settings\CountPuchi\My Documents\MMU Notes & Work\Software Engine\Login Test\LoginTest.mdb;Uid=Admin;Pwd=;
End Sub

It says my database is not recognized. Im using Access 2003.. any thoughts on this?

[zeezee]

wrong again
OK you have figured out where it went wrong.
So if you start from Post #1, and follow upto Post #7, I think you could get this sorted. Instead doing what I told in Post #7, you changed the Code something else too, so everything went wrong
This is actually a separate problem.

Try like that and see whether your original problem still exists.
(I dont know why that is coming actually)

[CountPuchi]

Thanks for the tip, Ive fixed the database unrecognized problem. However now i cant seem to add records (lol back to title.)

Public Sub WriteRecord()
    With rsDc
     'With test
        .NextRecordset.Fields("cust_name") = TxtCustName
        !cust_id = txtCustId
        !cust_name = TxtCustName
        !cust_phone = TxtCustPhone
        !cust_add = TxtCustAdd
        !cust_license = TxtCustLID
        !cust_ic = TxtCustIC
        !cust_age = TxtCustAge
         
     
    End With
    End Sub

Thats not complete yet but as i was testing it says Object Variable & With Block Variable not set to all those lines above. Or is it just my way of trying to add records is simply plain wrong.

Any good and simple ideas on how to add records?

[zeezee]

Any good and simple ideas on how to add records?

Check post #3

as for the Object Variable & With Block Variable not set thing, if you havent set the rsDc to a valid object (ie: its Nothing) this would come.
So before insertion, have you opened a recordset?
Anyway, you have changed the rsDataCust to rsDC ;have you declared this (rsDC) as a global recordset?

Arent you using Option Explicit

[CountPuchi]

No im not using Option Explicit

and yes RsDc is a global recordset

So your saying a valid object

etc

Dim save As String
save = rsDc


????

im abit confused lol sry :P

[zeezee]

Ok first thing.
You must use Option Explicit. Its the first step in Error free code.
So add Option Explicit to the start of ALL your modules.

Next, well the example you gave is wrong.

Thing is there are some kinds of variables, which needs a valid object to use them. For an example
Dim rsDataCust As ADODB.Recordset will not make a valid variable.
it names a varible, but it does not hold or point to a valid object. You need to create an object and then set this variable point to that.
There are two ways to achieve this.
1. Using NEW key word

Dim rsDataCust As New ADODB.Recordset

2. point to another object returned by a method or created earlier, using Set keyword

Set LoadDatabase_Cust = ConnectionData
 Set ConnectionData_Cust = New ADODB.Connection

without using one of these ways, a declared variable wont be a valid object.
So , what you need to do is, before using the rsDC make it a valid object.
ie: So, how would you do that?
1. Use Conn.Execute() to get a valid Recordset object

Set rsDataCust = DBConnectionCust.Execute("SELECT * FROM CUSTOMER")

2. Use New Keyword and rs.Open method to do that

 Dim rsDataCust As New ADODB.Recordset
     rsDataCust.Open "SELECT * FROM CUSTOMER",DBConnectionCust,adopenstatic,adopenoptimistic

so either of these ways make your record set a valid object.

I dont know how much of these you'd uinderstand, so if you dont understand it clearly, say that, so i can give you a solution

BTW, the code in post #1 , looks ok to me ,except that original problem of updation error.

[CountPuchi]

Thankyou thankyou!! I sorted the problem out..

Ok for my next learning process,

what does "Current Provider does not support returning multiple recordset from a single execution mean" ??

and

Operation is not allowed when the object is open

 Dim ConnectionDataCust As ADODB.Connection
    Set ConnectionDataCust = New ADODB.Connection
    Set rsDataCust = New ADODB.Recordset
    
    
        
    
    ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionDataCust.Open "DatBaseFyp.mdb", "", ""
    Set rsDataCust = ConnectionDataCust.Execute("SELECT * FROM CUSTOMER")
    rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, asOpenStatic, asOpenOptimistic <---

[zeezee]

Where does that occur?
may be, if you are using server side cursor (adUseServer), it wont allow you to open another record set while one is open. As the error says, current provider (or the DB driver you are using to connect) does not support that.
so, if you are trying to open a second recordset while one is open, that might be the problem.
But I'm not sure about that 100%

[CountPuchi]

What about operation is not allowed when object is open??

the code is on 25th post

[sansknowledge]

check if the table is open in ms access
HTH

[CountPuchi]

if the table is open in msaccess?

Msaccess is not even open.. so i guess something else??

[zeezee]

No No. It has nothing to do with the Access.
You cant use the Open for a Recordset (or even DB Connection) while its already Open. You have to Close before opening again.
And this is true for when you close your app. You have to close all DB connections, Open Recordsets, Open Files , and destroy all objects before closing app.

This is a code I use to close a Recordset

If Not rsDataCust Is Nothing Then
        If rsDataCust.State = adStateOpen Then
            rsDataCust.Close
        End If
        
        Set rsDataCust = Nothing
    End If

[zeezee]

Oh, well the code in Post#25 is wrong.
You dont have to use both Conn.Execute or rs.Open, you have to use only one of them.

eg:

 Dim ConnectionDataCust As ADODB.Connection
    Set ConnectionDataCust = New ADODB.Connection
    Set rsDataCust = New ADODB.Recordset
    
    
    ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionDataCust.Open "DatBaseFyp.mdb", "", ""
    rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, asOpenStatic, asOpenOptimistic

OR

Dim ConnectionDataCust As ADODB.Connection
    Set ConnectionDataCust = New ADODB.Connection
    
    ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionDataCust.Open "DatBaseFyp.mdb", "", ""
    Set rsDataCust = ConnectionDataCust.Execute("SELECT * FROM CUSTOMER")

got it?

[CountPuchi]

Oooh... So basicly i can only use 1 of them to access the database but if i use both it will conflict with each other?

However

Dim ConnectionDataCust As ADODB.Connection
    Set ConnectionDataCust = New ADODB.Connection
    
    ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionDataCust.Open "DatBaseFyp.mdb", "", ""
    Set rsDataCust = ConnectionDataCust.Execute("SELECT * FROM CUSTOMER")

if i use Con.Execute it will say the database does not support updating or change locktype (Which in turn to use the adLockOptimise i need to use the
rsdatacust.open right??)

but

if i do it

ConnectionDataCust.Provider = "Microsoft.Jet.OLEDB.4.0"
    ConnectionDataCust.Open "DatBaseFyp.mdb", "", ""
    'Set rsDataCust = ConnectionDataCust.Execute("SELECT * FROM CUSTOMER")
    rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, asOpenStatic, asOpenOptimistic, adLockOptimistic

It says

Arguments are wrong type, are out of acceptable range, or are in conflict of one another?

is it because of rsDataCust.Open "SELECT * FROM CUSTOMER", asOpenStatic & asOpenOptimistic ?

[CountPuchi]

Yes i got my records working!!

it was as i checked

rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, adOpenStatic, adLockOptimistic

I Deleted adOpenOptimistic

[zeezee]

Oooh... So basicly i can only use 1 of them to access the database but if i use both it will conflict with each other?

both are fine, you can use both with conjunction, but you cant open a Recordset which is already open. So in the previous code,

rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, asOpenStatic, asOpenOptimistic <---

line tries to open rsDataCust again , which is already open from the line above.
Thats why it says you cant open that.

the correct open code would look like this

rsDataCust.Open "SELECT * FROM CUSTOMER", ConnectionDataCust, adOpenStatic, adLockReadOnly

When you type this in the Code window, usually the correct constants comes automatically.

[CountPuchi]

Owh so thats why.. nevertheless i got it working!! Thank you all!!!!!

On to the next topic.

The customer ID is the primary key in my database, So in my form i want it to show there automatically what is the last ID that's in the record so i did my code like this

 If rsDataCust.RecordCount > 0 Then
        rsDataCust.MoveFirst
        
        Do Until rsDataCust.EOF Or rsDataCust.BOF
             
            Me.txtCustID.text = rsDataCust.Fields("Cust_ID")
        Loop
    End If

is this correct??

[zeezee]

It is correct.
However, IF your PK is Numeric (integer) and its incremental or auto number, there is an easier way (provided no customers are deleted also)

SELECT MAX(Cust_ID) FROM Customer

or even , without a loop, just use a rsDataCust.MoveLast, and get the CustomerID.

Is your Original Problem solved, Unable to Adding records?

[CountPuchi]

Yes the records are adding in smoothly like in post #33

Thanks to all for helping and especially to you zeezee for sticking with me ^_^

Now i can add all the forms i want and insert edit delete etc records ^_^

SELECT MAX(Cust_ID) FROM Customer

MAX = the last record right?
so

If rsDataCust.RecordCount > 0 Then
        rsDataCust.MoveFirst
        
        Do Until rsDataCust.EOF Or rsDataCust.BOF
             
            Me.txtCustID.text = rsDataCust.Open "SELECT MAX Cust_ID FROM Customer
        Loop
    End If

Is this how the code looks like?

[zeezee]

no it isn't.
to use the MAX thing, you need another RecordSet, ie another query.

its better to have a function for that
eg:

Private Function GetMaxCustID() As String
    Dim RstMaxID As New ADODB.Recordset
    
    RstMaxID.Open "SELECT MAX(Cust_ID) FROM Customer", ConnectionDataCust, adOpenStatic, adLockReadOnly
    If Not RstMaxID.EOF Then
    
        RstMaxID.MoveFirst
        ' if you need to make it next id, do it here
        ' eg :
        ' GetMaxCustID = CLng(Val(RstMaxID.Fields("Cust_ID"))) + 1
        
        
        GetMaxCustID = RstMaxID.Fields("Cust_ID")
               
    Else
        ' or set to initial ID
        GetMaxCustID = ""
    End If
End Function

So, if your Original Problem is solved, then you can mark the Thread Resolved
Because it's forum Policy to keep one thread for one problem.
If you have any other problems, you may ask them in separate threads .