I am trying to insert into sql server2000 a field from a form that is
of type varchar, and insert into a table which has the field defined as
smallmoney length 4.  I have tried a number of variations based upon
examples from the web, but none have worked in my case so far.  I
understand that I need to change from varchar to currrency or a
numeric.  Here is the error along with the sql statement that I  was
submitting and then my code listed below :  The field is Est_Unit_Price
which is giving me the problem.


#@!$%#! SQL Error: [Microsoft][ODBC SQL Server Driver][SQL Server]Error
converting data type varchar to numeric.-2147217913

Error on adding record number 1

insert into Item_Listing
(Requester,Date_Purch_Req,Item_Number,OEM_Spec,Prd_Spec,Part,Description,Vendor,Qty,JONO,RDD,Urgent,Est_Unit_Price,Adm_Fee)
values ('Auborn,John, ','9/6/2006,
','1','','','2','3','4','56','3','2','',CONVERT(smallmoney,
'33.33'),'')




code:


for i = 1 to 20


if Request.Form("Qty" & i) <> "" then

sql = "insert into Item_Listing ("
sql = sql & "Requester,"
sql = sql & "Date_Purch_Req,"
sql = sql & "Item_Number,"
sql = sql & "OEM_Spec,"
sql = sql & "Prd_Spec,"
sql = sql & "Part,"
sql = sql & "Description,"
sql = sql & "Vendor,"
sql = sql & "Qty,"
sql = sql & "JONO,"
sql = sql & "RDD,"
sql = sql & "Urgent,"
sql = sql & "Est_Unit_Price,"
sql = sql & "Adm_Fee)"

Dim var1
var1 = Request.Form("Est_Unit_Price" & i)
var1 = CCur(var1)


sql = sql & " values ("
sql = sql & "'" & Request.Form("requester") & "',"
sql = sql & "'" & Request.Form("date_purch_req") & "',"
sql = sql & "'" & Request.Form("Item_Number" & i) & "',"
sql = sql & "'" & Request.Form("OEM_Spec" & i) & "',"
sql = sql & "'" & Request.Form("Prd_Spec" & i) & "',"
sql = sql & "'" & Request.Form("Part" & i) & "',"
sql = sql & "'" & Request.Form("Description" & i) & "',"
sql = sql & "'" & Request.Form("Vendor" & i) & "',"
sql = sql & "'" & Request.Form("Qty" & i) & "',"
sql = sql & "'" & Request.Form("JONO" & i) & "',"
sql = sql & "'" & Request.Form("RDD" & i) & "',"
sql = sql & "'" & Request.Form("Urgent" & i) & "',"
sql = sql & "" & "CONVERT(smallmoney, '"& var1 & "'),"
sql = sql & "'" & Request.Form("Admin_fees") & "')"

MyConn.Execute(sql)

If Err.Number <> 0 Then
    Response.Clear
    Response.Write "<p>#@!$%#! SQL Error:  " & Err.Description &
Err.number & " </p>"
    Response.Write (sql)
    Response.End
End If

end if

Next



I have tried using the CCur() and CDbl() functions in VB, but they dont
seem to work.  I am using the CONVERT in the sql statement, but that is
not working either.

Any help is much appreciated.

David Bowman

This is useless to us.

How about showing the result of Response.Write (sql) ?

Also, the table structure would be helpful, so we know which columns are of
numeric datatypes.

To get a more precise error message and to avoid datatype conversion
problems, you should consider using stored procedures with parameters.

The issue is that you are attempting to pass in a function as a piece of
data. That is not allowed.

Declare a smallmoney variable, convert and assign the varchar() value to the
variable, and then include the variable in the insert statement.

Show quoteHide quote

Arnie Rowland wrote:
Actually if you mean that something like this:

INSERT INTO some_table ( some_integer_column )
VALUES ( CONVERT( INT, '123' ) )

is incorrect, then you are wrong.

