Logic edits all existing rows instead of adding new when using Save stored procedure
I have an issue regarding SQL stored procedures. I have connected my SQL Server db to my ASP.NET Web Forms app and have created a button that uses Stored Procedures to Save data inside the db.
I only want to use two fields on my front-end, namely Name and Surname. I do not want other moderators to input the Id (which is the primary key), so the stored procedure must increment it by itself once they add some Name and Surname.
However, once data has been put in the fields, all rows get populated with that data?
Example database.
| Id | Name | Surname |
|---|---|---|
| 1 | Alex | Smith |
| 2 | Bob | Phoenix |
| 3 | Correy | Lollas |
After clicking on the Insert button I have created populating Name and Surname (Id is supposed to autoincrement)
| Id | Name | Surname |
|---|---|---|
| 1 | Test | Name |
| 2 | Test | Name |
| 3 | Test | Name |
Stored procedure, although I think the problem is in the logic.
ALTER PROCEDURE [dbo].[spOrders_Save]
@Id int = NULL,
@Name nvarchar(max),
@Surname nvarchar(max),
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM tblOrders WHERE ([Id] = @Id OR @Id IS NULL) )
BEGIN
UPDATE tblOrders
SET
[Name] = @Name,
[Surname] = @Surname ,
WHERE ([Id] = @Id OR @Id IS NULL)
END
ELSE
BEGIN
INSERT INTO tblOrders
( [Name],
[Surname]
) VALUES
( @Name,
@Surname)
END
END
Logic
protected void FormView_Orders_DataBound(object sender, EventArgs e)
{
// If we have no rows, then change the formview into insert mode otherwise we are updating an existing row.
if (this.ORDERS_rowCount == 0)
{
FormView_Orders.ChangeMode(FormViewMode.Insert);
for (int p = 1; p <= ORDERS_PAGE_COUNT; p++)
{
(FormView_Orders.FindControl("btSaveData_" + p.ToString()) as Button).CommandName = "insert";
}
}
else
{
FormView_Orders.ChangeMode(FormViewMode.Edit);
for (int p = 1; p <= ORDERS_PAGE_COUNT; p++)
{
(FormView_Orders.FindControl("btSaveData_" + p.ToString()) as Button).CommandName = "update";
}
}
}
What I have tried to fix the issue:
I tried to hardcode the Id so see what will happen.
Surprisingly if I run localhost:1234/something.aspx?Id=4 and then enter the details, a new row is added. So I assume something is wrong with the C# logic? Or maybe the stored procedure? I know the front-end is okay, because it is automated.
| Id | Name | Surname |
|---|---|---|
| 1 | Alex | Smith |
| 2 | Bob | Phoenix |
| 3 | Correy | Lollas |
| 4 | Test | Name |
OK, as you note, you do NOT want to show the ID. And you should not = the PK id is for internal.
I don’t see the need for a stored procedure in SQL.
And the built in .net “adaptors” can handle if the row exists, and can handle if the row does not. And it will add or update for you automatic in both cases.
I really don’t see the problem.
Say we have this data table with 3 records like this:
So our markup is this:
<div style="width:40%">
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="ID"
cssclass="table table-hover">
<Columns>
<asp:TemplateField HeaderText ="First Name">
<ItemTemplate>
<asp:TextBox ID="txtFirst" runat="server" Text = '<%# Eval("FirstName") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="Last Name">
<ItemTemplate>
<asp:TextBox ID="txtLast" runat="server" Text = '<%# Eval("LastName") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText ="City">
<ItemTemplate>
<asp:TextBox ID="txtCity" runat="server" Text = '<%# Eval("City") %>'></asp:TextBox>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>
<asp:Button ID="cmdSave" runat="server" Text="Save" CssClass="btn-primary" />
<asp:Button ID="cmdAdd" runat="server" Text="Add Row" CssClass="btn-primary" style="margin-left:20px"/>
<br />
</div>
And the display thus looks like this:
Note the save button. It works like Word or Excel. You edit away, then hit save. So this is how 99% of software has worked for the last 30 years!
So you can hit add row - a new row will appear. You can tab around in that grid - make any changes to any row you want. We don't display the ID. And if you don't hit save, then the data and rows are not added and saved.
And the code to add the rows, and save BOTH edits and NEW rows looks like this:
private DataTable rstPeople = new DataTable();
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
LoadGrid();
ViewState["MyTable"] = rstPeople;
}
else
rstPeople = (DataTable)ViewState["MyTable"];
}
public void LoadGrid()
{
using (SqlCommand cmdSQL = new SqlCommand("SELECT * from People",
new SqlConnection(Properties.Settings.Default.TEST4)))
{
cmdSQL.Connection.Open();
rstPeople.Load(cmdSQL.ExecuteReader());
GridView1.DataSource = rstPeople;
GridView1.DataBind();
}
}
protected void cmdAdd_Click(object sender, EventArgs e)
{
// add a new row to the grid
DataRow OneRow = rstPeople.Rows.Add();
// OneRow("FirstName") = "Hello" ' setup defaults for valuees
GridView1.DataSource = rstPeople;
GridView1.DataBind();
}
protected void cmdSave_Click(object sender, EventArgs e)
{
// pull grid rows back to table.
foreach (GridViewRow rRow in GridView1.Rows)
{
int RecordPtr = rRow.RowIndex;
DataRow OneDataRow;
OneDataRow = rstPeople.Rows[RecordPtr];
OneDataRow["FirstName"] = ((TextBox)rRow.FindControl("txtFirst")).Text;
OneDataRow["LastName"] = ((TextBox)rRow.FindControl("txtLast")).Text;
OneDataRow["City"] = ((TextBox)rRow.FindControl("txtCity")).Text;
}
// now send table back to database with updates
string strSQL = "SELECT ID, FirstName, LastName, City from People WHERE ID = 0";
using (SqlCommand cmdSQL = new SqlCommand(strSQL,
new SqlConnection(Properties.Settings.Default.TEST4)))
{
cmdSQL.Connection.Open();
SqlDataAdapter daupdate = new SqlDataAdapter(cmdSQL);
SqlCommandBuilder cmdBuild = new SqlCommandBuilder(daupdate);
daupdate.Update(rstPeople);
}
}
The above is:
Not much code - if you writing too much code - then you doing this wrong.
You don't need stored procedure.
And like Word, Excel, and 99% of software? You just edit - and then hit save, and you are done. No complex UI, no complex edit. I mean, you could perhaps drop in a undo button (1 line of code - just call load grid again - really easy!).
But, as you can see, the autonumber (identity) column is all managed and done by .net - you don't have to worry about it. In above, we send the table back to the database IN ONE SHOT. It does not care if that table has a mix of edits, new rows.
And I suppose I might want to add a delete button - I would probably not have that button display unless the given row has the focus.
But, above shows:
- little markup
- very clean - straight forward code - nothing fancy - no linq or anything.