Update Query on Oracle Not Working.

Question

1.00/5 (1 vote)

See more:

This is my first time to use oracle as db. syntax are not the same. i try updating a certain column but it does not work.

DATA of the parameters:

VB

Public Con As New OracleConnection("Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=XXX)(HOST=xxx.xxx.xx.xxx)(PORT=1521))(CONNECT_DATA = (SID = xxxx)));user id=xxx;Password=xxx;")
    

  Protected Sub btnShipment_Click(sender As Object, e As EventArgs) Handles btnShipment.Click

   Con.Close()
   Con.Open()
 For i As Integer = 0 To GridView1.Rows.Count - 1
       
Dim cmd As New OracleCommand("UPDATE PRODUCT SET  SHIP_REQUEST_NO = :shipreqno,  SHIP_DATE = :sd, SHIP_DATETIME = :sdt, PURCH_ORDER = :po, ORDER_NO = :od, UPDATE_DATE = :dd WHERE PACKAGE = :pn", Con) With {.CommandType = CommandType.Text, .BindByName = True}
         cmd .Parameters.Add(New OracleParameter("pn", GridView1.Rows(i).Cells(1).Text))
         cmd .Parameters.Add(New OracleParameter("sd", sds))
         cmd.Parameters.Add(New OracleParameter("sdt", dtime))
         cmd .Parameters.Add(New OracleParameter("po", pon))
         cmd .Parameters.Add(New OracleParameter("od", ord))
         cmd .Parameters.Add(New OracleParameter("shipreqno", GridView1.Rows(i).Cells(0).Text))
         cmd .Parameters.Add(New OracleParameter("dd", DateTime.Today.ToString("dd-MMM-yy"))) '
 cmd .ExecuteNonQuery()

Next



end sub

I tried this code below.still not working:

SQL

Dim query As String = "UPDATE PRODUCT SET ORDER_NO = :od  WHERE PACKING =  :pn"
            Dim command As OracleCommand = New OracleCommand(query, code.OCon) _
With {.CommandType = CommandType.Text, .BindByName = True}
            command.Parameters.Add("pn", OracleDbType.Varchar2).Value = packingnumber
            command.Parameters.Add("od", OracleDbType.Varchar2).Value = ordernum

Posted 28-Apr-15 19:29pm

NekoNao

Updated 29-Apr-15 14:47pm

v7

Add a Solution

Comments

Jörgen Andersson 29-Apr-15 2:22am

You're not stating if you get an error or not, nor in what way it isn't working.
Question: Is UPDATE_DATE a string in the database?

NekoNao 29-Apr-15 3:17am

I don't get any error but that's the problem it does not give error but does not update.

no.its not. its date. i dont know how to coonvert it to date. with format of dd-MMM-yy [ex.14-APR-15]

Maciej Los 29-Apr-15 3:19am

If it's a date, you don't need to convert it to string or format!

Jörgen Andersson 29-Apr-15 3:58am

As Maciej wrote, you don't. Just use DateTime.Today directly.
What datatypes are SHIP_DATETIME and dtime?
Actually, can you list what datatypes your fields in PRODUCT is and also the datatypes of the corresponding parameters.

NekoNao 29-Apr-15 4:11am

all of it has datatypes of varchar2 except from update_date which is date. but it needs a format of dd-MMM-yy [ex.14-APR-15] to be able to save.

Jörgen Andersson 29-Apr-15 4:39am

Try: cmd.Parameters.Add(new OracleParameter("dd", OracleDbType.Date, DateTime.Today, ParameterDirection.Input))

NekoNao 29-Apr-15 4:39am

okay i will.

NekoNao 29-Apr-15 4:43am

how can i declare the other parameters if it is varchar2?

Maciej Los 29-Apr-15 3:09am

on is restricted word. I do believe you can't use it as parameter name!

NekoNao 29-Apr-15 3:16am

i did changed it. but still not working.

Maciej Los 29-Apr-15 3:18am

Debug the program and check what is passed into query...

NekoNao 29-Apr-15 4:10am

there is no error in the query. it just stops in cmd.executenonquery and loads endlessly in the browser..

Maciej Los 29-Apr-15 5:15am

Post the part of code you're using to update data.

Maciej Los 29-Apr-15 5:31am

It's not entire code! Where is the part resposible for connection? Use "Improve question" widget. Do not post code in comment!

NekoNao 29-Apr-15 5:59am

done adding all codes.

Maciej Los 29-Apr-15 6:25am

Thank you.
I missed something. Does parameters have to have the same name as is used in command string?
cmd .Parameters.Add(New OracleParameter(":sd", sd)) Have a look at first parameter of OracleParameter constructor!

NekoNao 29-Apr-15 21:35pm

tried changing that in my code. not working.

2 solutions

Solution 2

one thing that you can apply to solve your issue ,
if you pass where clause parameters at last then your query execute perfactly

as in this

VB

Dim query As String = "UPDATE PRODUCT SET ORDER_NO = :od  WHERE PACKING =  :pn"
            Dim command As OracleCommand = New OracleCommand(query, code.OCon) _
With {.CommandType = CommandType.Text, .BindByName = True}
            command.Parameters.Add("pn", OracleDbType.Varchar2).Value = packingnumber
            command.Parameters.Add("od", OracleDbType.Varchar2).Value = ordernum

Make this

VB

Dim query As String = "UPDATE PRODUCT SET ORDER_NO = :od  WHERE PACKING =  :pn"
            Dim command As OracleCommand = New OracleCommand(query, code.OCon) _
With {.CommandType = CommandType.Text, .BindByName = True}
            command.Parameters.Add(":od", OracleDbType.Varchar2).Value = ordernum
command.Parameters.Add(":pn", OracleDbType.Varchar2).Value = packingnumber

Then it's work 100% , please try

Posted 9-Aug-15 22:33pm

Praveen_Kumar Gupta

Comments

Shivaprasad Rao 6-Nov-22 21:06pm

BindByName = True did the trick for me, thanks a lot Praveen!

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2015-04-29T00:35:00

The comments about date and [on] parameter are still active.

On the second look, you have to initiate OracleParameter using the name you used in command string. So, instead of:

VB

cmd .Parameters.Add(New OracleParameter("sd", sd))

use

VB

cmd .Parameters.Add(New OracleParameter(":sd", sd))

Have a look at first parameter of OracleParameter constructor! Do you see the difference?

Please, read this too: Gotcha #1161: Using Named Parameters with Oracle ODP.NET[^]

Update Query on Oracle Not Working.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Update Query on Oracle Not Working.

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us