Data Set/ Data reader problems.

[Bahatmut]

Ok, I am writing a program where I am sadly having to mix datasets with datareaders..at least that's the aim. I finally got it to get past the fillign the data table (I Think) but now it's failignon the execution of the data reader.

[size=2][color=#0000ff]Dim[/color][/size][size=2] VHSData [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataSet

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] Findrow [/size][size=2][color=#0000ff]As[/color][/size][size=2] DataRow

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VHSTable [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataTable

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VHSQry [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "SELECT Ref, Genre FROM Videos WHERE Spare1='Video'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VHsAdapt [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbDataAdapter

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VhsConnStr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\videobarn.mdb;User ID=admin;Password="

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VHSConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(VhsConnStr)

VHsAdapt.SelectCommand = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(VHSQry, VHSConn)

VHsAdapt.FillSchema(VHSData, SchemaType.Source, "Videos")

VHsAdapt.Fill(VHSTable)

VHSConn.Close()

[/size][size=2][color=#008000]' Step 2, retreive Returned_Rents info to be processed.

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncQry [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select Ref,Return_Status,Cost FROM Returned_Loans WHERE Ref NOT = 'Import' AND NOT Return_Status = '-'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConnStr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\videobarn.mdb;User ID=admin;Password="

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(IncConnStr)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(IncQry, IncConn)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncRead [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

IncConn.Open()

IncRead = IncCmd.ExecuteReader  <-------FAILS HERE -------

[/size][size=2][color=#0000ff]While[/color][/size][size=2] IncRead.Read

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] IncRead.GetValue(1) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Findrow = VHSTable.Rows.Find(IncRead.GetString(0))

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] Findrow(1) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then

[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] Findrow(2) = "Adult" [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Label17.Text = Label17.Text + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]Else

[/color][/size][size=2]Label16.Text = Label16.Text + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2]Label18.Text = Label16.Text + Label17.Text

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While

[/color][/size][size=2]IncConn.Close()

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]Sub

End[/color][/size][size=2] [/size][size=2][color=#0000ff]Class[/color][/size]
[size=2][color=#0000ff]

[/color][/size]

The only error I get is a 'Unhandeled exception of type 'System.Data.OleDB.OleDBException' occured in system.data.dll'
and nothign else. The query for the reader appears to be in order, as does it's connection string, and command.

 

[jmcilhinney]

You've used "NOT" in two different ways in your query, so I'm betting that at least one of them is invalid. You've got "Ref NOT =" and "NOT Return_Status =", and I'm guessing that the first format is invalid. I'd actually get rid of both of them and use the "<>" (not equal) operator instead.

Two other things to note. You don't need that call to FillSchema. Set the the MissingSchemaAction of the DataAdapter to AddWithKey and that will be taken care of when you call Fill. The other thing is reagrding your exception. If you want more information on the exception then you should handle it and examine it, e.g.

Try
	IncReader = IncCmd.ExecuteReader()
Catch ex As Exception
	MessageBox.Show(ex.ToString())
End Try

 

[Bahatmut]

OK, I tried the <> in the SQL call iand it finally got past there. I edited the code as follws concerning the MissingScemaAction and I am getting a new error now once the reader tries to start finding rows.

[size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] VHSConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(VhsConnStr)

VHsAdapt.SelectCommand = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(VHSQry, VHSConn)

[/size][size=2][color=#008000]'VHsAdapt.FillSchema(VHSData, SchemaType.Source, "Videos")

[/color][/size][size=2]VHsAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey

VHsAdapt.Fill(VHSTable)

VHSConn.Close()

[/size][size=2][color=#008000]' Step 2, retreive Returned_Rents info to be processed.

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncQry [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select Ref,Return_Status,Cost FROM Returned_Loans WHERE Ref <> 'Import' AND Return_Status <> '-'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConnStr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\videobarn.mdb;User ID=admin;Password="

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(IncConnStr)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(IncQry, IncConn)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncRead [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

IncConn.Open()

IncRead = IncCmd.ExecuteReader

[/size][size=2][color=#0000ff]While[/color][/size][size=2] IncRead.Read

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] IncRead.GetValue(1) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Findrow = VHSTable.Rows.Find(IncRead.GetString(0)) <-----FAILS HERE----

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] Findrow(1) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then

[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] Findrow(2) = "Adult" [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Label17.Text = Label17.Text + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]Else

[/color][/size][size=2]Label16.Text = Label16.Text + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2]Label18.Text = Label16.Text + Label17.Text

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While

[/color][/size]

The error I am receiving at this point is 'An unhandled exception of type 'System.Data.MissingPrimaryKeyException' occurred in system.data.dll
Additional information: Table doesn't have a primary key.'

Now the database that the data is pulled from has NO KEYS IN ANY TABLE, and I CANNOT add them to the database as it will totally fark up the program that actually created and uses them. Is there a way I can get aroudn this problem, or do i have to figure out how to define a key?

 

[jmcilhinney]

The DataRowCollection.Find method that you are trying to use there does require you to provide a primary key value, so if your DataTable has no primary key then you cannot use it. You have two choices. You can either create a primary key, which you can do in your DataTable without affecting the database, or use a different method.

If the column you want to use to Find the row will definitely contain unique values, then I suggest you create a primary key. You would do that something like this:

VHSTable.PrimaryKey = New DataColumn() {X}

where X is either the index of the column you want to use as the primary key or a string containing the name of the column. Your DataTable now has a primary key but, as I said, this is local to your app only. The database is unaffected.

If the column may not contain unique values, then I suggest you use the DataTable.Select method instead. It returns an array of DataRows that match a specified conditon or conditions, e.g.

Dim myRows As DataRow() = VHSTable.Select("SomeField = SomeValue")

so it's like a query within your table, but with the "SELECT * FROM MyTable WHERE" bit removed.

 

[Bahatmut]

Ok, I added in the line as shown before, and it still fails to run, giving me the same 'Table has no key' It also says there is an error in how you told me to construct the line itself

Dim VHSConn As New OleDbConnection(VhsConnStr)
		VHsAdapt.SelectCommand = New OleDbCommand(VHSQry, VHSConn)
		'VHsAdapt.FillSchema(VHSData, SchemaType.Source, "Videos")
		VHsAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey
		VHSTable.PrimaryKey = New DataColumn("")   <-------LINE ADDED-----
		VHsAdapt.Fill(VHSTable)
		VHSConn.Close()
		' Step 2, retreive Returned_Rents info to be processed.
		Dim IncQry As String = "Select Ref,Return_Status,Cost FROM Returned_Loans WHERE Ref <> 'Import' AND Return_Status <> '-'"
		Dim IncConnStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\videobarn.mdb;User ID=admin;Password="
		Dim IncConn As New OleDbConnection(IncConnStr)
		Dim IncCmd As New OleDbCommand(IncQry, IncConn)
		Dim IncRead As OleDbDataReader

I tried it that way, as well as the following ways

VHSTable.PrimaryKey = New DataColumn() {0}
VHSTable.PrimaryKey = New DataColumn() {"Ref"}
VHSTable.PrimaryKey = New DataColumn("Ref")

None of them worked.

 

[jmcilhinney]

The PrimaryKey property is of type DataColumn() (array of DataColumn), so you have to create an array of DataColumns to assign to it:

'Get column by name.
VHSTable.PrimaryKey = New DataColumn() {VHSTable.Columns("Ref")}

'Get column by index.
 VHSTable.PrimaryKey = New DataColumn() {VHSTable.Columns(0)}

 

[Bahatmut]

Ok..almost workign

Ok, now it's coming together. The advice has helped, but now it's doign somethign odd. It compiles, and starts to run, but after 3 successful loops thorugh the reader, it fails out on the 4th run with the error
'An unhandled exception of type 'System.NullReferenceException' occurred in TotalVHSIncome.exe
Additional information: Object reference not set to an instance of an object.'

and it fails here:

[size=2]VHsAdapt.SelectCommand = [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(VHSQry, VHSConn)

[/size][size=2][color=#008000]'VHsAdapt.FillSchema(VHSData, SchemaType.Source, "Videos")

[/color][/size][size=2]VHsAdapt.MissingSchemaAction = MissingSchemaAction.AddWithKey

VHsAdapt.Fill(VHSTable)

VHSTable.PrimaryKey = [/size][size=2][color=#0000ff]New[/color][/size][size=2] DataColumn() {VHSTable.Columns("Ref")}

VHSConn.Close()

[/size][size=2][color=#008000]' Step 2, retreive Returned_Rents info to be processed.

[/color][/size][size=2][/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncQry [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Select Ref,Return_Status,Cost FROM Returned_Loans WHERE Ref <> 'Import' AND Return_Status <> '-'"

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConnStr [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]String[/color][/size][size=2] = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=z:\videobarn.mdb;User ID=admin;Password="

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncConn [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbConnection(IncConnStr)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncCmd [/size][size=2][color=#0000ff]As[/color][/size][size=2] [/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(IncQry, IncConn)

[/size][size=2][color=#0000ff]Dim[/color][/size][size=2] IncRead [/size][size=2][color=#0000ff]As[/color][/size][size=2] OleDbDataReader

IncConn.Open()

IncRead = IncCmd.ExecuteReader

[/size][size=2][color=#0000ff]While[/color][/size][size=2] IncRead.Read

CNT = CNT + 1

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] IncRead.GetValue(1) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Findrow = VHSTable.Rows.Find(IncRead.GetString(0))

[/size][size=2][color=#0000ff]If[/color][/size][size=2] [/size][size=2][color=#0000ff]Not[/color][/size][size=2] Findrow(0) [/size][size=2][color=#0000ff]Is[/color][/size][size=2] [/size][size=2][color=#0000ff]Nothing[/color][/size][size=2] [/size][size=2][color=#0000ff]Then <----FAILS HERE-----

[/color][/size][size=2][/size][size=2][color=#0000ff]If[/color][/size][size=2] Findrow(1) = "Adult" [/size][size=2][color=#0000ff]Then

[/color][/size][size=2]Label17.Text = [/size][size=2][color=#0000ff]CDbl[/color][/size][size=2](Label17.Text) + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]Else

[/color][/size][size=2]Label16.Text = [/size][size=2][color=#0000ff]CDbl[/color][/size][size=2](Label16.Text) + IncRead.GetValue(2)

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2][/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]If

[/color][/size][size=2]Label18.Text = Label16.Text + Label17.Text

[/size][size=2][color=#0000ff]End[/color][/size][size=2] [/size][size=2][color=#0000ff]While

[/color][/size][size=2]IncConn.Close()

Not sure why this is happening.
[/size]

 

[jmcilhinney]

I suggest you put the line that throws the exception inside a Try...Catch block like this:

Try
	'Code that throws exception goes here.
Catch ex As Exception
	Messagebox.Show(ex.ToString())
End Try

Note that the offending line is an If statement in your case so you will need to put the whole If block inside the Try block. When the exception is thrown you will get a message that gices you additional information, including a call stack that will show you exactly where the exception occurred. You can then go to the highest point in that call stack that is in your own code and place a break point. When you run the code again, execution will then halt at that breakpoint and you can examine your variables to see which is the culprit.