ASP.NET

ASP.NET RSS

Some Error Please help

Please support our ASP.NET advertiser: Intel Parallel Studio Home

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Some Error Please help

Jun 20th, 2009

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
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();

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Feb 2009

Posts: 3,204

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: Some Error Please help

Jun 20th, 2009

Your syntax should be:

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO Table (Col1, Col2, Col3)
SELECT Col1, Col2, Col3
FROM SomeOtherTable
Insert Into Table (Col1, Col2, Col3)
Select Col1, Col2, Col3
From SomeOtherTable

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Re: Some Error Please help

Jun 20th, 2009

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

Thanks

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Feb 2009

Posts: 3,204

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: Some Error Please help

Jun 20th, 2009

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:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
sql += " INTO('" + lid + "', '" + bid + "', '" ....
sql += " INTO('" + lid + "', '" + bid + "', '" ....

Evaluates to:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
INTO('','','' ....
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)

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO TableToInsert (lid, bid, MID, ldate, rdate, famount, fpaid)
SELECT LoanID, BookID, MemberID, LoanDate, ReturnDate, FineAmount, FinePaid
FROM Loan
WHERE loadId = 12345
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:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
string lid = "";
string bid = "";
string mid = "";
string ldate = "";
string rdate = "";
string famount = "";
string fpaid = "";
string lid = "";
string bid = "";
string mid = "";
string ldate = "";
string rdate = "";
string famount = "";
string fpaid = "";

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Re: Some Error Please help

Jun 20th, 2009

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

Last edited by Traicey; Jun 20th, 2009 at 10:50 am.

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Feb 2009

Posts: 3,204

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: Some Error Please help

Jun 20th, 2009

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:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
    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();
      }
    }
    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.

Last edited by sknake; Jun 20th, 2009 at 11:07 am. Reason: missed a dispose

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Mar 2008

Posts: 268

Reputation: Traicey is an unknown quantity at this point

Solved Threads: 19

Traicey

Offline

Posting Whiz in Training

Re: Some Error Please help

Jun 20th, 2009

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

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
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();
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();

Last edited by Traicey; Jun 20th, 2009 at 11:37 am.

Some people get so rich they lose all respect for humanity. That's how rich I want to be.

•

Join Date: Feb 2009

Posts: 3,204

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: Some Error Please help

Jun 20th, 2009

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

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
string lid = "";
//its conplaining about this line int loanid = int.Parse(lid) 
int loanid = int.Parse(lid);
string lid = "";
//its conplaining about this line int loanid = int.Parse(lid) 
int loanid = int.Parse(lid);

Here is functionally identical code:

 Help with Code Tags 
 c# Syntax (Toggle Plain Text) 
int i1 = int.Parse("");
int i1 = int.Parse("");

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

try

Help with Code Tags

c# Syntax (Toggle Plain Text)

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?

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Oct 2008

Posts: 2,612

Reputation: adatapost has much to be proud of

Solved Threads: 463

adatapost

Offline

Posting Maven

Re: Some Error Please help

Jun 21st, 2009

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)

 Help with Code Tags 
 C# Syntax (Toggle Plain Text) 
      string sql = "Select LoanID, BookID, MemberID, LoanDate, 
                          ReturnDate, FineAmount, FinePaid where LoanID = 
                          + " loanid;
      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.

Failure is not fatal, but failure to change might be. - John Wooden