SQL Insert Help

[Dot Net Daddy]

Hello,

I cannot get the following Insert Command work. I get the error:


Error converting data type varchar to numeric.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.

Exception Details: System.Data.Sql Client.SqlExcep tion: Error converting
data type varchar to numeric.

However 'rate' and 'maximum' variables are declared as Decimal

Dim rate As Decimal
Dim maximumAs Decimal

SqlDataSource1. InsertCommand = "INSERT INTO Example(userNam e, rate,
maximum, ticket) VALUES('blabla' ,'" & rate & "','" & maximum & "','" &
RadioButtonList 1.SelectedValue & "')"

SqlDataSource1. Insert()



CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)

[David Portas]

Re: SQL Insert Help

Dot Net Daddy wrote:

Hello,
>
I cannot get the following Insert Command work. I get the error:
>
>
Error converting data type varchar to numeric.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
>
Exception Details: System.Data.Sql Client.SqlExcep tion: Error converting
data type varchar to numeric.
>
However 'rate' and 'maximum' variables are declared as Decimal
>
Dim rate As Decimal
Dim maximumAs Decimal
>
SqlDataSource1. InsertCommand = "INSERT INTO Example(userNam e, rate,
maximum, ticket) VALUES('blabla' ,'" & rate & "','" & maximum & "','" &
RadioButtonList 1.SelectedValue & "')"
>
SqlDataSource1. Insert()
>
>
>
CREATE TABLE Example(
userName nvarchar(50),
rate decimal(2, 2),
maximum decimal(6, 2),
ticket nchar(1)
)

Constructing dynamic SQL strings using parameters is a careless and
inefficient practice. If the parameters are based on user-supplied
input then your code is also potentially unreliable and dangerous.

If you really must put SQL INSERT statements in your application then
use the InsertParameter s collection to pass the values. That way you
can avoid any unexpected type conversions. Better still, if your data
source is SQL Server only then use a stored procedure and use a
parameters collection to pass your values into the proc rather than the
INSERT statement. Your code will be much easier to manage and maintain
if you use procs.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:

http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx

--

[Erland Sommarskog]

Re: SQL Insert Help

Dot Net Daddy (cagriandac@gma il.com) writes:

I cannot get the following Insert Command work. I get the error:
>
>
Error converting data type varchar to numeric.
Description: An unhandled exception occurred during the execution of
the current web request. Please review the stack trace for more
information about the error and where it originated in the code.
>
Exception Details: System.Data.Sql Client.SqlExcep tion: Error converting
data type varchar to numeric.
>
However 'rate' and 'maximum' variables are declared as Decimal

In VB, yes. SQL does not read VB declarations.

Dim rate As Decimal
Dim maximumAs Decimal
>
SqlDataSource1. InsertCommand = "INSERT INTO Example(userNam e, rate,
maximum, ticket) VALUES('blabla' ,'" & rate & "','" & maximum & "','" &
RadioButtonList 1.SelectedValue & "')"

In you constructed SQL command, you put the values of rate and
maximum in single quotes. Why would you express decimal values as
character literals?

Anyway, the correct way to do this is to use parameterised command,
just as David said. I have an example at
http://www.sommarskog.se/dynamic_sql.html#queryplans. (You need to
scroll a bit down to get the VB .Net example.)



--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server 2005 at

http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

Books Online for SQL Server 2000 at

http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx