Dear Eros,
Everything is good. I'm just so curious.:)
Greetings
Axel
Printable View
Dear Eros,
Everything is good. I'm just so curious.:)
Greetings
Axel
Dear Axel,
in Italy we say "better late than nothing". Hope this is the case in this situation :oops:
Find here attached thinBasic_ADODB.dll in which I have developed a sord of wrapper of internal ADODB errors.
Please unzip in \thinbasic\Lib\ replacing your current one
Developed function are here summarized:
Code:'---To check if there is ana arror, always use connection and Errors.Count method
if pConn.Errors.Count then
'---Print info of at least first error. there can be more than one
printl "Error number..:", pConn.Errors(1).number
printl "Description...:", pConn.Errors(1).Description
printl "Source........:", pConn.Errors(1).Source
printl "SQLState......:", pConn.Errors(1).SQLState
printl "NativeError...:", pConn.Errors(1).NativeError
'---Clear internal errors collection. Otherwise errors collection will accumulate errors in case execution could continue
pConn.Errors.Clear
end if
...
'---To check if there is ana arror, always use connection and Errors.Count method
if pConnection.Errors.Count then
'---Show all errors in one go.
'---This function also clear internal errors collection
printl pConnection.Errors.Msg
end if
Here below an example on how to use new error functions. Example simulate adding a new record that violate table primary key duplication.
Code:Uses "Console"
Uses "ADODB"
Dim pConnection As New ADODB_CONNECTION
Dim sConn As String
'----------------------------------
Function Add_New_Record(byval pConn as adodb_connection, byval sISBN as string, byval lYear as long, byval sTitle as string)
'----------------------------------
Dim pRS As New ADODB_RECORDSET
pRS.Open "Titles", pConn, %ADOPENKEYSET, %ADLOCKOPTIMISTIC, %ADCMDTABLE
if pRS.STATE = %ADSTATEOPEN Then
pRs.UpdateS("ISBN") = sISBN '& " " & Timer'"ich 4711"
pRs.UpdateN("Year Published") = lYear'2016
pRs.UpdateS("Title") = sTitle'"Mein erster satz"
pRs.addnew
'pRs.update
'printl AdoDb_GetErrorInfo(pConn)
PrintL "Add new" In %CCOLOR_FLIGHTRED
'---To check if there is ana arror, always use connection and ErrorsCount method
if pConn.Errors.Count then
'---Print info of at least first error. there can be more than one
printl "Error number..:", pConn.Errors(1).number
printl "Description...:", pConn.Errors(1).Description
printl "Source........:", pConn.Errors(1).Source
printl "SQLState......:", pConn.Errors(1).SQLState
printl "NativeError...:", pConn.Errors(1).NativeError
'---Clear internal errors collection. Otherwise errors collection will cumulate errors in case execution could continue
pConn.Errors.Clear
end if
printL " pRecordSet.Close :", pRS.CLOSE
end if
End Function
'---------------------------------------------------------------
' Connection
'---------------------------------------------------------------
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APP_SourcePath & "biblio.mdb"
'---Set connection string
pConnection.ConnectionString = sConn
PrintL "-Connection------------------------------------------"
PrintL " Opening ..." , pConnection.OPEN'(sConn)
PrintL " pConnection.Connectionstring:" , pConnection.Connectionstring
PrintL " pConnection.State :" , pConnection.State
PrintL " pConnection.Version :" , pConnection.Version
PrintL "-Press a key to continue------------------------------" In %CCOLOR_FYELLOW
'---To check if there is ana arror, always use connection and ErrorsCount method
if pConnection.Errors.Count then
'---Show all errors in one go.
'---This function also clear internal errors collection
printl pConnection.Errors.Msg
end if
If pConnection.STATE = %ADSTATEOPEN Then
Add_New_Record(pConnection, "999999999", 9999, "Eros Test " & date$(2) & " " & time$(0))
PrintL "-Closing press a key-------------------------------------------" In %CCOLOR_FYELLOW
'WaitKey
PrintL " pConnection.Close :", pConnection.CLOSE
Else
PrintL "-It was not possible to open a connection-" In %CCOLOR_FLIGHTRED
End If
PrintL
PrintL "-Press a key to finish-------------------------------"
WaitKey
I've updated module and examples in previous post because I changed 3 functions making them more ... OOP like.
If someone has already downloaded it, please download it again and change the following ADODBConnection.Errors. ... methods
Code:------OLD-----------------------------------------NEW----------------------
<ADODBConnection>.ErrorsCount is now -----> <ADODBConnection>.Errors.Count
<ADODBConnection>.ErrorsClear is now -----> <ADODBConnection>.Errors.Clear
<ADODBConnection>.ErrorsMsg is now -----> <ADODBConnection>.Errors.Msg
Hi Eros,
great job. My first tests were successful and I would get the expected results.
In the next few days I'll do more tests and inform about the results.
Kind Regards,
Axel
Hi Eros,
I have done more tests and what i see let me smile. Insert, update, delete is working and the error checking works also fine.
But there is one thing I don't anderstand. If there was an error, eg missing value for a mandetory field for a new record, than
the recordset.close returns 1 an d not 0. at the moment I don't anderstand that.
my testcode:Code:' Empty GUI script created on 01-03-2017 12:50:16 by (thinAir)
Uses "Console"
Uses "ADODB"
Dim pConnection As New ADODB_CONNECTION
Dim sConn As String
Dim sSql As String
'----------------------------------
Function Add_New_Record(ByVal pConn As ADODB_CONNECTION, ByVal sISBN As String, ByVal lYear As Long, ByVal sTitle As String)
'----------------------------------
Dim pRS As New ADODB_RECORDSET
pRs.CursorLocation = %ADUSECLIENT
sSql = "select * from Titles where ISBN = " & "'" & sISBN & "';"
PrintL ssql
'pRS.Open "Titles", pConn, %ADOPENKEYSET, %ADLOCKOPTIMISTIC, %ADCMDTABLE
pRs.OPEN sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT
If pRS.State = %ADSTATEOPEN Then
PrintL pRs.RecordCount
If pRS.RecordCount = 1 Then
pRs.UpdateS("ISBN") = sISBN '& " " & Timer'"ich 4711"
pRs.UpdateN("Year Published") = lYear'2016
pRs.UpdateS("Title") = sTitle'"Mein erster satz"
pRs.update
'---To check if there is ana arror, always use connection and ErrorsCount method
If pConn.Errors.Count Then
PrintL "--> Fehler nach update" In %CCOLOR_FYELLOW
PrintL "Error number..:", pConn.Errors(1).Number
PrintL "Description...:", pConn.Errors(1).Description
PrintL "Source........:", pConn.Errors(1).Source
PrintL "SQLState......:", pConn.Errors(1).SQLState
PrintL "NativeError...:", pConn.Errors(1).NativeError
pConn.Errors.Clear
End If
PrintL "update" In %CCOLOR_FLIGHTRED
End If
If pRS.RecordCount = 0 Then
pRs.addnew
pRs.UpdateS("ISBN") = sISBN '& " " & Timer'"ich 4711"
pRs.UpdateN("Year Published") = lYear'2016
pRs.UpdateS("Title") = sTitle'"Mein erster satz"
pRs.UpdateS("PubID") = 69
pRs.update
'---To check if there is ana arror, always use connection and ErrorsCount method
If pConn.Errors.Count Then
PrintL "--> Fehler nach insert" In %CCOLOR_FYELLOW
PrintL "Error number..:", pConn.Errors(1).Number
PrintL "Description...:", pConn.Errors(1).Description
PrintL "Source........:", pConn.Errors(1).Source
PrintL "SQLState......:", pConn.Errors(1).SQLState
PrintL "NativeError...:", pConn.Errors(1).NativeError
pConn.Errors.Clear
End If
PrintL "insert" In %CCOLOR_FLIGHTRED
End If
PrintL " pRecordSet.Close :", pRS.CLOSE
End If
End Function
'---------------------------------------------------------------
' Connection
'---------------------------------------------------------------
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & APP_SourcePath & "biblio.mdb"
'---Set connection string
pConnection.ConnectionString = sConn
PrintL "-Connection------------------------------------------"
PrintL " Opening ..." , pConnection.OPEN'(sConn)
PrintL " pConnection.Connectionstring:" , pConnection.Connectionstring
PrintL " pConnection.State :" , pConnection.State
PrintL " pConnection.Version :" , pConnection.Version
PrintL "-Press a key to continue------------------------------" In %CCOLOR_FYELLOW
'---To check if there is ana arror, always use connection and ErrorsCount method
If pConnection.Errors.Count Then
'---Show all errors in one go.
'---This function also clear internal errors collection
PrintL pConnection.Errors.Msg
End If
If pConnection.State = %ADSTATEOPEN Then
Add_New_Record(pConnection, "999999900", 9999, "Axels Test " & Date$(2) & " " & Time$(0))
PrintL "-Closing press a key-------------------------------------------" In %CCOLOR_FYELLOW
'WaitKey
PrintL " pConnection.Close :", pConnection.CLOSE
Else
PrintL "-It was not possible to open a connection-" In %CCOLOR_FLIGHTRED
End If
PrintL
PrintL "-Press a key to finish-------------------------------"
WaitKey
Ok, thanks
I will check this evening and let you know.
Hi Eros,
last weekend I have a bit of testing and have found no further problems.
But I have a little wish list:
Recordset method .find and
Connection method .execute :)
kind regards,
Axel
Ciao Axel,
sorry for the delay but I was in bed for a health problem now ok.
Regarding <Recordset>.Close ... in reality that method does not return any value in standard ADO interface.
I developed it to return <Recordset>.State after Closing it.
So internally Close is the following:
Possible values are like the following: http://www.w3schools.com/asp/prop_rs_state.aspCode:'----------------------------------------------------------------------------
' Close
'----------------------------------------------------------------------------
Method cADODB_RecordSet_Close() As Dword
pRecordSet.Close
Method = pRecordSet.State
End Method
Maybe it's not a great idea to check Close return value.
Regarding new requests ... I'm already developing them.
<RecordSet>.Find is already done.
<Connection>.Execute is quite complex because it returns a <RecordSet> object and I need to understand how to implement this behavior in thinBasic.
Something like:
I will release a new module as soon as done.Code:MyRecordsSet = MyConnection.Execute(...)
And after that I will work on implementing ADODB_Command interface.
Hi Eros,
if I use the function Add_New_Record a second time with other parameter I have the following error:
Attachment 9664.
Any idear???
Brds,
Axel
I have to check.
I will try this evening and let your know.