How to check if Current ID exits in that table or not

Question

Hi,

I am using VB.net and Sql server 2005.

I have GridView in Application where I have my CPUserID. There can be thousands records in GridView with different CPUserIDs.

Now I have a button "Allocate Token". But before allocating the token I want to check in my Token Table that if that CPUserID has already exists in table it should not allow user to allocate token and will return some message for that user.

 For Each curRow As GridViewRow In GridView1.Rows
            Dim cpuserid As Label = CType(curRow.Cells(1).FindControl("lblCPUserID"), Label)                
        Next

The TOKEN table structure is given below:

TokenID, CPUserID, StatusID (All Integer)

Please Suggest! with some example code

Answer 1

Perform a query on the Token table to see if there already exists a row in that table for the given id:

SELECT COUNT(*) FROM Token WHERE CPUserID = 5

for instance.

In order to do that in VB.NET, you'll have to use the SqlConnection and SqlCommand classes. Also, be sure to make use of parameterized queries.

In C#, the code would look more or less like this:

SqlConnection conn = new SqlConnection ("here comes the connectionstring to the db.");

conn.Open();
try
{
    SqlCommand cmd = new SqlCommand ();
    cmd.Connection = conn;
    cmd.CommandText = "SELECT COUNT(*) FROM Token WHERE CPUserId = :p_UserId";
    cmd.Parameters.Add ("p_UserId", SqlDbType.Int32).Value = 5;
    object result = cmd.ExecuteScalar();

    if( Convert.ToInt32(result) > 0 )
    {
         MessageBox.Show ("Token already exists for user");
    }
}
finally
{
     conn.Close();
}

In order to improve performance, you will have to make sure that you create the correct indexes on the Token table. An index on CPUserId would maybe help for this query.

Answer 2

Hi Guys,

First of all thanks to all of them who responded for this question.

I solved above issues with below solutions:

SQL Procedure:

I created one procedure in SQL server 2005

GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[uspUpdateAllocateToken]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[uspUpdateAllocateToken]

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[uspUpdateAllocateToken]
( @CPUserID INT)
AS

IF NOT EXISTS(SELECT TokenID FROM tblToken WHERE CPUserId=@CPUserID AND StatusID IN (41,47))
BEGIN

UPDATE tblToken
SET 
CPUserID = @CPUserID,
StatusID=47

WHERE
 tblToken.TOKENID = (SELECT TOP 1 TOKENID FROM TBLTOKEN WHERE CPUSERID IS NULL AND STATUSID = 40)
END

Further in my application on my Button Click. I write below code:

Protected Sub ibtnAllocateTokens_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ibtnAllocateTokens.Click
        Try
            Dim conString As String = WebConfigurationManager.ConnectionStrings("LocalSqlServer").ConnectionString
            Dim con As New SqlConnection(conString)
            con.Open()
            Dim cmd As SqlCommand
            For Each gvRow As GridViewRow In GridView1.Rows
                cmd = New SqlCommand("uspUpdateAllocateToken", con)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@CPUserID", SqlDbType.Int).Value = CType(gvRow.Cells(1).FindControl("lblCPUserID"), Label).Text
                cmd.ExecuteScalar()
                lblAllocateTokenMessage.Visible = True
            Next

        Catch ex As Exception
            ErrorHandler.WriteError(ex.Message)
        End Try
    End Sub

Please have a look and let me know if there seems any problem in this implementation.

Cheers!