SQL String Connecting to MS Access via ADO

[Mike Fellows]

I have an SQL string that im trying to use to connect to an Access DB

im currently using the following string:

SQLStr = "SELECT
LEFT(LTRIM(RTRIM(Personal.postcode)),LEN(personal.postcode)-1) AS Expr1,
LiveTracker.ID, LiveTracker.MortgageAppSubmitted, Closed.ID FROM ((Personal
INNER JOIN PendingTracker ON Personal.ID = PendingTracker.ID) LEFT JOIN
LiveTracker ON Personal.ID = LiveTracker.ID) LEFT JOIN Closed ON personal.ID
= Closed.ID ORDER BY LEFT(LTRIM(RTRIM(Personal.postcode)),6);"

the above currently works perfectly and i have no problems but it doesnt do
exaclty as i want, what i would like it to do is this statement:

SQLStr = "SELECT Left(Personal.postcode,Len(personal.postcode)-2) AS Expr1,
LiveTracker.ID, LiveTracker.MortgageAppSubmitted, Closed.ID FROM ((Personal
INNER JOIN PendingTracker ON Personal.ID = PendingTracker.ID) LEFT JOIN
LiveTracker ON Personal.ID = LiveTracker.ID) LEFT JOIN Closed ON Personal.ID
= Closed.ID ORDER BY personal.postcode;"

the only difference between the two SQL statements is that the second
statement takes 2 charachetrs from the end of postcode whereas the first
statment only removes one!

when i try to run the second statement I get the following error:

An unhandled exception of type 'System.InvalidOperationException' occurred
in system.data.dll
Additional information: The provider could not determine the String value.
For example, the row was just created, the default for the String column was
not available, and the consumer had not yet set a new String value.

any help with this would be greatly appreciated


Regards

Mike Fellows

 

[Ravikanth[MVP]]

Hi

The exception that is thrown when a method call is
invalid for the object's current state.

InvalidOperationException is used in cases when the
failure to invoke a method is caused by reasons other
than invalid arguments. For example,
InvalidOperationException is thrown by:

MoveNext: if objects of a collection are modified after
the enumerator is created.
GetString: if the resource set is closed before the
method call is made.

HTH
Ravikanth[MVP]

-----Original Message-----
I have an SQL string that im trying to use to connect to an Access DB

im currently using the following string:

SQLStr = "SELECT
LEFT(LTRIM(RTRIM(Personal.postcode)),LEN

(personal.postcode)-1) AS Expr1,

LiveTracker.ID, LiveTracker.MortgageAppSubmitted, Closed.ID FROM ((Personal
INNER JOIN PendingTracker ON Personal.ID = PendingTracker.ID) LEFT JOIN
LiveTracker ON Personal.ID = LiveTracker.ID) LEFT JOIN Closed ON personal.ID
= Closed.ID ORDER BY LEFT(LTRIM(RTRIM (Personal.postcode)),6);"

the above currently works perfectly and i have no problems but it doesnt do
exaclty as i want, what i would like it to do is this statement:

SQLStr = "SELECT Left(Personal.postcode,Len

(personal.postcode)-2) AS Expr1,

LiveTracker.ID, LiveTracker.MortgageAppSubmitted, Closed.ID FROM ((Personal
INNER JOIN PendingTracker ON Personal.ID = PendingTracker.ID) LEFT JOIN
LiveTracker ON Personal.ID = LiveTracker.ID) LEFT JOIN Closed ON Personal.ID
= Closed.ID ORDER BY personal.postcode;"

the only difference between the two SQL statements is that the second
statement takes 2 charachetrs from the end of postcode whereas the first
statment only removes one!

when i try to run the second statement I get the following error:

An unhandled exception of

type 'System.InvalidOperationException' occurred

 

[Mike Fellows]

Ravikanth,

"The exception that is thrown when a method call is invalid for the object's
current state."

ok, that makes sense but the 'call' cannot be invalid for the objects
current state

if i copy and paste the details into access and run the query it returns
exactly what i wanted, what i dont understand is why it isnt working within
my application:

if its of any use i can include the section of my code, basically all it
does is populate a datagrid which is then sorted and counted then inserted
into an excel spreadsheet

 

[Mike Fellows]

just been running through the debugger with this and the sql string appears
to be running fine

the problem seems to be arising when I hit: da.fill(ds) though im still
unsure why


da.SelectCommand = New OleDbCommand(SQLStr, ocon)

da.Fill(ds) <--------------DEBUGGER STOPS HERE

RowCount = ds.Tables(0).Rows.Count
Me.DataGrid1.DataSource = ds.Tables(0)

da.Dispose()
ds.Dispose()
ocon.Close()
ocon.Dispose()