Trying to INSERT into a table

Problem with varchar vs int

Page 1 of 1

3 Replies - 664 Views - Last Post: 18 April 2010 - 10:44 AM Rate Topic: -----

#1 heady89  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 164
  • Joined: 31-December 09

Trying to INSERT into a table

Posted 18 April 2010 - 09:36 AM

Hello,

I am applying some SQL tests to my database and i've encountered an error.
When i try to run this syntax;

INSERT INTO Artikel (Artnamn, Antal, Pris, Rabatt)
VALUES ('Bildskärm-platt 10ms', '45', '2175,00', '0,00'),
('Tangentbord', '37', '280,00', '0,00'),
('Nätkabel TP kat 5', '1000', '2,50', '0,00')



I get an errormessage telling me the following:
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.


The table is formed such that:
Artnamn(Product) = Varchar(30)
Antal(Amount) = (int)
Pris(Price) = (decimal 6,2)
Rabatt(Discount) = (decimal 2,2)


I am still a beginner at this i've been and i am only at my 3rd week into this course.
Would appreciated any tips or hints very much, i don't understand why it want to convert my string in the first place.
I just wanna enter the product name :S

This post has been edited by heady89: 18 April 2010 - 09:37 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Trying to INSERT into a table

#2 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Trying to INSERT into a table

Posted 18 April 2010 - 10:16 AM

When you define a field as numeric (like Antal being of type integer) you must remember not to use single quotes. Single quotes tell the database that you are trying to enter string data (varchar or text). So for any field that is integer, float, decimal etc you should not have single quotes around your data.

I see you are using a different language there so keep in mind that while your language may use commas to represent decimal points, SQL sees commas as separating values. So you may need to use 0.00 instead of 0,00. Because to SQL 0,00 is really two field values... 0 and 00.

Try something like this...

INSERT INTO Artikel (Artnamn, Antal, Pris, Rabatt)
VALUES ('Bildskärm-platt 10ms', 45, 2175.00, 0.00)



Notice how the only value with single quotes is the first one which is going into a varchar field so it is string data.

So keep that in mind and it should fix up your query. :)

This post has been edited by Martyr2: 18 April 2010 - 10:19 AM

Was This Post Helpful? 1
  • +
  • -

#3 heady89  Icon User is offline

  • D.I.C Head

Reputation: 5
  • View blog
  • Posts: 164
  • Joined: 31-December 09

Re: Trying to INSERT into a table

Posted 18 April 2010 - 10:30 AM

View PostMartyr2, on 18 April 2010 - 09:16 AM, said:

When you define a field as numeric (like Antal being of type integer) you must remember not to use single quotes. Single quotes tell the database that you are trying to enter string data (varchar or text). So for any field that is integer, float, decimal etc you should not have single quotes around your data.

I see you are using a different language there so keep in mind that while your language may use commas to represent decimal points, SQL sees commas as separating values. So you may need to use 0.00 instead of 0,00. Because to SQL 0,00 is really two field values... 0 and 00.

Try something like this...

INSERT INTO Artikel (Artnamn, Antal, Pris, Rabatt)
VALUES ('Bildskärm-platt 10ms', 45, 2175.00, 0.00)



Notice how the only value with single quotes is the first one which is going into a varchar field so it is string data.

So keep that in mind and it should fix up your query. :)


Thanks very much m8 :)
It solved my problem.
Was This Post Helpful? 0
  • +
  • -

#4 Martyr2  Icon User is offline

  • Programming Theoretician
  • member icon

Reputation: 4337
  • View blog
  • Posts: 12,137
  • Joined: 18-April 07

Re: Trying to INSERT into a table

Posted 18 April 2010 - 10:44 AM

Glad I could help. Just another tip, when entering a date you will also use single quotes. Just in case you come across that.

:)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1