Sql procedure declare variable and store query result to variable and chek condition if rowcount > 0 execute statement

Create procedure [dbo].[PMP_GetActivityType]

@Role AS VARCHAR(200)=''

as

begin

IF (UPPER(@Role) = UPPER('Vendors') OR UPPER(@Role) = UPPER('Vendor'))

BEGIN

DECLARE @Rule1 INT

SELECT @Rule1 = RuleId FROM PMP_BusinessRules WHERE RuleId = '11'

IF @@rowcount > 0

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

WHERE RuleId = '11' AND Status = 1

END

ELSE

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

WHERE Status = 1

END

END

ELSE

BEGIN

select Eventtype_Id,Type_Name as ActivityType

from PMP_EventType where Status=1

order by Type_Name

END

end

----------------------------------------------- 2 -------------------------------------------

Create PROCEDURE [dbo].[PMP_GetEventType]

(

@Eventtypeid INT=0,

@Role AS VARCHAR(200)=''

)

AS

BEGIN

IF (@Eventtypeid <> '0')

BEGIN

IF (UPPER(@Role) = UPPER('Vendors') OR UPPER(@Role) = UPPER('Vendor'))

BEGIN

DECLARE @Rule1 INT

SELECT @Rule1 = RuleId FROM PMP_BusinessRules WHERE RuleId = '11'

IF @@rowcount > 0

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

where RuleId = '11' AND Status = 1

END

ELSE

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

WHERE Status = 1

END

END

ELSE

BEGIN

SELECT Eventtype_Id, [Type_Name]

FROM PMP_EventType

WHERE Eventtype_Id = @Eventtypeid

AND Status = 1

Order by OrderID

END

END

ELSE

BEGIN

IF (UPPER(@Role) = UPPER('Vendors') OR UPPER(@Role) = UPPER('Vendor'))

BEGIN

DECLARE @Rule2 INT

SELECT @Rule2 = RuleId FROM PMP_BusinessRules WHERE RuleId = '11'

IF @@rowcount > 0

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

where RuleId = '11' AND Status = 1

END

ELSE

BEGIN

SELECT DISTINCT ET.Eventtype_Id, ET.Type_Name

FROM PMP_EventType AS ET LEFT OUTER JOIN PMP_BusinessRules AS BR ON ET.Eventtype_Id = BR.FromId

WHERE Status = 1

END

END

ELSE

BEGIN

SELECT Eventtype_Id, [Type_Name]

FROM PMP_EventType

WHERE Status = 1

Order by OrderID

END

END

END

microsoft oledb 4.0 server can not register on your local machine

go to Administrative tools IIS manager under that select Application Pool there is a option Advance setting

Select 32-bit =true

How to load dropdownlist(drpBusinessUnit)

How to load dropdownlist(drpBusinessUnit)

----------write code on aspx.cs file-----------------------------

private void A_LoadBusinessUnit()

