Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

adding rows to gridview

Posted on 2010-09-22
10
Medium Priority
?
346 Views
Last Modified: 2012-05-10
This should be really simple.... but the best-practice solution is escaping me

ASP.net - Visual Basic - Code Behind

I have an input field - the user enters a user-id and clicks a lookup button.
The click event looks up the user in a database and writes a row to a visible gridview.
EACH time the user clicks the lookup button a NEW/ADDITIONAL row is added to the gridview.

What I currently have in my lookup button click code is
1. Make sure the user input is valid
2. build the sql string using the user input value
3. Use an OracleDataReader to hold the returned data
4. Bind a gridview to the datareader

The problem is that each time the lookup button is clicked the gridview will re-bind to the datareader that only has a single most recent record in it.   But I want the gridview to grow each time the user clicks the lookup button.  I tried using an intermediary datable but ran into the same problem - I couldn't manage to create anything that was growing at each click rather than overwriting.  

What is the best way to go about this?  actual code would be most handy - here is a bit of my click code:

 If (FoundError = False) Then
            ' lookup name and ssn in database and add the data to our datagrid
            Dim MyOracleConnectionString As String = ConfigurationManager.ConnectionStrings("MyOracleConnection").ConnectionString
            Using MyOracleConnection As New OracleConnection (MyOracleConnectionString)
                MyOracleConnection.Open()    
                Using MyCommand As New OracleCommand
                          Try
                        MyCommand.CommandText = "select bla bla bla " 
                       
                        MyCommand.Connection = MyOracleConnection

                        ' use a data-reader when using sql to return multiple columns
                            Using MyDataReader As OracleDataReader = MyCommand.ExecuteReader()
                                    If (MyDataReader.HasRows) then
                                Do While MyDataReader.Read()
                                    ' Add the datareader row to the datatable
                                    MyDataRow = MyDataTable.NewRow
                                    MyDataRow("StuID") = MyDataReader("StuID")
                                    MyDataRow("StuName") = MyDataReader("StuName")
                                    MyDataRow("StuSSN") = MyDataReader("StuSSN")
                                    MyDataTable.Rows.Add(MyDataRow)
                                          Loop
                                ' bind the datatable to the gridview
                          X_OutputSection_GridView.DataSource = MyDataTable
                                X_OutputSection_GridView.DataBind()
                             
0
Comment
Question by:santaspores1
  • 5
  • 5
10 Comments
 
LVL 41

Accepted Solution

by:
guru_sami earned 2000 total points
ID: 33735103
---> I tried using an intermediary datable but ran into the same problem
Option 1:
What you should be doing is storing your DataTable in ViewState.

Public Property MyDataTable() As DataTable
	Get
		If ViewState("MyDataTable") Is Nothing Then
			Return New DataTable()
		Else
			Return DirectCast(ViewState("MyDataTable"), DataTable)
		End If
	End Get
	Set
		ViewState("MyDataTable") = value
	End Set
End Property

'Modify your code to add row to you datatable like below:

'Get your my datatable in temp dt
Dim dt As DataTable = Me.MyDataTable
' here goes your reader loop and adding row to 
dt.Rows.Add(dt)

'set the mydatatabel to new value 
Me.MyDataTable = dt
X_OutputSection_GridView.DataSource = MyDataTable
X_OutputSection_GridView.DataBind()

Open in new window

0
 
LVL 41

Expert Comment

by:guru_sami
ID: 33735256
Then you have another option where you need to modify your query.

1: Store the usernames in session upon button click
2: Pass the comma separated list to your query

Something like below:
Note:
1:here you don't need to use Datatable, because your query will return rows for all users. So you can bind the reader directly to your GV.
2: Be sure to make your query a parameterized query instead of inline to prevent sql injection.
Public Property UserList() As CommaDelimitedStringCollection
	Get
		If Session("UserList") Is Nothing Then
			Return New CommaDelimitedStringCollection()
		Else
			Return DirectCast(Session("UserList"), CommaDelimitedStringCollection)
		End If
	End Get
	Set
		Session("UserList") = value
	End Set
End Property

'In your button click, you do this:

Dim tempcoll As CommaDelimitedStringCollection = Me.UserList
Dim un As String = "'" + TextBox2.Text & "'"
If Not tempcoll.Contains(un) Then
	tempcoll.Add(un)
	Me.UserList = tempcoll
End If

Dim sql As String = "Select * from table where Username IN (" & Me.UserList.ToString() & ")"

'Execute your query and bind the reader to GV

Open in new window

0
 

Author Comment

by:santaspores1
ID: 33735395
Sami,

Thanks!  I tried your code but it isn't working.... I must have something wrong.  I added the following

Public Property MyDataTable() As DataTable
      Get
            If ViewState("MyDataTable") Is Nothing Then
                  Return New DataTable()
            Else
                  Return DirectCast(ViewState("MyDataTable"), DataTable)
            End If
      End Get
      Set
            ViewState("MyDataTable") = value
      End Set
End Property

... I don't know what the line ViewState("MyDataTable") = value does

and I changed my sub to:

Dim TempDataTable = Me.MyDataTable
Dim TempDataRow As DataRow

                          Do While MyDataReader.Read()
                                    ' Add the datareader row to the datatable
                                    TempDataRow = TempDataTable.NewRow
                                    TempDataRow("StuID") = MyDataReader("StuID")
                                    TempDataRow("StuName") = MyDataReader("StuName")
                                    TempDataRow("StuSSN") = MyDataReader("StuSSN")
                                    TempDataTable.Rows.Add(TempDataRow)
                   Loop
                                ' bind the datatable to the gridview
                                Me.MyDataTable = TempDataTable
                                          X_OutputSection_GridView.DataSource = Me.MyDataTable
                                                   X_OutputSection_GridView.DataBind()

The gridview isn't showing me anything any more.

Is the error anything obvious from the above code?
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 41

Expert Comment

by:guru_sami
ID: 33735542
can you set breakpoint in the Get  and set of MyDataTable and see if it is set correctly?
0
 

Author Comment

by:santaspores1
ID: 33736346
Where do I define the datatable columns

MyDataTable.Columns.Add ("ID", System.Type.GetType("System.String"))
MyDataTable.Columns.Add ("Name", System.Type.GetType("System.String"))
MyDataTable.Columns.Add ("SSN", System.Type.GetType("System.String"))

P.S.  Thank you again sami and you are definately getting all the points.  
0
 

Author Comment

by:santaspores1
ID: 33736406
and how is your code actually doing a get or a set?
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 33736424
That's my bad....

Try modifying the Get if:

Get
      If ViewState("MyDataTable") Is Nothing Then
                       Dim dt As New DataTable()

                       ' define columns here

                        ViewState("MyDataTable") = dt
      End If
          Return DirectCast(ViewState("MyDataTable"), DataTable)
End Get
     
0
 
LVL 41

Expert Comment

by:guru_sami
ID: 33736442
In your modified code..

--> Dim TempDataTable = Me.MyDataTable ' This will do Get

--> Me.MyDataTable = TempDataTable ' This will do Set
0
 

Author Comment

by:santaspores1
ID: 33736596
yippeee - thanks sami!  All is well.  Good karma on you man.
0
 

Author Closing Comment

by:santaspores1
ID: 33736603
Lovely - thanks for all of your time and effort!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

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

Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
The article shows the basic steps of integrating an HTML theme template into an ASP.NET MVC project
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses
Course of the Month20 days, 21 hours left to enroll

810 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