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

  • 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)
            dgvwDatos.DataSource = ds.Tables(0)
        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

            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
            Hoja.Rows.Item(1).Font.Bold = 1
            Hoja.Rows.Item(1).HorizontalAlignment = 3
            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
        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.

        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
    Friday, May 4, 2018 12:31 PM

All replies

  • 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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Friday, May 4, 2018 1:34 PM
  • 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,


    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 MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Thursday, May 10, 2018 9:31 AM
    Monday, May 7, 2018 1:38 AM