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()`

Recommended Answers

All 4 Replies

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.

Here is a similar post you can read that which can solve your problem .

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.