Asked by:
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 ClassFriday, 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
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,
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 MSDNFSF@microsoft.com.- Proposed as answer by Stanly Fan Thursday, May 10, 2018 9:31 AM
Monday, May 7, 2018 1:38 AM