I want to insert a table cell into a table from another table by select method and after, to display an error in text prompted from the user was not found. Can someone tell what's the problem with my codes?

My codes are as follows:

Dim Item As String = InputBox("Scan barcode for stock to receive", "Receivestock New Stock")
        Dim Quantity As String = InputBox("Scan Quantity for stock to receive", "Receivestock New Stock")

        'Display Users on form load
        Dim con As New SqlClient.SqlConnection("Server = Tshukela-Pc\; database = Stock Rotation; Trusted_connection = yes;")
        Dim ds As DataSet


        Dim cmd As SqlCommand
        cmd = New SqlCommand("Insert  into Stock ([Stock description], [Quantity], [Barcode]) select ([Item description]) from Items Where Barcode = '" & Item & "','" & Quantity & "','" & Item & "')", con)


        Dim da As SqlDataAdapter

        da = New SqlDataAdapter(cmd)
        ds = New DataSet
        da.Fill(ds, "Items")
        If ds.Tables(0).Rows.Count > 0 Then
            con.Open()

            DataGridView1.DataSource = ds.Tables("Stock").DefaultView
        Else
            MsgBox("Item not found in the database, please contact system administrators to add the stock to the system")
            Me.Close()`
md = New SqlCommand("INSERT INTO Stock ([Stock description], [Quantity], [Barcode]) VALUES (SELECT TOP 1 [Item description] FROM Items WHERE Barcode = '" & Item & "', '" & Quantity & "', '" & Item & "')", con)

This might work, but you need to make sure there can only be one description returned, hence the TOP 1.

Edited 7 Months Ago by pritaeas

At last, this is what worked for me

cmd = New SqlCommand("INSERT INTO Stock ([Stock description], [Barcode], [Quantity])  (SELECT TOP 1 [Stock description], [Barcode],'" & Quantity & "' FROM Items WHERE Barcode = '" & Item & "')", con)

thanks

This article has been dead for over six months. Start a new discussion instead.