SQL Insert Problem - Precision Invalid

am 20.05.2006 23:35:44 von Robin Lawrie

Hi everyone,

I've been looking at this problem for the last couple of hours now and think
it's time I asked for some help!!

I've got the following code:

strSQL="INSERT INTO hk_saved_searches (user_id, search_title, search_sql)
VALUES (?,?,?)"
set cmd=Server.createobject("adodb.command")
with cmd
.CommandText=strSQL
.CommandType=1
Set .ActiveConnection=objConn
.Execute ,array(uid, frmSearchTitle, tmpSQL),adExecuteNoRecords
end with

This is meant to insert 3 values from an ASP page into a MS SQL db.

When this code runs I get err number -2147467259 and error description "The
precision is invalid."

My testing has confirmed that the problem field is user_id.

In the SQL db this field is the integer data type and it has a precision
value of 10.

When the user id is less than 10 digits long it fails i.e. when user_id =
145. When user_id is 10 digits long it works?!

The problem is that all my user id's are at most 3 digits long and I'm sure
I don't need to change all the user id's to be 10 characters long!

I thought having a precision field of 10 meant that the value could be up to
10 digits long, not that it HAS to be 10 digits long!

Any help, greatfully accepted :-)

Thanks,

Robin.

Re: SQL Insert Problem - Precision Invalid

am 21.05.2006 14:54:26 von reb01501

Robin Lawrie wrote:
> Hi everyone,
>
> I've been looking at this problem for the last couple of hours now
> and think it's time I asked for some help!!
>
> I've got the following code:
>
> strSQL="INSERT INTO hk_saved_searches (user_id, search_title,
> search_sql) VALUES (?,?,?)"
> set cmd=Server.createobject("adodb.command")
> with cmd
> .CommandText=strSQL
> .CommandType=1
> Set .ActiveConnection=objConn
> .Execute ,array(uid, frmSearchTitle, tmpSQL),adExecuteNoRecords
> end with
>
> This is meant to insert 3 values from an ASP page into a MS SQL db.
>
> When this code runs I get err number -2147467259 and error
> description "The precision is invalid."
>
> My testing has confirmed that the problem field is user_id.
>
> In the SQL db this field is the integer data type and it has a
> precision value of 10.

This seems to be a contradiction. In sql server, int columns don't have a
precision property. Only decimal/numeric columns (different terms for the
same datatype really) have precision and scale properties. So at this point,
I'm not sure about the real datatype of your column. From your description,
I suspect that it is numeric(10,0). All ambiguity would have been avoided if
you had included the CREATE TABLE statement for your table (including only
the columns that are relevant for your problem of course - nobody wants to
plow through a 50-column create table statement to find the definitions for
3 columns)

>
> When the user id is less than 10 digits long it fails i.e. when
> user_id = 145. When user_id is 10 digits long it works?!
>
> The problem is that all my user id's are at most 3 digits long and
> I'm sure I don't need to change all the user id's to be 10 characters
> long!
>
> I thought having a precision field of 10 meant that the value could
> be up to 10 digits long, not that it HAS to be 10 digits long!
>
That has been my experience (up to 10 digits). Does your column have a check
constraint on it to prevent shorter numbers (again, I would not have had to
ask this question if you had included the CREATE TABLE statement )?
I'm going to try to recreate your problem

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: SQL Insert Problem - Precision Invalid

am 21.05.2006 15:17:00 von reb01501