ORA-01722

[Naveed_786]

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

[debasisdas]

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

[Naveed_786]

Sorry i didn't understand what are you saying

[debasisdas]

post only the SQL insert statement.

[Naveed_786]

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)

[urtrivedi]

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.

[debasisdas]

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

[Naveed_786]

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

[debasisdas]

did you follow the steps as suggested by urtrivedi ?

[debasisdas]

change the last line to the following.

& textDateTime & "','mm/dd/yyyy hh24:mi:ss')",'" &  ComboBox1.Text.TRIM & "')"

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.)

[Naveed_786]

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??

[urtrivedi]

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

[Naveed_786]

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)

[debasisdas]

If you are getting syntax error what is fine then ?

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

[Naveed_786]

I have posted sql

[debasisdas]

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]

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

[Naveed_786]

Ok let me try

[Naveed_786]

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