input array is longer than the number of columns in this table help pls (vb.net)

Question

Imports System.Data.SqlClient

Public Class Form1
    Dim table As New DataTable("table")
    Private Sub cmdGetData_Click(sender As Object, e As EventArgs) Handles cmdGetData.Click

        Dim con As New SqlConnection("SERVER=localhost\sqlexpress;database=new1")
        Dim sSQL As String = "SELECT *FROM Table_1"
        Dim ds As New DataSet
        Dim da As SqlDataAdapter
        da = New SqlDataAdapter(sSQL, con)
        con.Open()
        da.Fill(ds)
        dgvwDatos.DataSource = ds.Tables(0)
        con.Close()
    End Sub
    Public Sub GridToExcel(ByVal dgvw As DataGridView)
        Dim Appli As New Microsoft.Office.Interop.Excel.Application
        Dim Libro As Microsoft.Office.Interop.Excel.Workbook
        Dim Hoja As Microsoft.Office.Interop.Excel.Worksheet
        Dim Col As Integer = dgvw.ColumnCount
        Dim Row As Integer = dgvw.RowCount
        Libro = Appli.Workbooks.Add
        Hoja = Appli.Worksheets.Add()

        For i As Integer = 1 To Col
            Hoja.Cells.Item(1, i) = dgvw.Columns(i - 1).Name.ToString
        Next

        For Fila As Integer = 0 To Row - 1
            For e As Integer = 0 To Col - 1
                Hoja.Cells.Item(Fila + 2, e + 1) = dgvw.Rows(Fila).Cells(e).Value
            Next
        Next
        Hoja.Rows.Item(1).Font.Bold = 1
        Hoja.Rows.Item(1).HorizontalAlignment = 3
        Hoja.Columns.AutoFit()
        Appli.Application.Visible = True
        Appli.Application.WindowState = Microsoft.Office.Interop.Excel.XlWindowState.xlMaximized 'Excel Maximizado.
        Hoja = Nothing
        Libro = Nothing
        Appli = Nothing
    End Sub

    Private Sub cmdToExcel_Click(sender As Object, e As EventArgs) Handles cmdToExcel.Click
        GridToExcel(dgvwDatos)
    End Sub

    Private Sub dgvwDatos_CellContentClick(sender As Object, e As DataGridViewCellEventArgs) Handles dgvwDatos.CellContentClick

    End Sub

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'New1DataSet.Table' table. You can move, or remove it, as needed.
        Me.TableTableAdapter.Fill(Me.New1DataSet.Table)

    End Sub

    Private Sub TextBox1_TextChanged(sender As Object, e As EventArgs)

    End Sub

    Private Sub btnadd_Click(sender As Object, e As EventArgs) Handles btnadd.Click
        table.Rows.Add(TextBox1.Text, TextBox2.Text, TextBox3.Text, TextBox4.Text, TextBox5.Text) ---------- problem here
        dgvwDatos.DataSource = table
    End Sub

    Private Sub TextBox5_TextChanged(sender As Object, e As EventArgs)

    End Sub
End Class

Answer 1

What I see is, you most likely dragged a DataGridView onto a form which created a TableAdapter and BindingSource. By default when doing this you get all columns in a table unless you remove or hide one or more DataGridViewColumns.

So let's say there are four columns in the DataGridView and you attempt to add five columns via table.Rows.Add then this is the issue.

Count the column count and if it has less columns the DataColumns in the DataTable this is the problem.

Also I noticed you are mixing TableAdapter with DataAdapter, either one but not both. This can easily account for a mismatch in column count.

---

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

Answer 2

Hi Mesho422,

From your code, you define table as Datatable, but I don't see you declare any columns in this table, maybe you declare it somewhere else. According to your error message, you can check table.columns.count,  the number of columns must match the number of objects you are inserting as a row. If you have created 4 columns in your table, and try to add 5 items, the system doesn't know what to do with them all and you get an exception.

Best Regards,

Cherry 

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].