Visual Basic Forum for Visual Basic Programmers VB

[termid0g]

Hi There,

The following snippet of code is used to import rows from an excel spreadsheet into an access database. The only problem is that if there is a data type mismatch (i.e. text is imported into a date field), the code doesn't return a descriptive error message.

I want it to display a message informing the user which column failed.

Any help would be appreciated.

Thanks!

If Not (rsXL.BOF And rsXL.EOF) Then
rsXL.MoveFirst
Do
rsImport.AddNew
rsImport.Collect("CollectionID") = mCollectionID
For c = 0 To ctGrid1.ItemCount - 1
If ctGrid1.ColumnBackColor(c) = RGB(225, 225, 225)Then 'column was selected
rsImport.Collect(ctGrid1.ColumnText(c)) = rsXL.Collect(c)
End If
Next c
rsXL.MoveNext
Loop Until rsXL.EOF
End If


On Error GoTo Import_Error
mCN.BeginTrans
Trans = True
Set rsImport.ActiveConnection = mCN
rsImport.UpdateBatch
mCN.CommitTrans
End If

Import_Exit:
MsgBox "Import Succeeded", vbOKOnly
Unload Me
Exit Sub

Import_Error:
MsgBox Err.Description, vbCritical, "Import Failed"
If Trans Then mCN.RollbackTrans
Exit Sub

[dougthomas]

Hi,

What exactly do you get out of Err.Description ?

If it is literally nothing you could try Err.LastDllError which returns a system error number. Trouble is that there are quite a few of those ! (you can find a complete list at: [link]) and they might not be as specific as you need.

I know it's going to take additional time, but you'd be better off actually doing a data vet yourself to ensure everything is OK, before the update. At least you'd be in total control and be able to explicitly point out the errors using phrases the user can understand.(rather than the "techie language")
(I "love" the "Invalid use of NULL" message) =D although my favourite of all time is "Unexpected Error" (as opposed to an expected one ? and if it wasn't expected how come there's an error message?, the programmer must have expected it!)

Regards
Doug

[termid0g]

"Error during multi-step operation." !!!

It's appearing when I try to insert a string into a date field. I'm hoping that I can provide a more meanigful error message without rewriting the whole function.

One potential solution would be to try to convert the source data to the target data type before kicking off updatebatch, but that'd be sort of kludgey imho.

Any more ideas?

Thanks!

[dougthomas]

Hi again,

I'm not sure about "kludgey"

Something somewhere has to take responsibility for Data Validation, ie making sure that the user is putting in values in the range and format that is expected.

Under normal circumstances this would be done as "close" to the user as possible, - in your case, when the data in the spreadsheet was actually entered. Given that this has not happened here you have a choice:

(1) Get the user to re-enter all the data in the correct format -
if there's a few records this may not be a problem, if there are hundreds then they may be slightly upset ("why wasn't I told after I put the first one in incorrectly?")

(2) Convert what's been put in, to something that will be acceptable. AND put some validation checks on the spreadsheet entry - so it doesn't happen again !


I don't think you've got many more options.

Regards
Doug