Solved

adding rows to gridview

Posted on 2010-09-22
10
334 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 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

746 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now