SQl Query - Precision Error

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Not sure which group to post this in so posting in two.

I am using a very basic query in Front Page 2002 against a SQL table

SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)

and f_docnumber is a search form field.

When I run I get

Description: The precision is invalid.
Number: -2147467259 (0x80004005)

The field is defined in the table as numberic 9(10,0)

Any ideas?


Thanks
 
Try

SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber



--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
The " seems out of place and not sure what might be missing

Tried it as is and it gives me a quotation mark error

Tried with and without single quotes as well
 
Nothing is missing, you are querying a numeric field.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber

gives me when testing the query

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
 
Ok, that does work when use Access as a database. Give me a few minutes and I will post the other
way to do this where you should get the error.


--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Art said:
SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber

gives me when testing the query

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
before the character string ' & f_docnumber'.
Thomas A. Rowe said:
Nothing is missing, you are querying a numeric field.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
Click to expand...
Click to expand...
 
Sorry, but testing this locally, I can only get the following to work

SELECT * FROM tablename WHERE f_docnumber = & f_docnumber

Which is what I originally posted for a numerical field.
--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Thomas A. Rowe said:
Ok, that does work when use Access as a database. Give me a few minutes and I will post the other
way to do this where you should get the error.


--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Art said:
SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber

gives me when testing the query

The following error message comes from the database driver software; it may
appear in a different language depending on how the driver is configured.
-------------------------------------------------------
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
before the character string ' & f_docnumber'.
Thomas A. Rowe said:
Nothing is missing, you are querying a numeric field.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

The " seems out of place and not sure what might be missing

Tried it as is and it gives me a quotation mark error

Tried with and without single quotes as well



:

Try

SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber



--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================

Not sure which group to post this in so posting in two.

I am using a very basic query in Front Page 2002 against a SQL table

SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber::)

and f_docnumber is a search form field.

When I run I get

Description: The precision is invalid.
Number: -2147467259 (0x80004005)

The field is defined in the table as numberic 9(10,0)

Any ideas?


Thanks
Click to expand...
Click to expand...
Click to expand...
 
If the statement were
SQL = SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber
that quote mark would be the closing one.
SQL = "SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber
would be correct.
In any case it's unbalanced.

I don't see how taking off the parens changes the data type or the precision. I'll
be curious to see the resolution to this one.
MikeR
 
Mark

You are correct, the " makes it unbalanced

And without the ::f_focnumber::, FP does not create the input field for the
user to input what f_docnumber they are seaching for.

It would seem that FP has some difficulty in searching for anything other
than a varchar or text query though an ASP generated page.
 
Thomas -
Your first post did have the quote in it.
Try

SELECT * FROM tablename WHERE f_docnumber = " & f_docnumber
<vbg>
MikeR
 
Thank for the catch.

--
==============================================
Thomas A. Rowe (Microsoft MVP - FrontPage)
WebMaster Resources(tm)

FrontPage Resources, WebCircle, MS KB Quick Links, etc.
==============================================
If you feel your current issue is a results of installing
a Service Pack or security update, please contact
Microsoft Product Support Services:
http://support.microsoft.com
If the problem can be shown to have been caused by a
security update, then there is usually no charge for the call.
==============================================
 
Hi Art,

Probably not the best solution but why not cast the field as varchar, eg
SELECT *
FROM Table
WHERE CAST(f_focnumber AS varchar) = '::f_focnumber::'

This time of course you'll need quotes around ::f_Focnumber::
 
Jon

Great idea but FP still doesnt like

Now it throws the error

Database Results Error
Your page contains a query with user input parameters that could not be
resolved.
This could happen if your DatabaseRegionStart webbot has an empty or missing
s-columnnames or s-columntypes attributes.
You may need to read Microsoft Knowledge Base Article 817029.
 
Back
Top