vb.net SQL输出作为VB.net中的变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19384237/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL output as variable in VB.net
提问by rlphilli
I cannot seem to figure out how to get an output of a SQL query as a variable in VB. I need something like what's below so that I can use that variable in another SQL query. SQL doesn't allow the use of variables as column headers when running queries, so I thought I could use VB to insert the actual output of one SQL task as the dynamic variable in a loop of update queries. Let me (hopefully) explain.
我似乎无法弄清楚如何在 VB 中将 SQL 查询的输出作为变量获取。我需要类似下面的内容，以便我可以在另一个 SQL 查询中使用该变量。SQL 不允许在运行查询时使用变量作为列标题，所以我想我可以使用 VB 将一个 SQL 任务的实际输出作为动态变量插入更新查询循环中。让我（希望）解释一下。
I have the following query:
我有以下查询：
DECLARE @id int = (SELECT max(id) FROM [views]), @ViewType nvarchar(3);
WHILE @id IS NOT NULL
BEGIN
SELECT @ViewType = (SELECT [View] FROM [views] WHERE id = @id);
UPDATE a
SET a.[@ViewType] = '1'
FROM [summary] a
INNER JOIN [TeamImage] b
ON a.[Part_Number] = b.[PartNum]
WHERE b.[View] = @ViewType;
SELECT @id = max(id) FROM [views] WHERE id < @id;
END;
The SET a.[@ViewType] = '1' obviously will not work in SQL. But if I could have the (SELECT [View] FROM [views] WHERE id = @id) equal to a variable, then I could write the SQL query in VB and execute it and the variable would become part of the string and therefore execute correctly.
SET a.[@ViewType] = '1' 显然在 SQL 中不起作用。但是如果我可以让 (SELECT [View] FROM [views] WHERE id = @id) 等于一个变量，那么我可以在 VB 中编写 SQL 查询并执行它，该变量将成为字符串的一部分并因此执行正确。
I'm newer to VB, but here's what I have so far:
我是 VB 的新手，但这是我到目前为止所拥有的：
Dim cn As SqlConnection = New SqlConnection("Data Source=Server1;" & _
"Initial Catalog=DB1;" & _
"Integrated Security=SSPI")
cn.Open()
Dim cmd As New sqlCommand("SELECT max(id) FROM orientation_view_experiment;", cn)
vID = cmd.ExecuteNonQuery()
Do While vID > 0
Dim cmd2 As New sqlCommand("SELECT [View] FROM [views] WHERE id ='" + vID + "'"
vViewType = cmd2.ExecuteNonQuery()
Dim cmd3 As New sqlCommand("UPDATE a
SET a.'" + vViewType + "' = '1' & _
FROM [summary] a & _
INNER JOIN [TeamImage] b & _
ON a.[Part_Number] = b.[PartNum] & _
WHERE b.[View] = '" + vViewType + "';"
cmd3.ExecuteNonQuery()
vID = vID - 1
Loop
cn.Close()
I hope some of that made sense, but I'm kind of lost at this point. I feel like I know what I need the SQL to do, but can't quite figure out how to make the computer/programs submit to my will and just do what I need it to do.
我希望其中一些是有道理的，但在这一点上我有点迷失了。我觉得我知道我需要 SQL 做什么，但无法弄清楚如何让计算机/程序服从我的意愿，只做我需要做的事情。
Thank you for any help/direction you can give.
感谢您提供的任何帮助/指导。
回答by Steve
Your code is wrong because you insist in using ExecuteNonQueryfor SELECTstatements. ExecuteNonQuerydoesn't return the rows selected but just a count of the rows affected by an INSERT/DELETE/UPDATE query (I think that for SELECT it returns always zero)
您的代码是错误的，因为您坚持使用ExecuteNonQueryforSELECT语句。ExecuteNonQuery不返回选定的行，而只返回受 INSERT/DELETE/UPDATE 查询影响的行数（我认为对于 SELECT 它总是返回零）
What you need is ExecuteScalarto get the MAX value and the VIEW value because ExecuteScalar is the best choice when you expect to get just the first field of the first row from your SQL statement
您需要的是ExecuteScalar来获取 MAX 值和 VIEW 值，因为当您希望仅从 SQL 语句中获取第一行的第一个字段时， ExecuteScalar 是最佳选择
Dim cmd As New sqlCommand("SELECT max(id) FROM orientation_view_experiment;", cn)
vID = Convert.ToInt32(cmd.ExecuteScalar())
Do While vID > 0
Dim cmd2 As New sqlCommand("SELECT [View] FROM [views] WHERE id =" + vID.ToString()
Dim result = cmd2.ExecuteScalar()
if Not string.IsNullOrEmpty(result)) Then
vViewType = result.ToString()
Dim cmd3 As New sqlCommand("UPDATE a SET a.[" + vViewType + "] = '1' " & _
"FROM [summary] a " & _
"INNER JOIN [TeamImage] b " & _
"ON a.[Part_Number] = b.[PartNum] " & _
"WHERE b.[View] = @vType"
cmd3.Parameters.AddWithValue("@vType", vViewType)
cmd3.ExecuteNonQuery()
End If
Loop
The last part of your code is not really clear to me, but you could use a couple of square brackets around the column name in table summaryand a parameter for the View field in table TeamImage.
你的代码的最后一部分对我来说不是很清楚，但你可以在 table 中的列名和 tablesummary中的 View 字段的参数周围使用几个方括号TeamImage。
As a last advice, be sure that the column View in table TeamImage is not directly modifiable by your end user because a string concatenation like this could lead to a Sql Injectionattacks
作为最后一个建议，请确保最终用户不能直接修改表 TeamImage 中的列 View，因为像这样的字符串连接可能会导致Sql 注入攻击
回答by BlueMonkMN
Have you tried replacing '" + vViewType + "'with [" + vViewType + "]... in other words use square brackets to delimit the column name instead of single quotes which are for delimiting string literals?
您是否尝试过替换'" + vViewType + "'为[" + vViewType + "]... 换句话说，使用方括号来分隔列名而不是用于分隔字符串文字的单引号？
Also, I would encourage stopping in the debugger, examining the command that you generated into cmd3 and try executing it directly. It might help you identify other similar problems such as the fact that vViewType is giving you a count of records instead of an actual value from the [View]column.
此外，我鼓励在调试器中停止，检查您在 cmd3 中生成的命令并尝试直接执行它。它可能会帮助您识别其他类似问题，例如 vViewType 为您提供记录计数而不是列中的实际值这一事实[View]。
回答by Steve
Do a little research into what the different methods of a commandare. When you call ExecuteNonQuery, this return the number of records effected. I think you want ExecuteScalaras your cmdand cmd2methods, so you can get a value from the database.
对 a 的不同方法进行一些研究command。当您调用时ExecuteNonQuery，这将返回受影响的记录数。我认为你想要ExecuteScalar作为你的cmd和cmd2方法，所以你可以从数据库中获取一个值。
