Solved

Insert and update

Posted on 2013-06-21
5
365 Views
Last Modified: 2013-06-21
Hi,

I have one record in a table and two records in a gridview as follows.

Id (PK) Name  Points
1         abc        10

and in gridView control
Id  Name     Points
1   abc           20
2   xyz           30

I want to insert and update records from the gridview to the table. In this scenario, Id=1 is already exists in the table so I want to update it and Id=2 is not in the table so I want insert it. i.e. Two operations in one execution. How can I deal this scenario?

Please help me.

Thanks

ayha
0
Comment
Question by:ayha1999
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 39266500
For each Rw as GridviewRow in Gridview1.rows
 ID=rw.cell(0).value
 dim qry as string="select id from table_name where id='" & ID & "'"
 dim t as new datatable
 dim adt as new sqldataadapter(Qry,YourSqlConn)
 t.clear()
 adt.fill(t)
 if t.rows.count>0 then
   'update
     qry="update table set name='" & rw.cell(1).value &"',Points='" & rw.cell(2).value &"' where ID='" & ID &"'"
 else
  'Insert
   qry="INSERT INTO TABLE(NAME,POINTS) VALUES('" & rw.cell(1).value &"','" & rw.cell(2).value &"')"
end
 dim k as new datatable
 dim adk as new sqldataadapter(Qry,YourSqlConn)
k.clear()
 adk.fill(k)
Next
0
 
LVL 13

Accepted Solution

by:
Jesus Rodriguez earned 500 total points
ID: 39266511
Also you can use the merge command with sql. Look at here

http://technet.microsoft.com/en-us/library/bb510625.aspx
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39266515
In the scenario here, insert and update should happen same time because id=1 is already in the table so need to update and id=2 is not there so need to insert. I think in the above code it either insert or update.

Thanks
0
 
LVL 7

Author Comment

by:ayha1999
ID: 39266559
Could you please give me a sample for the scenario I posted?

Thanks

ayha
0
 
LVL 7

Author Closing Comment

by:ayha1999
ID: 39266894
Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question