Using expressions (including use of functions) is allowed in DML satements
(INSERT, UPDATE, DELETE), and in many cases encouraged. Eg. when you
insert values in DATETIME field you SHOULD use:
CONVERT( DATETIME, <some_value>, <format_specifier> )
because not using it may cause unpredictable results when current
date format will differ (change) from what you expect. (It's also
useful when you retrieve DATETIME data from DB if there's no translation
layer like ADO in between, and you get the dates in text).


Kamil 'Hilarion' Nowicki

My bad, reply is confused with another posting, please ignore.

Show quoteHide quote

What about Adm_Fee?  What about all of the other columns for which you are
supplying an empty string?  And why are you supplying empty strings for what
appear to be non-character columns?

set nocount on
select cast ('' as decimal(5,3)) as x
go
select cast ('0' as decimal(5,3)) as x
go
select CONVERT(smallmoney, '33.33') as x
go

dbow***@sdi-inc.com wrote:
Show quoteHide quoteYou probably incorrectly assumed that the problem is with the "Est_Unit_Price"
only, but you probably have problems with much more fields. Eg. if "Date_Purch_Req"
is some datetime typed field, then '9/6/2006,' would not convert properly
(see the comma at the end of the date but still inside the single quotes;
you also should ALWAYS use CONVERT with proper format indication when you
specify dates), also if "Adm_Fee" field is a numeric (or datetime) field,
then you should not use empty string, as it may get converted to zero
and/or cause errors (use "NULL" instead of empty string if you do not want
to specify the value).

As Aaron Bertrand allready wrote: post the structure of "Item_Listing"
(all fields with their data types and constraints). It's the only way
we could see all the possible causes of your problem.

Kamil 'Hilarion' Nowicki

It appears that my question has raised even more questions!!!  I will
try to provide further information below:

I am using an ASP file with the language set to VBScript.  I have
created a form within with fields to populate the data that I want to
send to the SQL table.  The form is using the POST method, and I am
using server side script along with SQL to handle the data and get it
into the SQL table.

Sql Server Table with fields,types, etc

column name          data type          length          allow nulls
Requester                char                50                NO
date_purch_req         datetime          8                 NO
item_number            int                    4                 NO
oem_spec                bit                    1                 YES
prd_spec                  bit                    1                 YES
part                          char                20                YES
description               char                300                YES
vendor                     char                75                YES
qty                         smallint            2                  YES
jono                        char                 10                 YES
rdd                          char                 10
YES
urgent                      bit                   1
YES
est_unit_price           smallmoney      4                   YES
adm_fee                    numeric             9                  YES




After going through everyone's responses, I believe that I have found
the problem(s).   I believe the datefield had a problem for number 1.
There was also a problem with the other numeric fields having no data
in the sql statement for insertion.

I appreciate everyone's help and fast responses.  I will do better in
the future by providing more information when I post a problem.

Thanks!

Show quote Hide quote In that case the insert statement should look like this:

INSERT INTO Item_Listing (
  Requester, Date_Purch_Req, Item_Number,
  OEM_Spec, Prd_Spec, Part, Description, Vendor, Qty, JONO, RDD,
  Urgent, Est_Unit_Price, Adm_Fee
)
VALUES (
  'Auborn,John, ', CONVERT( datetime, '9/6/2006', 101 ), 1,
  NULL, NULL, '2', '3', '4', 56, '3', '2',
  NULL, 33.33, NULL
)

In general when you insert data of this type:

- CHAR, VARCHAR, TEXT
  Then you have to enclose the text into single-quotes,
  like 'Auborn,John' and remember that when the text
  contains single quotes then you have to double it
  (if you don't then SQL Server will treat it as the
  end of the text) like: 'John''s grandma'
  Remember that CHAR and VARCHAR have specified size
  and you'll not be able to store longer texts than
  that range (unless you change field definitions).
  In case of CHAR it's also important to remember
  that if you give a value that is shorter that
  specified field size, then SQL server will add
  trailing spaces to fill the specified size.

