-
Aug 19th, 2013, 04:45 AM
#1
Thread Starter
New Member
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
-
Aug 19th, 2013, 05:21 AM
#2
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.
-
Aug 19th, 2013, 05:28 AM
#3
Thread Starter
New Member
Re: Connect to sql using oledb
i have it set as Dim con As System.Data.OleDb.OleDbConnection
-
Aug 19th, 2013, 05:29 AM
#4
Thread Starter
New Member
Re: Connect to sql using oledb
ok thanks i'll make some changes and see where i end up.
-
Aug 19th, 2013, 06:20 AM
#5
Thread Starter
New Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|