Update row using a stored procedure

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update row using a stored procedure

Author

Topic

gator8869
Starting Member

37 Posts

Posted - 2010-05-17 : 17:19:39

The goal is to have the user be able to update the To Do List and the click the update button and have the update button run the stored procedure and update the SQL table.

SQL TABLE
=========

CREATE TABLE ThingsToDo
(
ToDoID int PRIMARY KEY IDENTITY,
EmployeeID int,
ToDoChkBox1 bit,
ToDoItem1 varchar(255),
ToDoPriority1 char(6),
ToDoChkBox2 bit,
ToDoItem2 varchar(255),
ToDoPriority2 char(6),
ToDoChkBox3 bit,
ToDoItem3 varchar(255),
ToDoPriority3 char(6),
ToDoChkBox4 bit,
ToDoItem4 varchar(255),
ToDoPriority4 char(6),
ToDoChkBox5 bit,
ToDoItem5 varchar(255),
ToDoPriority5 char(6),
ToDoChkBox6 bit,
ToDoItem6 varchar(255),
ToDoPriority6 char(6),
ToDoChkBox7 bit,
ToDoItem7 varchar(255),
ToDoPriority7 char(6),
ToDoChkBox8 bit,
ToDoItem8 varchar(255),
ToDoPriority8 char(6),
ToDoChkBox9 bit,
ToDoItem9 varchar(255),
ToDoPriority9 char(6),
ToDoChkBox10 bit,
ToDoItem10 varchar(255),
ToDoPriority10 char(6),
ToDoChkBox11 bit,
ToDoItem11 varchar(255),
ToDoPriority11 char(6),
ToDoChkBox12 bit,
ToDoItem12 varchar(255),
ToDoPriority12 char(6),
SubmitBy varchar(50),
DateTimeAdded timestamp,
)

SQL STORED PROCEDURE
====================

