Problem with Insert Statement...

  • Thread starter Thread starter Manuel Canas
  • Start date Start date
M

Manuel Canas

Hello there,

This is my SQL Insert Statement to insert a single into a table on a
database;

INSERT tb_test VALUES(' & _
txtTest1.Text & "' " & _
txtTest2.Text & "' " & _
txtPrice.Text & "')"

Now when I execute this code against the data provider I got and error
saying that I should use the Convert Function on the column "Price", because
of course, the Price on the SQL MSDE is of money type.

I have tried this to try to convert from varchar to money, but no luck.
Dim us As New CultureInfo("en-US")
CType((txtPrice.Text), Decimal).ToString("c", us)

Anybody out there to guide me or give a hint on how to solve this issue?

Thanks very much for your help on this one.

Manny
 
Manuel,

You need to use a SQLCommand class and not build the strings this way to
avoid all sorts of issues.

And when creating SQLParameters to add to that command class you can set the
dbtype to money and it will work fine.

HTH

Shane
 
Hi there, Thanks for replying to my post.

I am using a SQLCommand this is the complete code;
strSQL = "INSERT tb_product VALUES ('" & _
txtServiceCode.Text & "', '" & _

txtServiceName.Text & "', '" & _

txtPrice.Text & "')"

cnSQL = New SqlConnection(ConnectionString)

cnSQL.Open()

cmSQL = New SqlCommand(strSQL, cnSQL)

cmSQL.ExecuteNonQuery()

It fails right on the last line here.

When you say that SQLparameters are better to accomplish this, could you
extend on that?

Thanks for your help on this one,

Manny.
 
Well set the commandstring to
"INSERT tb_produce VALUES(@Servicecode,@ServiceName,@Price)
the @Price is symbolic that this is a parameter.
after you craete
Dim cmdSQL As New System.Data.SqlClient.SqlCommand(strSQL, cnSQL)
Add parameters like so... (I will show the one for price since it has money
type... you must do the other also)
Dim p As New System.Data.SqlClient.SqlParameter("@Price", txtPrice.Text)
p.DbType = System.Data.SqlDbType.Money
s.Parameters.Add(p)

now your command object will have the parameters, their values and types and
you should have no problems, don't have to worry about user string
containnig ' or injected SQL commands.

HTH,

Shane
 
Thanks a bunch Shane for your help.

Manny.

SStory said:
Well set the commandstring to
"INSERT tb_produce VALUES(@Servicecode,@ServiceName,@Price)
the @Price is symbolic that this is a parameter.
after you craete
Dim cmdSQL As New System.Data.SqlClient.SqlCommand(strSQL, cnSQL)
Add parameters like so... (I will show the one for price since it has money
type... you must do the other also)
Dim p As New System.Data.SqlClient.SqlParameter("@Price", txtPrice.Text)
p.DbType = System.Data.SqlDbType.Money
s.Parameters.Add(p)

now your command object will have the parameters, their values and types and
you should have no problems, don't have to worry about user string
containnig ' or injected SQL commands.

HTH,

Shane
Click to expand...
 
Back
Top