Importing excel file using for each statement

Question

0.00/5 (No votes)

See more:

I am trying to import an excel file by using a for each statement based upon the DataGridViewTextBoxColumn header text. The problem is that when I import the file it keeps saying that I can not have null values in the "ID" column. Interesting enough, I do not have this problem if I type the code out without the for each state, expressing each individual item as its own object.

I am trying to condense my code so its a lot easier to read, just in case there are future problems.

Here is my code:

For Each item As DataGridViewTextBoxColumn In DataGridView1.Columns

 dtExcelData.Columns.Add(New DataColumn(item.HeaderText, GetType(String)))

 Next

if anyone can help on this much would be appreciated.

What I have tried:

I have tried adding

If item.HeaderText = "ID" Then
Dim exceldat As New DataColumn("ID", GetType(Integer))
exceldat.AutoIncrement = True
exceldat.AutoIncrementSeed = DataGridView1.RowCount
exceldat.AutoIncrementStep = I
dtExcelData.Columns.Add(exceldat)
End If

under the for each statement but it hasn't helped and I keep getting the same error.

Posted 4-Apr-17 9:18am

Member 11856456

Updated 4-Apr-17 11:35am

Add a Solution

Comments

Richard Deeming 4-Apr-17 15:37pm

Do you have one or more blank columns at the end of your grid? Or blank rows at the bottom?

Member 11856456 4-Apr-17 16:02pm

How it is set up is the entire column is blank, as for the excel sheet there is no "ID" column. So, I assume when it is pulling in the information from the excel sheet its placing null or blank values. What I am trying to do is as I import an excel sheet it will automatically produce an integer = 1 per record. So that if there are multiple records it will give a number for that record. Let's say there are 5 records I am importing into the vb.net database, they will have a corresponding number up to 5. 1,2,3,4,5. initially I achieved this when I typed out each excel object in this format:

dtExcelData.Columns.Add(New DataColumn("Last_name", GetType(String)))

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Answer 1 · 2017-04-04T11:35:00

Quote:
if anyone can help on this much would be appreciated.

The error depend on your excel worksheet, we can't run your code.
Use the debugger to inspect variables as your code execute, 1 column is probably an unexpected one.

When you don't understand what your code is doing or why it does what it does, the answer is debugger.
Use the debugger to see what your code is doing. Just set a breakpoint and see your code performing, the debugger allow you to execute lines 1 by 1 and to inspect variables as it execute, it is an incredible learning tool.

Debugger - Wikipedia, the free encyclopedia[^]
Mastering Debugging in Visual Studio 2010 - A Beginner's Guide[^]
Basic Debugging with Visual Studio 2010 - YouTube[^]

The debugger is here to show you what your code is doing and your task is to compare with what it should do.
There is no magic in the debugger, it don't find bugs, it just help you to. When the code don't do what is expected, you are close to a bug.