0

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

4
Contributors
19
Replies
20
Views
6 Years
Discussion Span
Last Post by Naveed_786
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)
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.

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

0

change the last line to the following.

& textDateTime & "','mm/dd/yyyy hh24:mi:ss')",'" &  ComboBox1.Text.TRIM & "')"
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.)

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

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)

Edited by Naveed_786: n/a

0

If you are getting syntax error what is fine then ?

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

Edited by debasisdas: n/a

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.

Edited by debasisdas: n/a

0

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

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.