[jpcsmit]

I would like to ask your help on my project here. I have two SQL tables, one that has file properties and the other is for file paths. Both tables have MD5 hash for their relationship and the file paths is set to one-to-many per MD5 hash. (hope this is not confusing)

Now, I have a nested gridview to display these info. The outer gridview has all the data under file properties table. The inner gridview has the file paths that would be binded to the MD5 of the outer gridview. What I want is for each row, it would show all the properties of the file and its registered file paths.

Now my problem is, it shows all the file properties but for the inner gridview, it shows all the file paths for all files on the db. I'm not sure why its not binding to the outer gridview's MD5 so that the file paths that would be displayed should be for the particular MD5 of a row.

Here's what I got in my RowDataBound:

Private Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
		If e.Row.RowType = DataControlRowType.DataRow Then
			Dim gv As GridView = DirectCast(e.Row.FindControl("gridview3"), GridView)
			Dim conn As New SqlConnection
			Dim myCommand As New SqlCommand
			Dim myAdapter As New SqlDataAdapter
			Dim myData As New DataSet
			Dim SQL As String
			Dim myconn As String

			SQL = "SELECT filepath from tb_filepath WHERE md5='" & DirectCast(e.Row.DataItem, DataRowView)("md5").ToString & "'"

			myconn = "server=tmcm;database=db_Files;Trusted_Connection=True"
			conn.ConnectionString = myconn
			conn.Open()
			myCommand.Connection = conn
			myCommand.CommandText = SQL
			myCommand.ExecuteNonQuery()

			gv.AutoGenerateColumns = False
			gv.DataBind()			

		End If
End Sub

Sorry for being a noob...Still new in asp.net...if you could suggest another approach, i'm open for that. hope you could help me. thanks

This post has been edited by jpcsmit: 03 November 2009 - 03:14 PM

[Jayman]

If you are getting all the data, then you are not getting a value from your cast.

Running this statement:

SELECT filepath from tb_filepath WHERE md5=''

is the same as running this query.

SELECT filepath from tb_filepath

Try accessing the data directly from the GridViewRowEventArgs object, instead of casting it into a DataViewRow and then trying to get at the data.

Lets say the MD5 hash is in the 4th column in the GridView, then you would access it like so:

SQL = "SELECT filepath from tb_filepath WHERE md5='" & e.Row.Cells(3).Text.Trim() & "'"

Just change the index to the correct column number and give it a try. Keep in mind the index starts at 0 for the columns.

[jpcsmit]

It's still the same Jayman...I'm not sure what part I am wrong here...

This post has been edited by jpcsmit: 04 November 2009 - 07:13 PM

[Jayman]

I just noticed some problems when you are getting the data inside the RowDataBound event.

First off the ExecuteNonQuery method is used for UPDATE, INSERT, or DELETE statements. You need to use the SqlDataAdapter or the ExecuteReader methods to return the data.

Then you need to store it in the DataSet you created and then bind that DataSet to your GridView.

Look at the changes I made below.

Private Sub GridView1_RowDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs) Handles GridView1.RowDataBound
		If e.Row.RowType = DataControlRowType.DataRow Then
			Dim gv As GridView = DirectCast(e.Row.FindControl("gridview3"), GridView)
			Dim SQL As String
			Dim myconn As String

			Dim conn As New SqlConnection
			myconn = "server=tmcm;database=db_Files;Trusted_Connection=True"
			conn.ConnectionString = myconn

			SQL = "SELECT filepath from tb_filepath WHERE md5='" & e.Row.Cells(3).Text.Trim() & "'"

			Dim myCommand As New SqlCommand
			myCommand.Connection = conn
			myCommand.CommandText = SQL

			Dim myAdapter As New SqlDataAdapter(myCommand)
			Dim myData As New DataSet
			
			conn.Open()

			myAdapter.Fill(myData)

			gv.AutoGenerateColumns = False

			gv.DataSource = myData
			gv.DataBind()			

		End If
End Sub

If that still doesn't work, then put a break on the following line of code SQL = "SELECT filepath from tb_filepath WHERE md5='" & e.Row.Cells(3).Text.Trim() & "'" and see what value is being returned by 'e.Row.Cells(3).Text'.

[jpcsmit]

Thanks Jayman! I appreaciate all the help. It's now working as expected.