954,561 Members — Technology Publication meets Social Media
Username:
Password:
Lost login information?
Have something to say? Contribute New Article Reply to this Article
1 Year Ago
0

ORA-01722

I am using oracle 9i database with VB.NET when i am trying to insert record in my PR_REC table it gives me error "ORA-01722 Invalid number" here is my insert statement and the structure of the table

com = New OleDbCommand("INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES ('" & Me.txtprno.Text.Trim & "','" & Me.txtitm1.Text.Trim & "','" & Me.txtprc1.Text.Trim & "', '" & Me.txtqty1.Text.Trim & "','" & Me.txtitm2.Text.Trim & "', '" & Me.txtprc2.Text.Trim & "','" & Me.txtqty5.Text.Trim & "','" & Me.txtitm3.Text.Trim & "','" & Me.txtprc3.Text.Trim & " ','" & Me.txtqty9.Text.Trim & "','" & Me.txtitm4.Text.Trim & " ','" & Me.txtprc4.Text.Trim & " ','" & Me.txtqty2.Text.Trim & "','" & Me.txtitm5.Text.Trim & " ','" & Me.txtprc5.Text.Trim & " ','" & Me.txtqty6.Text.Trim & "','" & Me.txtitm6.Text.Trim & " ','" & Me.txtprc6.Text.Trim & " ','" & Me.txtqty6.Text.Trim & "','" & Me.txtitm7.Text.Trim & " ','" & Me.txtprc7.Text.Trim & " ','" & Me.txtqty10.Text.Trim & "','" & Me.txtitm8.Text.Trim & " ','" & Me.txtprc8.Text.Trim & " ','" & Me.txtqty3.Text.Trim & "','" & Me.txtitm9.Text.Trim & " ','" & Me.txtprc9.Text.Trim & " ','" & Me.txtqty7.Text.Trim & "','" & Me.txtitm10.Text.Trim & " ','" & Me.txtprc10.Text.Trim & " ','" & Me.txtqty11.Text.Trim & "','" & Me.txtpr.Text.Trim & "',to_date('" & textDateTime & "','mm/dd/yyyy hh24:mi:ss'),'" & ComboBox1.Text.Trim & "')", con)

Structure of the table:-

Name                            Null?    Type
 ------------------------------- -------- ----
 PR_NO                           NOT NULL VARCHAR2(50)
 ITEM1                           NOT NULL VARCHAR2(100)
 ITEM2                                    VARCHAR2(100)
 ITEM3                                    VARCHAR2(100)
 ITEM4                                    VARCHAR2(100)
 ITEM5                                    VARCHAR2(100)
 ITEM6                                    VARCHAR2(100)
 ITEM7                                    VARCHAR2(100)
 ITEM8                                    VARCHAR2(100)
 ITEM9                                    VARCHAR2(100)
 ITEM10                                   VARCHAR2(100)
 PRICE1                          NOT NULL NUMBER(30)
 PRICE2                                   NUMBER(30)
 PRICE3                                   NUMBER(30)
 PRICE4                                   NUMBER(30)
 PRICE5                                   NUMBER(30)
 PRICE6                                   NUMBER(30)
 PRICE7                                   NUMBER(30)
 PRICE8                                   NUMBER(30)
 PRICE9                                   NUMBER(30)
 PRICE10                                  NUMBER(30)
 PUR_REASON                               VARCHAR2(1200)
 PR_DATE                                  DATE
 STATUS                                   VARCHAR2(30)
 CUR_ST                                   VARCHAR2(30)
 PR_CL_DATE                               DATE
 QTY1                            NOT NULL NUMBER(30)
 QTY2                                     NUMBER(30)
 QTY3                                     NUMBER(30)
 QTY4                                     NUMBER(30)
 QTY5                                     NUMBER(30)
 QTY6                                     NUMBER(30)
 QTY7                                     NUMBER(30)
 QTY8                                     NUMBER(30)
 QTY9                                     NUMBER(30)
 QTY10                                    NUMBER(30)
 CDATE                                    DATE


Please help me

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

Can you print the SQL statement and post over here. (only the SQL not .net code)

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

Sorry i didn't understand what are you saying

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

post only the SQL insert statement.

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

This is my sql Statement

