Results 1 to 14 of 14

Thread: [RESOLVED] VB - Connection string to oracle and feed DataGridview1 based on SQL

  1. #1

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Resolved [RESOLVED] VB - Connection string to oracle and feed DataGridview1 based on SQL

    Hi all

    I'm new to Visual Studio but i have a few little knowledge on VB6.

    Here is the problem i'm facing and i don't know what to do after soo many different attempt.

    In Server explorer, i'm able to connect to an Oracle database different ways, including like below:
    Name:  2021-07-28_12-23-07.jpg
Views: 868
Size:  104.5 KB

    So far, so good.

    But my goal is to create a VB form with a button and a DataGridView1. Then, i need to connect to the Oracle database, and run an SQL query where the result will show in the DataGridView1.

    The last attempt among many others since few weeks now, i tried:
    Code:
    Imports System.Data
    Imports Oracle.ManagedDataAccess.Client
    Imports Oracle.ManagedDataAccess.Types
    Then below code:
    Code:
    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim oradb As String = "Data Source=laheotv39:1521/PT334;User Id=xxxxxxxxxxx;Password=xxxxxxxxxxxxxx;"
                Dim conn As New OracleConnection(oradb)
                conn.Open()
            Dim cmd As New OracleCommand With {
                .Connection = conn,
                .CommandText = "select NAME from carrier_t where carr_cd = 'Dycom'",
                .CommandType = CommandType.Text
            }
            Dim dr As OracleDataReader = cmd.ExecuteReader()
            If dr.Read() Then
                    Label1.Text = dr.Item("NAME")
                Else
                    Label1.Text = "NAME not found"
                End If
                conn.Dispose()
    
        End Sub
    But with this code, i have error:
    System.TypeLoadException: 'Could not load type 'System.Security.Principal.WindowsImpersonationContext' from assembly 'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.'

    So i'm assuming that i'm not doing it correctly for my connection.
    Why would i be able to connect to Oracle on server explorer but not able to connect and pull data to feed a DataGridView?
    Do i need extra app or drivers to be downloaded so that it works?

    How can i make it work? I really need to have some guidance cause after all the test i tried, i'm more lost then ever right now.

    Thanks for your help.

  2. #2
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Try using this NuGet package for .NET Framework, for .NET Core this package.

    Also get Oracle SQL-Developer, try your connection there.

  3. #3

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    This is very good. I'm now able to feed my lable1 using below code.
    Code:
      Dim oradb As String = "Data Source=laheotv39:1521/PT334;User Id=xxxxxxxxxxx;Password=xxxxxxxxxxxxxx;"
                Dim conn As New OracleConnection(oradb)
                conn.Open()
            Dim cmd As New OracleCommand With {
                .Connection = conn,
                .CommandText = "select NAME from carrier_t where carr_cd = 'Dycom'",
                .CommandType = CommandType.Text
            }
            Dim dr As OracleDataReader = cmd.ExecuteReader()
            If dr.Read() Then
                    Label1.Text = dr.Item("NAME")
                Else
                    Label1.Text = "NAME not found"
                End If
                conn.Dispose()

    Now how can i feed my DataGridView1.

    I've tried with below but did not work:
    Code:
    DataGridView1.DataSource = dr.Item("NAME")
    Also if my query looks like:
    Code:
    .CommandText = "select * from carrier_t",
    How would i see the full table result in the DataGridView1?
    Last edited by Wilder1234; Jul 29th, 2021 at 04:59 AM.

  4. #4
    Karen Payne MVP kareninstructor's Avatar
    Join Date
    Jun 2008
    Location
    Oregon
    Posts
    6,684

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Take a look at the following C# project.

    VB.NET is here

  5. #5

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Thank you so much. I will try it Monday and let you know if i have any questions.

  6. #6
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Karen Payne shows you how to get a DataTable. That, you can bind to your DataGridView

  7. #7

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Thank's Paul. I look forward to trying it out on Monday. I will use the VB.Net version.

  8. #8
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Quote Originally Posted by kareninstructor View Post
    Take a look at the following C# project.

    VB.NET is here
    That could be accomplished more simply without the Async stuff...

  9. #9

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    .Paul.
    How do you suggest I do this exactly?

  10. #10
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Ok. What do you want in Lable1? Just the 'Name' from the first row, or changing depending on which row is selected?

  11. #11
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Code:
    Dim oradb As String = "Data Source=laheotv39:1521/PT334;User Id=xxxxxxxxxxx;Password=xxxxxxxxxxxxxx;"
        Dim conn As New OracleConnection(oradb)
        conn.Open()
    Dim cmd As New OracleCommand With {
        .Connection = conn,
        .CommandText = "select NAME from carrier_t where carr_cd = 'Dycom'",
        .CommandType = CommandType.Text
    }
    Dim dt As New DataTable
    dt.Load(cmd.ExecuteReader())
    
    DataGridView1.DataSource = dt
    Label1.DataBindings.Add("Text", dt, "NAME")
    
    conn.Dispose()

  12. #12

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    I will try this and let you know.

    If it's only to use DataGridView1.DataSource = dt, that look pretty straight forward.

    Thanks .Paul.
    Last edited by Wilder1234; Aug 1st, 2021 at 07:46 AM.

  13. #13
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    25,464

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    Yeah, load dt as I showed you, then set your dgv datasource.

  14. #14

    Thread Starter
    Junior Member
    Join Date
    Jul 2021
    Posts
    23

    Re: VB - Connection string to oracle and feed DataGridview1 based on SQL

    I was too curious and i've tried it a few minutes ago. I confirm that this is working great with:
    Code:
    DataGridView1.DataSource = dt
    Thanks Karen Payne and Paul for your help.

Posting Permissions

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



Click Here to Expand Forum to Full Width