adding rows to gridview

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()
                             
santaspores1Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
guru_samiConnect With a Mentor Commented:
---> 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
 
guru_samiCommented:
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
 
santaspores1Author Commented:
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
guru_samiCommented:
can you set breakpoint in the Get  and set of MyDataTable and see if it is set correctly?
0
 
santaspores1Author Commented:
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
 
santaspores1Author Commented:
and how is your code actually doing a get or a set?
0
 
guru_samiCommented:
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
 
guru_samiCommented:
In your modified code..

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

--> Me.MyDataTable = TempDataTable ' This will do Set
0
 
santaspores1Author Commented:
yippeee - thanks sami!  All is well.  Good karma on you man.
0
 
santaspores1Author Commented:
Lovely - thanks for all of your time and effort!
0
All Courses

From novice to tech pro — start learning today.