Member Avatar for sushilsth 
  Private Sub btnload_Click(sender As Object, e As EventArgs) Handles btnload.Click
        Dim sqlquery As String = "select frmStudentReg.regno,nursery.rollno,[nursery.nepali(writing)],[nursery.nepali(oral)],[nursery.maths(writing1)],[nursery.maths(oral)],[nursery.maths(writing2)],[nursery.english(writing)],[nursery.english(oral)],[nursery.physical_edu(oral)],[nursery.creative(oral)] from frmStudentReg,nursery where frmStudentReg.regno=nursery.regno"
        Dim sqlcommand As New OleDbCommand
        Dim sqladapter As New OleDbDataAdapter
        Dim table As New DataTable
        With sqlcommand
            .CommandText = sqlquery
            .Connection = conn
            .ExecuteNonQuery()

        End With
        With sqladapter
            .SelectCommand = sqlcommand
            .Fill(table)
        End With
        DataGridView1.Rows.Clear()

        For i = 0 To table.Rows.Count - 1
            With DataGridView1
                .Rows.Add(table.Rows(i)("frmStudentReg.regno"), table.Rows(i)("nursery.rollno"), table.Rows(i)("nursery.nepali(writing)"), table.Rows(i)("nursery.nepali(oral)"), table.Rows(i)("nursery.maths(writing1)"), table.Rows(i)("nursery.maths(oral)"), table.Rows(i)("nursery.maths(writing2)"), table.Rows(i)("nursery.english(writing)"), table.Rows(i)("nursery.english(oral)"), table.Rows(i)("nursery.physical_edu(oral)"), table.Rows(i)("nursery.creative(oral)"))
            End With
        Next
    End Sub

hi this is my code for loading multiple table data to one datagridview. but i found the type mismatch expression error.. i think its error in my query. plz help asap..

  • 2 Contributors
  • 1 Reply
  • 277 Views
  • 23 Hours Discussion Span
  • Latest Post Latest Post by Begginnerdev

Dani AI

Generated

The error message here most often comes from malformed column identifiers (and from running a SELECT with ExecuteNonQuery). In the posted SQL the square brackets wrap the table-and-column token (for example “[nursery.nepali(writing)]”), which makes the engine treat the whole string as a single identifier that does not exist. Columns that contain parentheses or other special characters should be escaped around the column name only, and it’s much safer to alias them to simple names so the DataTable column names are predictable.

A corrected SELECT that fixes bracket usage and gives simple aliases might look like this:

SELECT f.regno AS RegNo,
       n.rollno AS RollNo,
       n.[nepali(writing)] AS Nepali_Writing,
       n.[nepali(oral)] AS Nepali_Oral,
       n.[maths(writing1)] AS Maths_Writing1,
       n.[maths(oral)] AS Maths_Oral,
       n.[maths(writing2)] AS Maths_Writing2,
       n.[english(writing)] AS English_Writing,
       n.[english(oral)] AS English_Oral,
       n.[physical_edu(oral)] AS Physical_Oral,
       n.[creative(oral)] AS Creative_Oral
FROM frmStudentReg AS f
INNER JOIN nursery AS n ON f.regno = n.regno;

When filling the DataTable, the adapter should execute the query (no ExecuteNonQuery needed). A typical pattern is:

Using cmd As New OleDbCommand(sql, conn)
  Using adapter As New OleDbDataAdapter(cmd)
    Dim table As New DataTable()
    adapter.Fill(table)
    DataGridView1.DataSource = table
  End Using
End Using

Notes and troubleshooting:

  • Test the SQL directly in Access (or the database client) — the engine’s error text is more precise there.
  • Verify the data types of both regno fields; mismatched types in joins/criteria can also produce “type mismatch” errors.
  • ’s idea to build DataGridViewRow is valid for custom rows, but setting DataSource or using aliases avoids fragile name/index lookups.
  • Long term: rename columns to remove parentheses/special characters to prevent repeated escaping.
Member Avatar for Begginnerdev

My guess would be this statement:

.Rows.Add(table.Rows(i)("frmStudentReg.regno"), table.Rows(i)("nursery.rollno"), table.Rows(i)("nursery.nepali(writing)"), table.Rows(i)("nursery.nepali(oral)"), table.Rows(i)("nursery.maths(writing1)"), table.Rows(i)("nursery.maths(oral)"), table.Rows(i)("nursery.maths(writing2)"), table.Rows(i)("nursery.english(writing)"), table.Rows(i)("nursery.english(oral)"), table.Rows(i)("nursery.physical_edu(oral)"), table.Rows(i)("nursery.creative(oral)"))

You need to set column values and I don't think the Rows.Add method allows that.

Something like this would work:

With DataGridView1
    Dim dgvr As New DataGridViewRow
        'add cells/values
    DataGridView1.Rows.Add(dgvr)
End With
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.