MS SQL Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > MS SQL Development
Reload this Page

sql / asp help

Discuss sql / asp help in the MS SQL Development forum on Dev Shed.
sql / asp help MS SQL Development forum discussing administration, MS SQL queries, and other MS SQL-related topics. SQL Server is Microsoft's enterprise database engine.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Page 1 of 2 1 2 >
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old July 29th, 2004, 07:10 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
sql / asp help
I'm trying to do an insert statement from my asp page into my sql server database. I'm reading a value from a textbox and then trying to insert it. The value is of type smallmoney. The error I get is

Disallowed implicit conversion from data type varchar to data type smallmoney

So how do I convert the text in the box to smallmoney? Do I do it in asp or in sql server?

Thanks
Reply With Quote
  #2  
Old July 29th, 2004, 09:33 AM
brouse brouse is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Atlanta, GA
Posts: 115 brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 21 h 16 m 58 sec
Reputation Power: 9
Quote:
Originally Posted by sql asp
I'm trying to do an insert statement from my asp page into my sql server database. I'm reading a value from a textbox and then trying to insert it. The value is of type smallmoney. The error I get is

Disallowed implicit conversion from data type varchar to data type smallmoney

So how do I convert the text in the box to smallmoney? Do I do it in asp or in sql server?

Thanks


You can do it in SQL Server using the CAST function, such as

UPDATE MyTable
Set MoneyField = CAST('1234.56' to Money)
Reply With Quote
  #3  
Old July 29th, 2004, 09:46 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Or you can use CONVERT(smallmoney, 'varchar')
Reply With Quote
  #4  
Old July 29th, 2004, 09:47 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Thanks for you reply

I want to cast the input text from my text box to money... I've called the field "cost_price", so do I just do:

UPDATE Item
Set cost_price = CAST('cost_price' to smallmoney)

?

I've tried this and it doesnt work... I'm assuming the cost_price in ' ' should be digits... but I don't know what the value will be because it's stored in a variable from the users input.......

Please help!
Reply With Quote
  #5  
Old July 29th, 2004, 10:07 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
try
UPDATE Item
SET Cost_Price = CONVERT(smallmoney, 'cost_price')

I swear down this works....if cost price is a varchar...! I've used it a zillion times
Reply With Quote
  #6  
Old July 29th, 2004, 10:20 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Doesn't work

My problem is that cost_price is smallmoney in the database... but when I want to update it from a text field in asp, the input is varchar.....

Hence:
Disallowed implicit conversion from data type varchar to data type smallmoney.

I don't know if I can change the input type of the text field? I'm pretty new to all this so I don't know if the problem is with the db or with the asp page...
Reply With Quote
  #7  
Old July 29th, 2004, 10:30 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Very odd - its always worked with me - the user types in an amount to a textfield in the browser, its posted to your server side script, comes in as a string value. The database field is set to smallmoney, hence you use the CONVERT function to convert the varchar value to smallmoney, to prevent MSSQL trying to do it implicity.

I normally receive this error if I'm trying to stick a value of on datatype into a field of another datatype without manually converting it myself using CAST or CONVERT.

You can't change the input type of the text field, it will always return a string, but I suppose you could try casting it as a vbLong (or whatever its called, I use PHP/Perl) in ASP before you run the query.

Do you have MSSQL Enterprise manager?? If so you should have MSSQL Books On Line, have a look on this for CAST or CONVERT, it should help you further.
Reply With Quote
  #8  
Old July 29th, 2004, 02:37 PM
brouse brouse is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: Atlanta, GA
Posts: 115 brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level)brouse User rank is Corporal (100 - 500 Reputation Level) 
Time spent in forums: 21 h 16 m 58 sec
Reputation Power: 9
Quote:
Originally Posted by sql asp
Thanks for you reply

I want to cast the input text from my text box to money... I've called the field "cost_price", so do I just do:

UPDATE Item
Set cost_price = CAST('cost_price' to smallmoney)

?

I've tried this and it doesnt work... I'm assuming the cost_price in ' ' should be digits... but I don't know what the value will be because it's stored in a variable from the users input.......

Please help!


Are you looking for something like this?

Code:
Dim strMoneyVar
Dim strSQLString

strMoneyVar = Request.QueryString("Money")
' or - if using POST instead of GET
' strMoneyVar = Request.Form("Money")

strSQLString = "UPDATE Item Set cost_price = CAST('" & strMoneyVar & "' to smallmoney)"

objADOCMD.CommandText = strSQLString
objADOCMD.Execute
Reply With Quote
  #9  
Old July 30th, 2004, 03:11 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Hi, yeah I think thats what I'm looking for. However I've tried doing it like that and I still get the same error
Reply With Quote
  #10  
