[LEMarshall]

All I get in the Immediate window is:
System.__ComObject the same number of times as the of records in the table.

And NOTHING shows up in the DataGrid1

As usual I am at a complete loss as to understanding the difference between vb6 and vb.net. I never needed the .ToString() for the rs.fields in vb6
Any help is greatly appreciated.

Larry

Imports System.Data
Imports System.Data.OleDb
Imports ADODB

Public Class Form1
	Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
		' Create a connection string
		Dim DataGrid1 As New DataGrid
		Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\ecotest2\Nwind.mdb"
		Dim sql As String = "SELECT CustomerId, CompanyName, ContactName From Customers"
		' Create a Connection object and open it
		Dim conn As Connection = New Connection
		Dim connMode As Integer = ConnectModeEnum.adModeUnknown
		conn.CursorLocation = CursorLocationEnum.adUseServer
		conn.Open(ConnectionString, "", "", connMode)
		'Dim recAffected As Object
		Dim cmdType As Integer = CommandTypeEnum.adCmdText
		Dim rs As _Recordset = conn.Execute(sql)
		' Create dataset and data adpater objects
		With rs
			Do While Not rs.EOF
				Debug.Print(rs.Fields("ContactName").ToString())
				rs.MoveNext()
			Loop
		End With
		Dim ds As DataSet = New DataSet("Recordset")
		Dim da As OleDbDataAdapter = New OleDbDataAdapter
		' Call data adapter's Fill method to fill data from ADO
		' Recordset to the dataset
		da.Fill(ds, rs, "Customers")
		' Now use dataset
		DataGrid1.DataSource = ds.DefaultViewManager
	End Sub

End Class

[PsychoCoder]

You're going about this all wrong. You're using legacy libraries in VB.NET, stick with the native libraries in the .Net Framework. Take a look at this tutorial on OleDb Basics in VB.NET. OleDb is what you want to be using in .Net, not the old VB6 way of doing things

[LEMarshall]

PsychoCoder, on 28 May, 2009 - 08:30 PM, said:

You're going about this all wrong. You're using legacy libraries in VB.NET, stick with the native libraries in the .Net Framework. Take a look at this tutorial on OleDb Basics in VB.NET. OleDb is what you want to be using in .Net, not the old VB6 way of doing things

That tutorial doesn't look to much different than the SQL Basics tutorial which seemed to be full of errors. I will wipe my sample project clean and try this approach but I still don't see how it will allow me to do certain things. such as take one table which has 15 fields in it and store each field to memory variables. I also don't see how I can take a recordset from 4 related tables and fill in various textboxes, checkboxes and option buttons on a form. In VB6 it was easy to go through the recordset and store the field name to the form control. I have to do some running around now and will be back in an hour and test. I'll upload any problems I come across. thanks for your assistance so far, and I appreciate you working with me to get this down. Like I've said before, I did some VB6 programming 5 years ago and now the company wants some changes made in vb.net and despiete having read 3-4 books so far- I feel like I'm in an entirely new world.

Larry

[PsychoCoder]

What errors are in the SQL tutorial? I wrote it and compiled all the code before posting the tutorial so I know it works.

[LEMarshall]

PsychoCoder, on 29 May, 2009 - 07:50 AM, said:

What errors are in the SQL tutorial? I wrote it and compiled all the code before posting the tutorial so I know it works.

I imagine it is the same problems I am experiencing here.

app.config looks like this

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
	<configSections>
	</configSections>
	<connectionStrings>
	  <add name="OLEDBTest" connectionString="Persist Security Info=False; 
		  Data Source=C:\Ecologics\Data\Ecologics.mdb 
		  Initial Catalog=Customer;
		  Integrated Security=SSPI;
		  Trusted_Connection=TRUE;
		  Application Name=EcoTest2"
		  providerName="System.Data.OleDB" />
	</connectionStrings>
	<system.diagnostics>
		<sources>

