Addnew and update method in ADOCE not working ? 
Author Message
 Addnew and update method in ADOCE not working ?

The following code raising error, while adding new record, any idea please >

Dim cnn As ADOCE.Connection
Set cnn = CreateObject("ADOCE.Connection.3.1")
cnn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; data
source=\my documents\luq.sdf;"
cnn.Open

Dim rs as ADOCE.recordset
Set rs = CreateObject("ADOCE.Recordset.3.1")
rs.ActiveConnection = cnn
rs.Open "test", cnn, adOpenKeyset, adLockOptimistic
rs.AddNew
rs.Fields("test") = "buq"
rs.Update
rs.Close

Best Regards,

Luqman



Wed, 14 Dec 2005 22:35:55 GMT  
 Addnew and update method in ADOCE not working ?


It might be inefficient, but this works for me:

    If Not OpenRecordset("SELECT * FROM markForbrug", rsForbrug) Then 'open
an empty recordset
        deb "couldn't open for save" ' deb is a debug routine I have
    Else
        rsForbrug.AddNew
        rsForbrug.Fields("Mfb_ID").Value = ID
        rsForbrug.Fields("Mfb_ForbrugsPakke_ID").Value = ForbrugspakkeID
        rsForbrug.Fields("Mfb_MarkAfgr_ID").Value = CurrentMark
        rsForbrug.Fields("Mfb_HostAar").Value = CurrentYear
        rsForbrug.Update

It uses the following to open a connection:

Public Sub DBConnect(strActiveConnection As String, strNormConnection As
String)
Dim errobj As ADOCE.Error

    On Error Resume Next

    Set objConn = CreateObject("ADOCE.Connection.3.1")
        If UseSQL Then
            objConn.ConnectionString =
"provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=" &
strActiveConnection & ";"
        Else
            objConn.ConnectionString = "provider=cedb;data source=" &
strActiveConnection & ";"
        End If

    If Not DBIsInitialised Then

        'Create all necessary recordsets for plan.
        Set rsMark = CreateObject("ADOCE.Recordset.3.1")
        Set rsBehandling = CreateObject("ADOCE.Recordset.3.1")
        Set rsForbrugsPakke = CreateObject("ADOCE.Recordset.3.1")
        Set rsForbrug = CreateObject("ADOCE.Recordset.3.1")
        Set rsGdnlager = CreateObject("ADOCE.Recordset.3.1")
        Set rsBM = CreateObject("ADOCE.Recordset.3.1")

        'Create all necessary recordsets for norm.
        Set rsStdAfgr = CreateObject("ADOCE.Recordset.3.1")
        Set rsStdUdbLinie = CreateObject("ADOCE.Recordset.3.1")
        Set rsUdbType = CreateObject("ADOCE.Recordset.3.1")
        Set rsStdForbrug = CreateObject("ADOCE.Recordset.3.1")
        Set rsSortsGrupper = CreateObject("ADOCE.Recordset.3.1")
        Set rsSort = CreateObject("ADOCE.Recordset.3.1")
        Set rsEnheder = CreateObject("ADOCE.Recordset.3.1")
        Set rsFasteTekster = CreateObject("ADOCE.Recordset.3.1")

        Set TmpRs = CreateObject("ADOCE.Recordset.3.1")

    End If
    objConn.Open

    If Err Then
        deb "Databasefejl: " & Err.Number & " - " & Err.Description & "
Connection string: '" & objConn.ConnectionString & "'"
        For Each errobj In objConn.Errors
            deb "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description
'            MsgBox "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description
        Next
        OpenRecordset = False
    Else
'        deb "Dyrkningsplan database ?bnet"
        Err = 0
        RSCounter = 0
        If UseSQL = 1 Then
            Set objNormConn = objConn
        Else
            Set objNormConn = CreateObject("ADOCE.Connection.3.1")
            objNormConn.ConnectionString = "provider=cedb;data source=" &
strNormConnection & ";"
            objNormConn.Open
        End If

        If Err Then
            deb "Databasefejl: " & Err.Number & " - " & Err.Description & "
Connection string: '" & objConn.ConnectionString & "'"
            For Each errobj In objConn.Errors
                deb "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description
'                MsgBox "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description
            Next
            OpenRecordset = False
        Else
            Err = 0
'            deb "Normdatabase ?bnet"
        End If
    End If

    On Error GoTo 0
    DBIsInitialised = True
End Sub

And the following to open a recordset.

Public Function OpenRecordset(strSQL As String, ByRef rs As Recordset)
Dim errobj As ADOCE.Error
Dim desc As String
    CloseRecordset rs
    On Error Resume Next
        OpenRecordset = True
        Err = 0
        rs.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText

        If Err Then
            desc = Err.Description
            deb "Databasefejl: " & Err.Number & " - " & Err.Description & "
SQL: '" & strSQL & "'"
            For Each errobj In rs.Errors
                deb "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description & " SQL: '" & strSQL & "'"
'                MsgBox "Databasefejl: " & errobj.NativeError & " - " &
errobj.Description
            Next
            OpenRecordset = False
        Else
            Err = 0
        End If
    On Error GoTo 0
End Function

It works for both SSCE and CEDB data.

/Keld Laursen



Wed, 14 Dec 2005 22:52:28 GMT  
 Addnew and update method in ADOCE not working ?
Here is how to open a recordset for addnew and update. Only table names are
allowed for addnew and update, not the sql.

rs.Open "myTable", objConn, adOpenKeySet, adLockOptimistic, adCmdTableDirect

The last parameter must be adCmdTableDirect or 512 otherwise you cannot
update the record.

Best Regards,

Luqman


