SQL Insert with money datatype problems

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

SQL Insert with money datatype problems

Author

Topic

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-04 : 19:57:32

Hey all, i was reading a few posts but found no help. Here's the problem;

I have a stored procedure which inserts a money value. I get an error everytime i run it thru an asp page passing parameters. The error is:

Implicit conversion from data type char to money is not allowed. Use the CONVERT function to run this query.

CREATE procedure proc_AddCar
	@usrDescription varchar (255),
	@usrPrice money
as
begin transaction
	insert [DealershipModel]
		(
		DealershipModelDescription,
		DealershipModelPrice
		)
	values
		(
		@usrDescription,
		@usrPrice
		)
	if @@error <> 0
		begin
			rollback tran
			return(1)
		end
commit transaction

return(0)
GO

I read up on this the added a few statements to the sql sp and I get a new error:

Cannot convert a char value to money. The char value has incorrect syntax.

CREATE procedure proc_AddCar
	@usrDescription varchar (255),
	@usrPrice varchar(255)
as
begin transaction
	insert [DealershipModel]
		(
		DealershipModelDescription,
		DealershipModelPrice
		)
	values
		(
		@usrDescription,
		cast(@usrPrice as money)
		)
	if @@error <> 0
		begin
			rollback tran
			return(1)
		end
commit transaction

return(0)
GO

Here is how I pass the parameters thru the .asp page:

		Form_Description = "test"
		Form_Price = 36.99
		
		dbc.BeginTrans
		
		Set cmd = Server.CreateObject("ADODB.Command")
		cmd.ActiveConnection = dbc
		cmd.CommandText = "proc_AddCar"
		cmd.CommandType = adCmdStoredProc
		
		z = 0
		z = z + 1
		p = "param" & z
		Set p = cmd.CreateParameter("usrDescription", adChar, adParamInput, Len(Form_Description), Form_Description)
		cmd.Parameters.Append(p)
		
		z = z + 1
		p = "param" & z
		Set p = cmd.CreateParameter("usrPrice", adCurrency, adParamInput, Len(Form_Price), Form_Price)
		cmd.Parameters.Append(p)
		
		Set rs = Server.CreateObject("ADODB.Recordset")
		Set rs = cmd.Execute
		
		If dbc.errors.count = 0 Then
			dbc.CommitTrans
		Else
			dbc.RollbackTrans
		End If

Any help would be greatly appreciated. Thanks.

- RoLY roLLs

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 21:30:18

On the first query:

EXEC proc_AddCar 'test 1','32.55'
--This produces your error.
EXEC proc_AddCar 'test 1',32.55
--This does not.

I can't reproduce the error on the second query though. I created a table like this:

CREATE TABLE DealershipModel(
DealershipModelDescription VARCHAR(255),
DealershipModelPrice MONEY)

Can you:
1. Actually capture the string you are sending to SQL Server and give that to us.

2. Give us the ddl(create statement) for your table.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-04 : 22:56:47

The second parameters of teh SP is a varchar but you are passing a money.
In general if this sort of thing happens there are a few things to check.

You should have a database access layer in the client code in which you can turn tracing on and off. When on it should write all database calls to a text file - then you can see the parameters being passed.
Try using the profiler to view the call made to the database - then run it in query analyser.
Put trace code in the SP. I have a trace table
create table testtrace (s varchar(1000), id int identity, d datetime default getdate())

then at the top of the sp

insert testtrace (s) select '<proc_AddCar><@usrDescription=' + @usrDescription + '><@usrPrice=' + @usrPrice + '>'

You will have to take the transaction out of the asp code or halt it before the rollback to view the trace.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-04 : 23:01:47

here is the create table code:

CREATE TABLE [DealershipModel] (
	[DealershipModelDescription] [varchar] (255) NOT NULL ,
	[DealershipModelPrice] [money] NOT NULL 
) ON [PRIMARY]
GO

And how do i "capture the string you are sending"

- RoLY roLLs

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-04-04 : 23:14:43

By doing what Nigel just said. :)

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-04 : 23:17:06

Ok i changed the line passing the money value from "adCurrency" to "adChar" and the 2nd sp version works but I also have a few other fields in this table, which i shortened out in this forum, and then I got another error on converting a char to a bit passing it as "adBoolean" and in the sp as @usrAvailable bit. I changed it from "adBoolean" to "adChar" and did the cast part on the sp. So now I have a question as far as how I pass the values thru the asp page.

This line apparently passes the value as a char:

		Set p = cmd.CreateParameter("usrPrice", adCurrency, adParamInput, Len(Form_Price), Form_Price)
		cmd.Parameters.Append(p)

as well does this:

		Form_Available = 1
		
		Set p = cmd.CreateParameter("usrAvailable", adBoolean, adParamInput, Len(Form_Available), Form_Available)
		cmd.Parameters.Append(p)

