Converting an Integer to a String from DGV to XML

Good Morning,

The below code works and does what it is supposed to do.

Private Sub btnMUnitSelNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnMUnitSelNext.Click

        Try

            Dim dt As New DataTable()
            For Each col As DataGridViewColumn In dtgMResults.Columns
                dt.Columns.Add(col.HeaderText)

            Next

            dt.Rows.Add()

            For j As Int32 = 0 To Me.dtgMResults.ColumnCount() - 1
                dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value

            Next

            dt.TableName = "tblUnits"

            'Write datatable to XML file
            dt.WriteXml("C:\XMLFile.xml")

        Catch ex As Exception
            MessageBox.Show("Exception: " + ex.Message)
        End Try

        frmChqNumVerify.Show()

    End Sub

Open in new window


The problem however is that one of my columns (NextChqNum) in the datagridview contains leading zeros.

So when the DGV selection is written to the XML file, the zeros for  the affected column are removed. Example: 030273 will be written to the xml as 30273.

DGV xmlfile
I understand that numbers cannot have a leading zero, so when writing to the XML file the zeros are being removed.

Can someone help me to modify my code where upon writing to the XML file the 'NextChqNum' numbers for the selection is converted to a string?

Kindly advise.

Regards,
N
LVL 1
KevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fernando SotoRetiredCommented:
Can you show the data type for this object, Me.dtgMResults, and how it is populated please.
0
KevinAuthor Commented:
Hi,

The DGV is being populated from an Access 2010 database. Code below:

  'Populate datagridview with data
        Dim conn As New OleDbConnection
        Dim da As New OleDbDataAdapter
        Dim ds As DataSet
        Dim tables As DataTableCollection
        Dim UnitSrc As New BindingSource

        'Try catch block for when no history exists

        Try

            'get connection string declared in the modFunctions.vb and assing it to conn variable

            conn = New OleDbConnection(sConnString)
            conn.Open()
            ds = New DataSet
            tables = ds.Tables
            da = New OleDbDataAdapter("Select * from [tblUnits]", conn)
            da.Fill(ds, "conn") 'Database name
            Dim view As New DataView(tables(0))
            UnitSrc.DataSource = view
            dtgMResults.DataSource = view

            'Displays with leading 0's in DGV
            dtgMResults.Columns("NextChqNum").DefaultCellStyle.Format = "000000"

            Me.dtgMResults.Columns("UnitID").Visible = False
            Me.dtgMResults.Columns("CreditAccNum").Visible = False
            'Me.dtgMResults.Columns("NextChqNum").Visible = False

            If dtgMResults.Rows.Count = 0 Then
                MsgBox("No records found!")
            End If

        Catch ex As Exception
            MsgBox(ErrorToString)
        Finally
            conn.Close()
        End Try

Open in new window


The properties of the 'NextChqNum' column in access is set to a 'Number' with the below general settings within Access.

Access
Do let me know if you require anything further.

Kind Regards,
N
0
Fernando SotoRetiredCommented:
Hi nobushi;

OK, the DataGridView is being loaded from the database and the column NextChqNum holds a numeric value. When the data is loaded into the DataGridView the column information is also loaded with the column name and data type as Integer. So in order to set the data type to String with leading zeros you can do the following, which should work. Change your for loop as follows.

For j As Int32 = 0 To Me.dtgMResults.ColumnCount() - 1
    If NextChqNum <> 5 Then
        dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value
    Else
        dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value.ToString("000000")
    End If
Next

Open in new window

0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

KevinAuthor Commented:
Hi Fernando,

Thank you for your response.

However it doesnt appear to be working. I set a breakpoint on the loop and at one point it does show the type as a string (below):

string
But then it loops once more and goes back to an integer (below) at no point do i see it hit that 'else' statement.

integer
Now it could be that I have the incorrect syntax as i changed what you had suggested for line 2:

If NextChqNum <> 5 Then

Open in new window


To this:

If dtgMResults.Columns.Contains("NextChqNum") <> 5 Then

Open in new window


Because it was complaining that the 'NextChqNum' was not declared.

Also could you please explain to me what the '<> 5' means? I am still learning and would like to know what the code looking for here.

Thanks,
N
0
Fernando SotoRetiredCommented:
Sorry nobushi;

My last post should have been the following. We are looking for column 6 which has the index 5 and when we find it use the statement with the ToString("000000") method in it.

For j As Int32 = 0 To Me.dtgMResults.ColumnCount() - 1
    If j <> 5 Then
        dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value
    Else
        dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value.ToString("000000")
    End If
Next
0
KevinAuthor Commented:
Thanks for the correction and the explanation Fernando.

However it's still not working. Below are some examples. I dont know where its pulling the values from now though.

ex1
ex2
ex3
Kindly advise.

Regards,
N
0
Fernando SotoRetiredCommented:
Please place a breakpoint on this line of code.

 dt(0)(j) = dtgMResults.SelectedRows(0).Cells(j).Value.ToString("000000")

Open in new window


When it breaks on that line hover your cursor over dt(0)(j) to see what value is being assigned.
0
KevinAuthor Commented:
No value is being assigned just has a '{}'

ex1
0
Fernando SotoRetiredCommented:
That is not telling me much. Do the same thing again but this time hover over Value as you did in your post here. Then post the results here.
0
CodeCruiserCommented:
Perhaps change your query to

Select othercolumnnames, Format(NextChqNum, "00000") As NextChqNum From ...


which would result in NextChqNum being converted to string by the query and then everything else will treat it as string.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
KevinAuthor Commented:
Thank you both for your help.

Adjusting the query for the 'NextChqNum' to be converted to string from that point worked.

Kind Regards,
N
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.