CODEWITHSUNDEEP
mysqlvb.netwinforms
eaponz
eaponz

Reputation: 604

insert multiple data mysql using for loop

This is the scenario, I have a select query then all fetched data must be inserted into another table.. This is what I came up, I don't know if the for loop does anything.

If I would remove the for loop. Example: Fetched data is id1, id2, id3 the inserted data in my database is id1, id1, id1 instead of id1, id2, id3

sql = "SELECT * FROM dummy_violate WHERE res_month <> @month Or res_year <> @year"
    cmd = New MySqlCommand(sql, con)
    cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
    cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
    dr = cmd.ExecuteReader
    While dr.Read
        id += dr(0)
        count += 1
    End While
    dr.Close()
    If count > 0 Then

        For i As Integer = 1 To count
            sql2 = "INSERT INTO dummy_violate(res_id, res_month, res_year, is_paid)VALUES(@id,@month,@year,@paid)"
            cmd = New MySqlCommand(sql2, con)
            cmd.Parameters.AddWithValue("@id", id)
            cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
            cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
            cmd.Parameters.AddWithValue("@paid", 0)
            cmd.ExecuteNonQuery()
        Next
    ElseIf count = 0 Then

        MsgBox("Wrong Query")

    End If

Upvotes: 0

Views: 5447

Answers (1)

Steve
Steve

Reputation: 216253

I don't really understand what you are trying to do and why, it seems that you are simply duplicating the records already present in your table with new records with the same data but with the is_paid flag set to zero. If so just try changing your code in this way:

sql = "SELECT res_id FROM dummy_violate WHERE res_month <> @month Or res_year <> @year"
cmd = New MySqlCommand(sql, con)
cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
Dim da = new MySqlDataAdapter(cmd)
Dim dt = new DataTable()
da.Fill(dt)

' if we have records to duplicate we have Rows.Count > 0
If dt.Rows.Count > 0 Then
   sql2 = "INSERT INTO dummy_violate(res_id, res_month, res_year, is_paid) " & _ 
          "VALUES(@id,@month,@year,@paid)"
    cmd = New MySqlCommand(sql2, con)

    ' Add all the parameters before entering the loop. Just @id changes for every loop,
    ' so we set it with a dummy value outside the loop and we change it when looping over
    ' the result table.....
    cmd.Parameters.AddWithValue("@id", 0) 
    cmd.Parameters.AddWithValue("@month", DateTime.Now.ToString("MMMM"))
    cmd.Parameters.AddWithValue("@year", DateTime.Now.ToString("yyyy"))
    cmd.Parameters.AddWithValue("@paid", 0)

    ' execute the insert for all the rows count 
    ' rows array starts at zero so loop to count -1
    For i As Integer = 0 To dt.Rows.Count - 1
        cmd.Parameters("@id").Value = dt.Rows(i)("res_id")
        cmd.ExecuteNonQuery()
    Next
ElseIf count = 0 Then
    MsgBox("Wrong Query")
End If

Remember that if the res_id is a primary key then you cannot insert the records with the same res_id twice. Also, if res_id is an auto_number column (a.k.a. IDENTITY) then you cannot explicitily set its value

Upvotes: 2

Related Questions