CREATE PROCEDURE dbo.UpdateToDoList
@ToDoID int PRIMARY KEY IDENTITY,
@EmployeeID int,
@ToDoChkBox1 bit,
@ToDoItem1 varchar(255),
@ToDoPriority1 char(6),
@ToDoChkBox2 bit,
@ToDoItem2 varchar(255),
@ToDoPriority2 char(6),
@ToDoChkBox3 bit,
@ToDoItem3 varchar(255),
@ToDoPriority3 char(6),
@ToDoChkBox4 bit,
@ToDoItem4 varchar(255),
@ToDoPriority4 char(6),
@ToDoChkBox5 bit,
@ToDoItem5 varchar(255),
@ToDoPriority5 char(6),
@ToDoChkBox6 bit,
@ToDoItem6 varchar(255),
@ToDoPriority6 char(6),
@ToDoChkBox7 bit,
@ToDoItem7 varchar(255),
@ToDoPriority7 char(6),
@ToDoChkBox8 bit,
@ToDoItem8 varchar(255),
@ToDoPriority8 char(6),
@ToDoChkBox9 bit,
@ToDoItem9 varchar(255),
@ToDoPriority9 char(6),
@ToDoChkBox10 bit,
@ToDoItem10 varchar(255),
@ToDoPriority10 char(6),
@ToDoChkBox11 bit,
@ToDoItem11 varchar(255),
@ToDoPriority11 char(6),
@ToDoChkBox12 bit,
@ToDoItem12 varchar(255),
@oDoPriority12 char(6),
@SubmitBy varchar(50),
@DateTimeAdded timestamp,
AS
INSERT ThingsToDo( ToDoID, EmployeeID,
ToDoChkBox1, ToDoItem1, ToDoPriority1,
ToDoChkBox2, ToDoItem2, ToDoPriority2,
ToDoChkBox3, ToDoItem3, ToDoPriority3,
ToDoChkBox4, ToDoItem4, ToDoPriority4,
ToDoChkBox5, ToDoItem5, ToDoPriority5,
ToDoChkBox6, ToDoItem6, ToDoPriority6,
ToDoChkBox7, ToDoItem7, ToDoPriority7,
ToDoChkBox8, ToDoItem8, ToDoPriority8,
ToDoChkBox9, ToDoItem9, ToDoPriority9,
ToDoChkBox10, ToDoItem10, ToDoPriority10,
ToDoChkBox11, ToDoItem11, ToDoPriority11,
ToDoChkBox12, ToDoItem12, ToDoPriority12,
SubmitBy, DateTimeAdded,
VALUES( @ToDoID, @EmployeeID,
@ToDoChkBox1, @ToDoItem1, @ToDoPriority1,
@ToDoChkBox2, @ToDoItem2, @ToDoPriority2,
@ToDoChkBox3, @ToDoItem3, @ToDoPriority3,
@ToDoChkBox4, @ToDoItem4, @ToDoPriority4,
@ToDoChkBox5, @ToDoItem5, @ToDoPriority5,
@ToDoChkBox6, @ToDoItem6, @ToDoPriority6,
@ToDoChkBox7, @ToDoItem7, @ToDoPriority7,
@ToDoChkBox8, @ToDoItem8, @ToDoPriority8,
@ToDoChkBox9, @ToDoItem9, @ToDoPriority9,
@ToDoChkBox10, @ToDoItem10, @ToDoPriority10,
@ToDoChkBox11, @ToDoItem11, @ToDoPriority11,
@ToDoChkBox12, @ToDoItem12, @ToDoPriority12,
@SubmitBy, @DateTimeAdded)
RETURN

DESIGN CODE
===========

</td>
<td class="style37" nowrap="nowrap">
<asp:Panel ID="Panel1" runat="server" BackColor="#6B696B" Height="396px">
<table class="style24">
<tr>
<td bgcolor="Silver" colspan="2">
<span class="style34" lang="en-us">THING|S TO DO</span></td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox1" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox21" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList1" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox2" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox20" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList10" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox3" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox3" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList11" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox4" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox22" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList12" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox5" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox23" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList13" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox6" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox24" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList14" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox7" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox25" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList15" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox8" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox26" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList16" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style32">
<asp:CheckBox ID="CheckBox9" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style33">
<asp:TextBox ID="TextBox27" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList17" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style35">
<asp:CheckBox ID="CheckBox10" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style36">
<asp:TextBox ID="TextBox28" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList18" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style35">
<asp:CheckBox ID="CheckBox11" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style36">
<asp:TextBox ID="TextBox29" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList19" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style35">
<asp:CheckBox ID="CheckBox12" runat="server" ForeColor="#6B696B" Text="-" />
</td>
<td class="style36">
<asp:TextBox ID="TextBox30" runat="server" CssClass="style28" Width="480px"></asp:TextBox>
<asp:DropDownList ID="DropDownList20" runat="server" BackColor="#CCCCFF"
Height="18px" Width="119px">
<asp:ListItem>High</asp:ListItem>
<asp:ListItem>Medium</asp:ListItem>
<asp:ListItem>Low</asp:ListItem>
<asp:ListItem>Hold</asp:ListItem>
</asp:DropDownList>
</td>
</tr>
<tr>
<td class="style35" colspan="2">
<asp:Button ID="UpdateThingsToDo" runat="server" Text="Update" Width="60px"
style="height: 26px" />
<asp:Button ID="Button14" runat="server" Text="Print" Width="60px" />
</td>
</tr>
</table>
</asp:Panel>

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-17 : 19:57:39

You should normalize the table as....

CREATE TABLE ThingsToDo
(
ToDoID int PRIMARY KEY IDENTITY,
EmployeeID int,
SubmitBy varchar(50),
DateTimeAdded timestamp
)

CREATE TABLE ToDoItems
(
ItemID int PRIMARY KEY IDENTITY,
ToDoID int REFERS ThingsToDo.ToDoID,
ItemChkBox bit,
ItemDescription varchar(255),
ItemPriority char(6)
)

Then create two update stored procedures: one to update a ThingsToDo record and the other to update a ToDoItems record.

Then in the application, you should check which values were changed by the user before executing the update stored procedures. And only call the procedure for the records that were actually changed.

Nikhil1home
Starting Member

23 Posts

Posted - 2010-05-17 : 20:01:08

a little correction in previous post:

The ToDoID column in the second table should be defined this way...

ToDoID int REFERENCES ThingsToDo(ToDoID),

gator8869
Starting Member

37 Posts

Posted - 2010-05-23 : 12:17:11

Thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources