Ok, I didn't realize you had a physical TableAdapter in your application, I assumed you were doing it in the code, so I apologize for that. I personally prefer to do it all in the code not with dragging a TableAdapter to the application (the .xsd File).
If you want to do it in the code instead of through the XSD file try this, add a class to your application, name it DataAccess, (make sure to add this statement at the top of the class)
CODE
Imports System.Data.OleDb
Imports System.Configuration
Imports System.Configuration.ConfigurationSettings
You may have to click on
Project,
Select Add Reference then scroll down and select those to have them in the project. Then add this function to it., for returning a BindingSource for your DataGridView
CODE
''' <summary>
''' Returns a BindingSource, which is used with, for example, a DataGridView control
''' </summary>
''' <param name="cmdSql">"pre-Loaded" command, ready to be executed</param>
''' <returns>BindingSource</returns>
''' <remarks>Use this function to ease populating controls that use a BindingSource</remarks>
Public Shared Function GetBindingSource(ByVal cmdSql As SqlCommand) As BindingSource
'declare our binding source
Dim oBindingSource As New BindingSource()
' Create a new data adapter based on the specified query.
Dim daGet As New SqlDataAdapter(cmdSql)
' Populate a new data table and bind it to the BindingSource.
Dim dtGet As New DataTable()
'set the timeout of the SqlCommandObject
cmdSql.CommandTimeout = 240
dtGet.Locale = System.Globalization.CultureInfo.InvariantCulture
Try
'fill the DataTable with the SqlDataAdapter
daGet.Fill(dtGet)
Catch ex As Exception
'check for errors
MsgBox(ex.Message, "Error in GetBindingSource")
Return Nothing
End Try
'set the DataSource for the BindingSource to the DataTable
oBindingSource.DataSource = dtGet
'return the BindingSource to the calling method or control
Return oBindingSource
End Function
Then in your button click procedure change it to this
CODE
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'create your OleDb Objects
Dim cnConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\MAOL.mdb;")
Dim cmdCommand As New OleDbCommand()
Dim daAdapter As New OleDbDataAdapter
Dim dtSet As New DataTable
'set your OledBCommand Properties
With cmdCommand
.CommandText = ("SELECT * FROM Accounts WHERE Username = 'Admin'") 'sql statement to execute
.CommandType = CommandType.Text 'let it know you're executing a string not a stored procedure
.Connection = cnConnection 'set its connection
End With
Try
Dim oBinding As BindingSource = DataAccess.GetBindingSource(cmdCommand)
'Now bind your grid
dgvAccounts.DataSource = oBinding
'catch any errors
Catch ex As Exception
'if theres an error fisplay it to the user
MessageBox.Show(ex.Message, "Error: Retrieving Data", MessageBoxButtons.OK, MessageBoxIcon.Error)
End Try
End Sub
I always suggest to do your data access in code anyways, not using the TableAdapter Control. I had to delete your original Form as it was really acting freaky, and also delete the reference to the .xls file and connection in the Settings of
MyProject.
I am attaching the updated project for you to look at. If you insist on using the BindingSource Control and the .xls file I will have to look into these as I've never used them before.
Happy Coding!
7 Aug, 2007 - 09:43 AM
|