Solved

adding rows to gridview

Posted on 2010-09-22
10
340 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 5
10 Comments
 
LVL 41

Accepted Solution

by:
guru_sami earned 500 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Increase Agility with Enabled Toolchains

Connect your existing build, deployment, management, monitoring, and collaboration platforms. From Puppet to Chef, HipChat to Slack, ServiceNow to JIRA, Splunk to New Relic and beyond, hand off data between systems to engage the right people.

Connect with xMatters.

Question has a verified solution.

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

Introduction This article shows how to use the open source plupload control to upload multiple images. The images are resized on the client side before uploading and the upload is done in chunks. Background I had to provide a way for user…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

717 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