a Problem in data reader,Can any one help me!!


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: a Problem in data reader,Can any one help me!!

  1. #1
    Join Date
    Mar 2006
    Posts
    9

    a Problem in data reader,Can any one help me!!

    I have a problem in the data reader,the error happens when I declare a sqldataadapter and assgin a dataset to it,befor that it was work correctly
    the error said:-
    there is an already data adapter that is associated with this connection which must be closed first

    the error happened here:
    Code:
    MyAdapter.SelectCommand = cmdUniversity
               'MyAdapter.Container = Global_Connection
    
               Dim ds As New DataSet
               MyAdapter.Fill(ds)
               Me.DG_Search.DataSource = ds
    and the full code:

    Code:
    Private Sub BtnSearch2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch2.Click
           'all variables are initialized in the radio buttons -checked Change Event
    
            Try
    
               Dim sql_statment As String
               If SearchKey = 0 Then
                   MsgBox("Please choose your Search Key", MsgBoxStyle.OKOnly, "Error")
               ElseIf SearchKey = 1 Then
                   University_ID = CInt(Me.txtUniversityID2.Text)
                   sql_statment = "select * from University where University_ID=" & University_ID & ";"
               ElseIf SearchKey = 2 Then
                   University_Name = Me.txtUniversityName2.Text
                   sql_statment = "select * from University where University_Name like '" & University_Name & "';"
               ElseIf SearchKey = 3 Then
                   University_MainLocation = Me.CB_City2.SelectedItem
                   'Dim cmdCity As New SqlCommand
                   cmdCity.Connection = Global_Connection
                   cmdCity.CommandText = "select City_ID from city where City_name='" & University_MainLocation & "';"
                   Dim drCity As SqlDataReader = cmdCity.ExecuteReader
                   drCity.Read()
                   City_ID_Value = drCity("City_ID")
                   drCity.Close()
    
                   sql_statment = "select * from University where Main_Loc=" & City_ID_Value & ";"
    
               ElseIf SearchKey = 4 Then
                   University_WebAddress = Me.txtWebsite2.Text()
                   sql_statment = "select * from University where Website_Address like '" & University_WebAddress & "';"
               ElseIf SearchKey = 5 Then
                   University_EMail = Me.txtEmail2.Text
                   sql_statment = "select * from University where Email like '" & University_EMail & "';"
               End If
    
               'Dim cmdUniversity As New SqlCommand
               cmdUniversity.Connection = Global_Connection
               cmdUniversity.CommandText = sql_statment
               cmdUniversity.ExecuteReader()
    
               'Dim MyAdapter As New SqlDataAdapter
               MyAdapter.SelectCommand = cmdUniversity
               'MyAdapter.Container = Global_Connection
    
               Dim ds As New DataSet
               MyAdapter.Fill(ds)
               Me.DG_Search.DataSource = ds
    
               MsgBox("Good,drModify", MsgBoxStyle.OKOnly)
       
           Catch ex As Exception
               MsgBox(ex.Message)
            
           Catch er As SqlException
    
               MsgBox(er.Number)
               MsgBox(er.Message)
           End Try
    
       End Sub

    I'm waiting for the experts answers!!

  2. #2
    Join Date
    Dec 2004
    Posts
    61
    Code:
    '   ... your code before
    
    ' if your code goes here
    
    ElseIf SearchKey = 3 Then
        University_MainLocation = Me.CB_City2.SelectedItem
        
        'Dim cmdCity As New SqlCommand
        ' /// <PotentialError Maker>    
        '  You set the connetion here
        cmdCity.Connection = Global_Connection
        ' /// </PotentialErrorMaker>
    
        cmdCity.CommandText = "SELECT City_ID FROM city WHERE City_name='" & University_MainLocation & "';"
        Dim drCity As SqlDataReader = cmdCity.ExecuteReader
        drCity.Read()
        City_ID_Value = drCity("City_ID")
        drCity.Close()
        sql_statment = "SELECT * FROM University WHERE Main_Loc=" & City_ID_Value & ";"
    ' you'll get error here

    Code:
     
        ' Dim cmdUniversity As New SqlCommand
    
        ' /// <TheError Maker>    
        '  You set the connetion here again without close the connection
        '  before if you want to change the commandtext
        cmdCity.Connection = Global_Connection
        ' /// </TheErrorMaker>
        cmdUniversity.CommandText = sql_statment
        cmdUniversity.ExecuteReader()
    Solution:

    One, if you want to use the same connection then why dont you set the connetion before the if statement.

    Two, everytime you want to change the commandtext, close the connetion first and then reconnect again.

    Three, Every time you use the datareader, it better to call .ExecuteReader (*.Close), so after you close the datareader it will close the connetion automatically, but only if the condition you'll not use the connetion after you close the datareader.
    *) i forgot what is the enumeration name, but what i want to tell you is call the overloaded ExecuteReader method that accept the enumeration that the one of the member of the enumeration is a flag to tell the method that it will automatically close the connection after the datareader is closed.

    Four, You put wrong place for the exception check, always put the general exception check at the end of the catch statement.

    Code:
           ' your code
           Catch ex As Exception
               MsgBox(ex.Message)
           ' this one will never executed 
           Catch er As SqlException
               MsgBox(er.Number)
               MsgBox(er.Message)
    
           ' my suggestion
           ' this will checked first
           Catch er As SqlException
               MsgBox(er.Number)
               MsgBox(er.Message)
           ' this will checked if all type of explicit exception is not the type of
           ' exception that you want to check.
           Catch ex As Exception
               MsgBox(ex.Message)
    I hope all of this will solve your problem.
    Last edited by me_code2004; 03-31-2006 at 05:53 AM.

  3. #3
    Join Date
    Mar 2006
    Posts
    9

    It Works!!!

    Very Intersting,
    First: Regarding on” if you want to use the same connection then why don’t you set the connection before the if statement”
    I heard that if the application contains more than one connection at the same form it will be so heavy ,and cause a lot of load which will reduce the application efficiency especially in the big complex systems.
    Second: Regarding on” call the overloaded ExecuteReader method that accept the enumeration that the one of the member of the enumeration is a flag to tell the method that it will automatically close the connection after the datareader is closed.”
    Can you tell me please the name of that method(exactly!!)
    Third: I follow your instructions then the code work correctly, thank you very much Boss!!
    For Members and everyone : To make a benefit of this discussion, I will attach the corrected Code, Enjoy it..


    Code:
     Private Sub BtnSearch2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnSearch2.Click
            'all variables are initialized in the radio buttons -checked Change Event
    
            Dim Global_Connection As New SqlConnection
    
            Try
    
                Dim sql_statment As String
                If SearchKey = 0 Then
                    MsgBox("Please choose your Search Key", MsgBoxStyle.OKOnly, "Error")
                ElseIf SearchKey = 1 Then
                    University_ID = CInt(Me.txtUniversityID2.Text)
                    sql_statment = "select * from University where University_ID=" & University_ID & ";"
                ElseIf SearchKey = 2 Then
                    University_Name = Me.txtUniversityName2.Text
                    sql_statment = "select * from University where University_Name like '" & University_Name & "';"
                ElseIf SearchKey = 3 Then
                    University_MainLocation = Me.CB_City2.SelectedItem
    
                    'declare a sqlcommand to get the value of the cityname
                    Dim cmdCity As New SqlCommand
                    cmdCity.CommandText = "select City_ID from city where City_name='" & University_MainLocation & "';"
                    cmdCity.Connection = Global_Connection
    
                    Global_Connection.ConnectionString = ("workstation id=INTUSER-F522271;packet size=4096;integrated security=SSPI;data source=INTUSER-F522271;persist security info=False;initial catalog=CASFSU")
                    Global_Connection.Open()
    
                    Dim drCity As SqlDataReader = cmdCity.ExecuteReader
                    drCity.Read()
                    City_ID_Value = drCity("City_ID")
                    drCity.Close()
                    'Global_Connection will be closed automatically
    
                    sql_statment = "select * from University where Main_Loc=" & City_ID_Value & ";"
    
    
                ElseIf SearchKey = 4 Then
                    University_WebAddress = Me.txtWebsite2.Text()
                    sql_statment = "select * from University where Website_Address like '" & University_WebAddress & "';"
                ElseIf SearchKey = 5 Then
                    University_EMail = Me.txtEmail2.Text
                    sql_statment = "select * from University where Email like '" & University_EMail & "';"
                End If
    
                Dim MyAdapter As New SqlDataAdapter(sql_statment, "workstation id=INTUSER-F522271;packet size=4096;integrated security=SSPI;data source=INTUSER-F522271;persist security info=False;initial catalog=CASFSU")
    
                Dim ds As New DataSet
                ds.Clear()
                MyAdapter.Fill(ds, "University")
                Me.DG_Search.DataSource = ds.Tables("University")
    
            Catch er As SqlException
    
                MsgBox(er.Number)
                MsgBox(er.Message)
    
            Catch ex As Exception
                MsgBox(ex.Message)
    
            End Try
    
        End Sub

    Enjoy!!

Similar Threads

  1. Data Environment - Cache Results problem
    By BernS in forum VB Classic
    Replies: 0
    Last Post: 06-20-2002, 10:27 AM
  2. Replies: 2
    Last Post: 05-02-2002, 04:10 PM
  3. Replies: 0
    Last Post: 01-09-2002, 10:34 AM
  4. Data List Control Problem
    By John in forum VB Classic
    Replies: 0
    Last Post: 10-12-2001, 06:09 PM
  5. problem with data islands and images
    By Michael Shutt in forum XML
    Replies: 0
    Last Post: 06-26-2001, 12:51 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center