and in the function GetRecords I get an error "An OLEDB provider was not specified in the ConnectionString. An example would be, "Provider=SQLOLEDB;" I changed it to that and still get the same error message.

   Public Shared Function GetRecords() As BindingSource
		'The value that will be passed to the Command Object (this is a query)
		Dim query As String = "SELECT * FROM ecoSystem"
		Dim cnGetRecords As New OleDbConnection([b][color=#FF0000]GetConnectionString[/color][/b]("OLEDBTest"))
		'OleDbConnection Object to use
		Dim cmdGetRecords As New OleDbCommand()

On my DataAccess class

Imports System.Data.OleDb
Imports System.Configuration

Public Class DataAccess

 

and on my form1.vb

Imports System.Data
Imports System.Data.OleDb
Imports ADODB
Imports EcoTest2.DataAccess

I added the imports ecotest2.dataacess so that the form load would work otherwise I can an error that getrecords() was not declared

	Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

		GetRecords()

	End Sub

I have no doubts it comes from my misunderstanding of the basics involved. To me VB6 was far more straight forward, but I'm starting to see some of the advantages of vb.net, just having a hard time adjusting my mindset. So much work to get done and I'm feeling like I'm going to scholl learning an entirely new language- and I needed to know it yesterday.

After I get this to work, then I want to do a more complex SQL statement and feed into a datagrid and then I want to doe a form with various other controls. I'm think I see how the tableadapter will allow me to assign each control to a field in the recordset. I imagine other things will be possible from there- I remember coding if a certain field was greater than 25 I would change the text to be in red, etc. That would be done after each record is loaded instaed of while loading the record.

But can't think of these until I get the basics working.

Larry

[LEMarshall]

I've gone so far as to change the code

   Public Shared Function GetConnectionString(ByVal conName As String) As String
		'variable to hold our connection string for returning it
		Dim strReturn As New String("")
		'' ''check to see if the user provided a connection string name
		'' ''this is for if your application has more than one connection string
		' ''If Not String.IsNullOrEmpty(conName) Then
		' ''	'a connection string name was provided
		' ''	'get the connection string by the name provided
		' ''	strReturn = ConfigurationManager.ConnectionStrings(conName).ConnectionString
		' ''Else
		' ''	'no connection string name was provided
		' ''	'get the default connection string
		' ''	strReturn = ConfigurationManager.ConnectionStrings("OLEDBTest").ConnectionString
		' ''End If
		'' ''return the connection string to the calling method
		strReturn = "Persist Security Info=False;Data Source=c:\ecologics\data\ecologics.mdb provider=System.Data.OleDb"
		Return strReturn
	End Function

and when it it gets back to the line here:

Public Shared Function GetRecords() As BindingSource
		'The value that will be passed to the Command Object (this is a query)
		Dim query As String = "SELECT * FROM ecoSystem"
		Dim cnGetRecords As New OleDbConnection([color=#FF0000]GetConnectionString[/color]("OLEDBTest"))

Immediate window shows:
? strreturn
"Persist Security Info=False;Data Source=c:\ecologics\data\ecologics.mdb provider=System.Data.OleDb"

or even the entire one in the tutorial:
? strreturn
"Persist Security Info=False;Data Source=c:\ecologics\data\ecologics.mdb;Initial Catalog=ecosystem;Integrated Security=SSPI; Trusted_Connection=TRUE; Application Name=EcoTest2 provider=System.Data.OleDb"

But I get the error message ArgumentException was unhandled

An OLE DB Provider was not specified in the ConnectionString. An example would be, 'Provider=SQLOLEDB;'.

I am at a lost. I can't seem to get any further. I'm trying to see if it may be another error for the ConnectionString that wasn't handled properly and the ArgumentException just picked the OLE DB Provider as the most convienant one.

Larry

[LEMarshall]

Found that a silly little ; was at fault but once i get beyond that error I get a series of others :

with the strReturn being forced to this:

		strReturn = "Persist Security Info=False;Data Source=c:\ecologics\data\ecologics.mdb;Initial Catalog=ecosystem;Integrated Security=SSPI; Trusted_Connection=TRUE; Application Name=EcoTest2; provider=System.Data.OleDB"

I get this error message : InvalidCastexception was unhandled. conversion from string "Error Retrieving Data" to type "Integer" is not valid. And the ex.message is "the System.Data.OLDB is not registered on the local machine"

this is in the GetRecords fucntion after it hits the .Open statement in the

   Public Shared Sub HandleConnection(ByVal conn As OleDbConnection)
		With conn
			'do a switch on the state of the connection
			Select Case .State
				Case ConnectionState.Open
					'the connection is open
					'close then re-open
					.Close()
					.Open()
					Exit Select
				Case ConnectionState.Closed
					'connection is open
					'open the connection
					.Open()
					Exit Select
	
 Public Shared Function GetRecords() As BindingSource
....other code......
HandleConnection(cnGetRecords)
....other code ....
  
   Catch ex As Exception
			MsgBox(ex.Message, "Error Retrieving Data")
			Return Nothing
		Finally
			HandleConnection(cnGetRecords)

With this other strReturn I get a different error message at same location for the same unhandled InvalidCastException :

strReturn = "Persist Security Info=False;Data Source=c:\ecologics\data\ecologics.mdb;Initial Catalog=ecosystem;Integrated Security=SSPI; Trusted_Connection=TRUE; Application Name=EcoTest2; provider=SQLOLEDB"

I get [DBNETLIB][ConnectionOpen (ParseConnectParams()).] Invalid Connection.

So if the tutorial works on other machiens, do I have to look at some kind of corruption in my install of Visual Studio or the references or components are not there or what. What the heck do I have to do to get this to work? Am I that stupid or is there something else going on here?

Again as always I appreciate any assistance.

Larry