www.webdeveloper.com
Results 1 to 5 of 5

Thread: A SQL procedure to STOP repeated info being stored

  1. #1
    Join Date
    Aug 2006
    Posts
    25

    A SQL procedure to STOP repeated info being stored

    In the web site that I am building ( in C# language ), a hypothetic customer who would buy something would then be redirected to a Secure payments company where he would make the payment and then the company would send back to my web site, information about this transaction.

    My program would then save this info in a Microsoft SQL database. The problem is that this company uses to send the same info several times repeatedly and I do not want to store the same info more than once.

    So I want a SQL procedure that takes the invoice number of the customer ( contained in its string of info ) and looks inside a table to see if it was already stored there. If it is there ( or not ), it would return a value, which could be false/true or 0/1 so my program could use this value to save a new info or not and then activate ( or not ) some related tasks.

    I am still learning SQL and I tried the below procedure but it is not working.

    Which alternative procedure could solve the problem ?

    ~~~~~~~~~~~~~~~~~~~~~~~~~
    CREATE PROCEDURE VerifyIfInvoiceExists
    (@Invoice VARCHAR(50))
    AS
    SELECT COUNT(*) FROM IPN_received
    WHERE Invoice = @Invoice
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

  2. #2
    Join Date
    Feb 2003
    Posts
    2,745
    Code:
    CREATE PROCEDURE usp_InsertInvoice
        @Invoice VARCHAR(50),
        @rcount tinyint output
    AS
    
    SET NOCOUNT ON
    
    IF NOT Exists(Select InvoiceId FROM Invoices WHERE InvoiceId = @Invoice)
    BEGIN
        Insert Invoices (fieldList)
        Values (valueList)
    END
    
    SET @rcount = @@RowCount
    
    GO
    Last edited by russell; 02-04-2007 at 12:52 PM.

  3. #3
    Join Date
    Aug 2006
    Posts
    25
    Hi Russell

    I don't want to insert any invoice number with this code in this stage.

    I just want that the code could verify if a invoice is there and then return 1 or 0 if it is not there.

    How can I achieve that ?

  4. #4
    Join Date
    Aug 2006
    Posts
    25
    Hi Russell

    Here is the adapted procedure code that I used :
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~
    CREATE PROCEDURE VerifyIfInvoiceExists
    (@Invoice VARCHAR(50),
    @DoesExist BIT OUT)
    AS
    IF EXISTS (SELECT NULL FROM IPN_received
    WHERE Invoice = @Invoice)
    SELECT @DoesExist = 1
    ELSE SELECT @DoesExist = 0
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    And here are the methods that calls it:
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~

    public static bool VerifyIfInvoiceExists(string invoice)
    {

    // get a configured DbCommand object
    DbCommand comm = GenericDataAccess.CreateCommand();
    // set the stored procedure name
    comm.CommandText = "VerifyIfInvoiceExists";
    // create a new parameter
    DbParameter param = comm.CreateParameter();
    param.ParameterName = "@Invoice";
    param.Value = invoice;
    param.DbType = DbType.String;
    param.Size = 30;
    comm.Parameters.Add(param);

    // create a new parameter
    param = comm.CreateParameter();
    param.ParameterName = "@DoesExist";
    param.Direction = ParameterDirection.Output;
    param.DbType = DbType.Boolean;

    comm.Parameters.Add(param);

    return bool.Parse(GenericDataAccess.ExecuteScalar(comm));

    }

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    // execute a select command and return a single result as a string
    public static string ExecuteScalar(DbCommand command)
    {
    // The value to be returned
    string value = "";
    // Execute the command making sure the connection gets closed in the end
    try
    {
    // Open the connection of the command
    command.Connection.Open();
    // Execute the command and get the number of affected rows
    value = command.ExecuteScalar().ToString();
    }
    catch (Exception ex)
    {
    // Log eventual errors and rethrow them
    Utilities.SendErrorLogEmail(ex);
    throw ex;
    }
    finally
    {
    // Close the connection
    command.Connection.Close();
    }
    // return the result
    return value;
    }
    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    But now, I am getting an exception error in this method ( however it works fine to all other countless associated methods ) :


    Here is the error message:

    ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    Server Error in '/SI' Application.
    ________________________________________
    Object reference not set to an instance of an object.
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

    Exception Details: System.NullReferenceException: Object reference not set to an instance of an object.

    Source Error:

    Line 94: // Log eventual errors and rethrow them
    Line 95: Utilities.SendErrorLogEmail(ex);
    Line 96: throw ex;
    Line 97: }
    Line 98: finally

    Source File: e:\SI\App_Code\GenericDataAccess.cs Line: 96

  5. #5
    Join Date
    Feb 2003
    Posts
    2,745
    i *think* this is the error
    Code:
    Set value = command.ExecuteScalar().ToString();

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles