How do I make an for each statement for an SQL stringed database using a sqlbulkcopy statement

Question

1.00/5 (1 vote)

See more:

I am now using my sql databases through manual string connections instead of connecting them through the vb.net database wizard. I found this method to more sufficient when trying to go between databases. what I am trying to do is make a connection to a dataset of my choosing and run a bulkcopy statement to import excel information.

Dim sqlconn As New SqlConnection(MySQLstring)
      Dim sqladaptor = New SqlDataAdapter("select * from" + "[" + TreeView1.SelectedNode.Text.ToString + "]", sqlconn)
      Dim sql As String = "select * from" + "[" + TreeView1.SelectedNode.Text.ToString + "]"
      Dim sqlcmd As SqlClient.SqlCommand
      sqlcmd = New SqlClient.SqlCommand(sql, sqlconn)
      Dim dt As New DataTable
      Dim ds As New DataSet
      sqladaptor.Fill(dt)

      For Each column In ds.Tables(TreeView1.SelectedNode.Text.ToString).Columns

          sqlBulkCopy.ColumnMappings.Add(column.ColumnName, If(String.IsNullOrEmpty(column.ColumnName), " ", column.ColumnName))


      Next

when I run my code I get this error message:
Additional information: Object reference not set to an instance of an object. This message appears at the "ds.Tables(TreeView1.SelectedNode.Text.ToString).Columns"

I do not know how to access the dataset while connecting to sql manually.
how can I write a statement using an sql connection string to a database?

What I have tried:

I have tried to call forth different tables and try the import but the all say the same thing. what is interesting is this worked when I used a dataset that the database wizard imported, but now it doesn't

Posted 3-Feb-17 23:04pm

Member 11856456

Updated 4-Feb-17 9:48am

Add a Solution

Comments

[no name] 4-Feb-17 8:01am

Learn to use the debugger. Your dataset is always null because you don't do anything with it. And your query won't work. And Text is already a string, what possible benefit do you think ToString on a string is going to give your? Like I told you before, there must be thousands of examples on the internet for you to look at, you just need to go look.

2 solutions

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)

Richard MacCutchan · Answer 1 · 2017-02-04T01:17:00

Solution 1

Object reference not set to an instance of an object.

It means that you are trying to use a reference variable which has never been initialised. The only way to discover which one is in error is to use the debugger to step through your code.

Posted 4-Feb-17 1:17am

Richard MacCutchan

Member 11856456 · Answer 2 · 2017-02-04T09:48:00

Solution 2

Richard I figured out what the problem was. I was referencing a newly created data table, which I changed up to reference the dataset. here is the code modification, it was just an extra step.

 Dim dt As New DataTable
Dim ds As New DataSet
sqladaptor.Fill(ds, TreeView1.SelectedNode.Text)

Posted 4-Feb-17 9:48am

Member 11856456