CODEWITHSUNDEEP
vb.net
Ajith Babu T S
Ajith Babu T S

Reputation: 13

vb.net 2010 - storing record on table

connection()
    k = "select community,unit,year,name,roll,mbl from info"
    cmd = New SqlCommand(k, con)
    dr = cmd.ExecuteReader
    While (dr.Read())
        If dr.GetString(0) = ComboBox2.Text And dr.GetString(1) = ComboBox3.Text And dr.GetString(2) = TextBox1.Text Then
            k = "insert into co values(@community,@unit,@year,@name,@roll,@mbl)"
            cmd = New SqlCommand(k, con)
            cmd.Parameters.AddWithValue("@community", dr.GetString(0))
            cmd.Parameters.AddWithValue("@unit", dr.GetString(1))
            cmd.Parameters.AddWithValue("@year", dr.GetString(2))
            cmd.Parameters.AddWithValue("@name", dr.GetString(3))
            cmd.Parameters.AddWithValue("@roll", dr.GetString(4))
            cmd.Parameters.AddWithValue("@mbl", dr.GetDecimal(5))
            MsgBox("added success")

        End If
    End While
    dr.Close()
    cmd.ExecuteNonQuery()

this is my code..it retrieves two rows from the "info" table but it stores only one row(last row) to "bld" table.

Upvotes: 0

Views: 40

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460158

Apart from your issue, why do you select all rows from that table and then check which record matches all user-selections at all? Instead you should only select the relevant records.

Or - since you actually want to insert into another table - insert only the relevant records without needing to select the rows at all:

Dim year As Int32
If Not Int32.TryParse(TextBox1.Text.Trim(), year) Then
    MessageBox.Show("Please insert a valid year")
    Return
End If
Dim insertSql =
    "INSERT INTO co(community, unit, year, name, roll, mbl) " & vbCrLf & _
    "  SELECT community, unit, year, name, roll, mbl FROM info " & vbCrLf & _
    "  WHERE community = @community AND unit = @unit AND year = @year"
Using con = New SqlConnection("ConenctionString")
    Using cmd As New SqlCommand(insertSql, con)
        cmd.Parameters.Add("@community", SqlDbType.VarChar).Value = ComboBox2.Text
        cmd.Parameters.Add("@unit", SqlDbType.VarChar).Value = ComboBox3.Text
        cmd.Parameters.Add("@year", SqlDbType.Int).Value = year
        con.Open()
        Dim numInserted As Int32 = cmd.ExecuteNonQuery()
    End Using
End Using

Upvotes: 1

Related Questions