Old July 30th, 2004, 03:17 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
And it doesn't work with

Code:
strSQLString = "UPDATE Item Set cost_price = CONVERT(smallmoney, '" & strMoneyVar & "') WHERE Something=something"


either? ???
Reply With Quote
  #11  
Old July 30th, 2004, 03:28 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
No it doesn't! It looks as though it should!
Reply With Quote
  #12  
Old July 30th, 2004, 03:32 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Hang on - I'm going to replicate it here with asp and MSSql and see what happens - as I said, I normally use PHP or Perl and this has always been ok......
Reply With Quote
  #13  
Old July 30th, 2004, 04:05 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Sorry, I tried it here on my SQL Server using CONVERT and it works OK. I only wrote a very simple script, just one html page pointing to an asp script that connects to the database via ODBC and updates the smallmoney field with the teststring submitted via the textfield.

It then reads back the field and displays the data entered.

Heres the code for the asp page:
Code:
<%
	
	'Connect to the database
	
Session.timeout = 15
Set myConn = Server.CreateObject("ADODB.Connection")
myConn.open "Testing","user","password"
Set Session("MyDB_conn") = myConn

	Dim strMoneyVar
	Dim strSQLQuery
	Dim returnedMoney
	
	strMoneyVar = Request.Form("Money")
	strSQLQuery = "Update Testing SET Testing_Money=CONVERT(smallmoney, '" & strMoneyVar & "') WHERE Testing_Id=1"
	Set rs = Server.CreateObject("ADODB.Recordset")
	rs.Open strSQLQuery, myConn, 3, 3
	
	strSQLQuery = "SELECT TOP 1 Testing_Money FROM Testing ORDER BY Testing_Id DESC"
	rs.Open strSQLQuery, myConn, 3, 3
	returnedMoney = rs("Testing_Money")
%>
<html>
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>

<body>
<% Response.Write(returnedMoney) %>
<br>
<a href="test.htm">BACK </a> 
</body>
</html>
<%
myConn.close
set myConn = Nothing
%>


Hope this helps in some way....?!

Sorry - forgot to mention, this only works if you enter numbers in the correct format, you'd have to do some either client side (javascript) or server side checking to make sure the user enters data in the correct format before its posted.
Reply With Quote
  #14  
Old July 30th, 2004, 04:18 AM
sql asp sql asp is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jul 2004
Location: UK
Posts: 259 sql asp User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 4 h 19 m 51 sec
Reputation Power: 5
Thanks for that... below is what I have which doesn't work. It looks pretty similar to what you posted so I really don't know what I'm doing wrong! Do I need to insert the cost_price in a different insert statement?


Code:
<html>
<head>
<title>Form to database</title>
</head>
<body> 
<%
Dim cat_ref, descrip, date_added, cat_type, contract, cost_price, supplier_name, supplier_phone
Dim conn, SQLStmt

descrip = Request.Form("descrip")
date_added = Request.Form("date_added")
cat_type = Request.Form("cat_type")
contract = Request.Form("contract")
cost_price = Request.Form("cost_price")
supplier_name = Request.Form("supplier_name")
supplier_phone = Request.Form("supplier_phone")

SQLStmt = "INSERT into Item (descrip, date_added, cat_type, contract, cost_price, supplier_name, supplier_phone) VALUES ('" & _
descrip & "', '" & date_added & "', '" & cat_type & "', '" & contract & "', 'CONVERT(smallmoney,'" & cost_price & "')', '" & supplier_name & "', '" & supplier_phone & "')" 

Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=catalogue; UID=sa; PWD=Joanne12!"
conn.execute(SQLStmt)

response.write "The form information was inserted successfully."

conn.Close
Set conn = Nothing
%>
</body>
</html>
Reply With Quote
  #15  
Old July 30th, 2004, 04:27 AM
sochanik sochanik is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Aug 2001
Posts: 67 sochanik User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 8
Ahhh - the CONVERT function is an SQL function, it doesn't need to be included in quotes at the SQL level...., only string literals do, I imagine it'd be the same with CAST.

Try this

Code:
SQLStmt = "INSERT into Item (descrip, date_added, cat_type, contract, cost_price, supplier_name, supplier_phone) VALUES ('" & _
descrip & "', '" & date_added & "', '" & cat_type & "', '" & contract & "', CONVERT(smallmoney,'" & cost_price & "'), '" & supplier_name & "', '" & supplier_phone & "')"


(nb: I removed the ' ' from around the CONVERT(smallmoney,'" & cost_price & "') )
Reply With Quote
Reply
Page 1 of 2 1 2 >

Viewing: Dev Shed Forums > Databases > MS SQL Development > sql / asp help

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump