vb.net searching access database returns date error when using #

Question

I've been struggling to figure this one out, and keep running into the same issue. I have an Access database that has linked Excel Files. The title of the excel file is Store # and the pound symbol is where I'm running into issues.

New OleDbCommand("SELECT * FROM LSM WHERE Store # = @Store", myConnection)
    lstscmd.Parameters.AddWithValue("@Store", CInt(gateDBSearch))

If I remove the # symbol, and remove it in my code it works (The richtexbox shows only the result for the record searched), unfortunately this isn't an option in the final version. If I leave the # symbol in the file, but remove it from the code, the richtextbox fills with all of the results. If I leave the # symbol on both the code and file I get a Syntax error in date in query expression 'Store # = @Store' exception. Any help on this would be great.

Dim lstscmd As OleDbCommand = New OleDbCommand("SELECT * FROM LSM WHERE Store # = @Store", myConnection)
        lstscmd.Parameters.AddWithValue("@Store", CInt(gateDBSearch))
        dr = lstscmd.ExecuteReader
        While dr.Read()

            txtRichInfo.AppendText(dr("Gate Tech").ToString + Environment.NewLine)
            lblAccessSysChange.Text = dr("Access System").ToString

        End While

        myConnection.Close()
        lblAccessSysChange.Visible = True

SLaks · Accepted Answer · 2016-10-27 15:41:13Z

1

Because your field name contains special characters, you need to wrap it in square brackets to tell the SQL parser that it's all one name.

answered Oct 27, 2016 at 15:41

SLaks

891k182 gold badges1.9k silver badges2k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Joshm Over a year ago

Thank you so much! A co-worker and I kept on trying every different thing and couldn't get it to work. This worked perfectly!

Collectives™ on Stack Overflow

vb.net searching access database returns date error when using #

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related