com = NEW OleDbCommand("INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES ('" & Me.txtprno.Text.TRIM & "','" & Me.txtitm1.Text.TRIM & "','" & Me.txtprc1.Text.TRIM & "', '" & Me.txtqty1.Text.TRIM & "','" & Me.txtitm2.Text.TRIM & "', '" & Me.txtprc2.Text.TRIM & "','" & Me.txtqty5.Text.TRIM & "','" & Me.txtitm3.Text.TRIM & "','" & Me.txtprc3.Text.TRIM & " ','" & Me.txtqty9.Text.TRIM & "','" & Me.txtitm4.Text.TRIM & " ','" & Me.txtprc4.Text.TRIM & " ','" & Me.txtqty2.Text.TRIM & "','" & Me.txtitm5.Text.TRIM & " ','" & Me.txtprc5.Text.TRIM & " ','" & Me.txtqty6.Text.TRIM & "','" & Me.txtitm6.Text.TRIM & " ','" & Me.txtprc6.Text.TRIM & " ','" & Me.txtqty6.Text.TRIM & "','" & Me.txtitm7.Text.TRIM & " ','" & Me.txtprc7.Text.TRIM & " ','" & Me.txtqty10.Text.TRIM & "','" & Me.txtitm8.Text.TRIM & " ','" & Me.txtprc8.Text.TRIM & " ','" & Me.txtqty3.Text.TRIM & "','" & Me.txtitm9.Text.TRIM & " ','" & Me.txtprc9.Text.TRIM & " ','" & Me.txtqty7.Text.TRIM & "','" & Me.txtitm10.Text.TRIM & " ','" & Me.txtprc10.Text.TRIM & " ','" & Me.txtqty11.Text.TRIM & "','" & Me.txtpr.Text.TRIM & "',to_date('" & textDateTime & "','mm/dd/yyyy hh24:mi:ss'),'" & ComboBox1.Text.TRIM & "')", con)
Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0
myquery="INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES ('" & Me.txtprno.Text.TRIM & "','" & Me.txtitm1.Text.TRIM & "','" & Me.txtprc1.Text.TRIM & "', '" & Me.txtqty1.Text.TRIM & "','" & Me.txtitm2.Text.TRIM & "', '" & Me.txtprc2.Text.TRIM & "','" & Me.txtqty5.Text.TRIM & "','" & Me.txtitm3.Text.TRIM & "','" & Me.txtprc3.Text.TRIM & " ','" & Me.txtqty9.Text.TRIM & "','" & Me.txtitm4.Text.TRIM & " ','" & Me.txtprc4.Text.TRIM & " ','" & Me.txtqty2.Text.TRIM & "','" & Me.txtitm5.Text.TRIM & " ','" & Me.txtprc5.Text.TRIM & " ','" & Me.txtqty6.Text.TRIM & "','" & Me.txtitm6.Text.TRIM & " ','" & Me.txtprc6.Text.TRIM & " ','" & Me.txtqty6.Text.TRIM & "','" & Me.txtitm7.Text.TRIM & " ','" & Me.txtprc7.Text.TRIM & " ','" & Me.txtqty10.Text.TRIM & "','" & Me.txtitm8.Text.TRIM & " ','" & Me.txtprc8.Text.TRIM & " ','" & Me.txtqty3.Text.TRIM & "','" & Me.txtitm9.Text.TRIM & " ','" & Me.txtprc9.Text.TRIM & " ','" & Me.txtqty7.Text.TRIM & "','" & Me.txtitm10.Text.TRIM & " ','" & Me.txtprc10.Text.TRIM & " ','" & Me.txtqty11.Text.TRIM & "','" & Me.txtpr.Text.TRIM & "',to_date('" & textDateTime & "','mm/dd/yyyy hh24:mi:ss'),'" & ComboBox1.Text.TRIM & "')"

print myquery // keep break point here in dubugger

com = NEW OleDbCommand(myquery, con)

go to debugger, copy value of variable myquery and post it here.

urtrivedi
Nearly a Posting Virtuoso
1,306 posts since Dec 2008
Reputation Points: 257
Solved Threads: 270
 
1 Year Ago
0

may be you need to fix the part between the date and combo field.

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

Every variable has value

Com = nothing

Me.txtprno.Text = "505051"
Me.txtitm1.Text = "CPU"
Me.txtprc1.Text = "21000"
Me.txtqty1.Text = "1"
Me.txtitm2.Text = "LCD"
Me.txtprc2.Text = "25000"
Me.txtqty5.Text = "1"
Me.txtitm3.Text = "RAM"
Me.txtprc3.Text = "6000"
Me.txtqty9.Text = "3"
Me.txtitm4.Text = "MOBILE"
Me.txtprc4.Text = "25000"
Me.txtqty2.Text = "1"
Me.txtitm5.Text = "VGA"
Me.txtprc5.Text = "25000"
Me.txtqty6.Text = "1000"
Me.txtitm6.Text = "POWER"
Me.txtprc6.Text = "15000"
Me.txtqty6.Text = "1000"
Me.txtitm7.Text = "CDS"
Me.txtprc7.Text = "1500"
Me.txtqty10.Text = "1000"
Me.txtitm8.Text = "DVD"
Me.txtprc8.Text = "1500"
Me.txtqty3.Text = "100"
Me.txtitm9.Text = "MOUSE"
Me.txtprc9.Text = "500"
Me.txtqty7.Text = "50"
Me.txtitm10.Text = "BOOKS"
Me.txtprc10.Text = "1500"
Me.txtqty11.Text = "100"
Me.txtpr.Text = "IT BACKUP"
textDateTime = "06/01/2011 17:33:16"
ComboBox1.Text = "IT Support"

These are the values in textboxes

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

did you follow the steps as suggested by urtrivedi ?

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

change the last line to the following.

