ADO .NET with VS2005

• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  Can anyone give a good explantion of how this works(with examples) for simple connections, using SQL statements, etc. - or direct me to a good web site?  I've been reading for awhile now and have only managed to get a headache.  Recordsets in VB6 came so easy to me - everything was straight forward - set a connection, give a query, execute.  The idea of a DataAdapter and a DataReader are foreign to me. 
  
  The most I have been able to do is the below code that reads information from a XLS file.  However, if I try to enter the SQL statement to the DataAdapter anywhere other than during declaration I get an error.  How do you program dynamically if you have to hard code a statement in and declare a new DataAdapter for a different statement?  Also, what if I don't want to read, but delete, modify, etc.?  The idea of filling a data set when I want to delete records seems bizare. 
  
  
  Anyway, any pointers are welcome.  I just can't seem to find a good reference for ADO .NET 2.0 that covers all the basics. 
  
  Thanks,
  -Tril
  
  Dim xlsConnection As New System.Data.OleDb.OleDbConnection
  Dim xlsDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [JOBS$]", xlsConnection)
  Dim xlsDataSet As DataSet = New DataSet
  Dim xlsDataRow As DataRow
  
  xlsConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=F:\Brian\WIP\BrianWIPLIST.xls;Extended Properties=Excel 8.0;HDR=Yes;IMEX=1"
  xlsDataAdapter.Fill(xlsDataSet)
  
  For Each xlsDataRow In xlsDataSet.Tables(0).Rows
      If xlsDataRow("JOB #").ToString = String.Empty Then
      Else
          If IsNumeric(xlsDataRow("JOB #") Then
              msgbox(xlsDataRow("JOB #"))
          End If
      End If
  Next
  xlsConnection.Close()
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by shazelton

  Simon Hazelton

  Southampton, United Kingdom

  Joined 15 years ago

  I'd suggest looking for Microsoft.Application.Blocks.Data.  This free component from Microsoft comes with some really good examples of how to use a data layer within your application.

  also check out the quickstarts in the .net SDK

  hope this helps

  Report abuse
• • Reply

  14 years ago

  by bharra

  Bharathi

  Bangalore, India, India

  Joined 15 years ago

  Hi,
  
  Try these links: http://www.vkinfotek.com/data.html (ADO.Net object model)
                          http://www.vkinfotek.com/object.html ( Retrieving data from database using DataReader)
  If you learn ADO.Net object model in .Net, you can easily develop the application.
  
  Regards
  Bhar
   
  
  
  
  
  
  
  
  
  
  

  Report abuse
• • Reply

  14 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 years ago

  Thanks for the responses.  I am swamped at work right now, so learning new things is on the backburner - but as soon as I get a break I will read up on those sites.  They look promising.  Thanks again.
  
  EDIT => Ok, I had a chance to look over the website - and here is where I keep running into a wall.  It seems that I must be missing something simple because the description for commands claim to allow exactly what I'm trying to do.
  
  Anyway, lets start with a simple problem first.  I can't get the DataAdapter to function unless I declare a new one and set it's properties all at the same time(which makes it impossible to code effectively).  For example:
  
  Working code
  Dim xlsDataAdapter As New System.Data.OleDb.OleDbDataAdapter("SELECT * FROM [JOBS$]", xlsConnection)
  
  Broken Code
  Dim xlsDataAdapter As New System.Data.OleDb.OleDbDataAdapter
  xlsDataAdapter.SelectCommand.CommandText = "SELECT * FROM [JOBS$]"
  xlsDataAdapter.SelectCommand.Connection = xlsConnection
  
  In the broken code, I get an error on the 2nd line "Object reference not set to an instance of an object."
  
  The problem with this is I can't declare a variable more than once in a piece of code, so I can't have multiple(or variable) commands to a data source unless I hard code them in.  That website explains what the DataAdapter can do, but does not supply syntax for using it.  What I need to know, is how do you declare ONE adapter and ONE connection 'variable', but be able to clear its contents and reuse.  That way the code is clean, but the function is versatile.
  
  Thanks for any input.
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  

  Report abuse