asked on

Populating DataTable from SQL

I've been looking around for a tutorial on how to use DataTable.
Below is the code I use to retrieve CustomerName, using DataReader.
How can I convert it to read the data into a DataTable instead?

Dim strConnection As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("myDB").ConnectionString
Dim objConnection = New OleDbConnection(strConnection)	
objConnection.Open()	
	
Dim objCommand as OleDbCommand
Dim objDataReader As OleDbDataReader
 
 
Dim strSQL as String = "SELECT * FROM Customer"
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader(CommandBehavior.Default)	
 
    While objDataReader.Read()
	 Response.Write(objDataReader("CustomerName") & "</ br>")
    End While 
 
objDataReader.Close()
objConnection.Close()

Open in new window

Dirk Haest

Dim objCommand as OleDbCommand
Dim strSQL as String = "SELECT * FROM Customer"
objCommand = New OleDbCommand(strSQL, objConnection)
Dim dtCustomer = GetDataTable(objCommand)

Public Shared Function GetDataTable(ByVal cmd As OleDbCommand) As DataTable 
    Try 
        ' create a new data adapter based on the specified query. 
        Dim da As New OleDbDataAdapter() 
        'set the SelectCommand of the adapter 
        da.SelectCommand = cmd 
        ' create a new DataTable 
        Dim dtGet As New DataTable() 
        'fill the DataTable 
        da.Fill(dtGet) 
        'return the DataTable 
            
        Return dtGet 
    Catch ex As OleDbException 
        MessageBox.Show(ex.Message) 
    Catch ex As Exception 
        MessageBox.Show(ex.Message) 
    End Try 
End Function

Open in new window

ASKER CERTIFIED SOLUTION

jinal

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

dennisdominic

ASKER

jinal,
I get this Error on line 13, the Load command. I've checked that the DataReader works. What did I do wrong? I'm more interested in this method as it is much shorter, even though Dhaest's method works.
System.NullReferenceException: Object reference not set to an instance of an object.

jinal

I tried this at my end it work fine.
Just try to select one column from in Select statement.
Like Select name from Customer from your side and test.

        Dim strConnection As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("myDB").ConnectionString
        Dim objConnection = New OleDbConnection(strConnection)
        objConnection.Open()
 
        Dim objCommand As OleDbCommand
        Dim objDataReader As OleDbDataReader
 
        Dim strSQL As String = "SELECT * FROM Customers"
        objCommand = New OleDbCommand(strSQL, objConnection)
        objDataReader = objCommand.ExecuteReader(CommandBehavior.Default)
 
        Dim dt As New DataTable("Customers")
        dt.Load(objDataReader)
 
        objDataReader.Close()
        objConnection.Close()

Open in new window

jinal

Dim strConnection As String = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("myDB").ConnectionString
Dim objConnection = New OleDbConnection(strConnection)	
objConnection.Open()	
	
Dim objCommand as OleDbCommand
Dim objDataReader As OleDbDataReader
 
Dim strSQL as String = "SELECT * FROM Customer"
objCommand = New OleDbCommand(strSQL, objConnection)
objDataReader = objCommand.ExecuteReader()	
 
Dim dt as new DataTable("Customers")
dt.Load(objDataReader) 
    
objDataReader.Close()
objConnection.Close()

Open in new window

jinal

Try with previous two comments of mine.

SOLUTION

Dirk Haest

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jinal

Hello Dhaest,

I already declared datatable this way.

Dim dt as new DataTable("Customers")

dennisdominic

ASKER

Oops.. I gave points to the wrong guy.
I'm supposed to give 400 to jinal instead.. how can I rectify this?

jinal

Hello Dennisdemonc ,

Assisted solution is same as mine. My previous comment is same as Dhaest. Can you please verify it ?

jinal

Can you please assign some point to me ?

dennisdominic

ASKER

I'm checking with the moderators..
I'm so sorry.

jinal

I will look back at it evening . Try to contact moderator or admin . They will help you out in this case.

dennisdominic

ASKER

The original code by jinal works, but Dhaest was correct to point out that I declared the datatable wrongly.
I used
Dim dt as DataTable
instead of
Dim dt as new DataTable("Customers")