select , update Query c# error

Question

I am importing excel file into sql database.Working code I am Using is:

 public static void ImportToSql(string excelfilepath)
              {
        string ssqltable = "Inventory";
        string myexceldataquery = "select LocalSKU,QOH from [sheet1$]";
        try
        {
            string sexcelconnectionstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" + excelfilepath + "; Extended Properties=\"Excel 12.0; HDR=Yes; IMEX=2\"";
            string ssqlconnectionstring = "Data Source=DELL\\SQLSERVER1;Trusted_Connection=True;DATABASE=CAMO;CONNECTION RESET=FALSE";

            //execute a query to erase any previous data from our destination table
            string sclearsql = "delete " + ssqltable;
enter code here
           "HERE I DON'T WANT THIS STEP TO DELETE TABLE DATA AND THEN INSERTING IT.INSTEAD I WANT TO UPDATE TABLE DATA"
            SqlConnection sqlconn = new SqlConnection(ssqlconnectionstring);
            SqlCommand sqlcmd = new SqlCommand(sclearsql, sqlconn);

            sqlconn.Open();
            sqlcmd.ExecuteNonQuery();
            sqlconn.Close();

            //series of commands to bulk copy data from the excel file into our sql table
            OleDbConnection oledbconn = new OleDbConnection(sexcelconnectionstring);
            OleDbCommand oledbcmd = new OleDbCommand(myexceldataquery, oledbconn);
            oledbconn.Open();
            OleDbDataReader dr = oledbcmd.ExecuteReader();
            SqlBulkCopy bulkcopy = new SqlBulkCopy(ssqlconnectionstring);
            bulkcopy.DestinationTableName = ssqltable;

            bulkcopy.WriteToServer(dr);
            while (dr.Read())
            {
                //bulkcopy.WriteToServer(dr);
            }

            Console.WriteLine(".xlsx file imported succssessfully into database.", bulkcopy.NotifyAfter);
            oledbconn.Close();
        }

I don't know how to update it that's why what I do is I delete data and then insert. Please help me with Updating columns QOH based on primarykey table LocalSKU.

I tried the following thing but it gives error saying "Merge statement must be terminated by a semi-column(;)"

             SqlCommand sqlcmd = new SqlCommand(@"MERGE Inventory AS target
                              USING (select LocalSKU, QOH from @source)  as source
                                ON (source.ID = target.ID)
                              WHEN MATCHED THEN
                              UPDATE SET QOH = source.QOH 
                              WHEN NOT MATCHED THEN
                              INSERT (LocalSKU, QOH )
                              VALUES (source.LocalSKU, source.QOH )", sqlconn);

            SqlParameter param=new SqlParameter();
            sqlcmd.Parameters.AddWithValue("@source", ssqltable);
            param.SqlDbType = SqlDbType.Structured;
            param.TypeName = "dbo.tStudent";

Your query should be: "select [KitSKU] , [Quantity] from Kits" — Habib
– Habib, Commented Apr 2, 2014 at 17:58
@user2525244 Perhaps it is better to ask the update question, in another question. More answers will help you and faster, since this question has been answered already. — Max
– Max, Commented Apr 3, 2014 at 6:38

Max · Accepted Answer · 2014-04-02 18:19:23Z

4

Basically I want only two columns KitSKU and Quantity out of many columns in table Kits.

Change:

SqlDataAdapter da = new SqlDataAdapter("select * from [KitSKU] , [Quantity] from Kits", sqlCon);

To:

SqlDataAdapter da = new SqlDataAdapter("select [KitSKU] ,[Quantity] from Kits", sqlCon);

you want to select column: [KitSKU] and [Quantity] from table: Kits

Thanks Max.. also If I want to Update it rather than inserting, how to implement that.

Use following code, for updating KitSKU and Quantity:

SqlDataAdapter da = new SqlDataAdapter("update Kits set [KitSKU] = 'entersku' ,[Quantity] = 5 where [KutSKU] = 'what record it should update'", sqlCon);

But I wouldn't recommend that code, since it isn't parameterized and I think you should try with some basic sql lessons and using sql parameters.

edited Apr 2, 2014 at 18:19

answered Apr 2, 2014 at 17:59

Max

13.5k16 gold badges78 silver badges105 bronze badges

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

7 Comments

user2525244 Over a year ago

Thanks Max.. also If I want to Update it rather than inserting, how to implement that.

user2525244 Over a year ago

KitSKU and Quantity in table Kits. Actuallly I am importing excel into sql data. Currently I am implementing it by deleting data from that table and then inserting. But I want to implement it as Update.

Max Over a year ago

What data types are KitSKU and Quantity?

user2525244 Over a year ago

KitSKU varchar(200) with pk [is Identity is set to No] and Quantity int

Max Over a year ago

So KitSKU is your primary key? So you only want to update the Quantity?

|

Collectives™ on Stack Overflow

select , update Query c# error

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related