question about locking

Hi,
I have a table where we save loan applications.
I've created a web page for processing these applications.
Now there are up to 10 people using the site, and they sometimes end up with
the same application.
In the table there is a column called STATUS. If the value of STATUS is -1,
the application is ready for processing.
The Id for the application is then selected, the table is updated and the
status changes to 1. Then the row is selected based on the Id.
They started out with only two people using the application, so everything
worked fine and everybody was happy, but not anymore.
So now Im looking into locking!
Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
better way to do this ?
BR,
SteinHi Stein
Locking is a huge topic, so you really should read about it in the Books
Online before you start changing your code.
You need to know that locking is not a problem in SQL Server. SQL Server
acquires and releases locks constantly. The problem is BLOCKING, when a lock
is held for too long and other processes block on the lock.
From your limited description, it's impossible to tell exactly what the
problem is, so we really can't know what will fix it.
The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
the SELECT. As soon as a result set is returned, the lock will be released.
So my guess is that it is not nearly enough to solve your problem.
You can use sp_who to see who is blocked, or use the current activity screen
in Enterprise Manager.
To solve this problem, you are most likely going to need to determine:
Which process is blocked and what exact statement is it trying to execute?
What process is it blocked by, and what resources are being locked by that
blocker process.
Also:
How big is the table?
What indexes are on it?
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
> Hi,
> I have a table where we save loan applications.
> I've created a web page for processing these applications.
> Now there are up to 10 people using the site, and they sometimes end up
> with the same application.
> In the table there is a column called STATUS. If the value of STATUS
> is -1, the application is ready for processing.
> The Id for the application is then selected, the table is updated and the
> status changes to 1. Then the row is selected based on the Id.
> They started out with only two people using the application, so everything
> worked fine and everybody was happy, but not anymore.
> So now Im looking into locking!
> Will "SELECT Id FROM BLABLA WITH(ROWLOCK)" fix the problem ? Or is there a
> better way to do this ?
> BR,
> Stein
>|||Hi Kalen,
I'll try to explain.
This is the table:
CREATE TABLE [dbo].[App_Application](
[LOPENR] [int] IDENTITY(1,1) NOT NULL,
[SOKNADID] [char](14) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[IDENT] [char](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[STATUS] [int] NULL,
CONSTRAINT [PK_SOKNAD_tmp] PRIMARY KEY CLUSTERED (
[LOPENR] ASC
) ON [PRIMARY]
) ON [PRIMARY]
The table has over 100 columns, so I just included the first four.
So, in this table we save all new applications.
I use the value of the STATUS column to see if the applications is under, or
done processing.
And this is the SP:
LTER PROCEDURE [dbo].[App_GetNextApplication]
@.Ident VARCHAR(20),
@.UPDATESTATUS BIT = 0
AS
BEGIN
DECLARE @.appIdentity VARCHAR(20),
@.Tag VARCHAR(20)
IF @.Ident IN (
SELECT IDENT
FROM App_Application app
WHERE app.IDENT = @.Ident
AND app.STATUS IN (
SELECT
VERDI
FROM
ApS_ApplicationStatus
WHERE
TAG = '<behandling>'
)
)
BEGIN
--Bruker har sknader som er til behandling EDIT: User allready has an
applcation under processing
SET @.tag = '<behandling>'
END
ELSE BEGIN
--Bruker har ingen sknader som er til behandling, hent ny EDIT: User
has no application under processing, get a new one
SET @.tag = '<ikke_registrert>'
END
SET @.appIdentity = (
SELECT TOP 1 app.SOKNADID
FROM App_Application app
WHERE app.STATUS IN (
SELECT VERDI
FROM ApS_ApplicationStatus
WHERE TAG = @.tag
)
ORDER BY app.POST_DATO ASC
)
IF @.UPDATESTATUS = 1 BEGIN
DECLARE @.STATUS AS VARCHAR(20)
EXEC ApS_GetApplicationStatus '<behandling>', @.STATUS OUTPUT
UPDATE App_Application
SET STATUS = @.STATUS,
STATUS_FORANDRET_IDENT = @.Ident,
STATUS_FORANDRET_DATO = GETDATE(),
IDENT = @.Ident,
REG_DATO = GETDATE()
WHERE SOKNADID = @.appIdentity
END
SELECT @.appIdentity AS 'SOKNADID',
app.IDENT,
app.POST_DATO
FROM App_Application app
WHERE app.SOKNADID = @.appIdentity
END
So then, where I set the @.appIdentity, I need to make sure that two users
never end up with the same identity.
Or maybe I have to lock until the @.UPDATESTATUS section is done ?
I dont know, I need help! :)
BR,
Stein Lundbeck
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:<uRoQXwCeGHA.3632@.TK2MSFTNGP02.phx.gbl>...
> Hi Stein
> Locking is a huge topic, so you really should read about it in the Books
> Online before you start changing your code.
> You need to know that locking is not a problem in SQL Server. SQL Server
> acquires and releases locks constantly. The problem is BLOCKING, when a
> lock is held for too long and other processes block on the lock.
> From your limited description, it's impossible to tell exactly what the
> problem is, so we really can't know what will fix it.
> The SELECT ... WITH (ROWLOCK) will only hold the lock for the duration of
> the SELECT. As soon as a result set is returned, the lock will be
> released.
> So my guess is that it is not nearly enough to solve your problem.
> You can use sp_who to see who is blocked, or use the current activity
> screen in Enterprise Manager.
> To solve this problem, you are most likely going to need to determine:
> Which process is blocked and what exact statement is it trying to execute?
> What process is it blocked by, and what resources are being locked by that
> blocker process.
> Also:
> How big is the table?
> What indexes are on it?
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Stein Lundbeck" <fatlaces@.newsgroups.nospam> wrote in message
> news:eR26neCeGHA.3484@.TK2MSFTNGP04.phx.gbl...
>|||Hello Stein,
It seems that in the whole SP you read a row from table and you do n't want
other sessioin to update and/or read the row before you update/process the
row in the SP. You may want to use consider set "repeatable read isolation
level" for this sitaution and put all these query in one transaction. The
row shall be locked in the whole transaction. Also, you may use hint
"xlock" for select query so that it could lock other reads.
Best Regards,
Peter Yang
MCSE2000/2003, MCSA, MCDBA
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

