what i write inside loop to display similar item code on datagridview and insert diff

ahmedsalah

Active member
Joined
Sep 26, 2018
Messages
32
Programming Experience
3-5
[FONT=&quot]Problem

[/FONT]

[h=1]what i write inside loop to display similar item code on datagridview and insert different itemcode on database sql server[/h][FONT=&quot]SQL Server Database(2014) Items Table[/FONT]
[FONT=&quot]ItemCode(pk) ItemName [/FONT]
[FONT=&quot]001 mouse[/FONT]
[FONT=&quot]002 keyboard[/FONT]
[FONT=&quot]003 Headphone[/FONT]
[FONT=&quot]On File Excel sheet 2010 [/FONT]
[FONT=&quot]ItemCode ItemName [/FONT]
[FONT=&quot]001 mouse[/FONT]
[FONT=&quot]002 keyboard[/FONT]
[FONT=&quot]004 screen[/FONT]
[FONT=&quot]005 Ram[/FONT]
[FONT=&quot]Actually i need when import excel file insert different items code that not exist[/FONT]
[FONT=&quot] on sql server database and Exist Items On Database and Found on Excel not insert but display on datagridview .[/FONT]
[FONT=&quot]according to my case insert itemcodes 004,005 on table Items.[/FONT]
[FONT=&quot]and show 001,002 in grid view as exist items . [/FONT]
[FONT=&quot]my function as below [/FONT]
[FONT=&quot]my code (Inside Loop)
[/FONT]

C#:
[COLOR=#000088]public[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]static[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]void[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]ImportFromExcelToDataBase[/COLOR][COLOR=#666600]()[/COLOR][COLOR=#000000]
    [/COLOR][COLOR=#666600]{[/COLOR][COLOR=#000000]
       [/COLOR][COLOR=#660066]Datatable[/COLOR][COLOR=#000000] dt [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]ShowExcelData[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
       [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#000000] dtItems [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]GetSqlItems[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
       
            [/COLOR][COLOR=#000088]for[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]([/COLOR][COLOR=#000088]int[/COLOR][COLOR=#000000] i [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#006666]0[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000] i [/COLOR][COLOR=#666600]<[/COLOR][COLOR=#000000] dt[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Rows[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Count[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000] i[/COLOR][COLOR=#666600]++)[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#666600]{[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#880000]//what i write here [/COLOR]
[COLOR=#880000]                // if itemcode exist on excel exist on sql server database[/COLOR]
[COLOR=#880000]                then display similar items exist on database and excel as 001,002 on datagridview[/COLOR]
[COLOR=#880000]                //else [/COLOR][COLOR=#000000]
                 [/COLOR][COLOR=#880000]// do insert data[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#000088]string[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]Insert[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#008800]"Insert Into Values ("[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]+[/COLOR][COLOR=#000000] data [/COLOR][COLOR=#666600]+[/COLOR][COLOR=#000000] [/COLOR][COLOR=#008800]")"[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]
                [/COLOR][COLOR=#660066]DataAccess[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]ExecuteNonQuery[/COLOR][COLOR=#666600]([/COLOR][COLOR=#660066]Insert[/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#666600]}[/COLOR][COLOR=#000000]
               
    [/COLOR][COLOR=#666600]}[/COLOR][COLOR=#000000]
 [/COLOR][COLOR=#000088]public[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]ShowExcelData[/COLOR][COLOR=#666600]()[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#666600]{[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#000088]string[/COLOR][COLOR=#000000] connectionString [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]string[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Format[/COLOR][COLOR=#666600]([/COLOR][COLOR=#008800]"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\";"[/COLOR][COLOR=#666600],[/COLOR][COLOR=#000000] txtpath[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Text[/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]

            [/COLOR][COLOR=#660066]OleDbConnection[/COLOR][COLOR=#000000] con [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]new[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]OleDbConnection[/COLOR][COLOR=#666600]([/COLOR][COLOR=#000000]connectionString[/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]


            con[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Open[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#000000] dt [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]new[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]

            dt [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] con[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]GetOleDbSchemaTable[/COLOR][COLOR=#666600]([/COLOR][COLOR=#660066]OleDbSchemaGuid[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Tables[/COLOR][COLOR=#666600],[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]null[/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]

            [/COLOR][COLOR=#000088]string[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]SheetName[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] dt[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Rows[/COLOR][COLOR=#666600][[/COLOR][COLOR=#006666]0[/COLOR][COLOR=#666600]][[/COLOR][COLOR=#008800]"TABLE_NAME"[/COLOR][COLOR=#666600]].[/COLOR][COLOR=#660066]ToString[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]


            [/COLOR][COLOR=#660066]OleDbCommand[/COLOR][COLOR=#000000] com [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]new[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]OleDbCommand[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
            com[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Connection[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] con[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]
           
            com[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]CommandText[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]@[/COLOR][COLOR=#008800]"SELECT  [ItemCode],[ItemsName],[ItemAddress] FROM  ["[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]+[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]SheetName[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]+[/COLOR][COLOR=#000000] [/COLOR][COLOR=#008800]"] "[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#660066]OleDbDataAdapter[/COLOR][COLOR=#000000] oledbda [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]new[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]OleDbDataAdapter[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
            oledbda[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]SelectCommand[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] com[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#660066]DataSet[/COLOR][COLOR=#000000] ds [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#000088]new[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]DataSet[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
            oledbda[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Fill[/COLOR][COLOR=#666600]([/COLOR][COLOR=#000000]ds[/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]
            dt [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] ds[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Tables[/COLOR][COLOR=#666600][[/COLOR][COLOR=#006666]0[/COLOR][COLOR=#666600]];[/COLOR][COLOR=#000000]
            con[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]Close[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#000088]return[/COLOR][COLOR=#000000] dt[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]


        [/COLOR][COLOR=#666600]}[/COLOR][COLOR=#000000]
dt [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]ShowExcelData[/COLOR][COLOR=#666600]();[/COLOR][COLOR=#000000]

 [/COLOR][COLOR=#000088]public[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]GetSqlItems[/COLOR][COLOR=#666600]()[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#666600]{[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#000088]string[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]GetItems[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]@[/COLOR][COLOR=#008800]"select ItemCode,ItemsName,ItemAddress from Items"[/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]


           [/COLOR][COLOR=#660066]DataTable[/COLOR][COLOR=#000000] tbGetItems [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]DataAccess[/COLOR][COLOR=#666600].[/COLOR][COLOR=#660066]ExecuteDataTable[/COLOR][COLOR=#666600]([/COLOR][COLOR=#660066]GetItems[/COLOR][COLOR=#000000] [/COLOR][COLOR=#666600]);[/COLOR][COLOR=#000000]
            [/COLOR][COLOR=#000088]return[/COLOR][COLOR=#000000] tbGetItems [/COLOR][COLOR=#666600];[/COLOR][COLOR=#000000]
        [/COLOR][COLOR=#666600]}[/COLOR][COLOR=#000000]
dtItems [/COLOR][COLOR=#666600]=[/COLOR][COLOR=#000000] [/COLOR][COLOR=#660066]GetSqlItems[/COLOR][COLOR=#666600]();[/COLOR]
[h=1][/h]
 

Latest posts

Back
Top Bottom