Problem updating table

Willemt · #1 (**permalink**) April 25th, 2009, 06:58 PM

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 · #2 (**permalink**) April 25th, 2009, 09: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 · #3 (**permalink**) April 26th, 2009, 04: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 · #4 (**permalink**) April 26th, 2009, 06: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 · #5 (**permalink**) June 28th, 2009, 03:56 AM

Willemt:

Just by "eyeballing" your update statement, I have a couple of suggestions:

Oracle recognizes "||" for concatenation;
Did not see the "FROM" keyword in UPDATE statement;
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.

Need to download code?

Navigation