question - inserting with formview control

Using formview control, I'm trying to insert a record. In the process of inserting, I want to save the categoryid from a shared class. The code runs fine but categoryid gets saved as null...Any pointers?? [When I display, it shows the value]

thanks

protected void savebutton_click(object sender, EventArgs e)
{
//this statement runs fine under debug...but values do not get saved?
SqlDataSource1.InsertParameters.Add("@.category", SharedValues.category.ToString());
SqlDataSource1.Insert();
}

<asp:FormView ID="FormView1" runat="server" DataSourceID="SqlDataSource1"
DataKeyNames="itemid" DefaultMode="edit" OnDataBound="FormView1_DataBound">
<EditItemTemplate>

Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>


<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update"
Text="Update">
</asp:LinkButton>
<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel"
Text="Cancel">
</asp:LinkButton>

</EditItemTemplate>
<InsertItemTemplate>
Title:
<asp:TextBox ID="titleTextBox" runat="server" Text='<%# Bind("title") %>'>
</asp:TextBox><br/>
Description:
<asp:TextBox ID="descriptionTextBox" runat="server" Text='<%# Bind("description") %>' Rows="10" TextMode="MultiLine" Width="500px" Height="166px">
</asp:TextBox><br/>

<asp:label ID="categoryLbl" runat="server" Text='<%# SharedValues.category %>'></asp:label>
<br/>

<div class="actionbuttons">
<Club:RolloverButton ID="GreenRolloverButton3" CommandName="Insert" Text="Save"
runat="server" OnClick="savebutton_click" />
<Club:RolloverLink ID="GreenRolloverLink2" Text="Cancel" runat="server" NavigateURL="Classifieds.aspx" />
</div>
</InsertItemTemplate>

</asp:FormView>
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ClubSiteDB %>"
InsertCommand="insert into tads (title,[description], categoryid) values (@.title,@.description, @.category)"
SelectCommand="Select title, [description], categoryid from tads whereitemid=@.itemid"
UpdateCommand="update tads set title = @.title, [description] = @.description where itemid = @.itemid" >
<SelectParameters>
<asp:QueryStringParameter Name="itemid" QueryStringField="itemid" Type="Int32" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="title" Type="String" />
<asp:Parameter Name="description" Type="String" />
<asp:Parameter Name="category" Type="Int32" />
</InsertParameters>
</asp:SqlDataSource>

You shouldn't need to handle the Button's Click event at all. The FormView automatically knows how to insert items with a data source control. The command name of the button should be enough to get the insert to happen. Also, rather than adding the parameter to the data source's InsertParameters collection, you should handle the data source's Inserting event and add a SqlParameter to the command:
void SqlDataSource1_Inserting(...) {
SqlParameter parameter = new SqlParameter("@.param_name", <param value>);
e.Command.Parameters.Add(parameter);
}
Thanks,
Eilon

Question

In SqlServer, What is the datatype to mask a field of a table ? I mean if I want to save a password in a table, entering to SQLServer I'd want to display "*****" instead of "hello".

Thanks...

There are HTML, .NET, and Win32 controls that can be used to mask user input as they're entering some sensitive data in your application.

If, however, you want to ensure that users can't query your database for the names and passwords of all of the other users of the database, then you'll want to use any of a variety of permissions on your database.

For instance, if some operation is being done on the user's behalf, you could control the permissions on a table containing sensitive data to allow only indirect access (e.g. allow a server process to insert, update, delete from the table on the user's behalf, but disallow direct manipulation of the underlying table by the user). This obviously requires some form of strong and reliable user authentication.

You could also use row and/or column level permissions to control the amount of data that a user is able to see. For example, row level security (applied correctly) would allow everyone to perform a SELECT username, password FROM users and only see the data from rows that they had specifically been given access to.

There is lots of good documentation on establishing permissions on securables (tables, views, columns, etc.) in the SQL Server Books Online. There is also an excellent article on this subject at: http://www.microsoft.com/technet/prodtechnol/sql/2005/multisec.mspx