SQL Statement

[Cathy]

I have a DB with 12 columns and hundreds of row with a key
on the Smartrac_No. I have created forms in FP2000 to
allow a user to add, change or delete rows of information.

The following is my SQL statement in the updaterecord.asp:
UPDATE smartracs2004
SET Description='::NewDescription::'
WHERE Smartrac_No='::Smartrac_No::'

I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

When I change the ':: to " I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

What is the proper statement for this update.

Thanks

 

[Kevin Spencer]

Your first query was closer to correct. In the SQL language, data types are
determined by punctuation, as SQL is pure text. Text data types are enclosed
in 'single quotes'. Numeric are not. My guess is that "Smartrac_No" is a
numeric data type, and that you only need to remove the single quotes from
that column reference.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

 

[Cathy]

By Golly that worked. Now f I wanted to include several
columns to update in one row. This is my statement, but
what if there is a particular column within the row that
the users doesn't want to update so they leave it blank.
If they leave any fields blank it gives an error.

UPDATE Smartracs2004
SET Description = '::NewDescription::', Reviewed_Date
= '::NewReviewed_Date::'
WHERE Smartrac_No = ::Smartrac_No::

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.

Thanks

-----Original Message-----
Your first query was closer to correct. In the SQL language, data types are
determined by punctuation, as SQL is pure text. Text data types are enclosed
in 'single quotes'. Numeric are not. My guess is that "Smartrac_No" is a
numeric data type, and that you only need to remove the single quotes from
that column reference.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I have a DB with 12 columns and hundreds of row with a key
on the Smartrac_No. I have created forms in FP2000 to
allow a user to add, change or delete rows of information.

The following is my SQL statement in the updaterecord.asp:
UPDATE smartracs2004
SET Description='::NewDescription::'
WHERE Smartrac_No='::Smartrac_No::'

I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

When I change the ':: to " I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

What is the proper statement for this update.

Thanks

.

 

[Kevin Spencer]

Hi Cathy,

Glad we're making progress! If the column is required by the query, and it
is NOT a text data type, you will need to make it required by the user, or
use server-side programming logic to exclude the column from the query. The
text data type is enclosed with single quotes, so it can be empty. But an
empty number value will cause a Syntax Error, and an empty Date value will
cause a Data Type Mismatch error.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

By Golly that worked. Now f I wanted to include several
columns to update in one row. This is my statement, but
what if there is a particular column within the row that
the users doesn't want to update so they leave it blank.
If they leave any fields blank it gives an error.

UPDATE Smartracs2004
SET Description = '::NewDescription::', Reviewed_Date
= '::NewReviewed_Date::'
WHERE Smartrac_No = ::Smartrac_No::

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.

Thanks

-----Original Message-----
Your first query was closer to correct. In the SQL language, data types are
determined by punctuation, as SQL is pure text. Text data types are enclosed
in 'single quotes'. Numeric are not. My guess is that "Smartrac_No" is a
numeric data type, and that you only need to remove the single quotes from
that column reference.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I have a DB with 12 columns and hundreds of row with a key
on the Smartrac_No. I have created forms in FP2000 to
allow a user to add, change or delete rows of information.

The following is my SQL statement in the updaterecord.asp:
UPDATE smartracs2004
SET Description='::NewDescription::'
WHERE Smartrac_No='::Smartrac_No::'

I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

When I change the ':: to " I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver] Too
few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

What is the proper statement for this update.

Thanks

.

 

[Cathy]

This has really helped me. Thanks for the answers Kevin.

-----Original Message-----
Hi Cathy,

Glad we're making progress! If the column is required by the query, and it
is NOT a text data type, you will need to make it required by the user, or
use server-side programming logic to exclude the column from the query. The
text data type is enclosed with single quotes, so it can be empty. But an
empty number value will cause a Syntax Error, and an empty Date value will
cause a Data Type Mismatch error.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

By Golly that worked. Now f I wanted to include several
columns to update in one row. This is my statement, but
what if there is a particular column within the row that
the users doesn't want to update so they leave it blank.
If they leave any fields blank it gives an error.

UPDATE Smartracs2004
SET Description = '::NewDescription::', Reviewed_Date
= '::NewReviewed_Date::'
WHERE Smartrac_No = ::Smartrac_No::

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

One or more form fields were empty. You should provide
default values for all form fields that are used in the
query.

Thanks

-----Original Message-----
Your first query was closer to correct. In the SQL language, data types are
determined by punctuation, as SQL is pure text. Text

data
types are enclosed

in 'single quotes'. Numeric are not. My guess is that "Smartrac_No" is a
numeric data type, and that you only need to remove the single quotes from
that column reference.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I have a DB with 12 columns and hundreds of row with

a
key

on the Smartrac_No. I have created forms in FP2000 to
allow a user to add, change or delete rows of information.

The following is my SQL statement in the updaterecord.asp:
UPDATE smartracs2004
SET Description='::NewDescription::'
WHERE Smartrac_No='::Smartrac_No::'

I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access Driver]
Data type mismatch in criteria expression.
Number: -2147217913 (0x80040E07)
Source: Microsoft OLE DB Provider for ODBC Drivers

When I change the ':: to " I receive this message:

Database Results Error
Description: [Microsoft][ODBC Microsoft Access

Driver]
Too

few parameters. Expected 1.
Number: -2147217904 (0x80040E10)
Source: Microsoft OLE DB Provider for ODBC Drivers

What is the proper statement for this update.

Thanks



.

.