A SQL procedure to STOP repeated info being stored

[paradise_wolf]

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
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

[russell]

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

[paradise_wolf]

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 ?

[paradise_wolf]

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

[russell]

i *think* this is the error

Set value = command.ExecuteScalar().ToString();