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)
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
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
Source: Stackoverflow.com