How to convert varchar to money in .NET

[GoingUp]

Im getting input for a price from a textbox and trying to put that into an SQL database. I keep getting this error.

Disallowed implicit conversion from data type varchar to data type money, table '424_A_04.dbo.Product', column 'Unit_price'. Use the CONVERT function to run this query. Disallowed implicit conversion from data type varchar to data type money, table '424_A_04.dbo.Product', column 'Purchase_price'. Use the CONVERT function to run this query.

Ive tried to run the conversion function to fix the problem but it doesnt seem to work.

What do I need to type in? Do I type the conversion directly into my SQL string that gets passed, or do I convert first and then insert the variable?

Here is what I currently have.

sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', '" & purprice & "', '" & unit & "', '" & qtystock & "', '" & note & "')"

 

[MrChad]

Have you tried removing the single quotes around your insert values?

sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', " & purprice & ", " & unit & ", '" & qtystock & "', '" & note & "')"

 

[WannaFly]

Originally posted by: MrChad
Have you tried removing the single quotes around your insert values?

sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', " & purprice & ", " & unit & ", '" & qtystock & "', '" & note & "')"

I believe single quotes are needed, the SQL should like like this, I believe:
sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', convert(money,'" & purprice & "'), '" & unit & "', '" & qtystock & "', '" & note & "')"

 

[MrChad]

Originally posted by: WannaFly

Originally posted by: MrChad
Have you tried removing the single quotes around your insert values?

sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', " & purprice & ", " & unit & ", '" & qtystock & "', '" & note & "')"

I believe single quotes are needed, the SQL should like like this, I believe:
sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "','" & pdesc & "', convert(money,'" & purprice & "'), '" & unit & "', '" & qtystock & "', '" & note & "')"

Isn't money just a derivative of the float data type? If it requires an explicit conversion for each insert, I would use a different data type all together. 😛

 

[skriefal]

Convert the string to a float or double using Convert.ToFloat() or Convert.ToDouble(), and then use that to insert to the database.

Oh... I also suggest using SqlCommand to create your SQL query, and specifying variables in the SQL query text that you use. i.e. "insert into product (product_no) values &product_no". And then adding the data to be inserted into the statement's parameter's list prior to executing the statement. There's an example of this shown here.

 

[GoingUp]

I got it figured out. Thanks everyone.

 

[HJB417]

had you used parameters, the sql data client would have converted the values automatically.
regardless, get in the habit of using parameters as string concatination is expensive in .net and ad-hoc queries can cause security problems.

 

[GoingUp]

Originally posted by: HJB417
had you used parameters, the sql data client would have converted the values automatically.
regardless, get in the habit of using parameters as string concatination is expensive in .net and ad-hoc queries can cause security problems.

you mean like this?

sql2 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES ('" & ID & "', '" & pname & "', '" & pdesc & "', CONVERT(money,'" & purprice & "'), CONVERT(money,'" & unit & "'), '" & qtystock & "', '" & note & "')"

 

[HJB417]

Originally posted by: Gobadgrs

Originally posted by: HJB417
had you used parameters, the sql data client would have converted the values automatically.
regardless, get in the habit of using parameters as string concatination is expensive in .net and ad-hoc queries can cause security problems.

no, like this.

sql3 = "INSERT INTO Product (Product_no, Name, Description, Purchase_price, Unit_price, Qty_in_stock, Notes) VALUES (@ID, @pname, @pdesc, @purprice, @unit, @qtystock, @note)"

but in your SqlCommand object (assuming you're using the sql client), you need to do

'sqlCmd is ur SqlCommand
sqlCmd.Parameters.Add("@ID", ID)
sqlCmd.Parameters.Add("@pname", pname)
sqlCmd.Parameters.Add("@purprice", purprice)
'etc.