How to get which columns actually have 'Data Type Mismatch' error in INSERT-SQL?

Hi all,

Sometimes, I do SQL-INSERT-insertion to a table that consists of many
columns. Something like this:
INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
VALUES (val-1, val-2, val-3,..., val-n)

Problem is, I often had this 'Data Type Mismatch' error. I hate this
error because it is very easy to fix *if* I know which column(s) is
(are) having the data mismatch error -and finding which columns could be
very tedious if it involves many columns.

Q: Anybody knows how to retrieve the information which column(s) is
(are) actually have the mismatch data error? It's easy to get if the
insertion is against remote table (via SPT) because AERROR() gives that
information, but what if it is a view or local tables which are accessed
by VFP natively? AERROR() doesn't seem to work.

TIA,
Willianto

Altman
Tue Sep 21 08:58:39 CDT 2004

I have always wished Fox would tell me which column I am having a problem
with but the truth is, it seems on any sql statement, if there is an error,
it is fairly generic. The best way I have found to figure it out is to set
my debug point at the insert statement and check every variable that is
being inserted and compare that with the data type that it needs to be. If
someone has a better way I would love to hear it though because this causes
me many headaches. Maybe this would be something to add to the fox wish
list, I would surely believe that the fox knows what column it is, so why
doesn't he tell us?


"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:u7xPZq5nEHA.1152@TK2MSFTNGP11.phx.gbl...
> Hi all,
>
> Sometimes, I do SQL-INSERT-insertion to a table that consists of many
> columns. Something like this:
> INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
> VALUES (val-1, val-2, val-3,..., val-n)
>
> Problem is, I often had this 'Data Type Mismatch' error. I hate this
> error because it is very easy to fix *if* I know which column(s) is
> (are) having the data mismatch error -and finding which columns could be
> very tedious if it involves many columns.
>
> Q: Anybody knows how to retrieve the information which column(s) is
> (are) actually have the mismatch data error? It's easy to get if the
> insertion is against remote table (via SPT) because AERROR() gives that
> information, but what if it is a view or local tables which are accessed
> by VFP natively? AERROR() doesn't seem to work.
>
> TIA,
> Willianto
>
>

Willianto
Tue Sep 21 13:00:38 CDT 2004

Altman:

Glad to know that I'm not alone here. What I did is the same as yours;
on the debuger, I point my mouse to the variable, and check the type and
the value. But - I'm sure you already know - things could be quite
tedious when you deal with 30 columns.

Maybe anybody with ideas could chime in here...

WBR,
Willianto


"Altman" <NotGiven@SickOfSpam.com> wrote in message
news:uSxgaM%23nEHA.1152@TK2MSFTNGP11.phx.gbl...
> I have always wished Fox would tell me which column I am having a
problem
> with but the truth is, it seems on any sql statement, if there is an
error,
> it is fairly generic. The best way I have found to figure it out is
to set
> my debug point at the insert statement and check every variable that
is
> being inserted and compare that with the data type that it needs to
be. If
> someone has a better way I would love to hear it though because this
causes
> me many headaches. Maybe this would be something to add to the fox
wish
> list, I would surely believe that the fox knows what column it is, so
why
> doesn't he tell us?
>
>
> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
> news:u7xPZq5nEHA.1152@TK2MSFTNGP11.phx.gbl...
> > Hi all,
> >
> > Sometimes, I do SQL-INSERT-insertion to a table that consists of
many
> > columns. Something like this:
> > INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
> > VALUES (val-1, val-2, val-3,..., val-n)
> >
> > Problem is, I often had this 'Data Type Mismatch' error. I hate this
> > error because it is very easy to fix *if* I know which column(s) is
> > (are) having the data mismatch error -and finding which columns
could be
> > very tedious if it involves many columns.
> >
> > Q: Anybody knows how to retrieve the information which column(s) is
> > (are) actually have the mismatch data error? It's easy to get if the
> > insertion is against remote table (via SPT) because AERROR() gives
that
> > information, but what if it is a view or local tables which are
accessed
> > by VFP natively? AERROR() doesn't seem to work.
> >
> > TIA,
> > Willianto
> >
> >
>
>

Rick
Tue Sep 21 14:22:26 CDT 2004

While VFP might "know" which column it is when dealing with native =
tables, it probably doesn't have the info available for Remote Views and =
SQL Passthrough. I believe it's just easier to deal with a generic =
message than worry about all the potential variants! (And 9.0 offers =
more field types and more ways to get it "wrong"!)

Rick

