I want OLEDB exception from this statement

Question

0.00/5 (No votes)

See more:

I have an Access Database in which some tables are created like TAXINVOICE with columns like INVOICENO,MOBILENUMBER,CUSTOMERNAME,ADDRESS1,ADDRESS2,TINNO,PONO,TRANSPORT,BILLDATE,
InvoiceType,PODATE,totalGrossAmt,totalVatAmt,BILLAMT,TRANSPORTAMT,OTHERAMT,FINALAMT,
ROUNDOFF,GRANDTOTAL,STATUS
another table InvoiceItems with columns InvoiceItemId,InvoiceNumber,ProductId,Quantity,UOM,UnitPrice,GrossAmount,VAT,VatAmount,
Total
and a Table Products with columns
ProductId and ProductName

My problem is to update and delete whatever the entries we have edited in the datagridview form from windows application (C#.NET code).I used OLEDB queris which are tried and I will add below

What I have tried:

C#

This is update statement for update button click code
try
           {
               cmd = new OleDbCommand("UPDATE from InvoiceItems,Products set Description ='" + txtProductName + "',Quantity = '" + txtQty.Text + "',UOM = '" + txtUOM.Text + "',UnitPrice ='" + txtUnitPrice.Text + "',GrossAmount ='" + txtGrossAmount.Text + "',VAT = '" + txtVAT.Text + "',VatAmount ='" + txtVatAmount.Text + "',Total ='" + txtTotal.Text + "' from InvoiceItems,Products where InvoiceItems.ProductId = Products.ProductId order by InvoiceNumber='" + txtInvoice.Text.Trim() + "'", conn);
               conn.Open();
               //dataGridView1.AutoGenerateColumns = true;
               cmd.ExecuteNonQuery();
               MessageBox.Show("Success");
               conn.Close();
               DisplayData();
               ClearData();
           }
           catch (OleDbException ex)
           {
               MessageBox.Show("Empty xseries");
               throw(ex);

           }

C#

This is delete statement for update button click code
try
           {
               cmd = new OleDbCommand("DELETE FROM InvoiceNumber,INVOICENO from InvoiceItems WHERE InvoiceItems.InvoiceNumber=TAXINVOICE.INVOICENO and InvoiceNumber=" + txtInvoice.ToString() + "", conn);
               conn.Open();
               //dataGridView1.AutoGenerateColumns = false;
               cmd.ExecuteNonQuery();
               MessageBox.Show("Deleted Entry");
               conn.Close();
               DisplayData();
               ClearData();
           }
           catch (OleDbException ex)
           {
               MessageBox.Show("ALL rows are inevitable");
               throw(ex);
           }

Posted 13-Oct-16 20:59pm

Member 12790908

Updated 13-Oct-16 22:17pm

Add a Solution

2 solutions

Solution 2

The query is wrong. It should be something like:

SQL

UPDATE InvoiceItems SET <<fields>> FROM InvoiceItems, Products where <<where conditions>>

I guess you have to update the InvoiceItems table, remember you can update only a table at a time.

Note: I think that also the DELETE command query is wrong. Check the syntax!

Bye

Posted 13-Oct-16 22:17pm

Alfonso Gag.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2016-10-13T22:08:00

If I understand your question correctly, you have a few problems with the statements.

First the update, try using following syntax
UPDATE tablename

SQL

SET column = value,
    column = value,
    column = value...
WHERE conditions

So in the example it could be something like

SQL

UPDATE InvoiceItems
SET Description = ?,
    Quantity = ?,
    UOM = ? ...
WHERE InvoiceItemKey = ?

Also note that you should not concatenate the values to the statement but instead use OleDbParameter Class (System.Data.OleDb)[^]

In the delete statement you cannot delete columns, you always delete rows So the syntax is

SQL

DELETE FROM tablename WHERE conditions

For example something like

SQL

DELETE FROM InvoiceItems 
WHERE InvoiceNumber = ?

And also with this statement, use parameters.