SQL Query - precision error

[Art]

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

 

[Kevin Spencer]

Hi Art,

Precision refers to the precision (number of digits, both to the right and
left of the decimal point) in a floating point number. According to your
field definition, the precision is 10, which means that you can store a
decimal number with a maximum of 10 digits in it. The second number is
scale, which is the number of digits allowed to the right of the decimal
point of the number, which you've defined as 0 (IOW, you can store an
integer with a maximum of 10 digits). I can't tell you much more, as you
mentioned a "SQL table." SQL is a query language, and SQL Server is a
database. Since a lot of non-database-professionals use the terms
interchangeably, I don't know what kind of database you're using.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.

 

[Art]

The database is a runny under SQL 2000. We cannot change the precision as
itis software set.

That said, why would I get a precision error when querying that database
from a Front Page query?

If I run the query in Query Analyzer without the Front Page Search variable,
it works fine.

 

[Kevin Spencer]

Hi Art,

What exactly is the value of f_docnumber?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.

 

[Art]

The value of f_doccumber can be any integar from 12000 up to 9999999999

I need to have a variable input from the user hence the ::f_docnumber::

 

[Kevin Spencer]

Hi Art,

What I'm asking is, when you run the query and get the error, what value are
you putting in?

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.

 

[Art]

I never get to the point of putting in a value because the script fails
immediately.

In the default value for f_docnumber I have 12345

 

[Kevin Spencer]

