Solved

Update sql Statement based on a GridView field

Posted on 2012-12-20
5
405 Views
Last Modified: 2013-01-09
Hello,
I am trying run an update statement based on a current GridView.  The reason is that if the sql table data is updated while in the current GridView, I only want to set an update for each record of the currently selected data.
Insert Statement:
using (SqlCommand command = new SqlCommand())
                        {command.Connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString);
command.CommandText = @"insert into [Comm](CommType, StudentID)
                                                    values(@CommType, @StudentID)";
command.Parameters.Add("@CommType", SqlDbType.VarChar, 16).Value = "Emailed";
command.Parameters.Add("@StudentID", SqlDbType.VarChar, 9).Value = **How do I add a GridView Column Name here for each record in my GridView?***;                           
                            command.Connection.Open();
                            command.ExecuteNonQuery();
                            command.Parameters.Clear();
                            command.Connection.Close();

Open in new window

Comm Table:
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[CommType] [varchar](16) NULL,
	[StudentID] [varchar](9) NULL,

Open in new window

The field I want to grab from the GridView is:
asp:BoundField DataField="StudentID"

Is is possible to do this, or is there a better way to achieve this?

Thank you for any assistance.
0
Comment
Question by:javierpdx
5 Comments
 
LVL 17

Assisted Solution

by:k_murli_krishna
k_murli_krishna earned 167 total points
ID: 38712176
See if this helps:

UPDATE       CommTable ct1
SET               ct1.CommType = @CommType, ct1.StudentID = @StudentID
WHERE      ct1.ID = (SELECT ct2.ID
                           FROM CommTable ct2
                           WHERE ct2.ID = ct1.ID)
AND              ct1.CommType = (SELECT ct2.CommType
                                       FROM CommTable ct2
                                       WHERE ct2.ID = ct1.ID)
AND       ct1.StudentID = (SELECT ct2.StudentID
                                     FROM CommTable ct2
                                     WHERE ct2.ID = ct1.ID);
0
 
LVL 83

Assisted Solution

by:CodeCruiser
CodeCruiser earned 166 total points
ID: 38712716
>command.Parameters.Add("@StudentID", SqlDbType.VarChar, 9).Value = **How do I add a GridView Column Name here for each record in my GridView?***;

So you have to do this for each record in grid.

One option is to loop through the rows, create a CSV of IDs and use that with IN clause.

For Each Row As GridViewRow In grid.Rows
   ID = GridViewRow.Cells(index).Text
Next
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 167 total points
ID: 38712984
This is not a direct answer, since I don't fully understand the problem... rather considerations.

1. The title of your question says it's about UPDATE sql statement, but the code has only INSERT. Confused.

2. Your code implies that you want to indicate in the database that you have sent email to each selected student. If so, then I think you will do better by making it as "transactional" as possible, i.e. you want to update each student not in the gridview, but in the routine that actually sends the email, after it has successfully sent it. It's in fact the opposite to  CodeCruiser's suggestion above, which is to collect all the id's and then update them all in one operation.

Here's one example where this can be important. Let's say, the user has selected 10 students to send email to. The code that sends email successfully sends to 9, but 10th can't resolve the recipient (assuming that it tries to), because that one student is no longer with the institution. If updating is based on the selection in the grid, it will mark what it should have done, rather than what it has actually done.

Another: you are sending to 100 recipients, and half way through the application has crashed. Unless you update the records per one, you either will have noone marked, or everyone.
0
 

Author Comment

by:javierpdx
ID: 38745274
Sorry for the delay in responding to your suggestions.  I have been away due to the holiday break.  I will test this over the weekend and respond as soon as I am able.  Thanks.
0
 

Author Closing Comment

by:javierpdx
ID: 38760287
Thanks for the expert advise.  I appreciate it.

This ended up working:

command.CommandText = @"insert into [Communication](CommType, ID)
values(@CommType, @ID)"
command.Parameters.Add("@CommType", SqlDbType.VarChar, 16).Value = Row.Cells[12].Text;
command.Parameters.Add("@ID", SqlDbType.VarChar, 9).Value = "0" + Row.Cells[2].Text;
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now