[RESOLVED] [2008] Space in mdb table column

[rhijaen]

I have a space in one of my columns in my database that cannot be changed (it causes issues with another app that works with the database).

I've tried "[Set ID]" and "'Set ID'" and neither have worked.

What's the proper way of dealing with a column name with spaces?

            If CStr(nextRow("fname")) = CStr(currentRow("fname")) AndAlso _
               CInt(nextRow("Set ID")) = CInt(currentRow("Set ID")) Then

[VBDT]

I am not sure since it is a long time I didn’t use mbd but I remember the column names should not have space. Why don’t you delete the space and try?

[rhijaen]

Renaming the table in microsoft access causes an issue with another app that uses the database.

[VBDT]

I am not sure about that but one last thing I would sagest is instead of space try it with "_".

[rhijaen]

I am not sure about that but one last thing I would sagest is instead of space try it with "_".

Nope, didn't work

[techgnome]

What is NextRow and CurrentRow?

When referencing field names from the client end, as long as it has quotes around it, it should be fine.... however, in the SQL it needs to have [] around it.

-tg

[rhijaen]

The error I get is: Column 'Set ID' does not belong to table .

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        dt = New DataTable

        OpenFileDialog1.Filter = "mdb files(*.mdb)|*.mdb"

        OpenFileDialog1.ShowDialog()

        con.ConnectionString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & OpenFileDialog1.FileName
        con.Open()
        sql = "SELECT * FROM Files ORDER BY fname, fsize DESC"
        da = New OleDb.OleDbDataAdapter(sql, con)
        da.Fill(dt)
        con.Close()

        Dim currentRow As DataRow
        Dim nextRow As DataRow
        Dim rows As DataRowCollection = dt.Rows
        Dim cb As New OleDb.OleDbCommandBuilder(da)

        For index As Integer = 0 To rows.Count - 2
            currentRow = rows(index)
            nextRow = rows(index + 1)

            If CStr(nextRow("fname")) = CStr(currentRow("fname")) AndAlso _
               CInt(nextRow("Set ID")) = CInt(currentRow("Set ID")) Then
                RichTextBox1.Text = RichTextBox1.Text & vbCrLf & CInt(currentRow("Set ID"))
                RichTextBox1.Text = RichTextBox1.Text & vbCrLf & CInt(nextRow("Set ID"))
                RichTextBox2.Text = RichTextBox2.Text & vbCrLf & CStr(currentRow("fname"))
                RichTextBox2.Text = RichTextBox2.Text & vbCrLf & CStr(nextRow("fname"))
                RichTextBox3.Text = RichTextBox3.Text & vbCrLf & CInt(currentRow("fsize"))
                RichTextBox3.Text = RichTextBox3.Text & vbCrLf & CInt(nextRow("fsize"))
                currentRow.Delete()
            End If
        Next

    End Sub

[techgnome]

Just for kicks... try this.... change your query....

sql = "SELECT * FROM Files ORDER BY fname, fsize DESC"

Change it to this:

sql = "SELECT [Set ID] As SetID, fsize, fname, ....  FROM Files ORDER BY fname, fsize DESC"

Now you should be able to reference it suing the ailias SetID and not have to worry about the space.

-tg

[stanav]

You can also run a simple loop to debug.write the columns' names in your datatable. That will let you see if you actually has a column "Set ID" in the table, and you can go from there to fix it.

[rhijaen]

Just for kicks... try this.... change your query....

sql = "SELECT * FROM Files ORDER BY fname, fsize DESC"

Change it to this:

sql = "SELECT [Set ID] As SetID, fsize, fname, ....  FROM Files ORDER BY fname, fsize DESC"

Now you should be able to reference it suing the ailias SetID and not have to worry about the space.

-tg

Nope, didn't work.

You can also run a simple loop to debug.write the columns' names in your datatable. That will let you see if you actually has a column "Set ID" in the table, and you can go from there to fix it.

How would I do this? I know Filename wasn't the real column, it was fname..found that out the hard way. So maybe "Set ID" isn't really a column?

[techgnome]

k, if that didn't work, then there's something else going on that you aren't telling us. Can you post a screenshot of the code, with the error message?

-tg

[stanav]

To find out what the column names are in your datatable after you have filled it, just do this:

For i as integer = 0 To dt.Columns.Count - 1
    Debug.WriteLine("Column" & i & " name: " & dt.Columns(i).ColumnName())
Next

[techgnome]

I know Filename wasn't the real column, it was fname..found that out the hard way. So maybe "Set ID" isn't really a column?

This is why SELECT * aren't a good idea sometimes.... so... you're not even sure WHAT columns you ARE getting? If this is the case, in the amount of time this has gone around, you probably could have opened the database, looked at the table, and see what the fields names are. Or ask the table owner.

-tg

[rhijaen]

To find out what the column names are in your datatable after you have filled it, just do this:

For i as integer = 0 To dt.Columns.Count - 1
    Debug.WriteLine("Column" & i & " name: " & dt.Columns(i).ColumnName())
Next

Thanks for this. The column name was actually SID. Even though when I open the mdb in access, the column name is "Set ID".

All fixed