PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197

PHP User Warning: fetch_template() calls should be replaced by the vB_Template class. Template name: bbcode_highlight in ..../includes/functions.php on line 4197
[RESOLVED] VS 2015 loading Sql data to Grid is this the right way?-VBForums
Results 1 to 13 of 13

Thread: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

  1. #1

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Resolved [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    hey,
    im loading some data to the grid but i dont know if this is the correct way.
    Code:
            Dim Records As New DataTable
            Dim sql As String = "select * from customers"
            Try
    
                Cn.Open()
    
                Cmd = New SqlCommand(sql, Cn)
                Cmd.Connection = Cn
    
                Dr = Cmd.ExecuteReader()
                Records.Load(Dr)
    
                Dr.Close()
                Cn.Close()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            End Try
            Return Records
    and if so
    why do the headers names (in the sql table ) also loads in the grid?
    is there a way to load only the data?
    tnx in advanced
    salsa

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: VS 2015 loading Sql data to Grid is this the right way?

    Firstly, I would suggest that you use Using blocks to create and destroy short-lived, disposable objects. In this case, that means the connection and data reader in particular, e.g.
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Using connection As New SqlConnection(connectionString),
    4.       command As New SqlCommand(sql, connection)
    5.     connection.Open()
    6.  
    7.     Using reader = command.ExecuteReader()
    8.         table.Load(reader)
    9.     End Using
    10. End Using
    That guarantees that those objects will be disposed even if an exception is thrown. In your code, if ExecuteReader threw an exception then your connection would not be closed. If you're going to close/dispose things explicitly then you need to do so in a Finally block, to ensure that it is done even if an exception is thrown.

    As for the question, it is generally preferable to populate a DataTable and bind that to a BindingSource, then bind that to the DataGridView. If you want to display data only, i.e. not edit it and save those changes, then populating the DataTable using a data reader is a good option. If you want to edit the data and save changes back tot he database then you should use a data adapter, because you can call Fill to retrieve and Update to save using the same adapter.

    As for the column names, I assume that you mean that they are being displayed in the headers in the grid. That's normal behaviour. If you don't want those column headers displayed then the grid has a property to control that. You can look for yourself but I think it's ShowColumnHeaders or something like that and it is set to True by default.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: VS 2015 loading Sql data to Grid is this the right way?

    Dim table As New DataTable

    Using connection As New SqlConnection(connectionString),
    command As New SqlCommand(sql, connection)
    connection.Open()

    Using reader = command.ExecuteReader()
    table.Load(reader)
    End Using
    End Using
    wow,i didnt know that it dispose it self this way!!tnk you

    i want to update insert and delete so i will use data adapter as you said.

    for the column names yes its displaying excatly like in the sql table.
    i want to create new names in the grid and populate only the data from sql.

    BTW
    in vb 6 i used record set and rs.open
    now i know it not the same
    but is it the same method?
    E.X
    in vb 6 i use to check if there is data in the table so i used if not rs.eof
    or if there is more then 1 record if rs!name > then ...

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: VS 2015 loading Sql data to Grid is this the right way?

    If you bind a DataTable then the ColumnName of each DataColumn is going to be displayed in the column headers of the grid by default. You could use aliases for each column in your query and then those aliases would be displayed instead of the database column names. Altermatively, you can set the HeaderText of each grid column explicitly, either after binding or by adding the columns in the designer first.

    A VB6 Recordset performs the same role as a number of ADO.NET types, including a DbConnection and a DataSet. One of the biggest differences is that ADO.NET works in a disconnected state, i.e. you connect and retrieve data into a local cache and then disconnect, make your edits while disconnected and then reconnect to save the changes. With ADO, you have a live connection to the database so any changes you make to a Recordset are reflected in the database immediately. There are advantages and disadvantages to each approach but, clearly, Microsoft consider the disconnected model to be the better option nowadays.

    If you use a data reader then the HasRows property can tell you whether there's data or not. If you use a data adapter, the Fill method returns an Integer that represents the number of rows retrieved. Once you have a populated DataTable, the Rows.Count property will tell you whether it contains any records.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  5. #5
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: VS 2015 loading Sql data to Grid is this the right way?

    If you bind a DataTable then the ColumnName of each DataColumn is going to be displayed in the column headers of the grid by default. You could use aliases for each column in your query and then those aliases would be displayed instead of the database column names. Altermatively, you can set the HeaderText of each grid column explicitly, either after binding or by adding the columns in the designer first.

    A VB6 Recordset performs the same role as a number of ADO.NET types, including a DbConnection and a DataSet. One of the biggest differences is that ADO.NET works in a disconnected state, i.e. you connect and retrieve data into a local cache and then disconnect, make your edits while disconnected and then reconnect to save the changes. With ADO, you have a live connection to the database so any changes you make to a Recordset are reflected in the database immediately. There are advantages and disadvantages to each approach but, clearly, Microsoft consider the disconnected model to be the better option nowadays.

    If you use a data reader then the HasRows property can tell you whether there's data or not. If you use a data adapter, the Fill method returns an Integer that represents the number of rows retrieved. Once you have a populated DataTable, the Rows.Count property will tell you whether it contains any records.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  6. #6

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: VS 2015 loading Sql data to Grid is this the right way?

    great info
    tnx for your help and time

  7. #7

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    sorry for bringing this up again but i dont get the Using statement and how to use it

    this is my code
    i have some statments in a moudle
    Code:
    Public Module ModConnection
        Public Dr As SqlDataReader
        Public Cmd As New SqlCommand
        Public Cn As New SqlConnection("Data Source=DESKTOP-N2KHSU6\SQLEXPRESS;Initial Catalog=Salon;Integrated Security=True")
        Public Ds As New DataSet
        Public DataAdp As New SqlDataAdapter
    End Module
    this is the code that i have now when i want to load data to the grid
    Code:
     Dim Records As New DataTable
            Dim sql As String = "select * from customers"
            Try
    
                Cn.Open()
    
                Cmd = New SqlCommand(sql, Cn)
                Cmd.Connection = Cn
    
                Dr = Cmd.ExecuteReader()
                Records.Load(Dr)
    
                Dr.Close()
                Cn.Close()
    
            Catch ex As Exception
                MessageBox.Show(ex.Message)
    
            End Try
            Return Records
    how can i combine my code and also with the statment Using like you did in post #2?
    i cant figure it out

  8. #8
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    Get rid of that module altogether. ADO.NET objects should be created and destroyed as required. That second code snippet should include the creation of the connection and command objects as I showed in post #2. Incorporating exception handling into it could look like this:
    vb.net Code:
    1. Using connection As New SqlConnection(connectionString),
    2.       command As New SqlCommand(sql, connection)
    3.     Try
    4.         connection.Open()
    5.  
    6.         Using reader = command.ExecuteReader()
    7.             Dim table As New DataTable
    8.  
    9.             table.Load(reader)
    10.  
    11.             Return table
    12.         End Using
    13.     Catch ex As Exception
    14.         '...
    15.     End Try
    16. End Using
    17.  
    18. Return Nothing
    There are various other ways you could construct it but there are various things to consider. Firstly, what do you want to do if an exception is thrown. Secondly, what type(s) of exception should you actually be catching in that scenario because it is pretty much NEVER a good thing to catch the Exception type. You should only ever be explicitly catching specific exceptions that you can reasonably expect to occur in that specific situation. You might also want to change things so that you never actually create a command object if the call to Open on the connection fails.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  9. #9

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    dont i need some public varibales to call it from any form ?
    i need instead of creating each time a new when i create a new form?
    BTW
    i didnt understand this
    Code:
    i need to create a varibale for this?
    
    Using connection As New SqlConnection(connectionString),
          command As New SqlCommand(sql, connection)
        Try
            connection.Open()
     
            Using reader = command.ExecuteReader()
                Dim table As New DataTable
     
                table.Load(reader)
     
                Return table
            End Using
        Catch ex As Exception
            '...
        End Try
    End Using
     
    Return Nothing

  10. #10
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    100,383

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    As I have now said more than once, you should be creating ADO.NET objects as and where you need them. You don't create a single connection object and then use it everywhere. Each time you need a connection you create one, use it, dispose it and discard it. That is how ADO.NET is intended to be used; that's how it was designed to be used; that's how you should use it. If you want to query the database in three different forms then you create a new connection object in each of those three forms. If you want to query the database in two different methods in the same form then you create a new connection object in each of those two methods. If you call one of those methods multiple times then you create a new connection object each time. The only reason to retain a single ADO.NET object is when it's a data adapter that is responsible for the retrieving and saving of data. Even then you could actually use a different one each time if you wanted.

    If you want to create a proper data access layer, so that you separate your data access code form your presentation code, you still treat ADO.NET the same way, i.e. create and destroy objects as required. For instance if you have a DAL method that returns a DataTable populated with data from a specific table then, inside that method, you would create a connection and a command, use them, then destroy them and discard them, creating new ones again later as required.

    As for the code I posted, yes you do need to declare variables and that code does so. It would appear that you haven't bothered to do any reading on the Using statement because the documentation states that a Using statement declares a variable. In that code, 'connection' and 'command' are variables. This:
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Using connection As New SqlConnection(connectionString),
    4.       command As New SqlCommand(sql, connection)
    5.     connection.Open()
    6.  
    7.     Using reader = command.ExecuteReader()
    8.         table.Load(reader)
    9.     End Using
    10. End Using
    is equivalent to this:
    vb.net Code:
    1. Dim table As New DataTable
    2.  
    3. Dim connection As New SqlConnection(connectionString)
    4. Dim command As New SqlCommand(sql, connection)
    5.  
    6. Try
    7.     connection.Open()
    8.  
    9.     Dim reader = command.ExecuteReader()
    10.  
    11.     Try
    12.         table.Load(reader)
    13.     Finally
    14.         reader.Close()
    15.     End Try
    16. Finally
    17.     connection.Close()
    18.     command.Dispose()
    19. End Try
    except that, in the first case, the variables declared with the Using statement also fall out of scope at the End Using lines. In the second case, the variables can be reused afterwards. The Using statement creates a scope as well as providing implicit disposal. I suggest that you use the Help menu to open the documentation and do some reading on the subject. I suggest that you always do that as a first step whenever you encounter a new concept.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  11. #11

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    i am reading everything your writing here.
    everything you wrote here is news flash for me.
    i have to separate the vb6 code from vb net code in my head, i know.
    i now understand
    i appreciate your time and help
    thanks for your patience.

  12. #12
    Frenzied Member ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    1,368

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    Hi Salsa,

    I posted this a while back for somebody, see if it helps

    Code:
    Imports System.Data.OleDb
    
    
    Public Class Form3
        Private objConnection As OleDbConnection
        Private objDataAdapter As OleDbDataAdapter
        Private objDataTable As DataTable
        Private objCommand As OleDbCommand
        '//Access Database::
        Private strConnectionString As String = _
               "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=E:\Nordwind.mdb;"
        '--------------------------------------------------------
    
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            CreateTable()
            CreateIndex()
            CreateSampleData()
        End Sub
        Public Sub CreateTable()
            Dim sSQL As String
    
            Dim con As OleDbConnection = New OleDbConnection(strConnectionString)
            'here you create your Table in the Database
            sSQL = "  Create Table tbl_Zoo"
            sSQL = sSQL & "( [ZO_ID] Integer Identity"
            sSQL = sSQL & ", [ZO_Firstname] varChar(50)"
            sSQL = sSQL & ", [ZO_Surname] varChar(50) "
            sSQL = sSQL & ")"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
        Public Sub CreateIndex()
            Dim sSQL As String = Nothing
            Dim con As OleDbConnection = New OleDbConnection(strConnectionString)
            sSQL = sSQL & "Alter Table [tbl_Zoo] Add Constraint [PrimaryKey] Primary Key (ZO_ID)"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
        Public Sub CreateSampleData()
            Dim sSQL As String = Nothing
            Dim con As OleDbConnection = New OleDbConnection(strConnectionString)
            'add some Data to the Table in the Database
            sSQL = " Insert Into tbl_Zoo (ZO_Firstname,ZO_Surname)Values ('Daktari','Cheeta')"
            con.Open()
            ExecuteSQL(con, sSQL)
            con.Close()
            con = Nothing
        End Sub
    
        Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
                                        ByVal sSQL As String, _
                                        Optional ByRef ErrMessage As String = Nothing, _
                                        Optional ByVal TransAction As  _
                                        OleDb.OleDbTransaction = Nothing) As Integer
            ErrMessage = Nothing
            Try
                Dim Result As Integer = 0
                Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
                    Result = Cmd.ExecuteNonQuery
                End Using
                Return Result
            Catch ex As Exception
                ErrMessage = ex.Message
                Return 0
            End Try
        End Function
    
        Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
            objConnection = New OleDbConnection(strConnectionString)
    
            Dim sSQL As String = "Select * From tbl_Zoo "
            Dim sWhere As String = Nothing
    
            If TextBox1.Text <> Nothing Then
                sWhere &= "And (ZO_Firstname Like '" & TextBox1.Text & "%') "
            End If
            'add more search Params
            'If TextBox2.Text <> Nothing Then
            '    sWhere &= "And (ZO_Surname Like '" & TextBox2.Text & "%') "
            'End If
            If sWhere <> Nothing Then
                sWhere = "Where " & sWhere.Substring(4)
            End If
            sSQL &= sWhere & "Order by [ZO_Firstname]"
    
            objCommand = New OleDbCommand(sSQL, objConnection)
            objDataAdapter = New OleDbDataAdapter
            objDataAdapter.SelectCommand = objCommand
            objDataTable = New DataTable
            objDataAdapter.Fill(objDataTable)
    
            'use a Datagridview
            DataGridView1.DataSource = objDataTable.DefaultView
    
            'Clean up
            objDataAdapter.Dispose()
            objDataAdapter = Nothing
            objCommand.Dispose()
            objCommand = Nothing
            objConnection.Dispose()
            objConnection = Nothing
        End Sub
    End Class
    regards
    Chris
    to hunt a species to extinction is not logical !
    since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.

  13. #13

    Thread Starter
    Enjoy the moment
    Join Date
    Feb 2011
    Location
    Barrio Del pilar madrid spain
    Posts
    4,625

    Re: [RESOLVED] VS 2015 loading Sql data to Grid is this the right way?

    thank you chris

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width