ADODB Question

[axelmoe]

Hi,

at the moment I am testing the ADODB functionality and for me it works great.
My question is what is the best way to check if a insert, update or delete operation
is error-free?

Thanks for your support.

Axel

[ErosOlmi]

Ciao Axel,

that area is still to be implemented.
I also use a lot ADODB at work and I too need error checking. So I will start to check about it.

Eros

PS: mask edit is still on the table, sorry.

[axelmoe]

Ciao Eros,

sounds good :p
Thank you for the fast answer.

Axel

[ErosOlmi]

I think I will have a solution in few days that I can post here to test.

Actually I'm facing the problem that every single ADODB operation can fire an error but I'm only able to catch the last one.
So, if an operation composed by 3 steps fails at first or second step but third is OK, I get no error.

An example, the following code taken from \thinBasic\SampleScripts\ADODB\ example is composed by 4 pRecordset calls (record count plus 3 field data retrieve).
If error occurs in one of the first 3 calls but the 4th is ok, I get no error.


PrintL _
lRecCount,
pRecordSet.RecordCount,
pRecordset.CollectS("ISBN"),
pRecordset.CollectN("Year Published"),
pRecordset.CollectS("Title")




I can store errors as an array of errors but it would be complex to handled them.
Anyway, thinking. Maybe I will just release catching last error and then I will refine it in some way.

Ideal would be to have TRY/CATCH in thinBasic but so far I found implementing it quite difficult considering the interpretative nature of thinBasic.

Will see.

[ErosOlmi]

Dear Axel,

I've developed something that can be a start.
I've modified all internal ADODB functionality in such a way to intercept errors at any level.
All errors are stored into an internal Module repository in such a way programmer can check and decide what to do.

Mainly I've developed 3 functions:

AdoDb_Errors_Count: will return number of errors so far intercepted
AdoDb_Errors_Clear: will clear internal errors repository. Can be called whenever programmer wants to reset situation
AdoDb_Errors_GetCode(Idx): will return error code in Idx position with Idx between 1 and AdoDb_Errors_Count
AdoDb_Errors_GetDescription(Idx): will return error description in Idx position with Idx between 1 and AdoDb_Errors_Count


An example on how to use:


'---Do whatever with ADODB ... then check if some error.
printl "-----"
printl "Errors found:", AdoDb_Errors_Count
if AdoDb_Errors_Count then
for lError = 1 to AdoDb_Errors_Count
printl AdoDb_Errors_GetCode(lError), AdoDb_Errors_GetDescription(lError)
next
end if
printl "-----"
AdoDb_Errors_Clear


Attached an updated thinBasic_AdoDB module to put into \thinBasic\Lib\ substituting your current one.

As I said, this is just a start in order to have something.
I will see If I can develop something that react as an event.

Let me know if it works.

Ciao
Eros

[axelmoe]

Dear Eros,

sounds fantastic!!!!!

I'll test it asap and give you a feedback.

Many thanks

Axel

[axelmoe]

Dear Erol,

risrst tests, I use your code in a function and use this after the ADO-Operation with the ADO Example PGM.

No Errors.

Than I will insert a new record:


Function ado_check()
'---Do whatever with ADODB ... then check if some error.
PrintL "-----"
PrintL "Errors found:", AdoDb_Errors_Count In %CCOLOR_FYELLOW
If AdoDb_Errors_Count Then
For lError = 1 To AdoDb_Errors_Count
PrintL AdoDb_Errors_GetCode(lError), AdoDb_Errors_GetDescription(lError) In %CCOLOR_FLIGHTRED
Next
End If
PrintL "-----"
AdoDb_Errors_Clear
WaitKey
End Function

Function new_rec()
' pRecordset.movefirst
' pRecordset.MoveLast
' pRecordset.addnew
' pRecordset.UpdateS("ISBN") = "ich 4711"
' pRecordset.UpdateN("Year Published") = 2016
' pRecordset.UpdateS("Title") = "Mein erster satz"
' pRecordset.Update
' PrintL "nach add new" In %CCOLOR_FLIGHTRED
pRS.Open "Titles", pConnection, %ADOPENKEYSET, %ADLOCKOPTIMISTIC, %ADCMDTABLE
pRs.addnew
pRs.UpdateS("ISBN") = "ich 4711"
pRs.UpdateN("Year Published") = 2016
pRs.UpdateS("Title") = "Mein erster satz"
pRs.Update
PrintL "nach add new" In %CCOLOR_FLIGHTRED
ado_check
End Function


No error but also no new rrecord in the database.
If I change the code to produce an error ( bad table name) the function ado_check reports errors
but raise an error "FOR LERROR = 1 TO ADODB_ERRORS_COUNT"

