VB.NET and sorting

[Tmuld]

(using vb.net)

Finally got paging working...not sorting taunts me...

For sorting on a datagrid - do you HAVE to use a dataview?

I used the msdn example and now get his error:

DataTable must be set prior to using DataView

So I have a dataset created from the input of the user and an SQL
statement. Then I use a dataview - well because I have to. The
dataview was made at design time along with the textbox and label.

Here is the code to generate and display my data retrieved:

Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btn.Click


Dim Count As Integer
Dim ds As New DataSet
Dim SQL As String
SQL = "select [First],[Last],[Title],[Authors],[Address] from
DB1 WHERE [Title] LIKE '%" & txt.Text & "%'" & "OR [Address] LIKE '%" &
txt.Text & "%'" & " ORDER BY [Authors]"
Dim da As New SqlDataAdapter(SQL, SqlConnection2)

Try
SqlConnection2.Open()

da.Fill(ds)
Session("ds") = ds

Dim dv As DataView = ds.Tables(0).DefaultView

Count = dv.Count

dg.DataSource = dv
dg.DataBind()
lblCount.Text = Count

Catch ex As Exception
txtQuery.Text = ex.Message.ToString
Finally
SqlConnection2.Close()
End Try
End Sub

Private Sub dg_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dg.SortCommand
dv.Sort = e.SortExpression
dg.DataBind()
End Sub



Where do I go from here? How do I add a Datatable? I know what the
columns are from my dataset?

Thanks!

Tmuld

 

[Marcie Jones]

For sorting on a datagrid - do you HAVE to use a dataview?
Yes.

I used the msdn example and now get his error:

DataTable must be set prior to using DataView

So I have a dataset created from the input of the user and an SQL
statement. Then I use a dataview - well because I have to. The
dataview was made at design time along with the textbox and label.

Here is the code to generate and display my data retrieved:

Private Sub btnQuery_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles btn.Click


Dim Count As Integer
Dim ds As New DataSet
Dim SQL As String
SQL = "select [First],[Last],[Title],[Authors],[Address] from
DB1 WHERE [Title] LIKE '%" & txt.Text & "%'" & "OR [Address] LIKE '%" &
txt.Text & "%'" & " ORDER BY [Authors]"
Dim da As New SqlDataAdapter(SQL, SqlConnection2)

Try
SqlConnection2.Open()

da.Fill(ds)
Session("ds") = ds

Dim dv As DataView = ds.Tables(0).DefaultView

Count = dv.Count

dg.DataSource = dv
dg.DataBind()
lblCount.Text = Count

Catch ex As Exception
txtQuery.Text = ex.Message.ToString
Finally
SqlConnection2.Close()
End Try
End Sub

Private Sub dg_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dg.SortCommand
dv.Sort = e.SortExpression
dg.DataBind()
End Sub

Try changing to this:

Private Sub dg_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dg.SortCommand
Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = e.SortExpression
dg.DataSource = dv
dg.DataBind()
End Sub

Or better yet, put your databinding logic into a separate routine, all
call that from your Button_Click, your sort event, and your paging
events.

Marcie

 

[Tmuld]

Wow! You must have some .NET experience!

My dataset was created programatically and dimmed in the button routine
- so when I get to the sort routine - I get a squiggly stating
ds.Tables(0).Defautlview is not declared.

So if I put it in a separate routine from the btnClick - it will
alleviate this?

Or can I pass it using session variable - or is that really bad coding?

Private Sub dg_SortCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridSortCommandEventArgs) Handles
dg.SortCommand

Dim ds as new dataset
ds=session("ds")

Dim dv As DataView = ds.Tables(0).DefaultView
dv.Sort = e.SortExpression
dg.DataSource = dv
dg.DataBind()
End Sub

Many thanks for the help and suggestions!

Tmuld.

 

[Marcie Jones]

Put that all in a separate routine, creating/dimming the DataSet and
setting the grid's DataSource.

Hope that helps!
Marcie

 

[Tmuld]

I guess that would make sense, but I am not sure where to put it.

Right now, poorly coded use of session variables to pass data between
routines works. But not the most optimized.

My dataset filling is dependent on the SQL statement that comes from
the user entered data.

Are you saying put this :

Private sub MakeDs()

Dim ds As New DataSet
Dim SQL As String
SQL = "select [First],[Last],[Title],[Authors],[Address] from
DB1 WHERE [Title] LIKE '%" & txt.Text & "%'" & "OR [Address] LIKE '%" &
txt.Text & "%'" & " ORDER BY [Authors]"
Dim da As New SqlDataAdapter(SQL, SqlConnection2)

da.Fill(ds)
Session("ds") = ds

Dim dv As DataView = ds.Tables(0).DefaultView

End Sub

in a routine?

I guess the the problem I am having is do variables in subroutines stay
declared outside routines?

Thanks,

Tmuld.

 

[Marcie Jones]

That's fine, but also add:

dg.DataSource = dv
dg.DataBind()

Marcie