Urgent: please help with this as im now completley stuck

[Mike Fellows]

Ive asked this a few times over the last few weeks and still im no further
forward


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

 

[William Ryan]

Mike, I just ran the same query in Query Analyzer and it works fine. What
specific exception are you getting? Can you wrap the da.Fill in a try catch
statement

like

Try
da.Fill(ds)
Catch ex as System.Exception
Debug.Writeline(ex.ToSTring)
End Try

 

[Mike Fellows]

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.

 

[William Ryan]

Since it works with every number but two, this is quite puzzling. Can you
make a dummy tale, and just have two or three values in it. See if it can
work at all with dummy data that you know is good. If it is the exact same
query and it runs from Access, the usual culprit is a reserved word, but
this can't be the case b/c it works with other numbers.I'll keep looking but
I'm stumped.