Re: DataAdapter / Datatable - Visual Basic Discussion Boards

Hello David,

Thank you very much.

Quick question though - what is DT?

Is that DataTable?

Thanks,

Dan

Oops. I forgot to declare the datatable.

<br />
Dim DA As SQLDataAdapter<br />
Dim DT As New DataTable   ' I forgot this line before<br />
 <br />
DA = New SqlDataAdapter(SQLcmd)<br />
DA.Fill(DT)<br />
 <br />
Gridview2.Datasource = DT<br />
 <br />

Give me a vote of 5 if this works for you. Smile | :)

Hello David,

I have made the changes you suggested, but am still getting the same error:

Here is the code now:

<script runat="server" >          
        
        Public Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            'Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=ServerName;Initial Catalog=dbName;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlCmd.CommandText = "exec ProcedureName'" & TextBox2.Text & "' "
                
                'This will read the rows in the database.
                'sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                'If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                'GridView2.DataSource = sqlReader
                'GridView2.DataBind()
                    
                    sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
                GridView2.DataBind()
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                'End If
                
                'This is closing the connection to the database once we have finished with it.
                'sqlConn.Close()
                
              
            End If
        End Sub
                           
        
        


        Public Sub gvSorting_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
            
            Dim dt = TryCast(Session("TaskTable"), DataTable)
            Dim dtSortView As New DataTable
            dtSortView = GridView2.DataSource
            
            If dtSortView Is Nothing Then
                Dim dvSortedView As New DataView(dtSortView)

                dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
                
                GridView2.DataSource = dvSortedView
                GridView2.DataBind()
            End If
        End Sub
                 
        Public Function getSortDirectionString(ByVal SortDirection As SortDirection) As String

            Dim newSortDirection As String = String.Empty
        
            If (SortDirection = SortDirection.Ascending) Then
                newSortDirection = "ASC"
            Else
                newSortDirection = "DESC"
                'Return newSortDirection
            End If

            Return newSortDirection

        End Function
        
        
        </script>

Any ideas at all?

Is there code still in there that needs to be taken out?

Or have I missed a bit of code?

Thanks for your help,

Dan

Declare the DataTable at the class level and use it wherever required.

Here is some code I just tested this morning and when I click on the column headings the grid does the sorting.

<br />
Dim SQLconn As SqlConnection<br />
Dim DA As New SqlDataAdapter<br />
Dim DT As New DataTable<br />
<br />
  SQLconn = New SqlConnection(GetConnString())<br />
<br />
  DA.SelectCommand = New SqlCommand("EXEC z_select_test", SQLconn)<br />
<br />
  DA.Fill(DT)<br />
<br />
  Me.DataGridView1.DataSource = DT<br />

You may want to un-hook your gvSorting_Sorting and getSortDirectionString routines because they are not necessary.

give it a shot.

BTW: I'm based on the East Coast of the United States, that's why it takes so long for me to respond to you.

Hello David,

I have done what you have advised, and am now getting a different error:

"ExecuteReader: CommandText property has not been initialized"

The code is:

Public Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            'Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=ServerName;Initial Catalog=KenyaKCSE;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "' ")
                
                'This will read the rows in the database.
                'sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                'If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                'GridView2.DataSource = sqlReader
                'GridView2.DataBind()
                    
                    sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
 
                
                'GridView2.DataSource = sqlDataTable
                GridView2.DataBind()
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                'End If
                
                'This is closing the connection to the database once we have finished with it.
                'sqlConn.Close()
                
              
            End If
        End Sub

Is there anything else that I am doing wrong?

I really appreciate all of your help.

Regards,

Dan

Couple of things I would try.

1) Comment out: sqlConn.Open
2) Comment out: sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
3) Comment out: sqlCmd = sqlConn.CreateCommand
4) Add the sqlConn as shown below:

sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "'",sqlConn)

5) Comment out: sqlAdapter = New SqlDataAdapter(sqlCmd)

Then I believe your code will match my example more closely.

Remember to Un-Hook those other routines like I mentioned in an earlier post.

david

Hello David,

I did what you suggested, and now when I click on a column, I get the following error page:

"The GridView 'GridView2' fired event Sorting which wasn't handled."

My code (after being edited):

Public Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            'Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=servername;Initial Catalog=dbName;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                'sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                'sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                'sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "' ", sqlConn)
                
                'This will read the rows in the database.
                'sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                'If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                'GridView2.DataSource = sqlReader
                'GridView2.DataBind()
                    
                'sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
 
                
                'GridView2.DataSource = sqlDataTable
                GridView2.DataBind()
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                'End If
                
                'This is closing the connection to the database once we have finished with it.
                'sqlConn.Close()
                
              
            End If
        End Sub

I create my GridView as thus:

<asp:GridView ID="GridView2" runat="server" Height="143px" AllowSorting="true"

