I wanna share…

Archive for the ‘SqlServer and MySql’ Category

MySQL :: Could not start the service MySQL. Error:0

At the time of Mysql installation we’re facing this bug..What’s the solution?.

Follow three steps:

1. Start-> Settings–> Control Panel–> Add/Remove programs: remove all the programs of Mysql
2. Go to this directory C:/Program Files/MySQL and delete the Mysql folder
3. Give this C:/Documents and Settings/All Users/Application Data/MySQL path in Run –> Open: Then delete the info of Mysql

Now start the fresh installation…….

hope it works……

SQL Server Stored Procedure with C#.Net

I will show you about how to insert the data into data table from user interface page using Stored Procedures and view the data in GridView.

DownLoad Link–Click Here:

UI page is like this:

Designing code (.aspx) and Code-behind page (.aspx.cs) are available in Download Link:

After inserting all the fields click on the “Save” button. The GridView will show all the records.

Now I will tell you the process. After click in “Save” button, then the Button Click Event Fires.

protected void Button1_Click(object sender, EventArgs e)

{

if (Session[“update”].ToString() == ViewState[“update”].ToString())

{

InsertingtheData();

            Clearall();

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

}

first(1);

}

public void InsertingtheData()

{

//creating a variable and insert the field values into it.

int employee_id= Convert.ToInt32(txtEmployeeId.Text);

————-

————

//Sql Stuff

SqlConnection sqlconn;

SqlCommand sqlcomm;

sqlconn = new SqlConnection(ConfigurationManager.AppSettings[“Sql_server”]);

sqlconn.Open();

sqlcomm = new SqlCommand();

sqlcomm.Connection = sqlconn;

//Here I am definied command type is Stored Procedure.

sqlcomm.CommandType = CommandType.StoredProcedure;

//Here I mentioned the Stored Procedure Name.

sqlcomm.CommandText = “EMP_MASTER_TESTING_I”;

//Here I fix the variable values to Stored Procedure Parameters. You can easily understand if you can see the Stored Procedure Code.

sqlcomm.Parameters.Add(new SqlParameter(“@emp_id”, SqlDbType.Int)).Value = employee_id;

sqlcomm.Parameters.Add(new SqlParameter(“@emp_desg_id”, SqlDbType.Int)).Value = employee_designation_id;

sqlcomm.Parameters.Add(new SqlParameter(“@emp_dob”,SqlDbType.DateTime)).Value = employee_dob;

sqlcomm.ExecuteNonQuery();

sqlcomm.Dispose();

sqlconn.Close();

}

//Clear all fields values like this.

public void Clearall()

{

txtBankaccNo.Text = “”;

txtBankname.Text = “”;

}

———————-

//This is the Stored Procedure Code.AvailableàDownLoad Link

ALTER PROCEDURE dbo.EMP_MASTER_TESTING_I

(

@emp_id         int,

@emp_desg_id      int,

@emp_dob        Datetime,

@emp_doj        Datetime,

@emp_fname      varchar(100),

@emp_lname      varchar(100),

@emp_gender     varchar(1),

–@emp_p_street   varchar(50),

@emp_p_city     varchar(50),

@emp_p_state      varchar(50),

@emp_p_country  varchar(50),

@emp_p_pin      Varchar(50),

@emp_phone      varchar(50),

@emp_loc_id     int,

@emp_bank_name  varchar(20),

@emp_bank_acno  varchar(20),

@pan_no             varchar(20),

@emp_off_email  varchar(50),

@emp_dor        Datetime,

–@created_on         Datetime,

@created_by     int

–@status           bit,

–@updated_on     int

)

AS

Begin

Insert into

EMP_MASTER_TESTING

(

EMP_ID,

EMP_DESG_ID,

EMP_DOB,

EMP_DOJ,

EMP_FNAME,

EMP_LNAME,

EMP_GENDER,

–EMP_P_STREET,

EMP_P_CITY,

EMP_P_STATE,

EMP_P_COUNTRY,

EMP_P_PIN,

EMP_Phone,

EMP_LOC_ID,

EMP_BANK_NAME,

EMP_BANK_ACNO,

PAN_NO,

EMP_OFF_EMAIL,

EMP_DOR,

CREATED_ON,

CREATED_BY

)

Values

(

@emp_id,

@emp_desg_id,

@emp_dob,

@emp_doj,

@emp_fname,

@emp_lname,

@emp_gender,

–@emp_p_street,

@emp_p_city,

@emp_p_state,

@emp_p_country,

@emp_p_pin,

@emp_phone,

@emp_loc_id,

@emp_bank_name,

@emp_bank_acno,

@pan_no,

@emp_off_email,

@emp_dor,

convert(Datetime, getdate(), 103),

@created_by

)

End

RETURN

After that first () method calls. GridView will show all the columns.

public void first(int a)

{

if (a == 0)

{

tblInsertPage.Visible = true;

tblgv.Visible = false;

}

if (a == 1)

{

tblgv.Visible = true;

tblInsertPage.Visible = false;

}

}

When page Refresh occurs the values are again inserted into the database. So to avoid that I wrote these lines in Page load event, Button Click event and PreRender event.

//In Page_Load Event

if (Session[“update”]==null)

{

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

}

//In Button Click Event

if (Session[“update”].ToString() == ViewState[“update”].ToString())

{

InsertingtheData();

Clearall();

Session[“update”] = Server.UrlEncode(System.DateTime.Now.ToString());

}

//In PreRender Event

private void Page_PreRender(object sender, EventArgs e)

{

ViewState[“update”] = Session[“update”];

}

Before you write a Stored Procedure you have the Data table that you are using in the SP. So I wrote an insert command. Insert command values are Stored Procedure input parameters. And Run the Stored Procedure.

I wrote a single command in stored procedure, but you can write as many commands as you like.  We can write conditional statements like If, while etc and looping statements like for-loop and switch case etc.

We can see GridView like this.

I hope you like this article.