I'm trying a sample program to list a field in an MS SQL db. The connection works but I get a message "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Invalid column name 'DOCS_ID'." when the objDataAdapter.Fill(objDataTable) instruction is executed (as I step through).

I've done a similar example with MS Access and it works fine. This is a variation on the "Visual Basic 2005 Databases" book Chapter 2 Exercise 1.

The code:
        Dim strConnectionString As String = _
            "Provider=SQLOLEDB;" & _
            "Data Source=ABC_Server;" & _
            "Database=ABC_database;" & _
            "User ID=user_id;Password=user_password"
        Dim objConnection As New OleDbConnection(strConnectionString)
        Dim strSQL As String = "SELECT DOCS_ID"
        Dim objCommand As New OleDbCommand(strSQL, objConnection)
        Dim objDataAdapter As New OleDbDataAdapter(objCommand)
        Dim objDataTable As New Data.DataTable("DOCUMENT_STORE")
        Dim objDataRow As DataRow

        Try
            'open db connection
            objConnection.Open()

            'fill the Datatable object
            objDataAdapter.Fill(objDataTable)

            'load the list box on the form
            For Each objDataRow In objDataTable.Rows
                ListBox1.Items.Add(objDataRow.Item("DOCS_ID"))
            Next

        Catch oledbexceptionerr As Exception
            Debug.WriteLine(oledbexceptionerr.Message)

            'catch invalidoperationexceptionerr as InvalidOperationException)
            ' Debug.WriteLine(InvalidOperationException.Message)
        End Try

Jim

You are missing the rest of the query. I assume you want:

Dim strSQL As String = "SELECT DOCS_ID FROM DOCUMENT_STORE"


-Peter

That's not it.

I get "A first chance exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll
Invalid object name 'dbo_THG_DOCS_DOCUMENT_STORE'."

In the example from the book, the "FROM" statement isn't there. I ASSUME that it gets is from this:

Dim objDataTable As New Data.DataTable("dbo_THG_DOCS_DOCUMENT_STORE")

But I don't know...

Jim

No. Dim objDataTable as New DataTable() is a memory resident object and, for all intents and purposes, it can be used as a container object to store a result set returned from a SQL query and do any manipulation that you may need.

That is what you do here:
objDataAdapter.Fill(objDataTable)

You are filling the datatable with the result set of your query.

The error you are getting is being generated by your RDBMS, not your code so, as Peter pointed out, I believe it has something to do with your Query OR in your query string you are not defining the correct catalog that the Table you are trying to select data from belongs to.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========

As Doug pointed out...

   Dim objDataTable As New Data.DataTable("DOCUMENT_STORE")

this line is meaningless to the database. You could just as well do this:

   Dim objDataTable As New Data.DataTable("bologna")

and this line

   objDataAdapter.Fill(objDataTable)

would still load the data. The name you provide when you instantiate the DataTable class is used strictly for reference within the dataset (when it's used in a dataset). If you are only creating a DataTable, you don't even need to provide it a name.

All this said, in the end you still need to provide a query that returns data. This: "SELECT DOCS_ID" doesn't return data. The database engine is going to try looking for an object name "DOCS_ID" which it can't find so it's giving you the error.

-Peter

Thanks to all. I'm a newbie at this and you've help clarify the problem for me. I'm still looking into it. I have an MS Access ODBC connection to the SQL db and the query I use there works fine. Hmmmm.

Jim

Are you saying you are using a Microsoft Access driver to connect to Microsof SQL Server?

Also, at least in MS Sql Server and MySQL the only time a select statement will work without a reference to a table is if you do something like this:

SELECT 'foo'

Executing this command will return the string literal value of foo as your result set in all other cases you need to specify where you are selecting data from so, for the sake of argument, humor me.

Change your SQL statement from:
SELECT DOCS_ID

to
SELECT DOCS_ID FROM DOCUMENT_STORE (assuming that Document_Store is the name of a table in the database you have supplied in your connection string.)

What does that do to your code?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for: Professional Search Engine Optimization with ASP.NET
http://www.wiley.com/WileyCDA/WileyT...470131470.html
================================================== =========
Why can't Programmers, program??
http://www.codinghorror.com/blog/archives/000781.html
================================================== =========