Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
dkeller
Starting Member
2 Posts |
 Posted - 2009-05-07 : 14:46:05
|
| I am new to Visual Studio 2008 and very basic knowledge of SQL and could use some help with getting the update and delete queries to work properly in my data grid. Once someone can help me with the correct code and syntax I could expand my knowledge on that sample.I am using a Web form using VB with a simple data grid and a simple table with one column. I have enabled editing on the data grid. I have the update and delete queries build in query builder of my SqlDataSource1. The issue I’m having is if I edit and then update or delete the row, all rows get updated with same value or all rows get deleted. In my research it appears I need to specify somehow “CURRENT OF cursor_name” so it only acts on the row I have selected to update or delete. I’m not sure how to do this correctly.Below is my code for the SqlDataSource1 update and delete queries. Can someone help me with the correct query syntax to only update or delete the selected row I am editing? I also need to figure out my Insert query where it would get the values form a text box on my form but I plan to post that request separately unless someone can also help me with that.My forms data source code<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:hsConnectionString2 %>" DeleteCommand="DELETE FROM fe_table_fe_program_types" InsertCommand="INSERT INTO fe_table_fe_program_types([Program Type]) VALUES ()" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [Program Type] AS Program_Type FROM fe_table_fe_program_types" UpdateCommand="UPDATE fe_table_fe_program_types SET [Program Type] = @Program_Type"> <UpdateParameters> <asp:Parameter Name="Program_Type" /> </UpdateParameters> </asp:SqlDataSource>Here is the entire form code in case you see anything else I’m missing or doing wrong.<%@ Page Language="vb" AutoEventWireup="false" CodeBehind="Update Project Types.aspx.vb" Inherits="Test_Web_Query.WebForm1" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>Update Project Types</title></head><body> <form id="form1" runat="server"> <div> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowEditButton="True" /> <asp:BoundField DataField="Program_Type" HeaderText="Program_Type" SortExpression="Program_Type" /> </Columns> </asp:GridView> <br /> <br /> </div> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:hsConnectionString2 %>" DeleteCommand="DELETE FROM fe_table_fe_program_types" InsertCommand="INSERT INTO fe_table_fe_program_types([Program Type]) VALUES ()" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [Program Type] AS Program_Type FROM fe_table_fe_program_types" UpdateCommand="UPDATE fe_table_fe_program_types SET [Program Type] = @Program_Type"> <UpdateParameters> <asp:Parameter Name="Program_Type" /> </UpdateParameters> </asp:SqlDataSource> </form></body></html>Thanks in advance for any assistance you can provide. |
|
|
whitefang
Enterprise-Level Plonker Who's Not Wrong
272 Posts |
Posted - 2009-05-08 : 07:41:58
|
| ???????You are specifying to delete all records (DeleteCommand="DELETE FROM fe_table_fe_program_types") or update all records (UpdateCommand="UPDATE fe_table_fe_program_types SET [Program Type] = @Program_Type")Where is your filter clause?For an example:UpdateCommand="UPDATE fe_table_fe_program_types SET [Program Type] = @Program_Type WHERE id = @id"Add the update/delete parameter for "@id".Also, using SqlDataSources is a poor decision. Linq, MVC, SqlDataSources are all "hacks" in the .NET framework. Their good for small projects but anything that requires scalability and easy maintenance, go with a proper n-tier model. |
 |
|
|
dkeller
Starting Member
2 Posts |
Posted - 2009-05-08 : 16:02:08
|
| Thank You Whitefang, you pointed me in the right direction and now I have it working, I was also missing the ID column in my table that i needed to add and auto number.Thanks for the tip on n-tier however I'm not familiar with it so not sure how to do it and I don’t have additional servers to do it, I'm a rookie and the visual aids in Visual Studio is where I figured out how to use the SqlDataSource. This will be at most a 300 user app with only half that number hitting the server at any given time. I did research n-tier and now understand what it is and realize some of our applications use this method that were developed by our developers, I'm just a technical project manager tasked with small app design that our developers don't have time for which is why I struggle with what seems very basic stuff to you.Could you offer suggestions on how to code a button in VB.net to insert a value from a field called TextBox1 on my form to my table and “program_type” field?All of this is just helping me learn the basic stuff before I start building the Webb app I need to build. |
|
|
|
|
|
|
Data grid Update/Delete query help needed.