"Altman" <NotGiven@SickOfSpam.com> wrote in message =
news:uSxgaM%23nEHA.1152@TK2MSFTNGP11.phx.gbl...
>I have always wished Fox would tell me which column I am having a =
problem=20
> with but the truth is, it seems on any sql statement, if there is an =
error,=20
> it is fairly generic. The best way I have found to figure it out is =
to set=20
> my debug point at the insert statement and check every variable that =
is=20
> being inserted and compare that with the data type that it needs to =
be. If=20
> someone has a better way I would love to hear it though because this =
causes=20
> me many headaches. Maybe this would be something to add to the fox =
wish=20
> list, I would surely believe that the fox knows what column it is, so =
why=20
> doesn't he tell us?
>=20
>=20
> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message=20
> news:u7xPZq5nEHA.1152@TK2MSFTNGP11.phx.gbl...
>> Hi all,
>>
>> Sometimes, I do SQL-INSERT-insertion to a table that consists of many
>> columns. Something like this:
>> INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
>> VALUES (val-1, val-2, val-3,..., val-n)
>>
>> Problem is, I often had this 'Data Type Mismatch' error. I hate this
>> error because it is very easy to fix *if* I know which column(s) is
>> (are) having the data mismatch error -and finding which columns could =
be
>> very tedious if it involves many columns.
>>
>> Q: Anybody knows how to retrieve the information which column(s) is
>> (are) actually have the mismatch data error? It's easy to get if the
>> insertion is against remote table (via SPT) because AERROR() gives =
that
>> information, but what if it is a view or local tables which are =
accessed
>> by VFP natively? AERROR() doesn't seem to work.
>>
>> TIA,
>> Willianto
>>
>>=20
>=20
>

Sietse
Tue Sep 21 14:25:36 CDT 2004

Hi,
I don't know of any tricks on this one but I'm always gratefull for naming
conventions for this one. Fields and variables most of the time are named so
that i can easely spot errors in the values based on the name of the values
inserted. Again the debuger is invaluable here.

Sietse Wijnker

"Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
news:OMIXRRAoEHA.3460@TK2MSFTNGP15.phx.gbl...
> Altman:
>
> Glad to know that I'm not alone here. What I did is the same as yours;
> on the debuger, I point my mouse to the variable, and check the type and
> the value. But - I'm sure you already know - things could be quite
> tedious when you deal with 30 columns.
>
> Maybe anybody with ideas could chime in here...
>
> WBR,
> Willianto
>
>
> "Altman" <NotGiven@SickOfSpam.com> wrote in message
> news:uSxgaM%23nEHA.1152@TK2MSFTNGP11.phx.gbl...
>> I have always wished Fox would tell me which column I am having a
> problem
>> with but the truth is, it seems on any sql statement, if there is an
> error,
>> it is fairly generic. The best way I have found to figure it out is
> to set
>> my debug point at the insert statement and check every variable that
> is
>> being inserted and compare that with the data type that it needs to
> be. If
>> someone has a better way I would love to hear it though because this
> causes
>> me many headaches. Maybe this would be something to add to the fox
> wish
>> list, I would surely believe that the fox knows what column it is, so
> why
>> doesn't he tell us?
>>
>>
>> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
>> news:u7xPZq5nEHA.1152@TK2MSFTNGP11.phx.gbl...
>> > Hi all,
>> >
>> > Sometimes, I do SQL-INSERT-insertion to a table that consists of
> many
>> > columns. Something like this:
>> > INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
>> > VALUES (val-1, val-2, val-3,..., val-n)
>> >
>> > Problem is, I often had this 'Data Type Mismatch' error. I hate this
>> > error because it is very easy to fix *if* I know which column(s) is
>> > (are) having the data mismatch error -and finding which columns
> could be
>> > very tedious if it involves many columns.
>> >
>> > Q: Anybody knows how to retrieve the information which column(s) is
>> > (are) actually have the mismatch data error? It's easy to get if the
>> > insertion is against remote table (via SPT) because AERROR() gives
> that
>> > information, but what if it is a view or local tables which are
> accessed
>> > by VFP natively? AERROR() doesn't seem to work.
>> >
>> > TIA,
>> > Willianto
>> >
>> >
>>
>>
>
>

Willianto
Wed Sep 22 04:36:47 CDT 2004

Sietse:

> I don't know of any tricks on this one but I'm always gratefull for
naming
> conventions for this one.
Yes Sietse. Unfortunately, VFP won't refuse if I put 'Some String' into
lnVariableName or ldVariableName. Anyway, that brings another topic to
discuss: anybody knows how to force a strong typing in VFP -such that if
I declare a variable as integer, than VFP would refuse if the user tries
to store other types (oh well, I think I know the answer; there is no
way VFP could do that)

