Dear Sir,
I am in try to set a condition in my sql query. I want to execute this query if satisfy two conditions. Please give the condition to be set in the form.cs also to this query. Here the query:-
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER Procedure [dbo].[Delete_New_invPurchase](
@as_ID bigint,
@upduserid nvarchar(20),
@modidate datetime,
@as_preDeliveryStatus bit,
@as_isAudited bit
)
AS
BEGIN
IF @as_preDeliveryStatus=1 AND @as_isAudited=0
--will this condition work?, if not please correct to the correct syntax
BEGIN
DECLARE
@rowCount INT,
@i int,
@PID bigint,
@batchno nvarchar(20),
@STOCKLTYPE smallint,
@STOCKGID int,
@STOCK float,
@QTY float,
@FREEQTY float,
@STOCKcompcode nvarchar(3),
@STOCKdivcode nvarchar(3),
@STOCKyearcode nvarchar(5)
DECLARE
@tempTable table
(
RowID int not null primary key identity(1,1),
PID bigint,
batchno nvarchar(20),
LTYPE smallint,
GID int,
STOCK float,
QTY float,
FREEQTY float,
compcode nvarchar(3),
divcode nvarchar(3),
yearcode nvarchar(5)
)
INSERT INTO @tempTable(PID, batchno, QTY, FREEQTY, LTYPE, STOCK, GID, compcode, divcode, yearcode)
SELECT
INVPURCHASEITEMS.PID,
INVPURCHASEITEMS.batchno,
INVPURCHASEITEMS.qty,
INVPURCHASEITEMS.FREEQTY,
INVSTOCK.LTYPE,
INVSTOCK.QTY AS STOCK,
INVSTOCK.GID,
INVSTOCK.Compcode,
INVSTOCK.DivCode,
INVSTOCK.Yearcode
FROM INVPURCHASEITEMS
INNER JOIN INVSTOCK ON INVPURCHASEITEMS.PID = INVSTOCK.PID
INNER JOIN INVPURCHASE ON INVPURCHASE.ID = INVPURCHASEITEMS.PURCHASEID
WHERE INVPURCHASEITEMS.PURCHASEID = @as_ID AND INVPURCHASEITEMS.isdeleted = 'False'
AND INVPURCHASE.LTYPE = INVSTOCK.LTYPE AND INVPURCHASE.YEARCODE = INVSTOCK.YEARCODE
AND INVPURCHASE.GID = INVSTOCK.GID AND INVPURCHASEITEMS.batchno = INVSTOCK.batchno
SET @rowCount = @@ROWCOUNT
SET @i = 0
WHILE (@i < @rowCount)
BEGIN
SET @i=@i+1
SELECT
@PID = PID,
@batchno = batchno,
@STOCKLTYPE = LTYPE,
@STOCKGID = GID,
@STOCK = STOCK,
@QTY = QTY,
@FREEQTY = FREEQTY,
@STOCKcompcode = compcode,
@STOCKdivcode = divcode,
@STOCKyearcode = yearcode
FROM @tempTable
WHERE RowID=@i
IF (@STOCK - (@QTY + @FREEQTY)) > 0
BEGIN
UPDATE INVSTOCK SET qty = @STOCK - (@QTY + @FREEQTY)
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END
ELSE
BEGIN
UPDATE INVSTOCK SET qty = 0
WHERE PID = @PID AND Batchno = @batchno AND LTYPE = @STOCKLTYPE AND GID = @STOCKGID
AND Compcode = @STOCKcompcode AND DivCode = @STOCKdivcode AND Yearcode = @STOCKyearcode
END
END
END
UPDATE INVPURCHASE SET isdeleted = 'True', upduserid = @upduserid, modidate = getdate()
WHERE ID = @as_ID
UPDATE INVPURCHASEITEMS SET isdeleted = 'True'
WHERE PURCHASEID = @as_ID
END
Loading
NathanPosted Feb 18, 2014, 1:17 AM
The code is pasted in my application, still in out of getting the result.
I manually replace one of the row's isaudited cell value to true. and still I getting the result mentioned below. Its actual value in table is True, but it became false here because of i assign false to it.
But in case of preDeliveryStatus, the result is right one. we assign false to it, yet the value of it became true. why? See the below screenshot:-
Jignesh TrivediPosted Feb 18, 2014, 12:46 AM
hi,
you code is also look ok
public void DeletePurchase()
{
SqlTransaction transaction = null;
try
{
string cnn_Pharma = ConfigurationManager.ConnectionStrings["MSCon_Pharm"].ToString();
SqlConnection cn = new SqlConnection(cnn_Pharma);
cn.Open();
transaction = cn.BeginTransaction();
SqlCommand cmd = new SqlCommand("Delete_New_invPurchase", cn, transaction);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter { ParameterName = "as_ID", SqlDbType = SqlDbType.BigInt, Value = txtCode.Text, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new SqlParameter { ParameterName = "upduserid", SqlDbType = SqlDbType.NVarChar, Value = clsGeneral.uname, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new SqlParameter { ParameterName = "modidate", SqlDbType = SqlDbType.DateTime, Value = DateTime.Today, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new SqlParameter { ParameterName = "as_preDeliveryStatus", SqlDbType = SqlDbType.Bit, Value = preDeliveryStatus, Direction = ParameterDirection.Input });
cmd.Parameters.Add(new SqlParameter { ParameterName = "as_isAudited", SqlDbType = SqlDbType.Bit, Value = isAudited, Direction = ParameterDirection.Input });
int resultCount = cmd.ExecuteNonQuery();
if (resultCount > 0 && isAudited == false)
{
transaction.Commit();
MessageBoxEx.Show("Successfully Deleted", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Information);
Clear();
LockControls();
}
else
{
transaction.Rollback();
MessageBoxEx.Show("Failed To Delete", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
cn.Close();
}
catch (Exception ex)
{
transaction.Rollback();
MessageBoxEx.Show(ex.Message + "\r\n\r\n(Error No : 0050)", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
NathanPosted Feb 18, 2014, 12:32 AM
I did as you suggested. But there still problems.
Please take a look to the function where i called this query:-
private bool preDeliveryStatus = false;
private bool isAudited = false;// when I assign false, the value become false, else true. I need the value from the sql query into it
public void DeletePurchase()
{
SqlTransaction transaction = null;
try
{
string cnn_Pharma = ConfigurationManager.ConnectionStrings["MSCon_Pharm"].ToString();
SqlConnection cn = new SqlConnection(cnn_Pharma);
cn.Open();
transaction = cn.BeginTransaction();
SqlCommand cmd = new SqlCommand("Delete_New_invPurchase", cn, transaction);
cmd.Parameters.Add("as_ID", SqlDbType.BigInt).Value = txtCode.Text;
cmd.Parameters.Add("upduserid", SqlDbType.NVarChar).Value = clsGeneral.uname;
cmd.Parameters.Add("modidate", SqlDbType.DateTime).Value = DateTime.Today;
cmd.Parameters.Add("as_preDeliveryStatus", SqlDbType.Bit).Value = preDeliveryStatus;
cmd.Parameters.Add("as_isAudited", SqlDbType.Bit).Value = isAudited;// the value will be what i assign during the declaration
cmd.CommandType = CommandType.StoredProcedure;
int resultCount = cmd.ExecuteNonQuery();
if (resultCount > 0 && isAudited == false)
{
transaction.Commit();
MessageBoxEx.Show("Successfully Deleted", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Information);
Clear();
LockControls();
}
else
{
transaction.Rollback();
MessageBoxEx.Show("Failed To Delete", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
cn.Close();
}
catch (Exception ex)
{
transaction.Rollback();
MessageBoxEx.Show(ex.Message + "\r\n\r\n(Error No : 0050)", "Purchase", MessageBoxButtons.OK, MessageBoxIcon.Error);
}
}
Jignesh TrivediPosted Feb 17, 2014, 11:51 PM
hi,
your Store procedure is look ok.
only one suggestion
change INVPURCHASEITEMS.isdeleted = 'False' to INVPURCHASEITEMS.isdeleted = 0
Are you got any exception?
hope this will help you.