Problem with Insert Statement...

[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

 

[SStory]

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

 

[Manuel Canas]

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.

 

[Cor Ligthert]

Hi Manuel,

Have a look at this and look than as well somewhere further to the different
contstructions of the parameters.

http://msdn.microsoft.com/library/d...ientsqldataadapterclassinsertcommandtopic.asp

On this pages they are more difficult than strictly needed.

http://msdn.microsoft.com/library/d...systemdatasqlclientsqlparameterclasstopic.asp

I hope this helps?

Cor

 

[SStory]

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

 

[Manuel Canas]

Thanks a bunch Shane for your help.

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

 

[SStory]

No problem.

Thanks a bunch Shane for your help.

Manny.

way