Willianto


"Sietse Wijnker" <sietse.wijnker@ATsw-software.nl> wrote in message
news:uZSYsDBoEHA.1608@TK2MSFTNGP15.phx.gbl...
> Hi,
> I don't know of any tricks on this one but I'm always gratefull for
naming
> conventions for this one. Fields and variables most of the time are
named so
> that i can easely spot errors in the values based on the name of the
values
> inserted. Again the debuger is invaluable here.
>
> Sietse Wijnker
>
> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in message
> news:OMIXRRAoEHA.3460@TK2MSFTNGP15.phx.gbl...
> > Altman:
> >
> > Glad to know that I'm not alone here. What I did is the same as
yours;
> > on the debuger, I point my mouse to the variable, and check the type
and
> > the value. But - I'm sure you already know - things could be quite
> > tedious when you deal with 30 columns.
> >
> > Maybe anybody with ideas could chime in here...
> >
> > WBR,
> > Willianto
> >
> >
> > "Altman" <NotGiven@SickOfSpam.com> wrote in message
> > news:uSxgaM%23nEHA.1152@TK2MSFTNGP11.phx.gbl...
> >> I have always wished Fox would tell me which column I am having a
> > problem
> >> with but the truth is, it seems on any sql statement, if there is
an
> > error,
> >> it is fairly generic. The best way I have found to figure it out
is
> > to set
> >> my debug point at the insert statement and check every variable
that
> > is
> >> being inserted and compare that with the data type that it needs to
> > be. If
> >> someone has a better way I would love to hear it though because
this
> > causes
> >> me many headaches. Maybe this would be something to add to the fox
> > wish
> >> list, I would surely believe that the fox knows what column it is,
so
> > why
> >> doesn't he tell us?
> >>
> >>
> >> "Willianto" <willianto@remove-me.telkom-and-me.net> wrote in
message
> >> news:u7xPZq5nEHA.1152@TK2MSFTNGP11.phx.gbl...
> >> > Hi all,
> >> >
> >> > Sometimes, I do SQL-INSERT-insertion to a table that consists of
> > many
> >> > columns. Something like this:
> >> > INSERT INTO my_table (col-1, col-2, col-3,..., col-n);
> >> > VALUES (val-1, val-2, val-3,..., val-n)
> >> >
> >> > Problem is, I often had this 'Data Type Mismatch' error. I hate
this
> >> > error because it is very easy to fix *if* I know which column(s)
is
> >> > (are) having the data mismatch error -and finding which columns
> > could be
> >> > very tedious if it involves many columns.
> >> >
> >> > Q: Anybody knows how to retrieve the information which column(s)
is
> >> > (are) actually have the mismatch data error? It's easy to get if
the
> >> > insertion is against remote table (via SPT) because AERROR()
gives
> > that
> >> > information, but what if it is a view or local tables which are
> > accessed
> >> > by VFP natively? AERROR() doesn't seem to work.
> >> >
> >> > TIA,
> >> > Willianto
> >> >
> >> >
> >>
> >>
> >
> >
>
>

Andrew
Thu Sep 23 07:47:51 CDT 2004

Rick Bean wrote:
> While VFP might "know" which column it is when dealing with native
> tables, it probably doesn't have the info available for Remote Views
> and SQL Passthrough. I believe it's just easier to deal with a
> generic message than worry about all the potential variants! (And 9.0
> offers more field types and more ways to get it "wrong"!)

Ummm, isn't that rather ironic though? WIllianto said he gets the message
for remote views but it's the native tables where it /isn't/ reported.
Just to add, I get annoyed by this but felt I had nothing to contribute so
didn't post ;)


The errors are very similar for SELECT - SQL in that you get one error for
the whole line of code and you can write some mightily big lines of SELECT,
INSERT etc. commands.

--
Regards
Andrew Howell

Willianto
Fri Sep 24 03:59:28 CDT 2004

A> Willianto said he gets the message
A> for remote views but it's the native tables where it /isn't/
reported.

<lol>
My fault Andrew. The insertion code in my first post should go like
this:
INSERT INTO my_remote_view (col-1, col-2, col-3,..., col-n);
VALUES (val-1, val-2, val-3,..., val-n)

> The errors are very similar for SELECT - SQL in that you get one error
for
> the whole line of code and you can write some mightily big lines of
SELECT,
> INSERT etc. commands.

Yup. Looks like one has to live with this.

Thanks anyway for chime in.

WBR,
Willianto