Urgent: please help with this as im now completley stuck

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
Wed Sep 17 08:25:17 CDT 2003

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" <michael.fellows@equityhouse.co.uk.SPAM> wrote in message
news:V5Z9b.611$Ah7.477@newsfep1-gui.server.ntli.net...
> 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()
>
>

Mike
Wed Sep 17 08:41:20 CDT 2003

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" <dotnetguru@comcast.nospam.net> wrote in message
news:uYo2N7RfDHA.1212@TK2MSFTNGP12.phx.gbl...
> 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" <michael.fellows@equityhouse.co.uk.SPAM> wrote in message
> news:V5Z9b.611$Ah7.477@newsfep1-gui.server.ntli.net...
> > 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
Wed Sep 17 12:15:30 CDT 2003

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.
"Mike Fellows" <michael.fellows@equityhouse.co.uk.SPAM> wrote in message
news:4wZ9b.709$Ah7.308@newsfep1-gui.server.ntli.net...
> 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" <dotnetguru@comcast.nospam.net> wrote in message
> news:uYo2N7RfDHA.1212@TK2MSFTNGP12.phx.gbl...
> > 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" <michael.fellows@equityhouse.co.uk.SPAM> wrote in message
> > news:V5Z9b.611$Ah7.477@newsfep1-gui.server.ntli.net...
> > > 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()
> > >
> > >
> >
> >
>
>