Nested queries to populate a DataSet using OdbcDataAdapter

The class listed below uses a CSV file and OdbcDataAdapter object to populate a DataSet using a nested query BUT I keep getting an Access Violation exception when I call OdbcDataAdapter.Fill(). The message is:

"Attempted to read or write protected memory. This is often an indication that other memory is corrupt."

This is written in VS 2005 (.NET Framework 2.0).

Are nested queries in the SelectCommand() method of the OdbcDataAdapter object not valid I essentially need to execute this SQL query to populate the DataSet with different pressure/temp combinations. The query executes perfectly in Query Analyzer:

SELECT Value,Pressure,Temperature
FROM DewPointValues
WHERE Pressure IN ((SELECT MAX(Pressure)
FROM DewPointValues
WHERE Pressure <= 9.2),
(SELECT MIN(Pressure)
FROM DewPointValues
WHERE Pressure >= 9.2))
AND Temperature IN ((SELECT MAX(Temperature)
FROM DewPointValues
WHERE Temperature <= 92.3),
(SELECT MIN(Temperature)
FROM DewPointValues
WHERE Temperature >= 92.3))

How can I do this Any help would be appreciated!!!

public class DewPoint
{
protected DataSet DewPointValues = new DataSet("DewPointValues");
protected OdbcConnection DewPointValuesConn;

public DewPoint()
{
LoadDewPointValuesFile();
}

public double GetH2OContentValue(double pressure, double temperature)
{
double lookupValue = 0.0;

string query = "SELECT * FROM DewPointValues.csv WHERE Pressure IN ((SELECT MAX(Pressure) FROM DewPointValues.csv WHERE Pressure <= " + pressure + "), (SELECT MIN(Pressure) FROM DewPointValues.csv WHERE Pressure >= " + pressure + ")) AND Temperature IN ((SELECT MAX(Temperature)
FROM DewPointValues
WHERE Temperature <= " + temperature + "),
(SELECT MIN(Temperature)
FROM DewPointValues
WHERE Temperature >= " + temperature + "))";

OdbcDataAdapter da = new OdbcDataAdapter();

da.SelectCommand = new OdbcCommand(query, DewPointValuesConn);

da.Fill(DewPointValues, "Values");

DataRow[] foundRows = DewPointValues.Tables["Values"].Select();

if (foundRows.GetUpperBound(0) >= 0)
{
foreach (DataRow row in foundRows)
{
lookupValue += Convert.ToDouble(row["Value"]);
}
}

return lookupValue;
}

private void LoadDewPointValuesFile()
{
System.Reflection.Assembly myAssembly = this.GetType().Assembly;

string path = myAssembly.Location;

path = path.Substring(0, path.LastIndexOf("\\") + 1);

string ConnectionString = @"Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=" + path;

DewPointValuesConn = new OdbcConnection(ConnectionString);

DewPointValuesConn.Open();
}

Answer this question

Nested queries to populate a DataSet using OdbcDataAdapter

Pravin Pagare

The original problem posted here is a bug in the Jet engine, but I don't have any more information on a fix.

If you are running into this against SQL Server, without a Jet (i.e Access) database involved, then you are running into a different problem. The problem that started this thread can even be reproduced inside of Access itself.

Can you give more specific steps on how to reproduce the problem you are seeing If it reproduces against any table, you can give steps using a table in AdventureWorks. Are you seeing the exact error that was posted earlier in this thread, or just something similar When you post your repro steps, please copy and paste the error that you get as well.

Thanks,

Sarah
mrehpine
Thanks Sarah.

I too was able to reproduce the error utilizing ADODB (2.7/2.8) in a simple VB 6.0 app before I read your response. Bummer!!

I look forward to a possible solution...
Shah Yogesh
Has anyone come up with anything yet I'm hitting the same error message, but I'm using VS2005 and trying to created a stored procedure on my SQL Server 2005 DB using the out of the box QA functionality. I can build select statements left and right, but when I try and build an insert statement on any table, even trying to set up one variable to one column, as soon as I close the QA I get the error, and I haven't even executed anything... I'm stumped, and I'm too new to this to troubleshoot it. I tried deleting the connection to the DB and rebuilding it, but that didn't fix it... Any suggestions
Vanni Torelli

Good troubleshooting...

Best of luck

Adamus
Target Su.
Adamus:

Thanks for the reply BUT there's not any other program that would be accessing the same memory space that I can think of. The app throws the same error when I run it both in debug OR release...doesn't matter.

Any other ideas

Thx again...
Shawn Ramey

For future reference, this link has information on the sample AdventureWorks database: http://msdn2.microsoft.com/en-us/library/ms143739.aspx.

To try to reproduce what you are seeing, I created a table with the same column names as yours, although you didn't mention the data types, so I just used int for the ones named xxxID, and nvarchar(256) for the others. I could reproduce the "Unable to parse query text" error, but not the other memory error.

I can explain why the parse error is happening, but regardless, you shouldn't get the memory error. If you can reproduce this against AdventureWorks I can look into the memory error in more detail, or if you can provide a CREATE TABLE SQL statement that I can use to duplicate your exact table. I'm not sure if the table structure matters, but it would help to make sure we are doing the same thing.

Here are some tips for you regarding the parse error:

(1) INSERT statements don't allow a WHERE clause. You are inserting a new row, so there is no need to limit any rows using a WHERE clause. You are providing all new values for a row that does not exist in the table. Either you really want an UPDATE instead, or you just need to get rid of the WHERE clause.

