Repost: Error message

  • Thread starter Thread starter Carolyn
  • Start date Start date
C

Carolyn

I've got a program that collects info from an Access. I'm trying to use the
following select command to select all the information from the "Families"
table and store it in a dataset, plus join the Surname and FirstName fields
to create a new field, FamilyName:

selFamilies.CommandText = "SELECT Families.*, Families.Surname + ', ' +
Families.FirstName AS FamilyName FROM Families ORDER BY Surname, FirstName"

This has been working perfectly a number of times until recently, and now
when I try to run the program, it stops on this line:

adpFamilies.Fill(datSPARK, "Families")

and gives me this error message:

"An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: Invalid attempt to NextResult when reader is
closed."

I'm new to the whole database thing, and this message is Greek to me. If I
use this simple select command instead:

selFamilies.CommandText = "SELECT * FROM Families ORDER BY Surname,
FirstName"

the program doesn't give me the error.

I would really appreciate any help anyone can give me.

Thanks
Carolyn
 
Hi,

Try this.

selFamilies.CommandText = "SELECT Families.*, (Families.Surname + ', ' +
Families.FirstName) AS FamilyName FROM Families ORDER BY Surname, FirstName

Ken
 
Try this

Try

selFamilies.CommandText ="SELECT Families.Surname, Families.Surname FROM
People ORDER BY Families.Surname, Families.Surname"

Catch ex as Exception

MessageBox.Show(ex.ToString())

End try

Hopefully this amended Commandtext will do the trick, if not at least you
will get an error message with a bit more detail.


Regards OHM
 
Carolyn said:
I've got a program that collects info from an Access. I'm trying to
use the following select command to select all the information from
the "Families" table and store it in a dataset, plus join the Surname
and FirstName fields to create a new field, FamilyName:

selFamilies.CommandText = "SELECT Families.*, Families.Surname + ', '
+ Families.FirstName AS FamilyName FROM Families ORDER BY Surname,
FirstName"
[...]
Click to expand...

I read the whole message and I think the question is not specific to VB.NET.
I think you should turn to microsoft.public.dotnet.framework.adonet
 
Try this.
selFamilies.CommandText = "SELECT Families.*, (Families.Surname + ', ' +
Families.FirstName) AS FamilyName FROM Families ORDER BY Surname, FirstName
Click to expand...

This returns the same error message.
 
Try
selFamilies.CommandText ="SELECT Families.Surname, Families.Surname FROM
People ORDER BY Families.Surname, Families.Surname"

Catch ex as Exception

MessageBox.Show(ex.ToString())

End try

Hopefully this amended Commandtext will do the trick, if not at least you
will get an error message with a bit more detail.
Click to expand...

Sorry, I don't understand what your select statement was supposed to
accomplish. If I use that statement, no errors are encountered--the adapter
is not having any trouble accessing the FirstName and Surname columns. But
I tried the Catch and got this "explanation":

System.InvalidOperationException: Invalid attempt to NextResult when reader
is closed.
at System.Data.OleDb.OleDbDataReader.NextResult()
at System.Data.Common.DbDataAdapter.FillNextResult(IDataReader dataReader)
at System.Data.Common.DbDataAdapter.FillFromReader(Object data, String
srcTable, IDataReader dataReader, Int32 startRecord, Int32 maxRecords,
DataColumn parentChapterColumn, Object parentChapterValue)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable,
IDataReader dataReader, Int32 startRecord, Int32 maxRecords)
at System.Data.Common.DbDataAdapter.Fill(Object data, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord,
Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior
behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at SPARK.frmFamilyInfo.GetFamilies() in C:\Documents and Settings\Owner\My
Documents\Visual Studio Projects\SPARK\Info Boxes\Family Information.vb:line
1511

Line 1511 is the statement "adpFamilies.Fill(datSPARK, "Families")"
 
Just another note that may be of help. I tried setting the CommandText
properties in the form design with my original select query, then
right-clicked the data adapter and went to Preview Data. When I fill the
dataset with the selFamilies query, it returns the correct information.
However, when I run the program without readjusting the query in code, I
still encounter the error message. It is obviously gathering the correct
data from the database; is it trying to do something else with the dataset?
 
Hi,

This works with the northwind database. You should check the field
names capitalization counts.
Dim strConn As String

Dim conn As SqlConnection

Dim drCustomer As SqlDataReader

Dim cmd As SqlCommand

strConn = "Server = " + Environment.MachineName + "\VSdotNet;"

strConn += "Database = NorthWind;"

strConn += "Integrated Security = SSPI;"

conn = New SqlConnection(strConn)

cmd = New SqlCommand("Select Customers.*, (Customers.ContactTitle + ', ' +
Customers.ContactName) as FullName from Customers Order by ContactTitle,
ContactName", conn)

conn.Open()

drCustomer = cmd.ExecuteReader

Do While drCustomer.Read

Debug.WriteLine(drCustomer("FullName"))

Loop

conn.Close()

Ken

-------------------------------
 
OK,

Well from the diagnostic output, it would appear that the reader has somehow
decided that the connection was closed ( appears ), check the connection
status after the MessageBox.Show statement to determine if that is the case.

If not, it may be that there is some corruption in the data which is causing
the reader to fail. Try creating a new table with say three records in it
which you have manually created and see if the same thing happens.

Failing that you could post your code and DB ( with dummy data ) to this
newsgroup and we can try and help.

OHM
 
OK,
Well from the diagnostic output, it would appear that the reader has somehow
decided that the connection was closed ( appears ), check the connection
status after the MessageBox.Show statement to determine if that is the
Click to expand...
case.

Thanks a lot. This seems to be the case. Now can you tell me why/how the
connection is being closed?
 
Did you try my suggestions?

///
If not, it may be that there is some corruption in the data which is causing
the reader to fail. Try creating a new table with say three records in it
which you have manually created and see if the same thing happens.

Failing that you could post your code and DB ( with dummy data ) to this
newsgroup and we can try and help.
\\\

OHM
 
I think I may have fixed the problem, but I'm still a little confused as to
what caused it. In preparing the project to post, I stripped it down to
just the one form causing the problem, then deleted all the form's code
except what would be necessary for you to examine. In doing so, I found
that the programme works fine if I leave out the SaveInfo subroutine, which
includes an Update call. I may need to start a new post to get some help on
this updating procedure if I can't figure it out. Thanks so much for your
help.

Carolyn
 
Back
Top