Click here to Skip to main content
15,557,873 members
Please Sign up or sign in to vote.
4.00/5 (1 vote)
See more:
Hi friends,
I am using MS-Access as db in C# .net Windows Application
I have Two Table as temp & Count.
temp table Column are
ItemCode as Number,
ItemName as text,
Qty as number,

Count table column are
ItemCode as Number,
ItemName as text,
Count as number,


In temp table values are
ItemCode            ItemName             Qty
  5                    fish                2
  2                    rice                2
  3                    chicken             3
  1                    bone less fish      3
  5                    fish                4
  2                    rice                3
  3                    chicken             4
  1                    bone less fish      6

Count Table Values
ItemCode            ItemName             Count
  1                    bone less fish      0  
  2                    rice                0
  3                    chicken             0
  5                    fish                0



I want to Update the Count table as
ItemCode            ItemName             Count
  1                    bone less fish      9  
  2                    rice                5
  3                    chicken             7
  5                    fish                6

How can i update the count table like this Pleas Help me


Thanks With Regards
Dineshkumar R
Posted
Updated 1-Jun-11 2:07am
v2

i think you have used foreign key concept to link the tables ...

then just pass the query ..

use select query

and in while(reader.read())
{

write your update query to increment the count
}
 
Share this answer
 
Comments
R_D_K 1-Jun-11 8:42am    
that update command only i asking
Step 1: Read in temporary table with query:
SQL
SELECT ItemCode, SUM(Qty) as Cnt FROM temp GROUP BY temp.ItemCode

Step 2: While reading values from that query
C#
while(reader.read())
{
    //Here build your update query on a new OLEDBCommand
    string sqlString="UPDATE Count1 SET Cnt=? Where ItemCode=?"
    //Build your update command
    myCommand.Parameters.Add(@"Count",/*Put your Item Count which is read from reader*/);
    myCommand.Parameters.Add(@"ItemCode",/*Put your Item Code*/);
    // Other stuff if you want to
    myCommand.ExecuteNonQuery();
}

I hope this clears it.

[Edit]Below is for SQL Server Sorry about that I missed "Access" tag.
SQL
UPDATE [Count] set [Count].[Count] = tmp.cnt FROM (SELECT ItemCode, SUM(Qty) as cnt FROM temp GROUP BY temp.ItemCode) as tmp WHERE [Count].ItemCode=tmp.ItemCode


Advice: Do not give Table/Column names which has other meanings in the database like "Count".
 
Share this answer
 
v3
Comments
R_D_K 1-Jun-11 8:41am    
i change the Count table as Count1 And count as cot in Count1 table
<pre>
OleDbCommand cmd22=new OleDbCommand(" UPDATE [Count1] set [Count1].[Cot] = tmp.cnt FROM (SELECT ItemCode, SUM(Qty) as cnt FROM temp GROUP BY temp.ItemCode) as tmp WHERE [Count1].ItemCode=tmp.ItemCode",conec);
cmd22.ExecuteNonQuery();
</pre>

I got following error message
<pre>
Syntax error (missing operator) in query expression 'tmp.cnt FROM (SELECT ItemCode, SUM(Qty) as cnt FROM temp GROUP BY temp.ItemCode) as tmp'.
</pre>
yesotaso 1-Jun-11 10:09am    
Ahem... Sorry about that I missed "Access" tag. This is for SQL server.
R_D_K 1-Jun-11 10:33am    
if u know access mean tell what i want to do

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



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900