p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   Oracle ASP (http://p2p.wrox.com/forumdisplay.php?f=106)
-   -   Problem updating table (http://p2p.wrox.com/showthread.php?t=74105)

Willemt April 25th, 2009 05:58 PM

Problem updating table
 
Hello,

I have an Oracle DB with some table's, I can insert data to the table's, but when I want to update the table's there is nothing happend.

I use the query below written in Delphi.Prism. What is wrong with this query?
Code:

  try
    OracleConn := new OracleConnection;
    OracleConn.Connectionstring := 'User Id=SCHULDHULP;Password=pass; Data Source=Omgeving;';
    OracleConn.Open;
    myOracleTransaction := OracleConn.BeginTransaction();
    cmd := new OracleCommand;
    cmd := OracleConn.CreateCommand;
    if RadioButton2.Checked = true then SEXE := 'Man'else
        SEXE := 'Vrouw';
    if RadioButton5.Checked = true then VER := 'Ja' else
        VER := 'Nee';
    if RadioButton3.Checked = true then GEH := 'Ja' else
        GEH := 'Nee';
    if RadioButton7.Checked = true then MOB := 'Abonnement' else
        MOB := 'Prepaid';
    TextBox154.Text := BSNTxt.Text;
    cmd.Connection := OracleConn; 
        cmd.CommandText := "Update AANVRAGER SET GESLACHT='"+SEXE+"',GEHEIM='"+GEH+"',ABONNEMENT='"+MOB+"',GAAT_VERHUIZEN='"+VER+"'WHERE ALGEMEEN.BSN = 9675463772";
        cmd.ExecuteNonQuery();
        OracleConn.BeginTransaction.Commit;
    finally
        OracleConn.Close();
        OracleConn.Dispose();
    end;


Greg Griffiths April 25th, 2009 08:31 PM

Have you tried printing out the contents of
Code:

cmd.CommandText
after you populate it to see what is in there ? Then if it looks ok try running that SQL in SQL Plus, or a similar DB tool, to ensure that it is valid, if you are not seeing the update then your SQL could be suspect, if the update works directly on the database then you will need to take another look at your code.

Willemt April 26th, 2009 03:44 AM

Thanks for the reply.

If I use this:

Code:

cmd.CommandText := "Update AANVRAGER SET GESLACHT='test' WHERE ALGEMEEN.BSN = 9675463772";


The query is not updating the table from code.

If I do the same query from querybuilder within Visual Studio 2008 then the table is updated.

There must be something wrong in the code.

Willemt April 26th, 2009 05:10 AM

Thanks Greg,

It seems that for some other reason the cmd.CommandText produces an empty string. I have recoded it and now the update is perfectly done.

Code:

  try
    OracleConn := new OracleConnection;
    cmd := new OracleCommand;
    OracleConn.Connectionstring := 'User Id=SCHULDHULP;Password=pass; Data Source=Omgeving;';
    OracleConn.Open;
    cmd.Connection := OracleConn;
        cmd.CommandText := "UPDATE AANVRAGER SET GESLACHT = 'Vrouw' WHERE BSN = 9675463772";
        cmd.ExecuteNonQuery();
    end;
    finally
        OracleConn.Close();
        OracleConn.Dispose();
    end;


peace95 June 28th, 2009 02:56 AM

Willemt:

Just by "eyeballing" your update statement, I have a couple of suggestions:
  1. Oracle recognizes "||" for concatenation;
  2. Did not see the "FROM" keyword in UPDATE statement;
  3. Not knowing your Scheme Definition for field BSN, if BSN is character, it needs to be enclosed in single-quotes '12345'.
Remember to write your SELECT statement for ORACLE using Delphi, not the other way around. Just a suggestion.

Hope this helps.

kadihall001 June 24th, 2015 04:34 AM

GOOD QUESTION ASKED BY YOU


All times are GMT -4. The time now is 09:15 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.