I hope it's understandable and can help you

Greetings
Axel

[ErosOlmi]

Thanks for testing.
I will let you know.

[axelmoe]

Ciao Eros,

any news?
I have very great interest in these functions.

Thank you very much for your efforts,

Axel

[ErosOlmi]

Yes, Axel, sorry. I'm late.

In reality I already have a solution but not jet ready to give you new module.
I will try to give you a new module to test in few days, by this week-end I hope.

Eros

[axelmoe]

Dear Eros,

Everything is good. I'm just so curious.:)

Greetings
Axel

[ErosOlmi]

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:


'---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.



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

[ErosOlmi]

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




------OLD-----------------------------------------NEW----------------------

<ADODBConnection>.ErrorsCount is now -----> <ADODBConnection>.Errors.Count
<ADODBConnection>.ErrorsClear is now -----> <ADODBConnection>.Errors.Clear
<ADODBConnection>.ErrorsMsg is now -----> <ADODBConnection>.Errors.Msg

[axelmoe]

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

[axelmoe]

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:

' 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

[ErosOlmi]

Ok, thanks

I will check this evening and let you know.

[axelmoe]

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

[ErosOlmi]

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:

'----------------------------------------------------------------------------
' Close
'----------------------------------------------------------------------------
Method cADODB_RecordSet_Close() As Dword
pRecordSet.Close
Method = pRecordSet.State
End Method

Possible values are like the following: http://www.w3schools.com/asp/prop_rs_state.asp
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:

MyRecordsSet = MyConnection.Execute(...)
I will release a new module as soon as done.

And after that I will work on implementing ADODB_Command interface.

[axelmoe]

Hi Eros,

if I use the function Add_New_Record a second time with other parameter I have the following error:
9664.

Any idear???

Brds,

Axel

[ErosOlmi]

I have to check.
I will try this evening and let your know.

[ErosOlmi]

Hi Axel,

I cannot replicate the error. I tried to execute more than once with different parameters but no error.
Are you able to create a sample minimal script in which you replicate the problem?

Thanks
Eros

[axelmoe]

Hi Erol,

this is my example:


Uses "Console"
Uses "ADODB"


Dim pConnection As New ADODB_CONNECTION
Dim sConn As String
Dim sSql As String
'--------------------------------------------------------------
Function select_record(ByVal pconn As ADODB_CONNECTION, ByVal sSSS As String)
Dim pRS As New ADODB_RECORDSET
pRs.CursorLocation = %ADUSECLIENT
sSql = "select * from T_SSS where sss = " & "'" & sSSS & "';"
pRs.OPEN sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT

If pRS.State = %ADSTATEOPEN Then
PrintL "Sätze gefunden: " & pRs.RecordCount

End If


pRs.close


End Function

'----------------------------------
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"
sConn = "Provider=sqloledb;Data Source=\\server\db;Initial Catalog=KNI2M;User Id=xxxxxx;Password=yyyyy ;"

'---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))

select_record(pConnection, "11000238")

select_record(pConnection, "11000237")

' 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

Thanks for your assist.

Axel

[ErosOlmi]

Ciao Axel,

got the same error, thanks.

I need more time to understand why, it is a parsing problem in thinBasic Core engine.
There are some "optimizations" during first execution of a function that are applied from the second execution on.
Obviously I've made some error in there :oops:

As a workaround, please try to add parenthesis to:

pRs.OPEN sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT
and change to

pRs.OPEN(sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT)


Here seems to work, let me know if it works also for you.
Ciao
Eros

[axelmoe]

Hi Eros,

first check works fine for me, many thanks for the workearound.

Axel

[axelmoe]

Hello Eros,

i have to insert a value , e.g. 58.58 in a database money field. I'm loosing the cent, the value in the database is 58.
i have test pRs.updateN, pRs.updateD and prs.updateD. Always the same result. Do you have any idea?

Thank you for your support.

Regards,

Axel

[ErosOlmi]

Here it seems working fine.
Attached an example with a BIBLIO.MDB Access DB in which I pass a numeric with 2 decimals and insert a new record with currency, long integer and double numeric field

Let me know if it works for you.
Eros

[Petr Schreiber]

Just a wild shot from DB amateur - couldn't it be something related to regional settings for floating point separator character?


Petr

[axelmoe]

Dear All,

Please excuse my late reply. Yes at first I am searching in the locale setup values.
I have overlooked my own mistakes. It all works as expected if you do it right.

But thank you very much for your support.

Kind regards,

Axel

[ErosOlmi]

Great, happy it is working.

