Results 1 to 5 of 5

Thread: Connect to sql using oledb

  1. #1

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    4

    Connect to sql using oledb

    Hi

    I'm trying to fill a datagridview with a filtered dataset/view based on a text box selection.

    I have found this code which i've added to a button click:
    Code:
    Dim cnnStr As String
            Dim sql As String
    
            'open connection
            cnnStr = "Provider=SQLOLEDB;Data Source=myServer;User ID=myUser;Password=myPassword; Initial Catalog=myDatabase
            sql = "Select JobStartDate, Job, StockCode, StockDescription, Customer, CustomerName, QtyToMake, QtyManufactured " & _
                   "from WipMaster " & _
                   "where Complete = 'N' and StockCode between '9000000' and '9999999' " & _
                   "order by JobStartDate"
    
            con.ConnectionString = cnnStr
            con.Open()
    
            Dim dataAdapter As New OleDbDataAdapter(sql, con)
            Dim ds As New DataSet()
            Dim dsView As New DataView
            Dim bs As New BindingSource()
    
            dataAdapter.Fill(ds, "WipMaster")
            con.Close()
    
            dsView = ds.Tables(0).DefaultView
            bs.DataSource = dsView
            bs.Filter = " Job LIKE '" & Me.TextBoxJob.Text & "*'"
    
            dgv_WipMaster.DataSource = bs
            con.Close()
    i am getting the following error :
    stringSystem.NullReferenceException was unhandled
    Message=Object reference not set to an instance of an object.

    I also have a datasource set up. Can i use that in my code instead of defining the connection.
    this is my first VS effort to connect to a db so laymans terms please.

    Thanks

  2. #2
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,336

    Re: Connect to sql using oledb

    Have you actually assigned anything to the 'con' variable? You can't set the ConnectionString of a connection that doesn't exist.

    By the way, unless you need to be able to switch to some other OLE DB data source later, don't use OleDb for SQL Server. Use SqlClient, which is designed specifically for SQL Server.

    Finally, get rid of any reference to a DataView. It's pointless. Just bind the DataTable. Also, if all you're using is one DataTable, just create one DataTable and don't use a DataSet at all. Create a DataTable, Fill it, bind it to the BindingSource.

  3. #3

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    4

    Re: Connect to sql using oledb

    i have it set as Dim con As System.Data.OleDb.OleDbConnection

  4. #4

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    4

    Re: Connect to sql using oledb

    ok thanks i'll make some changes and see where i end up.

  5. #5

    Thread Starter
    New Member
    Join Date
    Aug 2013
    Posts
    4

    Re: Connect to sql using oledb

    sorted. i found this series extremely useful/easy...

    http://www.youtube.com/watch?v=qUesXr6rjkQ

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