VB.NET: Adding columns from two datatables into third one.

Question

2.00/5 (2 votes)

See more:

I get two datatables (dt3 and dt4) from two SQL queries. Now i want to add columns "MA", "QTY" (from dt3) and "STOCK" (from dt4) to new datatable.

Below is my code, but it's wrong. I know it's because of bad looping - it should only loop 10x, because there is 10 rows. In my case it loops 100x (each row in dt3 with each row in dt4)... i can't figure it out.

Thanks everybody in advance!

dt3:

  MA    QTY
MA00631	288
MA00631	240
MA00631	470,4
MA00959	1836
MA00959	1108,8
MA00959	1285,2
MA01070	2560
MA01070	1280
MA06195	989,4
MA06210	510

dt4:

  MA    STOCK
MA00631	19194
MA00631	19194
MA00631	19194
MA00959	34589
MA00959	34589
MA00959	34589
MA01070	72431
MA01070	72431
MA06195	11073
MA06210	17220

Code:

Dim newRow As DataRow

       For t As Integer = 0 To dt3.Rows.Count - 1
           For t2 As Integer = 0 To dt4.Rows.Count - 1

               newRow = dt10.NewRow()
               dt10.Rows.Add(newRow)

               Dim drow As DataRow = dt3.Rows(t)
               Dim drow2 As DataRow = dt4.Rows(t2)

               newRow("MA") = drow("MA")
               newRow("QTY") = drow("QTY")
               newRow("STOCK") = drow2("STOCK")

           Next
       Next

Posted 28-Aug-15 11:01am

mrUR

Add a Solution

Comments

RedDk 29-Aug-15 15:36pm

Just at a glance, when a column count is being considered in a data row scenario, the procedure that fails is likely to be committing to some sort of column count consideration. YOU do this by nesting. The logic is loop, so there's no nest. Therefore there's no column count consideration.

Effectively doing a 10 x 10 (for each row do the loop 10 times). Which is not right. Get rid of your inner and outer idea by vanquishing either. Process a row.

Wordy I know, but I haven't the time to show code.

mrUR 30-Aug-15 14:06pm

Regard my comment below, do you have any tip how to solve this? :)

2 solutions

Solution 1

Your code do not behave the way you expect, and you don't understand why !

There is an almost universal solution: Run your code on debugger step by step. Pay attention to variables.

The downside of this solution:
- It is a DIY, you are the one tracking the problem and finding its roots, which lead to the solution.
The upside of this solution:
- You see your code behaviour, you match it against your expectations.

secondary effects
- Your will be proud of finding bugs yourself.
- Your skills will improve.

You should find pretty quickly what is wrong.
I can tell you that by looping 100 times, your code is doing exactly what you asking for.

Posted 28-Aug-15 11:28am

Patrice T

Updated 28-Aug-15 12:20pm

v2

Comments

mrUR 29-Aug-15 3:12am

Of course i already did that with debugger - my loop first take row1 in dt3 then all (10) rows in dt4, then it moves to row2 in dt3 and again all rows in dt4 and so on...

Patrice T 29-Aug-15 6:27am

Then, you know why you are looping 100 times instead of 10 times !
And you know the solution !

RedDk 29-Aug-15 15:43pm

... and NEVER shows code ... why I ALWAYS downvote this sort of nonsensical answer

Patrice T 29-Aug-15 16:04pm

Recommending to use the debugger and giving the solution at same time is counter productive.
The goal being to make the requester think by himself.

RedDk 30-Aug-15 13:39pm

Well you've found the "reply" button ...

But did you know that by clicking on your own name, getting to your "Professional Profile" page, and there clicking on the "Reputation" tab to bring up the graph, by clicking on the link under it, "Recent Reputation Points", you can see in the "Points" column of ... list ... how bashed up you were by posting a comment as an answer?

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)

RedDk · Accepted Answer · 2015-08-30T10:12:00

This is how I look at the problem. (I do all this using ssmse):

USE [cpqaAnswers]
GO

CREATE TABLE [cpqaAnswers].[cpqa].[tbl_UM_dt3](
	[MA] nvarchar(11),
		[QTY] float(2)
		)

BULK INSERT	 [cpqaAnswers].[cpqa].[tbl_UM_dt3] FROM 'C:\UM\dt3.txt'

Then I do some inspection:

SELECT * FROM [cpqaAnswers].[cpqa].[tbl_UM_dt3]

Which yields:

MA      QTY
~~~~~~~~~~~~~~
MA00631 288
MA00631 240
MA00631 470.4
MA00959 1836
MA00959 1108.8
MA00959 1285.2
MA01070 2560
MA01070 1280
MA06195 989.4
MA06210 510

And then there's a second table, so I need that also:

CREATE TABLE [cpqaAnswers].[cpqa].[tbl_UM_dt4](
	[MA] nvarchar(11),
		[STOCK] nvarchar(23)
		)
		
BULK INSERT	 [cpqaAnswers].[cpqa].[tbl_UM_dt4] FROM 'C:\UM\dt4.txt'

I run a query to check whether the operation has occured. Sometimes BULK INSERT from file can be thwarted by poor format of the textfile.

SELECT * FROM [cpqaAnswers].[cpqa].[tbl_UM_dt4]

MA      STOCK
~~~~~~~~~~~~~
MA00631 19194
MA00631 19194
MA00631 19194
MA00959 34589
MA00959 34589
MA00959 34589
MA01070 72431
MA01070 72431
MA06195 11073
MA06210 17220

So far so good. Now, this is where I'm drawing the line on your post. First of all something's not quite right about the data in these two tables. But anyway, since you speak of this quantity 100x, I think to myself "he's running through a nest of loops not just one loop". Here is what I see in my mind's eye:

SELECT DISTINCT U.[MA], U.[QTY], M.[STOCK] FROM [cpqaAnswers].[cpqa].[tbl_UM_dt3] AS U
	JOIN[cpqaAnswers].[cpqa].[tbl_UM_dt4] AS M
		ON(U.[MA]=M.[MA])

Running this query will result in:

MA    QTY       STOCK
~~~~~~~~~~~~~~~~~~~~~~~~~~
MA00631   240       19194
MA00631   288       19194
MA00631   470.4     19194
MA00959  1108.8     34589
MA00959  1285.2     34589
MA00959  1836       34589
MA01070  1280       72431
MA01070  2560       72431
MA06195   989.4     11073
MA06210   510       17220

Does it help to know that there's a way in TSQL to do what you want to do
using vbnet frontend? I think it's best to layer the database access with TSQL. Then serve up the data itself using an interface such as vbnet.

Hope this helps.