& textDateTime & "','mm/dd/yyyy hh24:mi:ss')",'" &  ComboBox1.Text.TRIM & "')"
debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

Well... For a start...
Remove the single quotes surrounding your prices and quantities to see if that has any effect.
(My guess is the implicit conversion of a character string to a number is NOT happening.)

hfx642
Posting Pro
515 posts since Nov 2009
Reputation Points: 248
Solved Threads: 105
 
1 Year Ago
0

well its fine when i try to enter all values its ok but when i try to enter some values and leave some text boxes blank it gives me following error

"ONE OR MORE ERROR OCCURRED DURING PROCESSING OF COMMAND "ORA-00936"

What could be the problem??

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

You are expecting a solution but not following what people asking you to do. Have you read my previous post

http://www.daniweb.com/web-development/databases/oracle/threads/366673/1573429#post1573429

urtrivedi
Nearly a Posting Virtuoso
1,306 posts since Dec 2008
Reputation Points: 257
Solved Threads: 270
 
1 Year Ago
0

HERE IS THE STATEMENT YOU ASKING FOR

"INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES (505056,'CPU',15000, 1,'RAM', 1200,1,'KEYBOARD',1500,2,'',,,'',,,'',,,'',,,' ',,,' ',,,' ',,,'BACKUP',to_date('06/03/2011 11:41:43','mm/dd/yyyy hh24:mi:ss'),'IT Support')"

AND THE INSERT STATEMENT IS THIS I HAVE MADE SOME CHANGES IN IT

com = New OleDbCommand("INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES (" & Me.txtprno.Text.Replace(",", ".") & ",'" & Me.txtitm1.Text & "'," & Me.txtprc1.Text.Replace(",", ".") & ", " & Me.txtqty1.Text.Replace(",", ".") & ",'" & Me.txtitm2.Text & "', " & Me.txtprc2.Text.Replace(",", ".") & "," & Me.txtqty2.Text.Replace(",", ".") & ",'" & Me.txtitm3.Text & "'," & Me.txtprc3.Text.Replace(",", ".") & "," & Me.txtqty3.Text.Replace(",", ".") & ",'" & Me.txtitm4.Text & "'," & Me.txtprc4.Text.Replace(",", ".") & "," & Me.txtqty4.Text.Replace(",", ".") & ",'" & Me.txtitm5.Text & "'," & Me.txtprc5.Text.Replace(",", ".") & "," & Me.txtqty5.Text.Replace(",", ".") & ",'" & Me.txtitm6.Text & "'," & Me.txtprc6.Text.Replace(",", ".") & "," & Me.txtqty6.Text.Replace(",", ".") & ",'" & Me.txtitm7.Text & "'," & Me.txtprc7.Text.Replace(",", ".") & "," & Me.txtqty7.Text.Replace(",", ".") & ",'" & Me.txtitm8.Text & " '," & Me.txtprc8.Text.Replace(",", ".") & "," & Me.txtqty8.Text.Replace(",", ".") & ",'" & Me.txtitm9.Text & " '," & Me.txtprc9.Text.Replace(",", ".") & "," & Me.txtqty9.Text.Replace(",", ".") & ",'" & Me.txtitm10.Text & " '," & Me.txtprc10.Text.Replace(",", ".") & "," & Me.txtqty10.Text.Replace(",", ".") & ",'" & Me.txtpr.Text & "',to_date('" & textDateTime & "','mm/dd/yyyy hh24:mi:ss'),'" & ComboBox1.Text & "')", con)
Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

If you are getting syntax error what is fine then ?

what happened to all those single quotes, why are they missing ?

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

I have posted sql

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
1 Year Ago
0

try this

INSERT INTO PR_REC(pr_no,item1,price1,qty1,item2,price2,qty2,item3,price3,qty3,item4,price4,qty4,item5,price5,qty5,item6,price6,qty6,item7,price7,qty7,item8,price8,qty8,item9,price9,qty9,item10,price10,qty10,Pur_reason,pr_date,Status) VALUES (505056,'CPU',15000, 1,'RAM', 1200,1,'KEYBOARD',1500,2,'','','','','','','','','','','','',' ','','',' ','','',' ','','','BACKUP',to_date('06/03/2011 11:41:43','mm/dd/yyyy hh24:mi:ss'),'IT Support')


and fix all that missing single quotes from your .net code.

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

modify your .net code and ensure that you get the above SQL before executing the same.

debasisdas
Posting Genius
6,872 posts since Feb 2007
Reputation Points: 666
Solved Threads: 434
 
1 Year Ago
0

Ok let me try

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 
11 Months Ago
0

I have resolved the problem by setting the default values of all prices and qunatity to 0 any way thanks for your help.

Naveed_786
Posting Whiz in Training
268 posts since Jul 2010
Reputation Points: 20
Solved Threads: 11
 

This question has already been solved

Post: Markdown Syntax: Formatting Help
You
 
© 2012 DaniWeb® LLC
Home | About Us | Contact Us | Terms of Service | Advertising Opportunities
RSS Feed RSS Feed