SQL查询影响多个表 [英] SQL query to affect multiple tables
问题描述
我想要一个SQL查询从一个表中选择数据并用数据更新第二个表。
这就是我所做的:
con1.Open()
Dim myReader As SqlDataReader = Nothing
dim one as string
Dim query As String = select * from tblname where column1 = @ one
Dim myCommand As New SqlCommand(query,con1)
myCommand.Parameters.AddWithValue(@ one,one)
myReader = myCommand.ExecuteReader()
而myReader.Read()
Tbxorgan.Text =(myReader(0).ToString())
结束时
con1.close
con1.open
Dim query1 As String =UPDATE tblsign SET column2 = @col where username = @ usen
con1.Open()
使用cmd作为新的SqlCommand(query1,con1)
cmd.Parameters.AddWithValue( @col,Tbxorgana.Text)
cmd.Parameters.AddWithValue(@ usen,user.text)
cmd.ExecuteNonQuery()
结束使用
con1.Close()
它工作正常,但我不想打开和关闭连接
请帮忙。
我认为你可以使用存储过程来执行这两个任务,并在命令连接时只需运行过程参数
I want a single SQL query to pick data from one table and update the second table with the data.
this is what I have done:
con1.Open()
Dim myReader As SqlDataReader = Nothing
dim one as string
Dim query As String = "select * from tblname where column1=@one"
Dim myCommand As New SqlCommand(query, con1)
myCommand.Parameters.AddWithValue("@one",one )
myReader = myCommand.ExecuteReader()
While myReader.Read()
Tbxorgan.Text = (myReader(0).ToString())
End While
con1.close
con1.open
Dim query1 As String = "UPDATE tblsign SET column2 = @col where username=@usen"
con1.Open()
Using cmd As New SqlCommand(query1, con1)
cmd.Parameters.AddWithValue("@col", Tbxorgana.Text)
cmd.Parameters.AddWithValue("@usen", user.text)
cmd.ExecuteNonQuery()
End Using
con1.Close()
It works fine but I dont want to be opening and closing the connection
Please help.
I think you can use a stored procedure to perform these two tasks and in command of the connection simply run the procedure parameters.
这篇关于SQL查询影响多个表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!