Nice Guy Nice Guy - 7 months ago 54
Vb.net Question

DataTable must be set prior to using DataView. Am I binding my data the wrong way?

it have been a week since I start with this project but I'm totally clueless on what to fix anymore. down here is part of my coding.

Protected Sub bpn_Click(sender As Object, e As EventArgs)
Try
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
connection.Open()
Dim sql As String = ("select * from LOT_ WHERE PRODUCTNAME ='" & txtbpn.Text & "'")
Dim cmd As SqlCommand = New SqlCommand(sql, connection)
Using reader As SqlDataReader = cmd.ExecuteReader
If reader.HasRows Then
lblerror.Visible = False
connection.Dispose()
connection.Close()
Me.BindBpn()

txtbpn.Text = String.Empty
TextBox2.Text = String.Empty
TextBox3.Text = String.Empty
TextBox4.Text = String.Empty
TextBox5.Text = String.Empty
Else
connection.Dispose()
connection.Close()

lblerror.Text = "bpn not found"
lblerror.Visible = True
txtbpn.Text = String.Empty
TextBox2.Text = String.Empty
TextBox3.Text = String.Empty
TextBox4.Text = String.Empty
TextBox5.Text = String.Empty
End If
End Using
Catch ex As Exception
Response.Write(ex.Message)
Response.AppendHeader("Refresh", "1;url=Summary.aspx")
End Try
End Sub


1.the binding sub

Private Sub BindBpn()

Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString())
Using cmd As New SqlCommand("SELECT * FROM LOT_ WHERE PRODUCTNAME='" & txtbpn.Text & "'order by checkin asc")
Using sda As New SqlDataAdapter()
con.Open()
cmd.Connection = con
sda.SelectCommand = cmd
Dim dt As New DataTable()
sda.Fill(dt)
Dim dv As New DataView(dt)

GridView1.DataSource = dv
GridView1.DataBind()
' Me.BindGridView()
con.Close()

End Using
End Using
End Using
If GridView1.Visible = False Then
GridView1.Visible = True
End If
If Button1.Visible = False Then
Button1.Visible = True
End If
End Sub


2.the error happen when I'm trying to sort the data.

Protected Sub SortRecords(sender As Object, e As GridViewSortEventArgs)

Dim SortDir As String = String.Empty
Dim sortExpression As String = e.SortExpression


Dim dv As New DataView(GridView1.DataSource)
If dv IsNot Nothing Then
If direction = SortDirection.Ascending Then
direction = SortDirection.Descending

SortDir = "Desc"
ViewState("SortExpression") = Convert.ToString(e.SortExpression & " " & SortDir)
Else
direction = SortDirection.Ascending

SortDir = "Asc"
ViewState("SortExpression") = Convert.ToString(e.SortExpression & " " & SortDir)

End If


End If

dv.Sort = ViewState("SortExpression").ToString
GridView1.DataSource = dv
GridView1.DataBind()
end sub


The error happen on this code

dv.Sort = ViewState("SortExpression").ToString


Any help would be great. Thanks in advance

this is my aspx GridView:

<asp:GridView ID="GridView1" width="100%" runat="server"
AllowSorting="True"
OnSorting="SortRecords"
OnRowDataBound="GridView1_RowDataBound"
ItemStyle-HorizontalAlign="Center"
AutoGenerateColumns="False" >
<Columns>
<asp:TemplateField HeaderText="ID" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<%# Container.DataItemIndex + 1 %>
</ItemTemplate>
<ItemStyle HorizontalAlign="center" Width="5%" />
</asp:TemplateField>

<asp:BoundField DataField="Location" HeaderText="Location" />
<asp:BoundField DataField="LOT_ID" HeaderText="Lot ID" />
<asp:BoundField DataField="PkgName" HeaderText="PIN PACKAGE NAME" />
<asp:BoundField DataField="MBTBoardNo" HeaderText="MBTBoardNo" SortExpression="MBTBoardNo" />
<asp:BoundField DataField="BTProgramName" HeaderText="BT Program Name" />
<asp:BoundField DataField="ProductRank" HeaderText="ProdRank"/>
<asp:BoundField DataField="BASEPRODUCTNAME" HeaderText="BPN" />
<asp:BoundField DataField="MCNo" HeaderText="MC No" />
<asp:BoundField DataField="QTY" HeaderText="Qty" />
<asp:BoundField DataField="CreateDate" HeaderText="Start Date" />
<asp:BoundField DataField="CheckIn" HeaderText="CheckIn" />
<asp:BoundField DataField="CheckOut" HeaderText="CheckOut" />
</Columns>
</asp:GridView>

Answer

This may be overkill butI need to explain some things.

Sql injection attacks aside for the moment you have a lot of other thing to understand first.

  1. You are having trouble sorting because you have not Cached the retrieved data to any persistence mechanism. This Code in your sorting mechanism will always evaluate to Nothing:

    Dim dv As New DataView(GridView1.DataSource)

  2. A GridView DataSource is not persisted across postbacks unless you use a Caching data control like SqlDataSource which manages the cache for you.

  3. Or you do so programmatically in one of:

    1. ViewState(bad idea)
    2. SessionState(not perfect)
    3. the Application Cache(ideal)

But back to basics:

Here is an edited and annotated version of the first part of your post I'll provide a cleaned up version following this:

