Solved

Update sql Statement based on a GridView field

Posted on 2012-12-20
5
416 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

728 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