VB.net and SQL Question

Question

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.

Please don't write code that concatenates SQL. It leaves you wide open for SQL injection attacks. Instead, use parameters. — Steven Sudit
– Steven Sudit, Commented Sep 22, 2010 at 20:27
@Steven: my first thought was, "true, but he's concatenating a known string, Date.ToShortDateString. Then my mind started winding through how one might exploit that with unexpected locales. — Michael Petrotta
– Michael Petrotta, Commented Sep 22, 2010 at 20:37
@Steve Didn't know that.. thanks anyway.. i'll be more careful next time.. — demic0de
– demic0de, Commented Sep 22, 2010 at 21:07
@Michael Petrotta tnx for editing my post it's much better^^ — demic0de
– demic0de, Commented Sep 22, 2010 at 21:08
@Michael: You're right that there are a few cases where string concatenation is hard to exploit. Having said that, if you use parameters (and no dynamic SQL on the server side, of course), then this becomes the rule. — Steven Sudit
– Steven Sudit, Commented Sep 22, 2010 at 21:52

Tim Murphy · Accepted Answer · 2010-09-23 00:00:14Z

2

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

answered Sep 23, 2010 at 0:00

Tim Murphy

4,9524 gold badges45 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

demic0de Over a year ago

Thanks for teaching me how to work with parameters^^.. really appreciate it.. but still can't figure out how to add only the rows i selected..

demic0de Over a year ago

Im getting an error No mapping exists from object type System.Data.DataRowView to a known managed provider native type.

Tim Murphy Over a year ago

What UI are using WinForms or WPF?

Tim Murphy Over a year ago

I now see you've started a new question stackoverflow.com/questions/3775543/vb-net-im-getting-an-error for this error. Lets move the conversation to that question we I believe its not related to your original question.

Beth · Accepted Answer · 2010-09-22 21:09:52Z

0

You need to get the selected row criteria from the listbox and add that to the where clause of your sql.

answered Sep 22, 2010 at 21:09

Beth

9,6171 gold badge26 silver badges44 bronze badges

3 Comments

demic0de Over a year ago

Thank you.. can u give me a sample code on how to get row criteria in a listbox?..

demic0de Over a year ago

Hi i made some changes but im getting "Public member 'Selected' on type 'DataRowView' not found." when i run my code..

Beth Over a year ago

You may need more help than I can provide here. To refer to the selected row in a listbox, see @Tim Murphy 's example, using param.Value = listBox.SelectedValue You'll need to iterate through all of the listbox's selected values if you're allowing more than one.

Collectives™ on Stack Overflow

VB.net and SQL Question

2 Answers 2

4 Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related