• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 554
  • Last Modified:

VB.net Loop problem with executereader

I import a csv file into a dataset and then I want to take this information from this dataset and import it to my database. It runs over the first row but then it states:

"Error: System.Data: There is already an open DataReader associated with this Command which must be closed first"

I added cmdstudent.executereader.close() in the loop but it still does not work.

Any suggestions?
Private Sub btn1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn1.Click
 
        Dim conn As SqlConnection
        Dim cmdStudent As New SqlCommand
        Dim cmdStudent1 As New SqlCommand
        Dim daStudent As New SqlDataAdapter
        Dim dsStudent As New DataSet
        Dim dtStudent As New DataTable
 
        Dim ds1 As New DataSet
        Dim sCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\;Extended Properties=Text;"
        Dim objConn As New OleDbConnection(sCon)
 
        objConn.Open()
 
        Dim objCmdSelect As New OleDbCommand("SELECT * FROM daily.csv", objConn)
 
        Dim objAdapter1 As New OleDbDataAdapter
 
        objAdapter1.SelectCommand = objCmdSelect
 
        objAdapter1.Fill(ds1, "daily")
 
        DGV1.DataSource = ds1.Tables(0).DefaultView()
 
        objConn.Close()
 
        Dim i As Integer
 
        Try
            conn = GetConnect()
            conn.Open()
 
 
            For i = 0 To (ds1.Tables(0).Rows.Count - 1)
                cmdStudent = conn.CreateCommand
                cmdStudent.CommandText = "INSERT INTO dailyrounds (date,name,price,no,url,high,highnum,mid,midnum,low,lownum)" & _
                   "VALUES ('" & Date.Today & "','" & _
                   ds1.Tables(0).Rows(i)("name") & "'," & _
                   ds1.Tables(0).Rows(i)("price") & "," & _
                   ds1.Tables(0).Rows(i)("no") & ",'" & _
                   ds1.Tables(0).Rows(i)("url") & "','" & _
                   ds1.Tables(0).Rows(i)("high") & "'," & _
                   ds1.Tables(0).Rows(i)("highnum") & ",'" & _
                   ds1.Tables(0).Rows(i)("mid") & "'," & _
                   ds1.Tables(0).Rows(i)("midnum") & ",'" & _
                   ds1.Tables(0).Rows(i)("low") & "'," & _
                   ds1.Tables(0).Rows(i)("lownum") & ") "
 
                cmdStudent.ExecuteReader.Read()
                cmdStudent.ExecuteReader.Close()
            Next i
 
            Refresh_Form()
            conn.Close()
 
        Catch ex As Exception
            MsgBox("Error: " & ex.Source & ": " & ex.Message, MsgBoxStyle.OkOnly, "Connection Error !!")
        End Try
 
    End Sub

Open in new window

0
ralphcarter2008
Asked:
ralphcarter2008
1 Solution
 
sunithnairCommented:
I think your line number 50 should be cmdStudent.ExecuteNonQuery() since you are trying to insert the values into a database table. ExecuteReader is just for reading the data and ExecuteNonQuery is used for DML queries.
0
 
ralphcarter2008Author Commented:
Good catch!!
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now