VB.NET:将两个数据表中的列添加到第三个数据表中。 [英] VB.NET: Adding columns from two datatables into third one.

查看:71
本文介绍了VB.NET:将两个数据表中的列添加到第三个数据表中。的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我从两个SQL查询中获取两个数据表(dt3和dt4)。现在我想添加MA,QTY(从dt3)和STOCK(从dt4)到新数据表的列。



以下是我的代码,但这是错误的。我知道这是因为循环不好 - 它应该只循环10次,因为有10行。在我的情况下,它循环100x(dt3中的每一行,dt4中的每一行)......我无法弄明白。



提前感谢大家!



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





代码:

 Dim newRow As DataRow 

for t As Integer = 0到dt3.Rows.Count - 1
对于t2 As Integer = 0到dt4.Rows.Count - 1

newRow = dt10.NewRow()
dt10.Rows。添加(newRow)

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

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

下一个
下一个

解决方案

你的代码没有你想象的那样,你不明白为什么!



有一个几乎通用的解决方案:运行你的调试器上的代码一步一步。注意变量。



这个解决方案的缺点:

- 这是一个DIY,你是跟踪问题和寻找的人它的根源,导致解决方案。

这个解决方案的优点:

- 你看到你的代码行为,你把它与你的期望相匹配。



次要效果

- 你会为自己找到虫子感到骄傲。

- 你的技能会提高。



你应该很快就会发现什么是错的。

我可以告诉你,通过循环100次,你的代码完全符合你的要求。


这是我看问题的方法。 (我使用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:\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\\ > SELECT * FROM [cpqaAnswers]。[cpqa]。[tbl_UM_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



然后有一个第二个表,所以我也需要它:

 CREATE TABLE [cpqaAnswers]。[cpqa]。[tbl_UM_dt4](
[MA] nvarchar(11),
[股票] nvarchar(23)


BULK INSERT [cpqaAnswers]。[cpqa]。[tbl_UM_dt4] FROM'C:\\\\\\\\\\\ / pre>

我运行查询来检查操作是否已发生。有时来自文件的BULK INSERT可能被文本文件格式不佳所阻碍。

 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



到目前为止一切顺利。现在,这是我在你的帖子上画线的地方。首先,这两个表中的数据并不完全正确。但无论如何,既然你说这个数量是100倍,我想自己他正在经历一个循环巢而不仅仅是一个循环。以下是我在脑海中看到的内容:

 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(美国[MA] = M。[MA])



运行此查询将导致:

 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



知道TSQL中有什么办法可以做你想做的事吗

使用vbnet前端?我认为最好使用TSQL对数据库访问进行分层。然后使用vbnet等接口提供数据。



希望这会有所帮助。


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

解决方案

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.


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.


这篇关于VB.NET:将两个数据表中的列添加到第三个数据表中。的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