分享程序员开发的那些事...
更新时间：2023-01-31 12:17:59
你的代码没有你想象的那样，你不明白为什么！
有一个几乎通用的解决方案：运行你的调试器上的代码一步一步。注意变量。
这个解决方案的缺点：
- 这是一个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.