Solved

adding rows to gridview

Posted on 2010-09-22
10
336 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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Syntax Error 2 47
Split in Javascript 5 31
Get size of each directory on each mapped drive 5 24
Code works but it's slow 24 45
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…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
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…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.

863 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

18 Experts available now in Live!

Get 1:1 Help Now