Im not sure, but the value it is putting in may be blank. Still, you
wouldn't get a precision error if that was the case. I'm baffled. I'm not
too familiar with how FrontPage writes ASP code these days (or is it ASP.net
you're working with?). I could look over the code, but I wouldn't know what
files to tell you to send. If it's ASP, you might try putting a
Response.Write statement just after the query is formed, to get the web page
to write out the query where you could actually see what's going into the
database. You could also use SQL Profiler to view the query at the database
end, but I'm not sure you know how to use it.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.

 

[MikeR]

Hmmm...If the SQL executes before you entered a value for f_docnumber, I think you
may have more of a logic problem than a database problem. I'm not sure what the
script would initialize f_docnumber to. The error message may not be correct. Can
we see a bigger chunk of the code?

I gather your database is SqlServer? If you have the client-side software, and the
correct permissions, you can change it. But I don't think that's the issue.
Thoughts, Kevin?
MikeR

 

[Kevin Spencer]

I'm at a loss, Mike. I wish he could Response.Write the query out to the
page. That would definitely give me a clue. Any ideas you can provide are
most welcome to me!

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.

 

[MikeR]

Yeh a response.write and response.end might show something. If it came up blank
tho, is it a space, an empty string or a null or a zero. I can't test against a
SQLServer db (I think that's what Art has), so I dunno how it would react to those.
I also don't use the FP wizard, so I'm not up on the syntax.

Seems like it should just not return the recordset, but numerics are a lot more
picky in the way they want be be handled.

I'm REALLY curious about the solution to this one.
MikeR

 

[MikeR]

OK - not a zero.....
MikeR

Yeh a response.write and response.end might show something. If it came
up blank tho, is it a space, an empty string or a null or a zero. I
can't test against a SQLServer db (I think that's what Art has), so I
dunno how it would react to those.
I also don't use the FP wizard, so I'm not up on the syntax.

Seems like it should just not return the recordset, but numerics are a
lot more picky in the way they want be be handled.

I'm REALLY curious about the solution to this one.
MikeR

 

[Art]

Guys - the entire query is
SELECT * FROM tablename WHERE (f_docnumber = ::f_docnumber:

Nothing fancy. Front Page puts the :: for a search filed on the ASP page.
All of this is generated in the FP wizard.

Here is the entire FP html script

<html>

<head>
<meta name="GENERATOR" content="Microsoft FrontPage 5.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<% ' FP_ASP -- ASP Automatically generated by a Frontpage Component. Do not
Edit.
FP_CharSet = "windows-1252"
FP_CodePage = 1252 %>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>f</title>
</head>

<body>

<form BOTID="0" METHOD="POST" ACTION="doc_id.asp">
<table BORDER="0">
<tr>
<td><b>f_docnumber</b></td>
<td>
<input TYPE="TEXT" NAME="f_docnumber"
VALUE="<%=Server.HtmlEncode(Request("f_docnumber"))%>" size="20"></td>
</tr>
</table>
<p><br>
<input TYPE="Submit"><input TYPE="Reset"><!--webbot bot="SaveAsASP"
clientside suggestedext="asp" preview=" " --></p>
<p> </p>
</form>
<table width="100%" border="1">
<thead>
<tr>
<td><b>f_docnumber</b></td>
<td><b>conv_date</b></td>
<td><b>image</b></td>
</tr>
</thead>
<tbody>
<!--webbot bot="DatabaseRegionStart"
s-columnnames="f_docnumber,f_entrydate,a31,a35,a36,a37,a38,a39,a40,a41,a42,conv_date,link,link2,image,document_listing,f_docclassnumber,a46,a47,ReportDate,a33,a34"
s-columntypes="131,3,200,200,200,200,200,200,200,200,200,135,200,200,200,200,3,3,200,135,200,200"
s-dataconnection="DMIS_MMINET_QUERIES" b-tableformat="TRUE"
b-menuformat="FALSE" s-menuchoice s-menuvalue b-tableborder="TRUE"
b-tableexpand="TRUE" b-tableheader="TRUE" b-listlabels="TRUE"
b-listseparator="TRUE" i-listformat="0" b-makeform="TRUE" s-recordsource
s-displaycolumns="f_docnumber,conv_date,image" s-criteria s-order
s-sql="SELECT * FROM mminet_fn WHERE f_docnumber = ::f_docnumber::"
b-procedure="FALSE" clientside suggestedext="asp"
s-defaultfields="f_docnumber=" s-norecordsfound="No records returned."
i-maxrecords="256" i-groupsize="0" botid="0" u-dblib="_fpclass/fpdblib.inc"
u-dbrgn1="_fpclass/fpdbrgn1.inc" u-dbrgn2="_fpclass/fpdbrgn2.inc" tag="TBODY"
preview="<tr><td colspan=64 bgcolor="#FFFF00" align="left" width="100%"><font
color="#000000">This is the start of a Database Results
region.</font></td></tr>" startspan --><!--#include
file="_fpclass/fpdblib.inc"-->
<% if 0 then %>
<SCRIPT Language="JavaScript">
document.write("<div style='background: yellow; color: black;'>The Database
Results component on this page is unable to display database content. The
page must have a filename ending in '.asp', and the web must be hosted on a
server that supports Active Server Pages.</div>");
</SCRIPT>
<% end if %>
<%
fp_sQry="SELECT * FROM mminet_fn WHERE f_docnumber = ::f_docnumber::"
fp_sDefault="f_docnumber="
fp_sNoRecords="<tr><td colspan=3 align=left width=""100%"">No records
returned.</td></tr>"
fp_sDataConn="DMIS_MMINET_QUERIES"
fp_iMaxRecords=256
fp_iCommandType=1
fp_iPageSize=0
fp_fTableFormat=True
fp_fMenuFormat=False
fp_sMenuChoice=""
fp_sMenuValue=""
fp_sColTypes="&f_docnumber=131&f_entrydate=3&a31=200&a35=200&a36=200&a37=200&a38=200&a39=200&a40=200&a41=200&a42=200&conv_date=135&link=200&link2=200&image=200&document_listing=200&f_docclassnumber=3&a46=3&a47=200&ReportDate=135&a33=200&a34=200&"
fp_iDisplayCols=3
fp_fCustomQuery=True
BOTID=0
fp_iRegion=BOTID
%>
<!--#include file="_fpclass/fpdbrgn1.inc"-->
<!--webbot bot="DatabaseRegionStart" endspan i-checksum="27479" --><tr>
<td>
<!--webbot bot="DatabaseResultColumn"
s-columnnames="f_docnumber,f_entrydate,a31,a35,a36,a37,a38,a39,a40,a41,a42,conv_date,link,link2,image,document_listing,f_docclassnumber,a46,a47,ReportDate,a33,a34"
s-column="f_docnumber" b-tableformat="TRUE" b-hashtml="FALSE"
b-makelink="FALSE" clientside b-MenuFormat preview="&lt;font
size="-1">&lt;&lt;</font>f_docnumber<font size="-1">&gt;&gt;</font>"
startspan --><%=FP_FieldVal(fp_rs,"f_docnumber")%><!--webbot
bot="DatabaseResultColumn" endspan i-checksum="29621" --></td>
<td>
<!--webbot bot="DatabaseResultColumn"
s-columnnames="f_docnumber,f_entrydate,a31,a35,a36,a37,a38,a39,a40,a41,a42,conv_date,link,link2,image,document_listing,f_docclassnumber,a46,a47,ReportDate,a33,a34"
s-column="conv_date" b-tableformat="TRUE" b-hashtml="FALSE"
b-makelink="FALSE" clientside b-MenuFormat preview="<font
size="-1">&lt;&lt;</font>conv_date<font size="-1">&gt;&gt;</font>" startspan
--><%=FP_FieldVal(fp_rs,"conv_date")%><!--webbot bot="DatabaseResultColumn"
endspan i-checksum="21654" --></td>
<td>
<!--webbot bot="DatabaseResultColumn"
s-columnnames="f_docnumber,f_entrydate,a31,a35,a36,a37,a38,a39,a40,a41,a42,conv_date,link,link2,image,document_listing,f_docclassnumber,a46,a47,ReportDate,a33,a34"
s-column="image" b-tableformat="TRUE" b-hashtml="TRUE" b-makelink="FALSE"
clientside b-MenuFormat preview="<font size="-1">&lt;&lt;</font>image<font
size="-1">&gt;&gt;</font>" startspan
--><%=FP_Field(fp_rs,"image")%><!--webbot bot="DatabaseResultColumn" endspan
i-checksum="10874" --></td>
</tr>
<!--webbot bot="DatabaseRegionEnd" b-tableformat="TRUE"
b-menuformat="FALSE" u-dbrgn2="_fpclass/fpdbrgn2.inc" i-groupsize="0"
clientside tag="TBODY" preview="<tr><td colspan=64 bgcolor="#FFFF00"
align="left" width="100%"><font color="#000000">This is the end of a Database
Results region.</font></td></tr>" startspan --><!--#include
file="_fpclass/fpdbrgn2.inc"-->
<!--webbot bot="DatabaseRegionEnd" endspan i-checksum="62730" --></tbody>
</table>

</body>

</html>

 

[MikeR]

Art -
I don't use the FP DB wizard, and I don't understand the code here. Sorry, maybe
someone else can debug it.

Having said that, I have some questions.
Do I understand you never get to enter a value for f_docnumber and click the
submit button? If that's the case, then maybe you're going to have to re-create
the page, cause I think that would mean the logic is scrambled.

I see where the SQL is being created in a couple of places, but I can't tell where
it's actually being executed.

I don't know if FP will let you edit this after it's been created, but I wonder if
there is a problem having the input variable and the column name the same, and it
confuses the db engine. Maybe some way to qualify one or the other?

Again, sorry. Hope it gets solved.
MikeR

 

[Kevin Spencer]

It looks to me (and the WebBot code is all Greek to me, for the most part,
not to mention a missing include file), like it is entirely likely that
there is no value being sent in the f_docnumber variable, but what puzzles
me is that in that case you should get a Syntax Error instead of a precision
error.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Sometimes you eat the elephant.
Sometimes the elephant eats you.