{

try

{

drpBusinessUnit.DataSource = objdt.GetBusinessUnit();

drpBusinessUnit.DataValueField = "BusinessUnit_ID";

drpBusinessUnit.DataTextField = "BusinessUnit_Name";

drpBusinessUnit.DataBind();

//ddlproduct.Items.Insert(0, "-Select-");

System.Web.UI.WebControls.ListItem cmbItem;

cmbItem = new System.Web.UI.WebControls.ListItem("-Select-", "0");

drpBusinessUnit.Items.Insert(0, cmbItem);

}

------------------------------------------write code on class file

public SqlConnection sqlconn = new SqlConnection(ConfigurationManager.AppSettings["Constr"]);

public DataSet GetBusinessUnit()

{

DataSet dsGetProduct = new DataSet();

try

{

if (sqlconn.State == ConnectionState.Closed)

{

sqlconn.Open();

}

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "PMP_GetBusinessUnit";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlconn;

SqlDataAdapter sqlda = new SqlDataAdapter();

sqlda.SelectCommand = cmd;

sqlda.Fill(dsGetProduct);

}

catch (Exception ex)

{

}

finally

{

sqlconn.Close();

}

return dsGetProduct;

}

-------------------write procedure---------------------------------------------

create procedure [dbo].[PMP_GetBusinessUnit]

as

select BusinessUnit_ID, BusinessUnit_Name

from PMP_BusinessUnit

file upload control by storing image on database as a binary image format

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="FileUpload.aspx.cs" Inherits="FileUpload" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<%@ Import Namespace="System.IO" %>

<html xmlns="http://www.w3.org/1999/xhtml" >

<head runat="server">

<title>Untitled Page</title>

</head>

<body>

<form id="form1" runat="server">

Product Name:

<asp:TextBox id="txtProductName" runat="server" />

<br />

Product Image:

<asp:FileUpload id="FileUpload1" runat="server" />

<asp:Button id="btnSave" runat="server" Text="Save Product" onclick="btnSave_Click" />

<br /><br />

<asp:Label id="lblMessage" runat="server" />

</form>

</body>

</html>

CREATE TABLE [dbo].[Products](

[ID] [int] NULL,

[ProductName] [nvarchar](100) NULL,

[ProductImage] [varbinary](max) NULL

) ON [PRIMARY]

protected void btnSave_Click(object sender, EventArgs e)

{

if (FileUpload1.HasFile)

{

string productName = txtProductName.Text;

byte[] productImage = FileUpload1.FileBytes;

string constr = "Data Source=TECH-SWAPNILM\\SQLEXPRESS; Initial Catalog=master; Integrated Security=true";

string query = "INSERT INTO Products(ProductName, ProductImage) VALUES(@ProductName, @ProductImage)";

SqlConnection con = new SqlConnection(constr);

SqlCommand com = new SqlCommand(query, con);

com.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = productName;

com.Parameters.Add("@ProductImage", SqlDbType.VarBinary).Value = productImage;

con.Open();

int result = com.ExecuteNonQuery();

con.Close();

if (result > 0)

{

lblMessage.Text = "Product Saved Successfully";

}

}

else

{

lblMessage.Text = "Please Select Product Image File";

}

}

}

Sometime because of identity column you dont have permission to insert values because identity column increment default

example:-

id	name	product	brand
1	swapnil	britaniya	good
2	bhavesh	mary	average

In this table id is identity column is u try to insert value on open table on id column it will not happen because column is readonly format when u insert new row it will automatically insert id 3

so if u want to insert third record id is 20 for that u have to do below code

SET IDENTITY_INSERT test ON

Insert Into test (Id,name,product,brand) VALUES (20,'wer','Product1','Brand1')

SET IDENTITY_INSERT test OFF

by using this code u can insert next id row to 20 successfully

----------------------------------------------------------------------------------------------

how to create identity column

select table right click design

column properties window display

select identityspecification =yes

isidentity =yes

identity increment =1

identity seed =1

how to pass session parameters through storeprocedure and how to apply multiple if else condition

 


ALTER procedure [dbo].[PMP_GetGrantDonation_Type_byGD_Type_Id] 2 ,'Employee'@GD_Type_Id int ,@Role AS VARCHAR(200)as IF (UPPER(@Role) = UPPER('Employees') OR UPPER(@Role) = UPPER('Employee')) Begin
if (@GD_Type_Id = 1)Begin
select * from PMP_GrantDonation_Typewhere gd_type_id=@GD_Type_Id and SubType_Name NOt In ( 'In Kind Donation')order
end
else
Begin
select by GD_SubType_id asc * from PMP_GrantDonation_Typewhere gd_type_id=@GD_Type_Id and SubType_Name NOt In ( 'In-kind Mission','In-kind Friends & Need','In-kind')order
end
end
else by GD_SubType_id asc if (UPPER(@Role) = UPPER('Vendors') OR UPPER(@Role) = UPPER('Vendor')) Begin
if (@GD_Type_Id = 1)Begin
select * from PMP_GrantDonation_Typewhere gd_type_id=@GD_Type_Id and SubType_Name NOt In ( 'In Kind Donation')order
end
else
Begin
select by GD_SubType_id asc * from PMP_GrantDonation_Typewhere gd_type_id=@GD_Type_Id and SubType_Name In ('In-kind')order
end
end by GD_SubType_id asc

SqlCommand cmd = new SqlCommand();

cmd.CommandText = "PMP_GetGrantDonation_Type_byGD_Type_Id";

cmd.CommandType = CommandType.StoredProcedure;

cmd.Connection = sqlconn;

cmd.Parameters.Add("@GD_Type_Id", GD_Type_Id);

//BSP2 11131

cmd.Parameters.Add("@Role", SqlDbType.VarChar, 200);

cmd.Parameters["@Role"].Value = HttpContext.Current.Session["Description"].ToString();

//BSP2 11131

SqlDataAdapter sqlda = new SqlDataAdapter();

sqlda.SelectCommand = cmd;

sqlda.Fill(ds);