error: sql command not properly ended

Question

I have to search the employee details, which is contained within 3 tables. I have used joins in the query query, but it shows error when I press the search button:

sql command not properly ended

c# coding:

try {
  //Search Employee Details
  Oracle.DataAccess.Client.OracleConnection cn = new Oracle.DataAccess.Client.OracleConnection();

  cn.ConnectionString = "user id=system; password=system;";
  Oracle.DataAccess.Client.OracleCommand cmd = new Oracle.DataAccess.Client.OracleCommand();

  cmd.Connection = cn;
  //cn = new Oracle.DataAccess.Client.OracleConnection();
  cmd.CommandText = " select deposit.loanid, 
                             form1.empedoj, 
                             form1.empshare, 
                             sharecapital.shareint, 
                             sharecapital.loandt, 
                             sharecapital.loandeduc, 
                             sharecapital.dividend, 
                             sharecapital.sharetot 
                        from form1, 
                             deposit, 
                             sharecapital 
                       where deposit.loanid(+) = sharecapital.loanid = '" + txtlnid.Text.Trim() + "'";  // shows sql command not properly ended

  Oracle.DataAccess.Client.OracleDataAdapter ada = new Oracle.DataAccess.Client.OracleDataAdapter(cmd);
  System.Data.DataTable dt = new DataTable();
  dt.Clear();
  ada.Fill(dt);

  //Display in Textbox
  if (dt.Rows.Count > 0) {
    txtlnid.Text = dt.Rows[0].ItemArray[0].ToString();
    admdate.Text = dt.Rows[0].ItemArray[1].ToString();
    txtadmamt.Text = dt.Rows[0].ItemArray[2].ToString();
    txtadmint.Text = dt.Rows[0].ItemArray[3].ToString();
    loandt.Text = dt.Rows[0].ItemArray[4].ToString();
    txtlnamt.Text = dt.Rows[0].ItemArray[5].ToString();
    txtlnint.Text = dt.Rows[0].ItemArray[6].ToString();
    txtsctot.Text = dt.Rows[0].ItemArray[7].ToString();
  }

  if (cn.State == ConnectionState.Closed) {
    cn.Open();
  }

  string str;
  str = cmd.ExecuteScalar().ToString();

  if (str != null) {
    MessageBox.Show("Record Found");
  } else {
    MessageBox.Show("ID not Match");
  }
} catch (Exception ex) {
  MessageBox.Show(ex.Message);
}

Your query doesn't have criteria to link the form1 table to any others. It's not the source of the error, but is very unlikely to return the results you want because it's producting a cartesian product. And the (+) is deprecated outer join syntax, Oracle specific -- what version of Oracle are you working with? — OMG Ponies
– OMG Ponies, Commented Mar 10, 2011 at 6:16
10g i am using. actually in that txtlnid using for to search employee details by using that textbox — Logan
– Logan, Commented Mar 11, 2011 at 6:40

Sathyajith Bhat · Accepted Answer · 2011-03-10 06:17:38Z

4

Your SQL statement becomes

  SELECT DEPOSIT.LOANID,
         FORM1.EMPEDOJ,
         FORM1.EMPSHARE,
         SHARECAPITAL.SHAREINT,
         SHARECAPITAL.LOANDT,
         SHARECAPITAL.LOANDEDUC,
         SHARECAPITAL.DIVIDEND,
         SHARECAPITAL.SHARETOT
  FROM   FORM1, DEPOSIT, SHARECAPITAL
  WHERE  DEPOSIT.LOANID(+) = SHARECAPITAL.LOANID =    '" + txtlnid.Text.Trim() + "'";

I suspect it should be:

  SELECT DEPOSIT.LOANID,
         FORM1.EMPEDOJ,
         FORM1.EMPSHARE,
         SHARECAPITAL.SHAREINT,
         SHARECAPITAL.LOANDT,
         SHARECAPITAL.LOANDEDUC,
         SHARECAPITAL.DIVIDEND,
         SHARECAPITAL.SHARETOT
  FROM   FORM1, DEPOSIT, SHARECAPITAL
  WHERE  DEPOSIT.LOANID(+) = SHARECAPITAL.LOANID 
  AND    SHARECAPITAL.LOANID = '" + txtlnid.Text.Trim() + "'";

Also, you have a 3-table join without the correct join conditions, the query is highly likely to return a Cartesian product.

answered Mar 10, 2011 at 6:17

Sathyajith Bhat

22k22 gold badges102 silver badges141 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Sathyajith Bhat Over a year ago

Also, query is susceptible to SQL Injection, and as @OMGPonies mentions - (+) join is depreciated

Jeffrey Kemp Over a year ago

OP - just to be clear - you can't have "X = Y = Z" as a predicate in SQL. Unfortunately :)

Logan Over a year ago

i have modified which you said it shows "object reference is not set to instance of an object "

Sathyajith Bhat Over a year ago

@Loganathan - that is an unrelated error. Please post a new question

Mike Atlas Over a year ago

Additionally, the cmd objects should be wrapped in using(){} clauses.

Phil3992 · Accepted Answer · 2018-01-05 13:17:39Z

-2

Have you tried putting a semicolon at the end of your query string?

cmd.CommandText = " select deposit.loanid, form1.empedoj, form1.empshare,
sharecapital.shareint, sharecapital.loandt, sharecapital.loandeduc, 
sharecapital.dividend, sharecapital.sharetot from form1, deposit , 
sharecapital where deposit.loanid(+) = sharecapital.loanid = '" + txtlnid.Text.Trim() + "';";

edited Jan 5, 2018 at 13:17

Phil3992

1,0557 gold badges21 silver badges46 bronze badges

answered Mar 10, 2011 at 6:18

kOhm

191 silver badge4 bronze badges

Collectives™ on Stack Overflow

error: sql command not properly ended

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related