Problem with SQL 'INSERT INTO' statement 1

[Greggie]

The 'insert' SQL string below doesn't seem to be accepted. I orginally was getting an error message that I should use the CONVERT function to give explicit conversion to the Money format in the 'Price' fields below. I then tried using the CAST function to convert these values. The values below actually come from a Form on a preceding web page where the User inputs a price into a text field.

Then I take the value entered by the user and use string processing to add "+$" to the number. The result is the SQL string below. I've actually been able to successfully insert rows into SQL databases but I've never had a SQL database with Money or Date formats.

conn.execute "INSERT INTO tblPricingSchedule (PricingIndex, PackCode, Price1Month,
Price2Months, Price3Months, Price6Months, DateChanged, CurrentPrice) VALUES
('1', 'PC1', CAST(+$19.95 As MONEY), CAST(+$35.9 As MONEY), CAST(+$50.85 As
MONEY), CAST(+$96 As MONEY), '6/25/2001', 'TRUE')"

Format of the fields in the Table called "tblPricingSchedule"

PricingIndex -Number
PackCode -Text
Price1Month -Money
Price2Months -Money
Price3Months -Money
Price6Months -Money
DateChanged -Date
CurrentPrice -Yes/No

Originally, the dabase was created in Access 97 then upsized to SQL Server. The format of the Price fields above was Currency in Access 97 but I think this has been changed to "Money" in SQL Server as I'm getting a message that I need to use the CONVERT function to have the values explicitly converted to the Money format.

Also, I'm not sure about the Date format, that is, if I have the value I want to enter into the 'DateChanged' field in the correct format eg '6/25/2001'.

Note, I haven't put the CAST statements in fixed quotes eg 'CAST(etc...)'

Also, I'm trying to enter 'TRUE' into the CurrentPrice field which was "yes/no" format in Access97 but I'm not sure if this should be 'YES' or '-1' or simply 'Y' when I'm trying to enter it into the SQL Server database.

I have successfully used the INSERT INTO statement for other tables in the database but none of the tables I've used have had fields in the Money, Date or Yes/No format.

Basically, I'm just trying to see if there is something inherently wrong with the INSERT INTO statement above.

 

[rajeevnandanmishra]

Hi there,
I don't think you have to use convert/cast function in your case. Because SQL Server automatically do conversion from
1. INT/FLOAT/REAL TO MONEY
2. Charater Date Literals TO DATE

And Yes/No field is supported by BIT datatype which accepts 1 or 0. Moreover about the datatypes of coloumns of a table you can issue following commands :
SP_HELP myTableName

I think the following string query will execute successfully.
--------------------------
INSERT INTO tblPricingSchedule (PricingIndex, PackCode, Price1Month,
Price2Months, Price3Months, Price6Months, DateChanged, CurrentPrice) VALUES
('1', 'PC1', 19.95, 35.9, 50.85, 96, '6/25/2001',1)"
--------------------------

Hope this will move you in right direction.

 

[tlbroadbent]

I agree with rajeevnandanmishra who explained things very well. I would add one small change. PricingIndex is a number (integer?) so you need to remove the quotes around the value to be inserted or the insert will fail.

INSERT INTO tblPricingSchedule (PricingIndex, PackCode, Price1Month, Price2Months, Price3Months, Price6Months, DateChanged, CurrentPrice)
VALUES (1, 'PC1', 19.95, 35.9, 50.85, 96, '6/25/2001',1)
Terry

http://members.home.net/tlbroadbent/sqlarticles.htm

"I'm not dumb. I just have a command of thoroughly useless information." - Calvin, of Calvin and Hobbes

 

[rajeevnandanmishra]

Thanks Terry, I missed that.

 

[Greggie]

Rajeevnandanmishra ,
Thanks very much. I really appreciate it. Your suggested string worked perfectly. Now I know the correct string to use I can go back and reconstruct the concatenated string I use to put the string together on the fly with values entered by the user in an ASP page.

Thanks also tlbroadbent.

-Greggie