Looping Data

[vividiah]

I will make the process of looping the data to save data into SQL Server database,
but field data that must be stored is retrieved from the database access,
data taken later not only a record, but a lot of records,
so it takes a process of looping in it, I am still confused with the looping process, please help,
This is my code which is simple, and there is still no looping process:

Private Sub ListTemp_Click()
Dim sqltambah As String
Set rsmusik = New ADODB.Recordset
rsmusik.Open "SELECT title,singer,id_kategori,nama from musik where id_musik=1000", conn
If rsmusik.EOF = False Then
sqltambah = "INSERT INTO iMUSIC " & _
        "(ID_MUSIC, TITLE, SINGER, FID_M_CATEGORY, FILE_NAME, SOURCE, PATH, ACTIVE, VOL , ANALOG, DATE_ADDED)" & _
        " VALUES (" & _
        "'" & Text1.Text & "', " & _
        "'" & Text3.Text & "', " & _
        "'" & Text2.Text & "', " & _
         "'" & CB_kategori.Text & "', " & _
        "'" & filename_txt.Text & "', " & _
         "'" & source_txt.Text & "', " & _
        "'" & path_txt.Text & "', " & _
         "'" & active.Caption & "', " & _
          "'" & vol_txt.Text & "', " & _
           "'" & CB_Analog.Text & "', " & _
           "'" & DTPicker1.Value & "' " & _
        ")"
        End If

Do While Not rsmusik.EOF
Text2.Text = rsmusik!singer
Text3.Text = rsmusik!Title
CB_kategori.Text = rsmusik!id_kategori
rsmusik.MoveNext
Loop



End Sub

[aktharshaik]

As per your code my assumptions
1) conn is ADODB.Connection for access DB
2) iMUSIC is table in SQL Server DB

Private Sub ListTemp_Click()

        Dim sqltambah As String

        'Better Add Error Handler Code also
        On Error goto ListTemp_Click_Error

        'if you already have an active ADODB.Connection to your SQL Server Schema use it.
        'If not then create one ADODB.Connection and connect it to your SQL Server Schema
        'where your iMUSIC table resides
        'Here I assume sqlconn as the ADODB.Connection to your SQL Server Schema

        Dim sqlconn as New ADODB.Connection
        sqlconn.open "<here goes the SQL provider connection string to your SQL Server Schema>"

        'sqltambah is only a string variable which holds your Insert Query
        sqltambah = "INSERT INTO iMUSIC " & _
                    "(ID_MUSIC, TITLE, SINGER, FID_M_CATEGORY, FILE_NAME, SOURCE, PATH, ACTIVE, VOL , ANALOG, DATE_ADDED)" & _
                    " VALUES (" & _
                    "'" & Text1.Text & "', " & _
                    "'" & Text3.Text & "', " & _
                    "'" & Text2.Text & "', " & _
                    "'" & CB_kategori.Text & "', " & _
                    "'" & filename_txt.Text & "', " & _
                    "'" & source_txt.Text & "', " & _
                    "'" & path_txt.Text & "', " & _
                    "'" & active.Caption & "', " & _
                    "'" & vol_txt.Text & "', " & _
                    "'" & CB_Analog.Text & "', " & _
                    "'" & DTPicker1.Value & "' " & _
                    ")"

        'This is the Access Database Recordset with  conn  as ADODB.Connection to your MS-Access DB
        Set rsmusik = New ADODB.Recordset
        rsmusik.Open "SELECT title,singer,id_kategori,nama from musik where id_musik=1000", conn

        'Here check for .BOF not .EOF
        If rsmusik.BOF = False Then

            'I have used While...Wend   You can use Do While...Loop
            While Not rsmusik.EOF

                'Starting to Populate the record to the relevant TextBoxes.
                'Initially the first record. And Once the loop returns back,
                'it will be the next record, which you have set using .MoveNext method
                'at the end of the loop
                Text2.Text = rsmusik!singer
                Text3.Text = rsmusik!Title
                CB_kategori.Text = rsmusik!id_kategori

                'Now you have to execute the query stored in sqltambah String Variable
                'to insert the values into your target table iMUSIC which is in your SQL Server Schema
                sqlconn.Execute sqltambah

                'Now move to next record in rsmusik recordset
                rsmusik.MoveNext

            Wend

        End If

    ListTemp_Click_Done:
        if rsmusik.State then rsmusik.Close
        Set rsmusik = Nothing
        if sqlconn.State then sqlconn.Close
        Set sqlconn = Nothing
        Exit Sub

    ListTemp_Click_Error:
        MsgBox "An Error Has Occured Procedure ListTemp_Click" & vbCrLf & vbCrLf & "Error No : " & Err.Number & vbCrLf & Err.Description
        Resume ListTemp_Click_Done

    End Sub

[vividiah]

thank you aktharshaik
I understand your point, and it worked!!
Thank you so much,