consuming SAP web service from web app part 1

CL-IS
CL-IS used Ask the Experts™
on
Hi

I've developed a c# web form vs2010 and it also consumes a web service that pulls data from SAP, It works fine to a certain extent.

What happens in my app is i enter in a value to a textbox click submit and it goes and calls the web service to bring back data for the value entered and will store this data in my db table.

If enter the value PME041011-K1, i will get 5 rows back from it. Now in my app i pull back back each row one at a time showing that row data in textboxes that are read only, i will then enter values into other textboxes to match the readonly data and if it matches in will insert the matched data to another table until there are no more rows left and then i have to re-enter a new value to go to SAP again. At anytime when im entering in data to match i can click cancel and restart my submit again, now this is my problem - say i only matched 3 rows out of the 5 when i canceled i should only have 2 more rows to scan, but becasue this is going away to SAP again it will get those 5 rows again making it now 7.

Can anyone help me to stop this from happening, i need a way that when i submit the value it goes away to SAP again and then tries populating my table again i need to stop this from happening and warn user that this is a duplicate, i think i need the database (sql server 2008) to check the matching? if rows already exist.


Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Author

Commented:
I think all i need is a sql query or code in my app that stops inserting data into table if it already exists and insert if it doesnt.

i just need to get c#/sql code to know that the data exists or does anyone have any ideas?

this is my table data just now..
WORKORDER      DOCYEAR      MOVEMENTTYPE      QTY      MATERIALNUMBER      BATCHNUMBER
PME041011-K1      2004      261      62.0      000000000000121606      040907-44
PME041011-K1      2004      261      355.0      000000000000202972      040624-23
PME041011-K1      2004      261      178.0      000000000000204513      040903-47
PME041011-K1      2004      261      27.0      000000000000121606      040915-40
PME041011-K1      2004      261      1.0      000000000000202972      040901-75

and the code below will have the same data so it cant insert existing data.

C# code snippet
string sql = "INSERT INTO wp4tbl_TblCompVerifySapBatch (Workorder,Docyear,movementtype,BatchNumber,Qty,MaterialNumber) VALUES (@Workorder,@Docyear,@movementtype,@BatchNumber,@Qty,@MaterialNumber)";
            SqlCommand myCommand;

            try
            {
                cnn.Open();
            }
            catch (SqlException SQLex)
            {
                errorMessage = SQLex.Message;
            }
            catch (Exception ex)
            {
                errorMessage = ex.Message;
            }

            myCommand = cnn.CreateCommand();
            myCommand.CommandText = sql;

            for (i = 0; (i <= (MySAPResults.Details.Length - 1)); i++)
            {
                Workorder = MySAPResults.Details[i].Aufnr;
                Docyear = MySAPResults.Details[i].Mjahr;
                Movementtype = MySAPResults.Details[i].Bwart;
                BatchNumber = MySAPResults.Details[i].Charg;
                Quantity = Convert.ToString(MySAPResults.Details[i].Menge);
                MaterialNumber = MySAPResults.Details[i].Matnr;
                myCommand.Parameters.Add(new SqlParameter("@Workorder", Workorder));
                myCommand.Parameters.Add(new SqlParameter("@Docyear", Docyear));
                myCommand.Parameters.Add(new SqlParameter("@movementtype", Movementtype));
                myCommand.Parameters.Add(new SqlParameter("@BatchNumber", BatchNumber));
                myCommand.Parameters.Add(new SqlParameter("@Qty", Quantity));
                myCommand.Parameters.Add(new SqlParameter("@MaterialNumber", MaterialNumber));
                myCommand.ExecuteNonQuery();
                myCommand.Parameters.Clear();
            }
            cnn.Close();
            if (MySAPResults.Details.Length == 0)
            {
                return null;
            }
            else
            {
                return MySAPResults.Details[0];
            }
Commented:
its ok i have it working with a delete statement before the insert.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
Well, that certainly makes it a lot easier to answer the question :)

Can I suggest you accept your own answer as a solution, or, could request to delete the question...

Cheers,
Mark Wills

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial