error adding class module to read data from delimited file

Hi;
I am attempting to read data from a comma-delimited file and eventually be able to view and manipulate the data. The data's eventual destination will be EXCEL where I can manipulate my desired data from particular fields in the original data source which again is comma-delimited format. To be honest, I have bumped into an example in MDSN library that sounded quite interesting for my needs. The title of this article is
CREATING A DATA-AWARE CLASS THAT READ RECORDS FROM A DELIMITED TEXT FILE(it describes that by creating a data-aware class, you can read data from a delimited text file into an ADO recordset and use the features of ADO to manipulate the data. The next article which is connected to the whole process is also what I continued to use in the program. The article is called CREATING A FORM THAT LETS YOU VIEW AND UPDATE DATA FROM DATA-AWARE CLASS.
I created a simple example comma-delimited file called "customers1.txt"; just to get the ball rolling to see if the example will work.The actual files I am to be working with will have about 234 fields. However I am running into an error for some reason constructing th form,class module and form code as it is illustrated in the MDSN library. GEEZ does Microsoft authors make mistakes?
Below is the contents of my simple example of a comma-delimited file with header names;

NameID,Name,Address,Phone
1,Dan Jones,Seattle Washington,777-6789
2,Steve Harrison,Los Angeles Ca,889-1973
3,Jethro Smith,Boston Ma,232-7782

I then started a new standard EXE project and added a class module from the Project Menu.
I set the Property name to ----CustomerDataSource
I set the property dataSourceBehavior to----vbDataSource

'below line of code went in the declaration section for the class
Public rsCustomers As ADODB.Recordset

Private Sub Class_GetDataMember(DataMember As String, Data As Object)
Set Data = rsCustomers

End Sub

Private Sub Class_Initialize()

Dim fld As ADODB.Field
Dim strRow As String
Dim strField As String
Dim intPos As Integer

Set rsCustomers = New ADODB.Recordset


With rsCustomers
'Set NameID as the primary key.
.Fields.Append "NameID", adChar, 5, adFldRowID
.Fields.Append "Name", adChar, 40, adFldUpdatable
.Fields.Append "Address", adChar, 60, adFldUpdatable
.Fields.Append "Phone", adChar, 24, adFldUpdatable
'Use keyset cursor to allow updating records.
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With

Open "C:Customers1.txt" For Input As #1

Do Until EOF(1)
Line Input #1, strRow
With rsCustomers
.AddNew
For Each fld In .Fields
'If a comma delimiter is found,field text is
'to the left fo the delimiter
If InStr(strRow, Chr(44)) <> 0 Then
'Move position to comma delimiter
intPos = InStr(strRow, Chr(44))
'Assign field text to strField variable
strField = Left(strRow, intPos - 1)
Else
'If a comma delimiter isn't found, field
'text is the last field in the row
strField = strRow
End If


'Strip off quotation marks.
If Left(strField, 1) = Chr(34) Then
strField = Left(strField, Len(strField) - 1)
strField = Right(strField, Len(strField) - 1)
End If


'this line of code below is the one generating a yellow highlight for
'error message which I will explain more in depth later at the end

fld.Value = strField

'Strip off field value text from text row.
strRow = Right(strRow, Len(strRow) - intPos)
intPos = 0


Next
.Update
.MoveFirst
End With
Loop
Close

End Sub


Now that is it for the work necessary to finish the class module. I then moved on within the same project to work on construction of the form with textbox control and labels where I bind the textbox to the fields in the recordset,. To be honest the second MDSN article contained phrasing that said the easiest way to create an interface is to open a NEW Standard EXE project, then add TextBox and Label controls to a form. I am alittle confused by this since I would think one would remain in ths same project as the new class module I created and just create the form and code within the same project-----whatever???
I created 4 labels and 4 textbox controls
Label1(0) name---label1
Caption----NameID

Label1(1) name----label1
Caption-----Name

Label1(2)name-----label1
Caption---Address

Label1(3)name------label1
Caption----Phone

Obviously these labels were set up in an array for ease of convenience.The textbox controls were in similar format with code to bind them to fields in the recordset.

txtNameID(0) name------txtNameID
txtName(1) name------txtName
txtAddress(2) name------txtAddress
txtPhone(3) name------txtPhone


I also added 4 command buttons to the form for PREVIOUS,NEXT, LAST and FIRST RECORD commands.





‘below 2 lines of code were added to the declarations section of the form
Private objDataSource As CustomerDataSource
Private colBind As BindingCollection

Private Sub cmdFirst_Click()
objDataSource.rsCustomers.MoveFirst


End Sub

Private Sub cmdLast_Click()
objDataSource.rsCustomers.MoveLast

End Sub

Private Sub cmdNext_Click()
objDataSource.rsCustomers.MoveNext

End Sub

Private Sub cmdPrevious_Click()
objDataSource.rsCustomers.MovePrevious

End Sub

Private Sub Form_Load()

Set objDataSource = New CustomerDataSource
Set colBind = New BindingCollection

Set colBind.DataSource = objDataSource
col.Bind.Add txtNameID, "Text", "NameID"
col.Bind.Add txtName, "Text", "Name"
col.Bind.Add txtAddress, "Text", "Address"
col.Bind.Add txtPhone, "Text", "Phone"


End Sub


HERE IS THE PROBLEM.WHEN I RUN THE PROGRAM, I GET RUN-TIME ERROR

-2147217887(80040e21):

Multiple step operation generated errors.Check each status value.


When I click on the DEBUG button, the code line in YELLOW is;

Fld.Value = strField


I am just kinda of lost as what to look for to correct whatever is wrong. ADO in general compared to regular VB6 can be quite overwhelming so far. Can you help me at all. Thanx!


MAKO




Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories