VB.net 和 SQL 问题

发布于 2024-09-24 16:52:11 字数 918 浏览 1 评论 0原文

我刚刚开始学习 VB.Net 和 SQL。现在我正在创建我的第一个软件，但我遇到了一个问题：我的数据库中有两个表，并且我设法将数据从表 1 传输到表 2。我怎样才能将特定行从 table1 插入到 table2 。我不想将table1中的所有数据复制到table2；我只想复制选定的行。

这是我的代码：

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click       
        cmd.CommandText = "INSERT INTO returns(Department, Purpose, Item_details, Requested_by, Approved_by, ReturnDate) SELECT Department, Purpose, Items_Details, Requested_by, Approved_by, Date FROM borrow WHERE Date= '" & Today.Date.ToShortDateString & "';"
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Finally
            con.Close()
        End Try                
End Sub

我有一个列表框，其中的源绑定是borrow，我只想将选定的项目单行传输到我的表returns，但我不这样做知道怎么做。每当我单击该按钮时，表 borrow 中的所有内容都会被复制到表 returns 中。

原文

I've just started to learn VB.Net and SQL. Now I'm creating my first software but I have a problem: I have two tables in my database and I managed to transfer data from table1 to table2. How can I just insert specific rows from table1 to table2. I don't want to copy all the data in table1 to table2; I just want to copy the selected rows.

Here's my code:

 Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click       
        cmd.CommandText = "INSERT INTO returns(Department, Purpose, Item_details, Requested_by, Approved_by, ReturnDate) SELECT Department, Purpose, Items_Details, Requested_by, Approved_by, Date FROM borrow WHERE Date= '" & Today.Date.ToShortDateString & "';"
        cmd.Connection = con
        Try
            con.Open()
            cmd.ExecuteNonQuery()
        Finally
            con.Close()
        End Try                
End Sub

I have a listbox which has a sourcebinding which is borrow and I only want the selected items single row to be transferred to my table returns but I don't know how to do it. Whenever I click the button, everything in table borrow will be copied to table returns.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

木有鱼丸 2024-10-01 16:52:11

正如其他评论中所建议的，最好养成不在 SQL 语句中对参数值使用字符串连接的习惯。

以下代码演示了如何使用 SQL 参数并从列表框中获取行条件。

Private Sub Button1_Click(ByVal sender As System.Object,
                      ByVal e As System.EventArgs
) Handles button1.Click

    ' Note that I am using an XML literal to improve code readability. '
    Dim insertCommand = <xml>
        INSERT INTO returns(
            Department, 
            Purpose, 
            Item_details, 
            Requested_by, 
            Approved_by, 
            ReturnDate
        ) 
        SELECT
            Department, 
            Purpose, 
            Items_Details, 
            Requested_by, 
            Approved_by, 
            Date 
        FROM borrow 
        WHERE BorrowId = @BorrowId;
    </xml>

    Dim param = cmd.CreateParameter()
    param.ParameterName = "@BorrowId"
    param.Value = listBox.SelectedValue

    cmd.CommandText = insertCommand.Value
    cmd.Parameters.Add(param)

    cmd.Connection = con
    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

End Sub

As suggested in other comments is a good idea to get in the habit of not to use string concatenation for parameter values in a SQL statement.

The following code demonstrates how to use SQL parameters and get the row criteria from the list box.

Private Sub Button1_Click(ByVal sender As System.Object,
                      ByVal e As System.EventArgs
) Handles button1.Click

    ' Note that I am using an XML literal to improve code readability. '
    Dim insertCommand = <xml>
        INSERT INTO returns(
            Department, 
            Purpose, 
            Item_details, 
            Requested_by, 
            Approved_by, 
            ReturnDate
        ) 
        SELECT
            Department, 
            Purpose, 
            Items_Details, 
            Requested_by, 
            Approved_by, 
            Date 
        FROM borrow 
        WHERE BorrowId = @BorrowId;
    </xml>

    Dim param = cmd.CreateParameter()
    param.ParameterName = "@BorrowId"
    param.Value = listBox.SelectedValue

    cmd.CommandText = insertCommand.Value
    cmd.Parameters.Add(param)

    cmd.Connection = con
    Try
        con.Open()
        cmd.ExecuteNonQuery()
    Finally
        con.Close()
    End Try

End Sub

回复收藏 0 原文