Access "unspecified error" 2147467259

[Adagio]

I often get this error when running several select sql statements in a loop

The code looks something like this

Private Shared connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=J:\Adrian\Q-rapport\Database\kvalData_test.mdb"
Private Shared command As OleDbCommand

public shared function doSomething() as list (of someClass)
dim lst as list (of someClass) = new list (of someClass)

for i as integer = 0 to 100
  dim some as new someClass
  Dim dataReader As OleDbDataReader = runSelectSql("SELECT something FROM someTable WHERE ID = " & i)
  datareader.read()
  some.someVar = datareader.item("something")
  datareader.close
  command.connection.close()
  lst.add(some)
next
return lst
end sub

private shared function runSelectSql(strSql as string) as OleDbDataReader
Dim dataReader As OleDbDataReader
Try
  command = New OleDbCommand()

  With command
    .Connection = New OleDb.OleDbConnection(connectionString)
    .Connection.Open()
    .CommandText = strSql

     dataReader = .ExecuteReader(CommandBehavior.Default)
   End With
Catch oexpData As OleDb.OleDbException
   command.Connection.Close()
    return nothing
end try

Return dataReader
end function

It does seem to favor some IDs, but not always. And there's nothing special about them in the database as far as I can tell

Does anyone have any idea on what's going on here?

 

[Adagio]

According to this page it might be because I have more than 65 connections open: http://support.microsoft.com/kb/830133

But as far as I can tell I always close the connection (both when an error happens or when done)


EDIT: Seems like it was too many connections after all, it didn't close the connection every time I told it to, but with a few code changes the problem seems to have stopped

 

[cjard]

There are so many better ways of doing what youre doing.. Infact, I think it may be a little harsh, but fair, to say that what you've written here is one of the worst ways I've ever seen, of loading a range of ID numbers into a list

Take a read of the DW2 link in my signature..

 

[Adagio]

What would you have done it instead?

 

[cjard]

Created a typed dataset, with a datatable just for this list, based on the query:

"SELECT something FROM someTable WHERE ID >= :minID and ID < :maxID ORDER BY ID"

And then called it with the relevant parameters to, in a single transaction with the DB, fill the entire list with all the relevalt IDs, and then made that DataTable the datasource of the list

Read the DW2 link, it will tell you all about it.

 

[Adagio]

Created a typed dataset, with a datatable just for this list, based on the query:

"SELECT something FROM someTable WHERE ID >= :minID and ID < :maxID ORDER BY ID"

Unfortunately the IDs are not in a row, which means the SQL would be something like this:

"SELECT something FROM someTable WHERE ID = 2 AND ID = 4 AND ID = 7 (...) AND ID = 2545

Is that what you would do in such situation?

 

[cjard]

Unfortunately the IDs are not in a row

They are in your example. I have given you code that neatly covers exactly and only what you posted. When it comes to data access and SQL, I'm good, bordering on legendary, but I cant always read minds!
You need to be more forthcoming with information, and you need to post more relevant, accurate examples if you want accurate help.

"SELECT something FROM someTable WHERE ID = 2 AND ID = 4 AND ID = 7 (...) AND ID = 2545

Such an SQL wont actually return any results in this universe, because an integer cannot simultaneously take the values 2 and 4, at the least..

However, assuming you meant to use either an OR or an IN construct, it would look something like this

Is that what you would do in such situation?

I really dont know. You havent told me enough about these ID numbers for me to return any sort of answer. Previously, I deduced that they were derived from the iterative operation of a loop. Now you have presented me with an SQL having an indeterminate number of arbitrary (from my point of view) values. I have no idea what the nubmers 2, 4, 7 and 2545 mean to you, why you picked them, when you know them (design or run time) or what values are in the range denoted by .... dots

Write a better spec if you want a targeted solution..

 

[Adagio]

However, assuming you meant to use either an OR or an IN construct, it would look something like this

Was a bit sleepy when I wrote it, you're right, I ment it to be WHERE ID = 2 OR ID = 4

I really dont know. You havent told me enough about these ID numbers for me to return any sort of answer. Previously, I deduced that they were derived from the iterative operation of a loop. Now you have presented me with an SQL having an indeterminate number of arbitrary (from my point of view) values. I have no idea what the nubmers 2, 4, 7 and 2545 mean to you, why you picked them, when you know them (design or run time) or what values are in the range denoted by .... dots

Write a better spec if you want a targeted solution..

The reason why I used the "for" loop (i = 0 to 100) in my example was because that part was not important for the actual problem. The actual ID's to search for is based on userselection on the form (then an advanced* SQL is fired to get the ID's)



*Well, advanced for me, as I don't know much about SQL

 

[cjard]

the In list operator can be used to shortcut a block of ORring:

SELECT * FROM table WHERE column IN (2,3,5,7,99,134,789)


You would use a loop, iteratively build the SQL, and run it. This kind of query is hard, or usually somewhat pointless to parameterize. I (sadly) so far havent seen a database that can accept an array as a parameter, with the hope of a query like this:

SELECT * FROM table WHERE col IN arrayParameter)


Although a workaround that we use in PL/SQL on oracle is to supply a string, comma delimited, and have a plsql function split it into a rowcursor to a 1 wide x N high result set.

I'm also aware that advanced databases like oracle do have support for arrays in SQL, but the way they work is (in concept) rather like what you wrote initially:

You supply a parameterized sql, and an array, the driver takes the array and presents it to the database as a set of SQL to be run. Because the behaviour is supported natively it makes many performance improvements, keeping the connection open, and sending the array to the server to be run iteratively, rather than repeatedly calling from the client..(It essentially behaves with a similar speed and ease of use, to a single SQL).
I doubt Access has support for this, as it is quite an advanced functionality, but if it is indicative of a lot of queries you plan to run, you can use a free version of Oracle (Express 10g) if you want a more powerful and respected database (access needs to be dropped, soon! )

 

[Adagio]

Thanks for the info, now I'll see if I can get it working

And I agree that Access needs to be dropped, unfortunately the decision was made many years ago (while I've only been here a few months). There has been some talk about changing the database, but when (or if) that happens is still unknown
It was the other people in the IT-department who told me to do it this way, and since I don't know much about database access I just accepted their decision (while making it as easy as possible to change the code, in cases like this)