(2) Your INSERT statement is using several variables that you haven't defined in the stored procedure. You mentioned that you added a procedure variable "@UserName", but you also have @ProjectID, @ProjectTitle, etc, that are undefined.

(3) Why are you creating a new stored procedure here Do you just want to use this INSERT statement in your application somewhere, maybe as an InsertCommand If you really want this wrapped in a stored procedure, you are going to have to define all of the variables and then pass their values into the stored procedure when it's called. It would be more direct to just call the INSERT in code. You can still parameterize it like you will have to do for the stored proc anyway. However, you may have a reason for needing the extra layer of abstraction.

If you are pasting the entire INSERT into the Query Builder window anyway, you don't really need the Query Builder. You could just paste the INSERT into the stored procedure template that is created when you selected "Add New Stored Procedure". The Query Builder just helps so you don't have to put together the SQL yourself.

Thanks,
Sarah
tonyr1977

Since Query Analyzer connects to SQL Server, you can't directly compare the behavior there to the behavior when you connect to the text file in your application. QA is using the SQL Server ODBC driver to connect to SQL, and the app is using the Microsoft Jet ODBC driver and the Text ISAM driver to connect to the text file. In general, anything that works in QA should work in System.Data.Odbc, but in this case, your app is not going against the same backend that QA is using, so it's not a valid comparison.

I was able to reproduce the behavior you are seeing, and after some investigation, I determined that this is not a problem in System.Data.Odbc or the Jet/Text ODBC driver. The same problem occurs even with System.Data.OleDb and the Jet/Text OLE DB provider, and with both ODBC and OLE DB, I could reproduce the crash in simple applications not using .NET. Furthermore, I created the same test table in Access itself and the query fails there too. I looked at all three scenarios (ODBC, OLE DB, and Access) under the debugger, and all of them are failing with identical call stacks, with the failure in msjet40, the Jet engine.

This looks like a bug in the Jet engine, or this combination of syntax is not supported for some reason(although it should fail with a better error if that were the case). Jet does support subqueries, so I don't see anything obviously unsupported.

I will forward this to the Jet team and let them post back here if they have anything further to add.

Thanks,

Sarah
Ashish757

Well, in a nutshell, all I'm trying to do is use a FormView to allow the user to insert records into the table, and tag that row with their UserID (or UserName) from the Membership records. I can pull the value using Membership.GetUser.UserName (or append ProviderKey to UserName), but due to my position on the learning curve I'm having a heck of a time passing it into the INSERT statement. I had it up and running where I was able to somehow dump it into the UserID textbox using Formview1.FindControl(UserIDTextbox), but inadvertantly deleted the Webform and can't remember how I had it set up. (make a copy, make a copy, make a copy! LOL) I tried config the Data Source with SELECT * FROM [Projects] WHERE ([UserName] = @UserName) and passing Session("UserName") as the parameter, and then setting Session("UserName") = Membership.GetUser.UserName on Page_Load, but not having much luck. I hope you can give me a straightforward way to do this, I'm trying to finish this for a school project and time is getting very short!

Thanks!

Dennis
jesc

jd,

"Attempted to read or write protected memory. This is often an indication that other memory is corrupt." is missing "...or is being used by another program."

Hope this helps,

Adamus
rainielle73

Well Sarah, I'm a little new to this, so I don't know how to utilize AdventureWorks. I have a table out on a live SQL Server 2005 table called "Projects" with 10 columns, ProjectID, ProjectTitle, ContactID, AuthorLastName, AuthorFirstName, SurfaceItem, PublicationID, ColorScheme, Theme, UserID. In Visual Studio 2005, when I right click on Stored Procedures and select Add New Stored Procedure, it opens a pane with an empty procedure template. I set my procedure variable as "@UserName nvarchar(256)" and right click to open a Query Builder. I add my Project table and set my statement as

INSERT INTO Projects
(ProjectID, ProjectTitle, ContactID, AuthorLastName, AuthorFirstName, SurfaceItem, PublicationID, ColorScheme, Theme, UserID)
VALUES (@ProjectID,@ProjectTitle,@ContactID,@AuthorLastName,@AuthorFirstName,@SurfaceItem,@PublicationID,@ColorScheme,@Theme,@UserID)
WHERE (Projects.UserID = @UserName)

I do get an error that says Unable to parse table, if I ignore that (which is probably where my problem lies) and click OK, the Query Builder closes and I get a pop up on the Procedures pane that says "Attempted to read or write protected memory. This is often an indication that other memory is corrupt."
Asen Asenov
I didn't think to try that as I didn't think that the Microsoft Text Driver utilized that param. I'm talking to a CSV file rather than a DB...
tbrauer
Actually now that I think about it, I can populate the DataSet object using a much simpler query (i.e. select * from DewPointValues.csv where pressure = 10 and temperature = 120) so I know that the security param is irrelevant.
Scott1978
This is happening on more than one PC. If in fact the SelectCommand can handle the same types of queries as QA then why is this app throwing the access violation Any takers

Thank you in advance!!!
chachu207

Well coming from a SQL background, the query is fine and SelectCommand will accept anything that works in Query Analyzer. My only suggestion, without researching it myself and spoon feeding the answer, would be to look into the authentication properties of the connection string. For example, "integrated security=true"

Adamus
Nested queries to populate a DataSet using OdbcDataAdapter

Answer this question