binding datagrid to access table

[toon10]

Hi

I’m trying to create a datagrid based on results from an Access database. I have achieved this using SQL but my new project uses Access. Basically, the user selects a part number from the main form and then clicks on a results button. A new form appears and a datagrid is dynamically built showing all records in a table where the part number = the one selected.

I get a data type mismatch error on my bindingSource1.DataSource code.

Here’s my code

    Private Sub InitializeDataGridView()
        Dim varSQL As String
        varSQL = ("SELECT * FROM [tblCost] WHERE [Part Number] = " & frmMain.cboPartNumber.Text & " ORDER BY [Part Number]")

        Dim AccessConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & varSource & ";Persist Security Info=True;Jet OLEDB ")
        AccessConn.Open()
        Dim AccessCommandPartNoRetrieve As New System.Data.OleDb.OleDbCommand(varsql)

        With AccessCommandPartNoRetrieve
            .Connection = AccessConn
            .CommandType = CommandType.Text
        End With

        Try
            With Me.dataGridView1 'configure the DataGridView
                'set the datagrid allow permissions
                .AllowUserToAddRows = False
                .AllowUserToDeleteRows = False
                .AllowUserToOrderColumns = True
                .AllowUserToResizeRows = True
                .AutoGenerateColumns = True
                bindingSource1.DataSource = AccessCommandPartNoRetrieve.ExecuteNonQuery
                .DataSource = bindingSource1 'bind the datasource to the datagrid
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.AllCellsExceptHeaders
                .AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
                .BorderStyle = BorderStyle.Fixed3D
                .EditMode = DataGridViewEditMode.EditOnEnter
            End With

            If bindingSource1.Count < 1 Then 'no records
                MsgBox("There are no records matching criteria", MsgBoxStyle.Exclamation, "Filter Results")
                'Me.Close()
            End If

            AccessConn.Close()

        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Data results error")
            System.Threading.Thread.CurrentThread.Abort()
        End Try
    End Sub

Has anyone got any ideas on how to achieve this?

Thanks

 

[jebenson]

You are getting this error because ExecuteNonQuery returns the number of rows affected, not a datatable, dataset, dataview or anything else that can actually be used as the datasource for the bindingSource1. You need to create a datatable and an OleDbDataAdapter, fill the datatable from the dataadapter, then assign the datatable to the bindingsource:

Dim AccessCommandPartNoRetrieve As New System.Data.OleDb.OleDbDataAdapter(varsql, AccessConn)

Dim dt As Datatable

dt = New DataTable

AccessCommandPartNoRetrieve.Fill(dt)


'blah blah blah

bindingSource1.DataSource = dt



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[toon10]

Hi

Thanks for the reply. What you say makes sense. I'm rather new to .NET!

I'm still getting the same error of a data type mismatch on bindingSource1.DataSource = dt. I'll look on msdn for creating DataTable's.

Thanks

 

[toon10]

Actually, it throws the error out at the

AccessCommandPartNoRetrieve.Fill(dt) line.

 

[jebenson]

Check your SQL, it sounds like the criteria in a Where clause is not matching. This is usually cause by enclosing numeric values in single quotes, or not enclosing string values in single quotes.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day! Ye has a choice: talk like a pira

 

[toon10]

Indeed you are correct. I made the school boy error of not using single quotes for my Part Numbers which are of type text.

Thanks for the help, it's appreciated!