I just cant understand what could be going wrong??

I really appreciate your help but do you know anything else that could poss be wrong?

Thanks,

Dan

Holy cow.
I must be totally sleep deprived.

You were talking about ASP.NET and I was testing with VB.NET. I believe you need to put back those routines for gvSorting and SortDirection.

This is the VB forum. Right? Not the ASP.NET forum. You can see my source of confusion.

Re-hook those routines and give it a shot.

Sorry, my bad.

Hi David,

Sorry for the confusion caused.

I made the changes you suggested and am now getting the following error:

"DataTable must be set prior to using DataView."

The error points to this line:

dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)

My code is now as follows:

Public Sub Button3_Click(ByVal sender As Object, ByVal e As EventArgs) Handles Button3.Click
            
            Dim sqlConn As New SqlConnection
            Dim sqlCmd As New SqlClient.SqlCommand
            'Dim sqlReader As SqlDataReader
            Dim sqlAdapter As New SqlDataAdapter
            Dim sqlDataTable As New DataTable
                        
            'If no values are supplied in the textbox, throw an error message.
            If TextBox2.Text = "" Then
                MsgBox("A centre code needs to be provided...")
            End If
            
            If TextBox2.Text <> "" Then
            
                'Telling the system the location of the database.
                sqlConn.ConnectionString = "server=serverName;Initial Catalog=dbName;Trusted_Connection=yes"

                'Here we are opening the connection to the database.
                'sqlConn.Open()
                
                'This is to say that sqlCmd is a stored procedure.
                'sqlCmd.CommandType = System.Data.CommandType.StoredProcedure
                        
                'This is creating the command to execute the stored procedure based on the information given in the connection string.
                'sqlCmd = sqlConn.CreateCommand

                'The command is triggered to execute the stored procedure which grabs all information for the specific centre.
                sqlAdapter.SelectCommand = New SqlCommand("exec ProcName'" & TextBox2.Text & "' ", sqlConn)
                
                'This will read the rows in the database.
                'sqlReader = sqlCmd.ExecuteReader()
                
                'If there are rows of data that match are criteria
                'If (sqlReader.HasRows) Then

                    'The rows of data are grabbed for the specific centre from the database using the data reader.
                'GridView2.DataSource = sqlReader
                'GridView2.DataBind()
                    
                'sqlAdapter = New SqlDataAdapter(sqlCmd)
                    sqlAdapter.Fill(sqlDataTable)
                    
                GridView2.DataSource = sqlDataTable
 
                
                'GridView2.DataSource = sqlDataTable
                GridView2.DataBind()
                    
                Else
                    MsgBox("The centre code provided does not exist...")
                'End If
                
                'This is closing the connection to the database once we have finished with it.
                'sqlConn.Close()
                
              
            End If
        End Sub
                           
        
        


        Public Sub gvSorting_Sorting(ByVal sender As Object, ByVal e As GridViewSortEventArgs)
               
            Dim dt = TryCast(Session("TaskTable"), DataTable)
            Dim dtSortView As New DataTable
            dtSortView = GridView2.DataSource
              
            If dtSortView Is Nothing Then
                Dim dvSortedView As New DataView(dtSortView)
                
                dvSortedView.Sort = e.SortExpression + " " + getSortDirectionString(e.SortDirection)
                               
                GridView2.DataSource = dvSortedView
                GridView2.DataBind()
            End If
        End Sub
                   
        Public Function getSortDirectionString(ByVal SortDirection As SortDirection) As String
            
            Dim newSortDirection As String = String.Empty
                  
            If (SortDirection = SortDirection.Ascending) Then
                newSortDirection = "ASC"
            Else
                newSortDirection = "DESC"
                'Return newSortDirection
            End If
            
            Return newSortDirection
            
        End Function

Any more advice at all?

Many thanks,

Dan

In the section of code that loads the datatable to the grid, I had to store the datatable in a session variable.

VB

sqlAdapter.Fill(sqlDataTable)

' Here is where I'm storing the data in a session variable
If Not (Session("TaskTable") Is Nothing) Then
    Session.Remove("TaskTable")
End If

Session("TaskTable") = sqlDataTable

GridView2.DataSource = sqlDataTable

Here's the code for the "Sorting" event of the grid.

VB

    Protected Sub GridView2_Sorting(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.GridViewSortEventArgs) Handles GridView2.Sorting
        Dim dt As DataTable

        ' Now I'm pulling the data from the session variable out.
        dt = TryCast(Session("TaskTable"), DataTable)

        Dim dv As New DataView(dt)

