Helpful Information
 
 
Category: MS SQL Development
string to smallmoney - sql2000

using SQL2000, table has a field defined as 'smallmoney'. This is being populated from a text file that has two digits after the decimal -> 56.50

I was trying to use cast and convert going in but no luck, as near as I can determine they only work on output.

So the question is : how can I get string data into smallmoney. Otherwise I have to redefined the field as a varchar or decimal

Comments?

Thanks
RK

How are you trying to insert rows? Have you tried an insert statement like this:
INSERT INTO table (
amount
) VALUES (
CONVERT(smallmoney, '21.23')
)

The above works just fine for me.

using this, which works with a varchar def:

Cmd.CommandText = "INSERT INTO [WBS].[dbo].[pludec38] ([PLU], [Dscrpt1], Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & Var3 & "');"

Cmd.Execute
----------------------------------------------------
using convert(smallmoney,var3) and a smallmoney field def

Cmd.CommandText = "INSERT INTO [WBS].[dbo].[pludec38] ([PLU], [Dscrpt1], Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & convert(smallmoney, Var3) & "');"

Cmd.Execute

produces an error that smallmoney is an undefined variable

Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & "convert(smallmoney, " & Var3 & ")"

tried this - produces an error, unclosed quotation mark, when closed it looks for an ending -> ;

when added it looks for an expected end of statement at the convert

You have an error building your SQL statement.


Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ('" & Var1 & " ','" & Var2 & "'," & "convert(smallmoney, " & Var3 & ")"


You're adding a quote before convert(smallmoney, that is causing the SQL error.

the unclosed quote was the single quote at the end, with that in place I'm back to the beginning

Cmd.CommandText = "INSERT INTO [WBS].[dbo].[pludec38] ([PLU], [Dscrpt1], Price) VALUES ('" & Var1 & " ','" & Var2 & "','" & " convert(smallmoney, " & Var3 & "');"

produces

disallowed implicit conversion from varchar to smallmoney

Use my statement instead of yours. You're enclosing the CONVERT statement in single quotes, that is what is causing the problem.

Duh, make that statement:


Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ('" & Var1 & " ','" & Var2 & "'," & "convert(smallmoney, '" & Var3 & "')"

taking the quote out as in

"','" & convert(smallmoney, " & Var3 & ") ';"

produces an undefined variable error on 'smallmoney'

Let's start over again. Please read my statements very carefully.

STOP PUTTING SEMICOLONS IN YOUR SQL STATEMENT
STOP PUTTING QUOTES AROUND CONVERT. Put them around the variable.

With that said, use this statement:


Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ('" & Var1 & " ','" & Var2 & "'," & "convert(smallmoney, '" & Var3 & "')"

ok
direct cut and paste of your code produces a different error

incorrect syntax near ')'

Duh, I forgot the end closing bracket. Sorry about that:


Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ('" & Var1 & " ','" & Var2 & "'," & "convert(smallmoney, '" & Var3 & "'))"

Excellent!! that works. I thank you very much for the help.

would you also suggest a reference on concatenating sql stmts with transformations like this. This is just the start of an extensive goup I have to do. What I am looking for is the general guidelines for what to enclose and with what.


Thanks again
RK

Normally, I would use placeholders in my sql statement. This avoids all the BS about quoting or not quoting variables.


Cmd.CommandText = "INSERT INTO WBS.dbo.pludec38 (PLU,Dscrpt1, Price) VALUES ( ?, ?, CONVERT(smallmoney, ?))"
With Cmd do
.Parameters.Refresh
.Parameters(0) = Var1
.Parameters(1) = Var2
.Parameters(3) = Var3
.Execute
End With


The advantages of this are:
1. You don't have to worry about quoting or not quoting, if the variable is a string, decimal, date or whatever. Simply let the ADO object take care of it as needed. Not only that, it will automatically escape quotes for you, if Var1, Var2 or Var3 have quotes within them.
2. If you have to execute the statement multiple times with different values, you don't need to set the CommandText property each time. Simply set the .Parameters property values and Execute again.

Note that I haven't actually tested out the code above, but I guess it should work. :)

the placeholder approach works nicely with an insert statement and I've tried it with an update statement but keep getting an error. This is the current code:

Var1 = strArray(1)
Var2 = strArray(2)
Var3 = (strArray(3)


Cmd.CommandText = "UPDATE WBS.dbo.pludec38 SET PLU = ?, SET Dscrpt1 = ?, SET Price = ?"

Cmd.Parameters.Refresh
Cmd.Parameters(0) = Var1
Cmd.Parameters(1) = Var2
Cmd.Parameters(2) = Var3

Cmd.Execute

The error occurs at or near the refresh stmt. What am I doing that is causing this? And what is the difference between this and the insert. The insert uses the parameters block without choking.
Thanks
RK

Your SQL statement is incorrect. Change it to

Cmd.CommandText = "UPDATE WBS.dbo.pludec38 SET PLU = ?, Dscrpt1 = ?, Price = ?"

BTW
Var3 = strArray(3)

not using multiple 'set' stmts makes a difference. The stmt doesn't choke and runs through to completion. However it uses the last value in the array for the updates. I thought this would update each individual record as it looped through. In one way I guess it is, an update for each loop - each record and then stops on the last value.

So I need to find an index value for a where clause.

Thanks for the help on this.

RK

Yep, without a WHERE clause, the SQL statement will update every record in the table.










privacy (GDPR)