Got any ideas?

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-04 : 23:43:08

Those will create currency and boolean parameters They will probably be converted to character by the server if implicit convertion is available to match the datatype of the parameter. You may end up with a binary value in the character parameter though - depends on the interaction of the provider with the server

It's always a good idea to match the paramter datatypes.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-05 : 00:01:35

well if what you mean by "depends on the interaction of the provider with the server" is how I connect to the server, here is my onnection string:

		strConn = "Provider=SQLOLEDB;Data Source=127.0.0.1;User Id=my_user_name;Password=xxx;Initial Catalog=my_db_name;"
		Set dbc = Server.CreateObject("ADODB.Connection")
		dbc.open strConn

Also, I do pass all my parameters as the datatype they are on the database. I use SQL 2000 (think that's 7.0 right?)

Well if I must use this 'cast' function and pass all the variables as chars and convert them at the sp level, that would suck. What am I missing to make all this work correctly?

If you have any questions of any kind, feel free to ask, I just need this to work. I am in the process of converting 230+ Access queries SQL SP so I want to figure the best way to work with SPs before I get too far in teh conversion process.

Thanks!

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-05 : 07:47:02

Sorry I saw in the original queston you had the two parameters as varchars in the SP - didn't notice in the other version you had it as money.

I've tried this in VB and it worls happily - try this code in asp. Just put server. before the creteobject's and change the database connection string.

Set dbc = CreateObject("ADODB.Connection")
dbc.ConnectionString = "Provider=sqloledb;" & _
"server=(local);uid=sa;pwd=pwd;database=test"

dbc.CursorLocation = adUseClient
dbc.Open

Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = dbc
cmd.CommandText = "x"
cmd.CommandType = adCmdStoredProc

Set p = cmd.CreateParameter("usrDescription", adChar, adParamInput, 5, "hello")
cmd.Parameters.Append (p)

Set p = cmd.CreateParameter("usrPrice", adCurrency, adParamInput, 0, 1324.12)
cmd.Parameters.Append (p)

Set p = cmd.CreateParameter("usrPrice", adBoolean, adParamInput, 0, 1)
cmd.Parameters.Append (p)

Set rs = CreateObject("ADODB.Recordset")
Set rs = cmd.Execute

CREATE TABLE [DealershipModel] (
s [varchar] (10) NOT NULL ,
m money NOT NULL ,
b bit null
) ON [PRIMARY]

create proc x
@a varchar(10) ,
@b money ,
@c bit
as
insert DealershipModel
select @a, @b, @c
go

select * from DealershipModel

s m b
--------- --------------------- ----
hello 1324.1200 1

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-05 : 10:25:28

Well I think I kinda wasted everyone's time here, I just found my mistake after I tried yours and it worked and I was about to just paste everything I got to see if anyone finds the problem.

The problem was that I was trying to test something out. I was passing 21 variables, and i wanted to test whether or not I could pass the variables in the order I have them in the procedure (ie: in the sp I had desc then price, I tried passing the price then the procedure) and see if the variable was matched to the name, for example:

On this line:

Set p = cmd.CreateParameter("usrDescription", adChar, adParamInput, Len(Form_Description), Form_Description)

what is the bolded part for, if it doesn't match it in the sp?

Do I have to pass them in the order I have them in the sp? or am I missing something on the line above? (maybe it should be "@usrDescription" instead. I'm not sure, I noticed in access you have to pass them in order, but I was testin to see if the same applies to MSSQL)

Please let me know. Thanks.

One more thing...what's the big difference between a stored procedure and a user-defined function? After converting an access db thru adp, it made some of the queries into user-defined functions. Any big difference/advantages? or are they samll difference/advantages? Thanks again!

- RoLY roLLs

nr
SQLTeam MVY

12543 Posts

Posted - 2004-04-05 : 23:25:25

Yes you have to pass them in the order in the SP. The parameter name is ignored as far as the SP interface is concerned (not so in .net).

SPs / UDFs - usually they are used for different purposes so there is no confusion over which to use - but if there is a choice an SP is more flexible and usually more efficient.
I'm guessing that some subqueries in access have been turned into table valued functions. Look at using derived tables, table variables or temp tables instead.
If it's just access functions that have been turned into UDFs then try to code them in t-sql.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

RoLYroLLs
Constraint Violating Yak Guru

255 Posts

Posted - 2004-04-05 : 23:29:33

Thanks nr. You've been great help. Yeah i like SP better than UDF's, so never used them. Anyhow. thanks for info as far as .net is concerned and the variable passing. good to know. I'd like to say see you later, but not in the way where i run into another prob :) take care.

- RoLY roLLs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources