CODEWITHSUNDEEP
vb.netado.netdatatable
Bramenath
Bramenath

Reputation: 117

Read from database and fill DataTable

I'm getting a set of data by a DataReader and assigning to a string. Now I need to fill the DataTable columns with the query fields. The DataTable is connected to a grid to display the filled data.

query is : 

strSQL = "SELECT EmpCode,EmpID,EmpName FROM dbo.Employee

DataTable columns are EmpCode, EmpID, EmpName.

I need to read the query and assign to the columns of DataTable and fill the table. I have tried as below but i dont get the proper output, 

Me.DtShifts.Tables("NonAllocated").Clear()
Me.DtShifts.Tables("NonAllocated").Load(dr)

Upvotes: 9

Views: 135635

Answers (2)

Snookger Isolation
Snookger Isolation

Reputation: 21

Private Function LoaderData(ByVal strSql As String) As DataTable
    Dim cnn As SqlConnection
    Dim dad As SqlDataAdapter

    Dim dtb As New DataTable
    cnn = New SqlConnection(My.Settings.mySqlConnectionString)
    Try
        cnn.Open()
        dad = New SqlDataAdapter(strSql, cnn)
        dad.Fill(dtb)
        cnn.Close()
        dad.Dispose()
    Catch ex As Exception
        cnn.Close()
        MsgBox(ex.Message)
    End Try
    Return dtb
End Function

Upvotes: -1

SSS
SSS

Reputation: 5413

Connection object is for illustration only. The DataAdapter is the key bit:

Dim strSql As String = "SELECT EmpCode,EmpID,EmpName FROM dbo.Employee"
Dim dtb As New DataTable
Using cnn As New SqlConnection(connectionString)
  cnn.Open()
  Using dad As New SqlDataAdapter(strSql, cnn)
    dad.Fill(dtb)
  End Using
  cnn.Close()
End Using

Upvotes: 30

Related Questions