Resolved Exporting from Access to Text File

gwbasic

New member
Joined
Apr 25, 2010
Messages
4
Programming Experience
10+
Hi, this is my problem: i've some data in an access database, i collect this data from the table and when i try to record the data in the text file, with the fields separated with a "!", the registration fails, better, doesn't register all the datas that are in the table.

This is the code:

VB.NET:
Dim tw As TextWriter = File.AppendText(Application.StartupPath & "\DStampa.csv")
        Dim objDataTable As DataTable
        Dim TotaleDatiStampa As String = ""

        objDataTable = objData.QueryDatabase("SELECT * FROM SStato ORDER BY Cam, Dal")
        If objDataTable.Rows.Count <> 0 Then
            TotaleDatiStampa = ""
            For i = 0 To objDataTable.Rows.Count - 1
                If Not IsDBNull(objDataTable.Rows(i).Item("Cam")) Then TotaleDatiStampa = objDataTable.Rows(i).Item("Cam").ToString & "!" Else TotaleDatiStampa = "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Stato")) Then TotaleDatiStampa = TotaleDatiStampa & objDataTable.Rows(i).Item("Stato").ToString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Dal")) Then TotaleDatiStampa = TotaleDatiStampa & CDate(objDataTable.Rows(i).Item("Dal")).ToShortDateString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Al")) Then TotaleDatiStampa = TotaleDatiStampa & CDate(objDataTable.Rows(i).Item("Al")).ToShortDateString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("MieNote")) Then TotaleDatiStampa = TotaleDatiStampa & objDataTable.Rows(i).Item("MieNote").ToString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If i < objDataTable.Rows.Count - 1 Then i = i + 1
            Next
        End If
        tw.WriteLine(TotaleDatiStampa)
        tw.Close()
        objDataTable = Nothing
Now, i did not understand why also if the table has two rows, in the text file i register only one.
Seems that the problem is that the saving of rows in the db access is not yet completed, because when i bypass in debug mode the code, perche' quando vado in debug, sometime the loop say me that are present two rows and not one.

Help me, because i'vent no idea on the solution.

Thanx to all.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,319
Location
Sydney, Australia
Programming Experience
10+
The first point to note is that you should basically NEVER increment a For loop counter explicitly, so get rid of this line for a start:
VB.NET:
If i < objDataTable.Rows.Count - 1 Then i = i + 1
The next point to note is that you can simplify this:
VB.NET:
        If objDataTable.Rows.Count <> 0 Then
            TotaleDatiStampa = ""
            For i = 0 To objDataTable.Rows.Count - 1
                If Not IsDBNull(objDataTable.Rows(i).Item("Cam")) Then TotaleDatiStampa = objDataTable.Rows(i).Item("Cam").ToString & "!" Else TotaleDatiStampa = "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Stato")) Then TotaleDatiStampa = TotaleDatiStampa & objDataTable.Rows(i).Item("Stato").ToString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Dal")) Then TotaleDatiStampa = TotaleDatiStampa & CDate(objDataTable.Rows(i).Item("Dal")).ToShortDateString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("Al")) Then TotaleDatiStampa = TotaleDatiStampa & CDate(objDataTable.Rows(i).Item("Al")).ToShortDateString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If Not IsDBNull(objDataTable.Rows(i).Item("MieNote")) Then TotaleDatiStampa = TotaleDatiStampa & objDataTable.Rows(i).Item("MieNote").ToString & "!" Else TotaleDatiStampa = TotaleDatiStampa & "!"
                If i < objDataTable.Rows.Count - 1 Then i = i + 1
            Next
        End If
down to this:
VB.NET:
For Each row As DataRow In objDataTable.Rows
    TotaleDatiStampa = String.Format("{0}!{1}!{2}!{3}!{4}", _
                                     row("Cam"), _
                                     row("Stato"), _
                                     row("Dal"), _
                                     row("Al"), _
                                     row("MieNote"))
Next
Any null values will automatically be converted to empty strings when the formatting is done. Much neater, yes?

Now, with regards to the problem, you're only actually writing to the file once, at the end, after the loop. That means that you will only ever write the last record. If you want to write every record then you need to write inside the loop.

It's also worth noting that the StreamWriter itself supports formatting, so you don't even need that TotaleDatiStampa variable. In the above code, you just replace:
VB.NET:
TotaleDatiStampa = String.Format
with:
VB.NET:
tw.WriteLine
and the formatted text will be written directly to the file.
 

gwbasic

New member
Joined
Apr 25, 2010
Messages
4
Programming Experience
10+
jmcilhinney, very good piece of code. You've drastically reduced the lines of code with only one.

Tnx for your interest. I've seen that the problem not was generated only by the loop but also by a wrong registration of the dates in the db, so, when the datatable try to read the rows it load only some rows and not all.

I hope that is this the real problem; so, if i see that the other persist, i'll try a solution with you in the forum.

Thanks!
 
Top Bottom