I'm going on to implement new features for this module because I'm also using it at work.
In next thinBasic updated it will be also fully documented.

Ciao
Eros

[axelmoe]

Hi Erol,

Some information for you. I have finished my first TB ADO Project. The program replaces a COBOL / ODBC application.
I have ~ 2200 LOC. The program converts up to 80000 records per run. The old program was running up to 80 minutes,
the new needs of this 20 minutes max.
fantastic :D

two things I have:
-If I have a wrong field name in pRS.update("xxx") I get no error message
-if eg. the target field in pRS.update is a string with 20 digits and I set a value with 15 digits I get no error and teh field value is not set

Thank you for your fantastic work!

kind regards,

Axel

[ErosOlmi]

Ciao Axel,

thanks a lot for your reporting, always a pleasure to get such a feedback.
To know other people are using your software for real problems and seems having good results ... is a great push for doing better.

I will check what happen when field name is not valid.
I was working on ADODB Fields collection ( https://docs.microsoft.com/en-us/sql/ado/guide/data/the-fields-collection ) for the next version.
Maybe it will help on this.

Will let you know.

Ciao
Eros

[ErosOlmi]

-if eg. the target field in pRS.update is a string with 20 digits and I set a value with 15 digits I get no error and teh field value is not set


When you say "digits" you mean "characters"?
So you are saying that you always have to fill the string with the exact number of characters of the target Table field?

[axelmoe]

yes I mean characters.
e.g.
The target field is defined by 20 characters, not mandatory. When I set Zero to 20 characters all was fine.
When I set more then 20 characters the field the target field in the DB is not updated

Axel

[ErosOlmi]

OK, now I got it.

Inserting more bytes than the one allowed by a field, generates an error. I do not know if it is possible to switch into a warning.
Internally I can automatically fix it truncating data to the max allowed by the field but is an assumption I cannot keep for all situations. There are situations where this is not valid and depends by the programmer.
I will check what I can do, maybe adding few personalized options (not ADODB standard) to activate/deactivate.

[ErosOlmi]

Attached (and here below) an example trying to insert more data than the field can have, field ShortText20Chars
Example generates the following ADODB error:
--> Error happened:

Error type: OLE DB provider error

Error number: -2147217887 [&H80040E21]

Description: Multiple-step operation generated errors. Check each status value.

Source: Microsoft Cursor Engine

I think we are in scenario 1 of the scenarios exposed here: http://www.adopenstatic.com/faq/80040e21.asp

I will go into this direction:

I will implement RecordSet.Fields ... interface in order to be able to inspect fields characteristics. This can also be used to verify field existence
I will add an optional parameter into RecordSet.UpdateS in order to be able to specify if to check field max data length and truncate input string value


Stay tuned.

Ciao
Eros



Uses "Console"Uses "ADODB"


Dim pConnection As New ADODB_CONNECTION
Dim sConn As String
Dim sSql As String


'----------------------------------
Function Add_New_Record(pConn As ADODB_CONNECTION, sShort20 As string, sShort200 As string, sLong As string)
'----------------------------------
Dim pRS As New ADODB_RECORDSET
pRs.CursorLocation = %ADUSECLIENT


sSql = "select * from T_Test_Strings where ID = -1"
'---Wil not return any record because ID = 0
PrintL "Query:", ssql


pRs.OPEN sSql, pConn, %ADOPENDYNAMIC, %ADLOCKOPTIMISTIC, %ADCMDTEXT


If pRS.State = %ADSTATEOPEN Then
PrintL "Records found", pRs.RecordCount


If pRS.RecordCount = 1 Then
PrintL "Update (Should not happen in this example)" In %CCOLOR_FYELLOW
End If


If pRS.RecordCount = 0 Then
PrintL "Insert record" In %CCOLOR_FYELLOW

pRs.addnew
pRs.UpdateS("ShortText20Chars") = repeat$(200, sShort20) '---THIS WILL GENERATE ERROR because field is only 20 bytes
pRs.UpdateS("ShortText200Chars") = sShort200 & " " & Timer '---This field is 200 bytes
pRs.UpdateS("LongText") = sLong '---This field has dynamic size
pRs.update


'---To check if there is an error, always use connection and Errors.Count method
If pConn.Errors.Count Then
PrintL "--> Error happened:" In %CCOLOR_FLIGHTRED
printl pConn.Errors.Msg
'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


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"
'sConn = "Provider=sqloledb;Data Source=\\server\db;Initial Catalog=KNI2M;User Id=xxxxxx;Password=yyyyy ;"


'---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, "Short20", "Short200", Repeat$(100, "Long Text repeated") )


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