Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Re-using SQL datasets/adapter in a GridView in ASP.NET(VB)

Posted on 2009-12-22
33
Medium Priority
?
299 Views
Last Modified: 2013-11-26
I am using the code below to populate a GridView. Works great. What I want to do though is have a function that changes the value of the query (Label1.text) and then re-bind the data using the code below. When I try to do this however, I get the following:

The ConnectionString property has not been initialized.

How do I get around this?

TIA
Dim ds As New Data.DataSet
            Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, myConnection)
            da.Fill(ds, "employees")
            GridView1.DataSource = ds.Tables("employees")
            GridView1.DataBind()

Open in new window

0
Comment
Question by:Mike Miller
  • 15
  • 12
  • 5
  • +1
33 Comments
 
LVL 8

Expert Comment

by:deepu chandran
ID: 26106005
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106079
add this

myConnection.open

on top
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106089
and

myConnection.close

on bottom
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:Mike Miller
ID: 26106353
Deepu. I'm just reading data from a table. I'm not updating anything.

Kurt, I'm actually opening it in another part of the application. As I said, it does work fine on the first time populating. It's only after the gridview has data in it and I try to bind it with a different query that I'm running into problems. I did try your suggestion anyway and received the same result.
0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26106587
can i have your complete code whaer you have establised the connection string
0
 

Author Comment

by:Mike Miller
ID: 26106597

            myConString = DBConnect()
            myConnection.ConnectionString = myConString
            myCommand.Connection = myConnection

Open in new window

0
 

Author Comment

by:Mike Miller
ID: 26106605
here's the DBConnect function
Public Function DBConnect()
        Dim ConnectionString = "Data Source=myServer;" & _
                    "Initial Catalog=myDB;" & _
                    "Integrated Security=SSPI"
        Return ConnectionString
    End Function

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106625
on which line do you get this error? (1-5? based on first post)

Remarks
This implementation of the SqlDataAdapter opens and closes a SqlConnection if it is not already open. This can be useful in an application that must call the Fill method for two or more SqlDataAdapter objects. If the SqlConnection is already open, you must explicitly call Close or Dispose to close it.

--> http://msdn.microsoft.com/en-us/library/kx703tc9.aspx

so there is no need to open & close!...
0
 

Author Comment

by:Mike Miller
ID: 26106664
From my initial post, the error occurs on Line3. I'm not opening it with any code, so yes you are right. I'm only getting this error when there is already data in the grid.
0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26106747
whaer do you call the function during the first bind
0
 

Author Comment

by:Mike Miller
ID: 26106767

Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DropDownList1.SelectedIndexChanged
        Label1.Visible = True
        Label2.Visible = True
        Label3.Visible = True
        Panel1.Visible = True
        SetupPage()
        SetQueries()
        BindData()
    End Sub

Open in new window

0
 

Author Comment

by:Mike Miller
ID: 26106777

  Sub BindData()
        Dim ds As New Data.DataSet
            Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, myConnection)
            da.Fill(ds, "employees")
            GridView1.DataSource = ds.Tables("employees")
            GridView1.DataBind()
    End Sub

Open in new window

0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26106818
sorry where do u call the DBConnect()
0
 

Author Comment

by:Mike Miller
ID: 26106832
see my 3rd comment
0
 

Author Comment

by:Mike Miller
ID: 26106841
oh sorry....in the PageLoad
0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26106871
post ur pageLoad too
0
 

Author Comment

by:Mike Miller
ID: 26106883
  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        PagePrep()
        If Not Page.IsPostBack Then
            PopulateSupervisors()
        End If
    End Sub
    Sub PagePrep()
        Try
            lblFishUser.Text = GetName()
            strCSS = GetCSS()
            MyStyleSheet.Attributes.Add("href", strCSS)
            If lblWeek.Text = "0" Then
                strToday = Month(Now) & "/" & Day(Now) & "/" & Year(Now)
                LinkButton2.Enabled = False
            End If
            SetDates()
            myConString = DBConnect()
            myConnection.ConnectionString = myConString
            myCommand.Connection = myConnection
        Catch ex As Exception
            RecordError(ex)
            Response.Redirect("onerror.aspx", False)
        End Try
    End Sub
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106905
use this


Sub BindData() 
        Dim ds As New Data.DataSet 
        Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, DBConnect()) 
        da.Fill(ds, "employees") 
        GridView1.DataSource = ds.Tables("employees") 
        GridView1.DataBind() 
End Sub

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106920
sorry, above does not solve the issue ;)
0
 
LVL 15

Expert Comment

by:rajeeshmca
ID: 26106929
After you fill ur dataset  just close the connection

