Vanessa
asked on
VB code accessing Lotus/Domino and pastes into SQL fails at 244 records
The error I'm getting is:
"[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"
When I remove the two records that appear to be the problem it does fine. When those two are still gone and I add a new record, it goes back to the error. So then I removed the two bad ones, copy 2 that I saw had successfully inserted into SQL and pasted them in the Domino database and it still gives me that error.
That's making me think that it's just stopping at 244 rather than record data being bad? I know that max query results by default in Notes are 250 and there is no max results type of wording in my connection string so that may be the issue but I'm not sure how to word that.
Any ideas would be great!
"[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"
When I remove the two records that appear to be the problem it does fine. When those two are still gone and I add a new record, it goes back to the error. So then I removed the two bad ones, copy 2 that I saw had successfully inserted into SQL and pasted them in the Domino database and it still gives me that error.
That's making me think that it's just stopping at 244 rather than record data being bad? I know that max query results by default in Notes are 250 and there is no max results type of wording in my connection string so that may be the issue but I'm not sure how to word that.
Any ideas would be great!
Private Sub GetLicensingNotes_Click()
Dim connNotes As ADODB.Connection
Dim connSQL As ADODB.Connection
Dim rsSQL As ADODB.Recordset
Dim rsNotes As ADODB.Recordset
Dim count, j As Integer
Dim tmpSQL As String
count = 0
j = 0
'connect to the CPAS SQL Database
Set connSQL = New ADODB.Connection
connSQL.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=CPAS;Data Source=VFRASER\SQLEXPRESS"
connSQL.Open
'connect to the Lotus Notes database
Set connNotes = New ADODB.Connection
connNotes.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Data Source=DominoCPASLicense34;Mode=ReadWrite"
connNotes.Open
'set the SQL recordset & make sure it can be added to
Set rsSQL = New ADODB.Recordset
rsSQL.CursorLocation = adUseServer
rsSQL.Open "SELECT * FROM Licensing", connSQL, adOpenDynamic, adLockOptimistic
'get the Notes table so that it can be looped thru and added to the SQL table
Set rsNotes = New ADODB.Recordset
rsNotes.CursorLocation = adUseServer
Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")
While Not rsNotes.EOF 'I never could get this to recognize the EOF on my Notes so had to hard code a stop using count
'fill in the fields
count = count + 1 'count how many records have been processed
With rsSQL
.AddNew
!CompanyNo = rsNotes.Fields("CompanyNo").Value
!CompanyName = rsNotes.Fields("Name").Value
!Contact = rsNotes.Fields("Contact").Value
!Address1 = rsNotes.Fields("Address1").Value
!Address2 = rsNotes.Fields("Address2").Value
!City = rsNotes.Fields("City").Value
!State = rsNotes.Fields("State").Value
!Zip = rsNotes.Fields("Zip").Value
!WorkPhone = rsNotes.Fields("Phone").Value
!FaxNumber = rsNotes.Fields("Fax").Value
!EmailNo = rsNotes.Fields("EmailNo").Value
!PrimaryWebsite = rsNotes.Fields("WebSite").Value
!LicenseType = rsNotes.Fields("LicenseType").Value
!StatusOfLicenseProfile = rsNotes.Fields("Status").Value
!OfficeDaysHours = rsNotes.Fields("OfficeDaysHours").Value
!LicenseLookup = rsNotes.Fields("YorN").Value
!LicenseLookupURL = rsNotes.Fields("WebSite_1").Value
!LicenseDuration = rsNotes.Fields("RenewalTime").Value
!WorkWLicInProgress = rsNotes.Fields("LicenseProcess").Value
!CostAllLicenses = rsNotes.Fields("Fees").Value
!TempLicenseAvailable = rsNotes.Fields("TempLicense").Value
!TimeToGetLicense = rsNotes.Fields("Time").Value
!TimeToGetTempLicense = rsNotes.Fields("TempTime").Value
!MethodsAcceptablePayment = rsNotes.Fields("Payment").Value
!VerificationFees = rsNotes.Fields("Data1").Value
!ApplicationAvailableOnWebsite = rsNotes.Fields("YorN2").Value
!LicenseNecessary = rsNotes.Fields("NeededLicense").Value
!DetailedInformation = rsNotes.Fields("DetailedInfo").Value
!ApplicationAttachment = rsNotes.Fields("Comments").Value
!RenewalForm = rsNotes.Fields("YorN_1").Value
!TimeToGetRenewalLicense = rsNotes.Fields("Time_1").Value
!RenewalFormLink = rsNotes.Fields("WebSite_1_1").Value
!CostOfRenewalLicense = rsNotes.Fields("Fees_1").Value
!MethodsAcceptablePaymentRen = rsNotes.Fields("Payment_1").Value
!RenewalComments = rsNotes.Fields("comments_1").Value
!PostingStatus = rsNotes.Fields("NSPub").Value
!DescriptionUserSeesForMainWebsiteLink = rsNotes.Fields("txtMainWebsite").Value
!MainWebsiteLink = rsNotes.Fields("lnkMainWebsite").Value
!DescriptionUserSeesRenewalLink = rsNotes.Fields("txtWebsite2").Value
!RenewalWebsiteLink = rsNotes.Fields("lnkWebsite2").Value
.Update
rsSQL.MoveNext
End With
'I have to figure out why Lotus can't realize it is at the end of the recordset ...
If count < 244 Then
rsNotes.MoveNext 'this gives me the error "[Lotus][ODBC LotusNotes]Data value is not a valid date, time or timestamp"
Else
Stop
GoTo Done
End If
Wend
Done:
MsgBox "Licensing is now populated", vbOKOnly, "Process Complete"
rsNotes.Close
rsSQL.Close
connSQL.Close
connNotes.Close
Set rsSQL = Nothing
Set rsNotes = Nothing
Set connSQL = Nothing
Set connNotes = Nothing
End Sub
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You are right about not removing the problem. I selected one field that I knew was populated for every record properly and it selected and inserted without issue. T
Worked: Set rsNotes = connNotes.Execute("SELECT CompanyNo FROM Licensing_Profile")
I'll just add fields back into the working code a group at a time until I find the culprit. (I had also remarked out all the corresponding insert statements which I'll leave that way until I get the above line working with out crashing).
Doesn't work: Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")
To test it a little more, I copied all 246 documents so I had 492 documents and the altered "select" statement inserted perfectly.
Thanks! I'm going to mark this as closed since it makes perfect sense and works like a charm with only 1 field thus proving out that the problem exists in one of the Notes fields.
Worked: Set rsNotes = connNotes.Execute("SELECT CompanyNo FROM Licensing_Profile")
I'll just add fields back into the working code a group at a time until I find the culprit. (I had also remarked out all the corresponding insert statements which I'll leave that way until I get the above line working with out crashing).
Doesn't work: Set rsNotes = connNotes.Execute("SELECT * FROM Licensing_Profile")
To test it a little more, I copied all 246 documents so I had 492 documents and the altered "select" statement inserted perfectly.
Thanks! I'm going to mark this as closed since it makes perfect sense and works like a charm with only 1 field thus proving out that the problem exists in one of the Notes fields.
ASKER
Thanks for all your help!
ASKER
By the way, it was my Lotus field named "Time" ... I probably should have looked for a reserved word field right off the bat!
Time... Maybe there was one document with a field "Time" that didn't have date/time contents?? Because, after all, it worked on the other 244 documents. Anyway, glad you found it!
ASKER