Protected Sub bpn_Click(sender As Object, e As EventArgs)
  ' Added for clarity
  Dim cs as String = ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString()
  ' Try is generally not needed if you are using Using
  ' at least not here. Move it to the inner most Using block and 
  ' wrap it around the actual command execution to catch sql errors
  ' you want to personally manage.

  ' Try   

  Using connection As New SqlConnection(cs)
      connection.Open()
     ' String.Format() - learn to love this function 
     Dim sql As String = String.Format("select * from LOT WHERE PRODUCTNAME ='{0}' order by checkin asc", txtbpn.Text)

     Dim cmd As SqlCommand = New SqlCommand(sql, connection)

       ' This reader is not necessary as all you are doing 
       ' is using it to determine if there is anything to do
       ' This is a waste of time
       ' You already have one open connection and 
       ' now you are jumping to a bind operation 
       ' that opens more. 
       ' Generally speaking: 
       '   open a data connection, get the data, close the connection
       Using reader As SqlDataReader = cmd.ExecuteReader

         ' Do this here, not in both parts of the `If`
         lblerror.Visible = reader.HasRows

         ' lblerror.Text can be initialized here or in the .aspx 
         ' as you do nothing else with it but toggle its visibility
         ' Plus you can make use of a GridView's EmptyDataText property
         ' and get rid of the Label altogether.
         lblerror.Text = "bpn not found"

         If reader.HasRows Then

             Me.BindBpn() 

         End If

         ' This code is not needed as it's handled by the outer Using
         ' connection.Dispose()
         ' connection.Close()

         // Since this code happens in both parts of the 
         // IF, it only needs to happen once.
         txtbpn.Text = String.Empty
         TextBox2.Text = String.Empty
         TextBox3.Text = String.Empty
         TextBox4.Text = String.Empty
         TextBox5.Text = String.Empty

      // Check your original code: 2 Using requires 2 End Using  
      End Using
    End Using
  ' See Try above
  Catch ex As Exception
      Response.Write(ex.Message)

      'Why are you trying to refresh every second?
      Response.AppendHeader("Refresh", "1;url=Summary.aspx")
  End Try
End Sub

Here is the Revised Code without all the comments

Protected Sub bpn_Click(sender As Object, e As EventArgs)
  Dim cs as String = ConfigurationManager.ConnectionStrings("SQLServer2005DBConnectionString").ToString()
  Using connection As New SqlConnection(cs)
     connection.Open()
     Dim sql As String = String.Format("select * from LOT WHERE PRODUCTNAME ='{0}' order by checkin asc", txtbpn.Text)

     Dim cmd As SqlCommand = New SqlCommand(sql, connection)

     Using sda As New SqlDataAdapter( cmd )
        Try
          Dim dt As New DataTable()
          sda.Fill(dt)
          Dim dv as New DataView(dt)

          ' This will persist the retrieved record set
          ' -- Replaced C# syntax with VB 
          Cache("AStringToIdentifyThisGridviewCache") = dv
          GridView1.DataSource = Cache("AStringToIdentifyThisGridviewCache")

          Gridview1.EmptyDataText = "bpn not found"
          GridView1.DataBind()
          GridView1.Visible = (GridView1.Rows.Count > 0)

          txtbpn.Text = String.Empty
          TextBox2.Text = String.Empty
          TextBox3.Text = String.Empty
          TextBox4.Text = String.Empty
          TextBox5.Text = String.Empty

        Catch ex As Exception
          Gridview1.EmptyDataText = ex.Message
        End Try

     End Using
    End Using
End Sub

One more thing, your data is going to be retrieved from the database and will overwrite the Cache and rebind to the GridView every time you hit the button.

Typically you want to retrieve the data once and then work with the Cached dataset until the data is modified in some way that it requires another database hit.

Programmatic data retrieval and binding is certainly useful, but I find I rarely need to use it. You may be under requirements for this project but for GridView operations, it's hard to beat the provided DataSource Controls.

Hope this helps. Happy Coding.

Sorting Code lifted from MSDN Sample for GridView Sorting

  Protected Sub SortRecords(ByVal sender As Object, ByVal e As GridViewSortEventArgs)

    'Retrieve the table from the session object.
    Dim dv As DataView = TryCast(Cache("AStringToIdentifyThisGridviewCache"), DataView)

    If dv IsNot Nothing Then
        'Sort the data.
        dv.Sort = e.SortExpression & " " & GetSortDirection(e.SortExpression)
        GridView1.DataSource = Cache("AStringToIdentifyThisGridviewCache")
        GridView1.DataBind()

    End If

  End Sub


  Private Function GetSortDirection(ByVal column As String) As String

    ' By default, set the sort direction to ascending.
    Dim sortDirection = "ASC"

    ' Retrieve the last column that was sorted.
    Dim sortExpression = TryCast(ViewState("SortExpression"), String)

    If sortExpression IsNot Nothing Then
        ' Check if the same column is being sorted.
        ' Otherwise, the default value can be returned.
        If sortExpression = column Then
            Dim lastDirection = TryCast(ViewState("SortDirection"), String)
            If lastDirection IsNot Nothing _
              AndAlso lastDirection = "ASC" Then

                sortDirection = "DESC"

            End If
        End If
    End If

    ' Save new values in ViewState.
    ViewState("SortDirection") = sortDirection
    ViewState("SortExpression") = column

    Return sortDirection

  End Function