' I have to store the Sort Direction in the Viewstate because the Grid 
' does not automatically remember the change of the sort direction.

        If ViewState("SortDirection") Is Nothing Then
            ViewState("SortDirection") = "ASC"
        End If

        If ViewState("SortDirection") = "ASC" Then
            dv.Sort = e.SortExpression & " " & "DESC"
            ViewState("SortDirection") = "DESC"
        Else
            dv.Sort = e.SortExpression & " " & "ASC"
            ViewState("SortDirection") = "ASC"
        End If

        GridView2.DataSource = dv
        GridView2.DataBind()
    End Sub

Hopefully this will work for you, but I don't know if I would use this type of processing for large datatables and many users. Storing the datatable in a session variable take server memory and each person would have a copy of the datatable.

You could build a more sophisticated "GetData" routine and have the stored procedure return the data sorted for you, then re-bind it to the Gridview.

Hope this works for you.
Thumbs Up | :thumbsup:

Hello Shameel,

Thank you, how would I do that?

Thanks,

Dan

Follow David Mujica's instructions.

I did do a search but cannot find any sample. I am looking for a sample like this.
1. Search c: for say *.pdf files.
2. Show files in a grid with name of file, size, path.
3. Then I can delete all or just choose the files I want to delete.

Break the problem down into smaller problems:

1: get a list of files: http://www.developerfusion.com/code/3681/list-files-in-a-directory/[^]

2: Select the file you want in the list or datagrid (google will help you)

3: Perform the delete file on the selected record. (again google will do most of this for you).

I don't speak Idiot - please talk slowly and clearly

'This space for rent'

Driven to the arms of Heineken by the wife

You're never going to find an app that does all of this. You will however find examples for each of those steps. It's up to you to write the code to stitch all of those steps together in a single app.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Hello,

you can do something like this and then using a foreach loop, or list them on datagird.

Dim strFileSize As String = ""
        Dim di As New IO.DirectoryInfo("C:\path")
        Dim aryFi As IO.FileInfo() = di.GetFiles("*.pdf")
        Dim fi As IO.FileInfo

        For Each fi In aryFi
            strFileSize = (Math.Round(fi.Length / 1024)).ToString()
            
            'yourdatagridcell("File Name: {0}", fi.Name)
            'yourdatagridcell("File Full Name: {0}", fi.FullName)
            'yourdatagridcell("File Size (KB): {0}", strFileSize)
            'yourdatagridcell("File Extension: {0}", fi.Extension)
            'yourdatagridcell("Last Accessed: {0}", fi.LastAccessTime)
            'yourdatagridcell("Read Only: {0}", (fi.Attributes.ReadOnly = True).ToString)
        Next

Regards

Carmelo La Monica

Thank you

I am using this code to get files, but I can not get it to search for all files on C: drive.

How can I get it to show all files Like *.pdf of the dirve in the list box

Imports System.IO
Imports System
Imports System.Drawing
Imports System.Windows.Forms

Public Class SearchAvi

    Private Sub BtnGetAviFiles_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnGetAviFiles.Click

        Dim dir1 As New IO.DirectoryInfo("c:")
        Dim dir2 As IO.FileInfo() = dir1.GetFiles()
        Dim files1 As IO.FileInfo

        For Each files1 In dir2
            FrmListBox.Items.Add(files1)
        Next

    End Sub

You also have search all SUBDIRECTORIES of a given Startpoint.

You should write a function getting all files and directories, and then you can call this function recursive (calling it for each subdirectory with an new startdir).

I cannot remember: What did I before google?

I suggest using a LIFO stack (Stack collection in .NET) in stead of recursing. I've done this a couple of times now and the basic algorithm works like:

Push start directory onto stack
loop while stack is non-empty
pop last element off the stack (directory)
do action on element (here: find all *.pdf files)
list all subdirectories and for each push full subdirectory path on the stack
end loop

I have an application that has a DBGrid on a dialog. When I view certain data in the grid, sometimes data is not displayed, but only when the application is installed on a Windows 7 machine. The exact same program works reliably on any XP machine. The grid has its "DataSource" property set to a Data ActiveX control. I know the data is read properly because i can loop through the data with a "while not eof" loop and see the data. I can also open the data in MS Access and use the exact SQL statement I am using to read the data for the DBGrid and the proper data is displayed.

Does anyone have any ideas? I have other applications that utilize these controls and I am afraid to upgrade any of my office PC's to Windows 7 becuse of this problem.

Thank you

Have you tried running the program in XP Compatibility mode under Windows 7?

Silence is golden... but duct tape is silver!!

Booger Mobile - My bright green 1964 Ford Falcon - check out the blog here!! | If you feel generous - make a donation to Camp Quality!!

Why are you not asking the manufacturer of the control this question??

They wrote it, they tested it, they support it. We didn't.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak

Is your Windows 7 machine 64-bit? I believe 32-bit ActiveX controls will not work in 64-bit applications.

True, but it won't cause an problem like this. He'll get some kind of "invalid image" error and the app won't even run.

A guide to posting questions on CodeProject[^]

Dave Kreskowiak