- NCHAR, NVARCHAR, NTEXT
  Like above, but you have to precede the opening
  signle quote with letter "N" (uppercase):
  N'Auborn,John'

In both cases above empty string (eg. '') is different
than NULL (you do not enclose NULL value in single
quotes because it'd made it a text, not a NULL value).

- BIGINT, INT, SMALLINT, TINYINT
  Then you do not have to (and even should not) enclose
  them with anything, eg. you write 123, not '123'.
  When you want to leave the field empty, you use NULL
  value (not ''). Remember that those types have
  limited range and do not

- BIT
  As with INT, you simply use 1 or 0 (or NULL).

- DECIMAL, NUMERIC
  Almost same as INT, but you also give decimal part
  after a dot (it can't be comma nor anything else
  regardless of any regional settings) - no quotes.
  Eg.: 123, 123.45, 0.23, 123.0
  If you enclose them in single quotes (which is
  not recommended as with INT), then you should
  use decimal separator as defined in session
  properties (usualy as set in gerional settings
  or as defined in client application).
  Remember that range of values storable is
  defined by precision and scale. The first one
  specifies how many digits can the number have
  and the second one how many can be after decimal
  separator (dot). Eg. precision 5 and scale 2
  means that 123.23 will fit, but 2.345 or 123456
  will not (the first one will be automagically
  rounded to 2.35).

- MONEY, SMALLMONEY
  Those work almost as DECIMAL (or NUMERIC) with
  "precision" and "scale" built in, but they lack
  some real precision (eg. some arithmetic operations
  on those can give much less acurate results than
  DECIMAL). When you specify money values, you
  can use the same notation as with DECIMAL and
  optionaly can add currency symbol before the
  number (without space characters in between) - it
  does not affect the value in any way and is not
  stored.

- FLOAT, REAL
  They are called approximate numerics not without
  a reason. The value stored in the database depends
  on the given value and range of the type (FLOAT
  has bigger range than REAL). The numbers have
  to be very big (or small) to not fit into FLOAT,
  but still will probaby not be stored precisely.
  You give values for FLOAT (and REAL) fields the
  same way as for DECIMAL.

- DATETIME, SMALLDATETIME
  Even if specifying date simply as '9/6/2006'
  will sometimes (and even maybe almost all times)
  work OK, still this way of giving date values
  should not be used. Whenever it's possible
  you should use CONVERT function to inform
  the SQL server of the date format you used.
  Eg. CONVERT( DATETIME, '9/6/2006', 101 ) or
  CONVERT( DATETIME, '2006-09-06', 121 ).
  Remember that both those types also store
  time info (even if you do not give it - in
  that case it's '00:00:00.000'), so be careful
  when comparing dates and taking daylight
  saving time in count.
  There's a little problem when you want to
  specify a date value as an input parameter
  for a procedure - you can't use expressions,
  so can't use CONVERT "inline" - you have
  to use DATETIME variable, set it's value
  using CONVERT and pass that variable as
  the input parameter instead.

Also one general rule:
If the data comes from a user (eg. is typed
into WWW form or some desktop app) and you
are not totally and absolutely sure that
this data is 100% valid (eg. the application
does not do full validation), you should
not use dynamic SQL (eg. build "INSERT"
command with values in it), but parameterized
execution (or at least do a very very very
careful escaping). Usually this means that
you should use this way all times, because
you can't be sure if the validation process
of the app is 100% bugless and one little
bug can be used to perform SQL injection
attacks. Parameterized queries are also
usually at least a tiny little bit (and in
some cases a very big bit) faster that their
dynamic versions.


Kamil 'Hilarion' Nowicki

working with gaps in date series
How to get table data ?
updating data from different table
problems sending email from a trigger
Update Query in SQL 2005 with inner join