Quote:


> It might be inefficient, but this works for me:

>     If Not OpenRecordset("SELECT * FROM markForbrug", rsForbrug) Then
'open
> an empty recordset
>         deb "couldn't open for save" ' deb is a debug routine I have
>     Else
>         rsForbrug.AddNew
>         rsForbrug.Fields("Mfb_ID").Value = ID
>         rsForbrug.Fields("Mfb_ForbrugsPakke_ID").Value = ForbrugspakkeID
>         rsForbrug.Fields("Mfb_MarkAfgr_ID").Value = CurrentMark
>         rsForbrug.Fields("Mfb_HostAar").Value = CurrentYear
>         rsForbrug.Update

> It uses the following to open a connection:

> Public Sub DBConnect(strActiveConnection As String, strNormConnection As
> String)
> Dim errobj As ADOCE.Error

>     On Error Resume Next

>     Set objConn = CreateObject("ADOCE.Connection.3.1")
>         If UseSQL Then
>             objConn.ConnectionString =
> "provider=Microsoft.SQLSERVER.OLEDB.CE.1.0; data source=" &
> strActiveConnection & ";"
>         Else
>             objConn.ConnectionString = "provider=cedb;data source=" &
> strActiveConnection & ";"
>         End If

>     If Not DBIsInitialised Then

>         'Create all necessary recordsets for plan.
>         Set rsMark = CreateObject("ADOCE.Recordset.3.1")
>         Set rsBehandling = CreateObject("ADOCE.Recordset.3.1")
>         Set rsForbrugsPakke = CreateObject("ADOCE.Recordset.3.1")
>         Set rsForbrug = CreateObject("ADOCE.Recordset.3.1")
>         Set rsGdnlager = CreateObject("ADOCE.Recordset.3.1")
>         Set rsBM = CreateObject("ADOCE.Recordset.3.1")

>         'Create all necessary recordsets for norm.
>         Set rsStdAfgr = CreateObject("ADOCE.Recordset.3.1")
>         Set rsStdUdbLinie = CreateObject("ADOCE.Recordset.3.1")
>         Set rsUdbType = CreateObject("ADOCE.Recordset.3.1")
>         Set rsStdForbrug = CreateObject("ADOCE.Recordset.3.1")
>         Set rsSortsGrupper = CreateObject("ADOCE.Recordset.3.1")
>         Set rsSort = CreateObject("ADOCE.Recordset.3.1")
>         Set rsEnheder = CreateObject("ADOCE.Recordset.3.1")
>         Set rsFasteTekster = CreateObject("ADOCE.Recordset.3.1")

>         Set TmpRs = CreateObject("ADOCE.Recordset.3.1")

>     End If
>     objConn.Open

>     If Err Then
>         deb "Databasefejl: " & Err.Number & " - " & Err.Description & "
> Connection string: '" & objConn.ConnectionString & "'"
>         For Each errobj In objConn.Errors
>             deb "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description
> '            MsgBox "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description
>         Next
>         OpenRecordset = False
>     Else
> '        deb "Dyrkningsplan database ?bnet"
>         Err = 0
>         RSCounter = 0
>         If UseSQL = 1 Then
>             Set objNormConn = objConn
>         Else
>             Set objNormConn = CreateObject("ADOCE.Connection.3.1")
>             objNormConn.ConnectionString = "provider=cedb;data source=" &
> strNormConnection & ";"
>             objNormConn.Open
>         End If

>         If Err Then
>             deb "Databasefejl: " & Err.Number & " - " & Err.Description &
"
> Connection string: '" & objConn.ConnectionString & "'"
>             For Each errobj In objConn.Errors
>                 deb "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description
> '                MsgBox "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description
>             Next
>             OpenRecordset = False
>         Else
>             Err = 0
> '            deb "Normdatabase ?bnet"
>         End If
>     End If

>     On Error GoTo 0
>     DBIsInitialised = True
> End Sub

> And the following to open a recordset.

> Public Function OpenRecordset(strSQL As String, ByRef rs As Recordset)
> Dim errobj As ADOCE.Error
> Dim desc As String
>     CloseRecordset rs
>     On Error Resume Next
>         OpenRecordset = True
>         Err = 0
>         rs.Open strSQL, objConn, adOpenStatic, adLockOptimistic, adCmdText

>         If Err Then
>             desc = Err.Description
>             deb "Databasefejl: " & Err.Number & " - " & Err.Description &
"
> SQL: '" & strSQL & "'"
>             For Each errobj In rs.Errors
>                 deb "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description & " SQL: '" & strSQL & "'"
> '                MsgBox "Databasefejl: " & errobj.NativeError & " - " &
> errobj.Description
>             Next
>             OpenRecordset = False
>         Else
>             Err = 0
>         End If
>     On Error GoTo 0
> End Function

> It works for both SSCE and CEDB data.

> /Keld Laurse>



Wed, 14 Dec 2005 23:10:35 GMT  
 Addnew and update method in ADOCE not working ?

Quote:
> The last parameter must be adCmdTableDirect or 512 otherwise you cannot
> update the record.

i beg to differ.


Thu, 15 Dec 2005 00:10:58 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. AddNew method does not work

2. .addnew method ADO does not work pls help

3. AddNew Method not working for me anymore.

4. AddNew/REquery method of ADO Recordset not Working?

5. Update method in VB6 not working properly

6. CDO update method works W2K but not in NT4

7. Update method not working

8. Update method not working

9. Update method in VB6 not working properly

10. Seek method don't work on ADOCE and ECDB

11. AddNew and Update ADO methods

12. addnew and update methods ?

 

 
Powered by phpBB® Forum Software