Problem with Application and MS Access DB

[Mike Fellows]

i wasnt sure which group to post this in so i posted in both (here and
microsoft.public.dotnet.framework.adonet) so apologies if its in the wrong
place, but i feel it could be both

anyway.....


i have the following SQL string that im trying to use to retrieve data from
a access database and place into a datagrid the problem seem to be arising
with the Left(Personal.postcode,Len(personal.postcode)-2) AS Expr1 part of
the statement because if i set the Len(personal.postocde)-2) to be -1 the
whole thing works fine, similarly if is set it to -3, -4, or -5 it works
great but not with -2 which is what i want, the row its working on can have
a few possibilities for the data (being a UK postcode) which are:

A1 1AA
AA1 1AA
AA11 1AA

all i want to do is remove the last 2 characheters from the postcode but i
cannot get it to work, ive worked on this for a few weeks now with no joy,
if i put the SQL string into MS Access it works perfectly but not from my
..net app - any help would be greatly appreciated, below is my code:


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;"


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()


Thanks


Mike Fellows

 

[Herfried K. Wagner [MVP]]

Hello,

i wasnt sure which group to post this in so i posted in both (here and
microsoft.public.dotnet.framework.adonet) so apologies if its in the wrong
place, but i feel it could be both

microsoft.public.dotnet.framework.adonet is the right group for the
question.

;-)

 

[Cor]

Hi Mike,
I thought this is a Fergus Cooney Problem.
He was not active in daytieme today.
But I seen him helping.
I put a marker for him, if he wants to help you, he is the expert for this I
think.

(Or am I wrong Fergus)
Cor