Some Error Please help

[Traicey]

Hi all

I am trying to retrieve a data from Oracle database and display it in the textboxes to be editable but with no luck, am getting a ORA error, missing right parenthesis, that sounds easy and I also thought, I put in the right parenthisis but still it is complaining with it and my guess is that it looking for theInsert keyword since I have the into keyword but instead it is finding select

Please see the below code and tell me what could be wrong or alternative way to do this

string lid = "";
string bid = "";
string mid = "";
string ldate = "";
string rdate = "";
string famount = "";
string fpaid = "";

string stringConn = "Data source=127.0.0.1; user id=*********; Password=*************;";
OracleConnection conn = new OracleConnection(stringConn);
conn.Open();
string sql = "Select LoanID, BookID, MemberID, LoanDate, ReturnDate, FineAmount, FinePaid";
sql += " INTO('" + lid + "', '" + bid + "', '" + mid + "', '" + ldate + "', '" + rdate + "', '" + famount + "', '" + fpaid + "')";
sql += "From Loan Where loanID = " + "'" + lid + "'";

txtLoanID.Text = lid;
txtBookID.Text = bid;
txtMemberID.Text = mid;
txtLoanDate.Text = ldate;
txtReturnDate.Text = rdate;
txtFineAmount.Text = famount;
txtFinePaid.Text = fpaid;      
OracleCommand cmd = new OracleCommand(sql, conn);
int ok = cmd.ExecuteNonQuery();

[sknake]

Your syntax should be:

Insert Into Table (Col1, Col2, Col3)
Select Col1, Col2, Col3
From SomeOtherTable

[Traicey]

I dont think I understand what ur trying to say can u elaborarte a bit,

Thanks

[sknake]

Actually, you have a lot of problems here. You're declaring your variables as string.Empty and then you're assembling a string with them:

sql += " INTO('" + lid + "', '" + bid + "', '" ....

Evaluates to:

INTO('','','' ....

Notice how the column names would be missing, and what you should be doing is first using parameterized SQL, see threads:
http://www.daniweb.com/forums/thread191241.html
http://www.daniweb.com/forums/thread198304.html

Next your query should look like (if you continue dynamically building queries like this)

Insert Into TableToInsert (lid, bid, mid, ldate, rdate, famount, fpaid)
Select LoanID, BookID, MemberID, LoanDate, ReturnDate, FineAmount, FinePaid
From Loan
Where loadId = 12345

And the value of the fields you initialized is never changed after they are initialized, so I do not understand why you are setting a text box with those values:

These are never set after initialization in the code you posted:

string lid = "";
string bid = "";
string mid = "";
string ldate = "";
string rdate = "";
string famount = "";
string fpaid = "";

[Traicey]

Here it goes,

I want to get(Select) values from the Database and bind them into Textboxes, then the user can edit them and update them that means writting them to the database again with the new values from textbox, in this moment in time I dont wana insert anything to my database, I wana get it from the database bind it to textboxes, if U know what I mean but Im getting an error "MISSING WRITE PARANTHESIS" and I know its not the case, I just wana find out why is complaining with such error

Maybe I dont understand where u getting at, so can u do me a favor instead of breaking the code, maybe just write all of it the way u think it should be

[sknake]

Your query is badly formed so that is probably just a best-guess error that Oracle is spitting out. Ignore it for now. Why are you writing an Into() query if you're trying to select values? What you should be doing is reading a datatable to get the values in to text boxes. Use this example but move it over to oracle:

private void button1_Click(object sender, EventArgs e)
    {
      const string connStr = "Data Source=apex2006sql;Initial Catalog=Leather;Integrated Security=True;";
      const string query = "Select * From Invoice Where InvNumber = @InvNumber";
      using (DataTable dt = new DataTable())
      {
        using (SqlConnection conn = new SqlConnection(connStr))
        {
          conn.Open();
          using (SqlCommand cmd = new SqlCommand(query, conn))
          {
            cmd.Parameters.Add(new SqlParameter("@InvNumber", 1100));
            using (SqlDataReader dr = cmd.ExecuteReader())
            {
              dt.Load(dr);
            }
          }
          conn.Close();
        }
        if (dt.Rows.Count != 1)
          throw new Exception("Record not found!");
        DataRow row = dt.Rows[0];
        
        int invNumber = Convert.ToInt32(row["InvNumber"]);
        string invStatis = Convert.ToString(row["InvStatus"]);
        //Bind the text boxes after you have the values
        System.Diagnostics.Debugger.Break();
      }
    }

You could also use a DataSet if you wanted to do property binding with the text boxes.

[Traicey]

I have this code now but its complaining about the string not in a correct format

string lid = "";
//its conplaining about this line int loanid = int.Parse(lid) 
int loanid = int.Parse(lid);

string stringConn = "Data source=127.0.0.1; user id=*********; Password=*************;";

OracleConnection conn = new OracleConnection(stringConn);
conn.Open();

string sql = "Select LoanID, BookID, MemberID, LoanDate, ReturnDate, FineAmount, FinePaid where LoanID = + " loanid;

OracleCommand cmd = new OracleCommand(sql, conn);
cmd.Parameters.add("LoanID", OracleType.Number, loanid);
int ok = cmd.ExecuteNonQuery();

[sknake]

What did you expect to happen? You're calling int.Parse() on an empty string:

string lid = "";
//its conplaining about this line int loanid = int.Parse(lid) 
int loanid = int.Parse(lid);

Here is functionally identical code:

int i1 = int.Parse("");

Is "" a valid integer? No. What happens when you parse an invalid integer? An exception. What are you doing...?

try

int loanId = 1000;

Then expand your code from there. You have not indicated where you are trying to pull this loan id from? Probably a querystring in the URL if its a site, or a lookup control on a form?

[kvprajapati]

I think poster want to learn something but he/she doesnot know how to learn and where to learn?

>Do you know - SQL and PL (Structured Query Language and PL)?
No
Your select query answered this: (FROM clause is missing)

string sql = "Select LoanID, BookID, MemberID, LoanDate, 
                          ReturnDate, FineAmount, FinePaid where LoanID = 
                          + " loanid;

>cmd.Parameters.add("LoanID", OracleType.Number, loanid); What
is this?
No need. Learn ADO.NET classes.
>int ok = cmd.ExecuteNonQuery();
Without opening a connection?

I am not discourage you. Either you are misleading us or you have a misconception.