CODEWITHSUNDEEP
vb.netsql-server-2008
Pradnya Bolli
Pradnya Bolli

Reputation: 1943

error 'There is already an open DataReader associated with this Command which must be closed first.'

I use below code but it gives error on sentence icount = cmd.ExecuteNonQuery

cn.Open()
str = "SELECT [srno],[caste]FROM [SchoolERP].[dbo].[caste] where (caste ='" + (TextBox1.Text) + "')"
cmd = New SqlCommand(str, cn)
dr1 = cmd.ExecuteReader()

If Not dr1.HasRows Then
    str = "INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES('" + TextBox1.Text + "')"
    cmd = New SqlCommand(str, cn)
    icount = cmd.ExecuteNonQuery
    MessageBox.Show(icount)

Else
    MsgBox("Record Exists")
    cn.Dispose()
End If
cn.Close()

Upvotes: 1

Views: 18065

Answers (5)

Pradnya Bolli
Pradnya Bolli

Reputation: 1943

I got Answer ... i only close connection before icount = cmd.ExecuteNonQuery this sentence and again open connection...and its works.. thanks disha..

Upvotes: 0

Mak
Mak

Reputation: 11

Try this: 

Try 
    insert data in combobox 
    cmd.Connection = con 
    cmd.CommandText = "select name from party" 
    dr = cmd.ExecuteReader() 
    cb_ms.Items.Add("---Select---") 
    cb_ms.SelectedIndex = 0 
    While dr.Read() 'get error here.'
         cb_ms.Items.Add(dr("name")) 
    End While

    dr.Close()

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Upvotes: 1

dran1979
dran1979

Reputation: 3

Use Try.. Catch.. Finally... End Try somehing like this:

 Try
   cn.Open()
    str = "SELECT [srno],[caste]FROM [SchoolERP].[dbo].[caste] where (caste               ='" + (TextBox1.Text) + "')"
    cmd = New SqlCommand(str, cn)
    dr1 = cmd.ExecuteReader()

    If Not dr1.HasRows Then
       str = "INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES('" +     TextBox1.Text + "')"
       cmd = New SqlCommand(str, cn)
       icount = cmd.ExecuteNonQuery
       MessageBox.Show(icount)

   Else
       MsgBox("Record Exists")
       cn.Dispose()
   End If
       cn.Close()
 Catch error As Exception
   ........
 Finally
      dr1.Close
 End Try

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416179

You could just close the datareader before the 2nd query, but it's better still to get this all into a single query in the first place. Moreover, you really need to close that awful sql injection issue.

Try this to fix both issues:

Dim sql As String = _
  "IF NOT EXISTS    
    (
      SELECT 1 FROM [SchoolERP].[dbo].[caste] where (caste = @caste )   
    )    
    BEGIN
        INSERT INTO [SchoolERP].[dbo].[caste]([caste])VALUES( @caste)
    END"

Using cn As New SqlConnection("connection string here"), _
      cmd As New SqlCommand(sql, cn)

    'Use actual columnn type from the database here
    cmd.Parameters.Add("@caste", SqlDbType.NVarChar, 50).Value = TextBox1.Text

    cn.Open()
    icount = cmd.ExecuteNonQuery()
    MessageBox.Show(icount)
End Using

Upvotes: 0

Disha
Disha

Reputation: 392

Try always calling the Close method when you have finished using the DataReader object.

  dr1.Close();

Another optionis to turn on MARS , in your connection string just add "MultipleActiveResultSets=True;"

Upvotes: 8

Related Questions