Sub BindData()
        Dim ds As New Data.DataSet
            Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, myConnection)
            da.Fill(ds, "employees")
            GridView1.DataSource = ds.Tables("employees")
            GridView1.DataBind()
           myConnection.Close()
    End Sub

Try this one
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106953
rajeeshmca, myConnection is never opened in the code and  da.Fill(ds, "employees") opens and closes the connection, and open & close is already suggsted before...

can you please put a BP on

Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, myConnection)

and check the value & state of myConnection, value of connectionstring etc...
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26106961
maybe you are doing something here ;)

PopulateSupervisors()

we need teh code for this proc too...

because BindData() is called after page_load
0
 

Author Comment

by:Mike Miller
ID: 26106980
PopulateSupervisors is not relevant but here it is
    Sub PopulateSupervisors()
        Dim deGroup As DirectoryEntry
        Dim MembersCollection As IADsMembers
        Dim member As IADsUser
        Dim strRootAD As String = GetAllLDAP()
        Dim objRootDSE As New DirectoryEntry(strRootAD)
        Dim slGroupMembers As New SortedList
        Dim strManagerGroupName As String = GetManagerGroupWO()
        Dim strGroupPath As String = GetGroupLDAP()
        deGroup = Nothing
        MembersCollection = Nothing
        DropDownList1.Items.Add("")
        Dim strSupervisorGroupName As String = GetSupervisorGroupWO()
        deGroup = New DirectoryServices.DirectoryEntry("LDAP://CN=" & strSupervisorGroupName & "," & strGroupPath)
        MembersCollection = CType(deGroup.Invoke("Members"), IADsMembers)
        For Each member In MembersCollection
            Dim li2 As ListItem = New ListItem(member.LastName & ", " & member.FirstName)
            DropDownList1.Items.Add(li2)
        Next
    End Sub

Open in new window

0
 

Author Comment

by:Mike Miller
ID: 26107017
And yes I do call BindData() again. Anytime DropDownList1.text is changed or when Button1 is clicked. If there is any data in the GridView then I get the error in both cases.
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26107056
ok, letc clear the dats then ;)
maybe this fixes...
Sub BindData()  
        Dim ds As New Data.DataSet  
        Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, DBConnect())  
        da.Fill(ds, "employees")  
        GridView1.DataSource = Nothing
        GridView1.DataSource = ds.Tables("employees")  
        GridView1.DataBind()  
End Sub

Open in new window

0
 
LVL 61

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 26107065
but before you said you are getting error on

Dim da As New Data.SqlClient.SqlDataAdapter(Label1.Text, myConnection)

thats why we thought it is something with connection...

any comment on 26106953?
0
 

Author Comment

by:Mike Miller
ID: 26107086
I said line 3.  Line 3 for me is

da.Fill(ds, "employees")
0
 

Author Comment

by:Mike Miller
ID: 26107092
GridView1.DataSource = Nothing

does not fix it. It never even gets to that line
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26107194
still I am expecting some comments on 26106953?

you need to put a BP and check myConnection state, connection string etc, before you get error...
0
 

Author Comment

by:Mike Miller
ID: 26107208
I got it. A pain, but this works. Thanks guys
        myConString = DBConnect()
        myConnection.ConnectionString = myConString
        myCommand.Connection = myConnection
        myCommand.Connection.Open()
        Dim ds As New Data.DataSet
        Dim da As New Data.SqlClient.SqlDataAdapter(lblMon.Text, myConnection)
        da.Fill(ds, "employees")
        GridView1.DataSource = ds.Tables("employees")
        GridView1.DataBind()
        myCommand.Connection.Close()
        myConString = Nothing
        myConnection = Nothing
        ds = Nothing
        da = Nothing

Open in new window

0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26107333
why do you need all these

myCommand.Connection = myConnection
myCommand.Connection.Open()

myCommand.Connection.Close()

myConString = Nothing
myConnection = Nothing

I thought you have a global variable myConnection, and it is pproperly set in myConString = DBConnect()
in page_load... just I could not get ;)
0
 
LVL 61

Expert Comment

by:HainKurt
ID: 26107348
is this fine?
myConnection.ConnectionString = DBConnect()
myConnection.Open() 

Dim ds As New Data.DataSet 
Dim da As New Data.SqlClient.SqlDataAdapter(lblMon.Text, myConnection) 
da.Fill(ds, "employees") 
GridView1.DataSource = ds.Tables("employees") 
GridView1.DataBind() 

myCommand.Connection.Close() 
myConnection = Nothing 

ds = Nothing 
da = Nothing

Open in new window

0
 

Author Closing Comment

by:Mike Miller
ID: 31674550
not the solution but my account is locked from asking another question :-\
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Loops Section Overview
Suggested Courses
Course of the Month13 days, 7 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question