Inserting Query

romasha ali

13y1.7k.NET

what happened to this code when i ran this code it did not insert values into my database table ...

using System;

using System.Collections.Generic;

using System.Linq;

using System.Web;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Data.SqlClient;

using System.Configuration;

public partial class course : System.Web.UI.Page

{ // string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;

// SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

// protected void Page_Load(object sender, EventArgs e)

// {

// con.Open();

// }

// protected void Button1_Click(object sender, EventArgs e)

// {

// //SqlConnection con = new SqlConnection(connStr);

// SqlCommand cmd = new SqlCommand("insert into profile values('" + name.Text+ "','" + fname.Text+ "','" + ep.Text+ "','" + cls.Text+ "','" + semester.Text+ "')", con);

////con.Open();

// cmd.ExecuteNonQuery();

////cm.ExecuteNonQuery();

//// con.Close();

SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

protected void Page_Load(object sender, EventArgs e)

{

con.Open();

}

protected void Button1_Click(object sender, EventArgs e)

{

SqlCommand cmd = new SqlCommand("insert into profile(studentname,fathername,ep1) values('" +TextBox1.Text + "','" +TextBox2.Text+ "', '" +TextBox3.Text+ "')", con);

cmd.ExecuteNonQuery();

con.Close();

Label1.Visible = true;

Label1.Text = "Registered Successfully";

TextBox1.Text = "";

TextBox2.Text = "";

TextBox3. Text = "";

//string s1 = string.Empty;

//foreach (ListItem item in this.checkbox.Items)

//{

// if (item.Selected)

// {

// s1 = item.ToString();

// SqlCommand com = new SqlCommand("Insert into profile(courseId) values('" + s1 + "')", con);

// // con.Open();

// com.ExecuteNonQuery();

// con.Close();

// }

//} Response.Write("Inserted Successfully");

}

10 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sunny Sharma13y ago
Hi Romasha,

I went through your code... It's working absolutely fine.
As I started running your application it gave me few SQL Server connection errors because of the configuration settings did not match with mine.

Also it was required to make some changes in IIS configuration. I had to change Process Model Identity to allow application to read the database. I did this by going to my "Application Pools" list > selected "ASP.NET v4.0" >right click > Advanced Properties > under "Process Model" tab > Identity > Choose NetworkService from the dropdown list > ok >ok.

After rectifying all them I got this error finally:
"Failed to update database "C:\INETPUB\WWWROOT\APP_DATA\DATABASE.MDF" because the database is read-only.."
I solved this by grating write access to IIS_USERS on App_Data folder.

Finally I reset the IIS Server using iisreset.exe on command line with Admin rights and it all worked like a charm (You can see the snap for data that i added in).

Hope this helps!

Thanks.
romasha ali13y ago
here is my complete project
Riddhi Valecha13y ago
Hi...

Can you share your project ?? It will be easier to do changes there..
romasha ali13y ago
sir, i m trying this code but it is not doing anything no data is inserting into table and label is not changing its text, what to do now?

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class course : System.Web.UI.Page
{
//{ // string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
//// SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

//// protected void Page_Load(object sender, EventArgs e)
//// {
//// con.Open();
//// }
//// protected void Button1_Click(object sender, EventArgs e)
//// {
//// //SqlConnection con = new SqlConnection(connStr);
//// SqlCommand cmd = new SqlCommand("insert into profile values('" + name.Text+ "','" + fname.Text+ "','" + ep.Text+ "','" + cls.Text+ "','" + semester.Text+ "')", con);

//////con.Open();
//// cmd.ExecuteNonQuery();
//////cm.ExecuteNonQuery();
////// con.Close();

// SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
// protected void Page_Load(object sender, EventArgs e)
// {
// con.Open();
// }
// protected void Button1_Click(object sender, EventArgs e)
// {
// //SqlCommand cmd = new SqlCommand("insert into profile(studentname,fathername,ep1,class,semester,courseId) values('" +TextBox1.Text + "','" +TextBox2.Text+ "', '" +TextBox3.Text+ "','"+s1+"')", con);
// //cmd.ExecuteNonQuery();
// //con.Close();
// //Label1.Visible = true;
// //Label1.Text = "Registered Successfully";
// //TextBox1.Text = "";
// //TextBox2.Text = "";
// //TextBox3. Text = "";


//string s1 = string.Empty;
//foreach (ListItem item in this.CheckBoxList1.Items)
//{ if (item.Selected)
// { s1 = item.ToString();
// SqlCommand com = new SqlCommand("insert into profile(studentname,fathername,ep1,class,semester,courseId) values('" + TextBox1.Text + "','" + TextBox2.Text + "', '" + TextBox3.Text + "','" + s1 + "')", con);
// com.CommandType = System.Data.CommandType.Text;
// com.ExecuteNonQuery();
////com.ExecuteNonQuery();

//} }
//Response.Write("Inserted Successfully"); } }

protected void Button1_Click(object sender, EventArgs e)
{

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
string s1 = string.Empty;
foreach (ListItem item in this.CheckBoxList1.Items)
{
if (item.Selected)
{
s1 = item.ToString();
SqlCommand cmd = new SqlCommand(@"INSERT INTO PROFILE(studentname,fathername,ep1,class,semester,courseId) VALUES (@STUDNAME,@PARNTNAME,@EP1NAME,@CLASS,@SEMESTER,@COURSEID)", conn);

cmd.Parameters.AddWithValue("@STUDNAME", TextBox1.Text);
cmd.Parameters.AddWithValue("@PARNTNAME", TextBox2.Text);
cmd.Parameters.AddWithValue("@EP1NAME", TextBox3.Text);
cmd.Parameters.AddWithValue("@CLASS", TextBox4.Text);
cmd.Parameters.AddWithValue("@SEMESTER", TextBox5.Text);
cmd.Parameters.AddWithValue("@COURSEID", s1);
conn.Open();

Int32 rowsInserted = cmd.ExecuteNonQuery();
conn.Close();

if (rowsInserted > 0)
{
Label1.Visible = true;
Label1.Text = "Registered Successful";
TextBox1.Text = string.Empty;
TextBox2.Text = string.Empty;
TextBox3.Text = string.Empty;
TextBox4.Text = string.Empty;
TextBox5.Text = string.Empty;

}
else
{
Label1.Visible = true;
Label1.Text = "Registration Failed";
}
}
}
}


private void Form1_Load(object sender, EventArgs e)
{
Label1.Visible = false;
}

}
Sunny Sharma13y ago
So, are you getting any error?
Pankaj Pandey13y ago
try this...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data.SqlClient;
using System.Configuration;

public partial class course : System.Web.UI.Page

{ // string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
// SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

// protected void Page_Load(object sender, EventArgs e)
// {
// con.Open();
// }
// protected void Button1_Click(object sender, EventArgs e)
// {
// //SqlConnection con = new SqlConnection(connStr);
// SqlCommand cmd = new SqlCommand("insert into profile values('" + name.Text+ "','" + fname.Text+ "','" + ep.Text+ "','" + cls.Text+ "','" + semester.Text+ "')", con);

////con.Open();
// cmd.ExecuteNonQuery();
////cm.ExecuteNonQuery();
//// con.Close();

SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
protected void Page_Load(object sender, EventArgs e)
{

}
protected void Button1_Click(object sender, EventArgs e)
{
SqlCommand cmd = new SqlCommand("insert into profile(studentname,fathername,ep1) values('" +TextBox1.Text + "','" +TextBox2.Text+ "', '" +TextBox3.Text+ "')", con);
con.Open();
cmd.ExecuteNonQuery();
con.Close();
Label1.Visible = true;
Label1.Text = "Registered Successfully";
TextBox1.Text = "";
TextBox2.Text = "";
TextBox3. Text = "";

//string s1 = string.Empty;
//foreach (ListItem item in this.checkbox.Items)
//{
// if (item.Selected)
// {
// s1 = item.ToString();
// SqlCommand com = new SqlCommand("Insert into profile(courseId) values('" + s1 + "')", con);
// // con.Open();
// com.ExecuteNonQuery();
// con.Close();
// }
//} Response.Write("Inserted Successfully");

}

mark as answred if helpfull
Shankar M13y ago
Hi romasha,

It will be a best Practice to define a parameter and pass the value to the Insert Statement through SqlParameter Class or Passing the Input through command Parameters.AddWithValue to avoid SQL Injection.

And, you can get the No. of Records Inserted into the table by delaring a Int variable and get the No. of rows affected that is returned in the ExecuteNonQuery() Method.

Int32 count = cmd.ExecuteNonQuery();

Here is the Code.

private void button1_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
SqlCommand cmd = new SqlCommand(@"INSERT INTO PROFILE(STUDENTNAME,FATHERNAME,EP1) VALUES (@STUDNAME,@PARNTNAME,@EP1NAME)", conn);

cmd.Parameters.AddWithValue("@STUDNAME", textBox1.Text);
cmd.Parameters.AddWithValue("@PARNTNAME", textBox2.Text);
cmd.Parameters.AddWithValue("@EP1NAME", textBox3.Text);

conn.Open();

Int32 rowsInserted = cmd.ExecuteNonQuery();
conn.Close();

if (rowsInserted > 0)
{
label4.Visible = true;
label4.Text = "Registered Successful";
textBox1.Text = string.Empty;
textBox2.Text = string.Empty;
textBox3.Text = string.Empty;
}
else
{
label4.Visible = true;
label4.Text = "Registration Failed";
}
}

private void Form1_Load(object sender, EventArgs e)
{
label4.Visible = false;
}

Thanks, Shankar M
Riddhi Valecha13y ago
Hi...

Can you share your project ?? It's easier to make changes in the project....
Jignesh Trivedi13y ago
hi,
one suggestion here.
define scope of each variable. for example do not create connection and open it on page load instead of doing this create and open connection whenever it required.
SqlConnection con = new SqlConnection(connStr);
SqlCommand cmd = new SqlCommand("insert into profile values('" + name.Text+ "','" + fname.Text+ "','" + ep.Text+ "','" + cls.Text+ "','" + semester.Text+ "')", con);
con.Open();
cmd.CommandType = System.Data.CommandType.Text;
cmd.ExecuteNonQuery();
con.Close();
hope this will help you.
Sandeep Singh Shekhawat13y ago
Your following uncommented code is Okay

    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);

        protected void Page_Load(object sender, EventArgs e)

        {

            con.Open();

        }

        protected void Button1_Click(object sender, EventArgs e)

        {

            SqlCommand cmd = new SqlCommand("insert into profile(studentname,fathername,ep1) values('" + TextBox1.Text + "','" + TextBox2.Text + "', '" + TextBox3.Text + "')", con);

            cmd.ExecuteNonQuery();

            con.Close();

            Label1.Visible = true;

            Label1.Text = "Registered Successfully";

            TextBox1.Text = "";

            TextBox2.Text = "";

            TextBox3.Text = "";

        }

So please check your connection string in web.config that connect to right database.

<connectionStrings>

    <add name="ConnectionString" connectionString="Data Source=SANDEEPSS-PC;database=Tutorial; user=sa;password=******"/>



connectionStrings>

Otherwise you are getting some error then post error on the fourm so we can identify actual reason. One more thing that your profile tables fields are varchar/nvarchar type which are we passing.