Data not inserting into DB

Question

I am new to writing code. Please help me to get a solution. Insert command in my .aspx.cs file is not getting executed, therefore data is not getting inserted into the DB. My Code is below, please help:

File: Default.aspx.cs

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

    public partial class Default : System.Web.UI.Page
    {
        SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\GERP\gerp_support\App_Data\Database1.mdf;Integrated Security=True;User Instance=True");

        protected void Page_Load(object sender, EventArgs e)
        {
            Button1.Attributes.Add("OnClick", "Button1_Click");
        }

        protected void Button1_Click (object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into service_type (type) values('+TextBox1.Text+')";

            cmd.ExecuteNonQuery();
            con.Close();
        }
    }

File: Deafult.aspx

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

<head runat="server">
<title> GERP and MDM Support</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table> 
 <tr>
   <td> 
    Enter Service Type
    </td>
    <td>
     <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
     </td>
     </tr>

     <tr>
     <td colspan="2" align="center">
         <asp:Button ID="Button1" runat="server" Text="Button" onClientclick="Button1_Click" />
     </td>
    </tr>

    </table>
    </div>
    </form>
    </body>
</html>

File: Deafult.aspx.designer.cs

public partial class Default {

    /// <summary>
    /// form1 control.
    /// </summary>
    /// <remarks>
    /// Auto-generated field.
    /// To modify move field declaration from designer file to code-behind file.
    /// </remarks>
    protected global::System.Web.UI.HtmlControls.HtmlForm form1;

    /// <summary>
    /// TextBox1 control.
    /// </summary>
    /// <remarks>
    /// Auto-generated field.
    /// To modify move field declaration from designer file to code-behind file.
    /// </remarks>
    protected global::System.Web.UI.WebControls.TextBox TextBox1;

    /// <summary>
    /// Button1 control.
    /// </summary>
    /// <remarks>
    /// Auto-generated field.
    /// To modify move field declaration from designer file to code-behind file.
    /// </remarks>
    protected global::System.Web.UI.WebControls.Button Button1;
}

File: Web.config

<?xml version="1.0"?>

<!--
  For more information on how to configure your ASP.NET application, please visit
  http://go.microsoft.com/fwlink/?LinkId=169433
  -->

<configuration>
  <connectionStrings>
    <add name="ApplicationServices"
         connectionString="data source=.\SQLEXPRESS;AttachDbFilename=C:\GERP\gerp_support\gerp_support\App_Data\Database1.mdf;Integrated Security=True;User Instance=True;autocommit=true"
         providerName="System.Data.SqlClient" />
  </connectionStrings>

  <system.web>
    <compilation debug="true" targetFramework="4.0" />

    <authentication mode="Forms">
      <forms loginUrl="~/Account/Login.aspx" timeout="2880" />
    </authentication>

    <membership>
      <providers>
        <clear/>
        <add name="AspNetSqlMembershipProvider" type="System.Web.Security.SqlMembershipProvider" connectionStringName="ApplicationServices"
             enablePasswordRetrieval="false" enablePasswordReset="true" requiresQuestionAndAnswer="false" requiresUniqueEmail="false"
             maxInvalidPasswordAttempts="5" minRequiredPasswordLength="6" minRequiredNonalphanumericCharacters="0" passwordAttemptWindow="10"
             applicationName="/" />
      </providers>
    </membership>

    <profile>
      <providers>
        <clear/>
        <add name="AspNetSqlProfileProvider" type="System.Web.Profile.SqlProfileProvider" connectionStringName="ApplicationServices" applicationName="/"/>
      </providers>
    </profile>

    <roleManager enabled="false">
      <providers>
        <clear/>
        <add name="AspNetSqlRoleProvider" type="System.Web.Security.SqlRoleProvider" connectionStringName="ApplicationServices" applicationName="/" />
        <add name="AspNetWindowsTokenRoleProvider" type="System.Web.Security.WindowsTokenRoleProvider" applicationName="/" />
      </providers>
    </roleManager>

  </system.web>

  <system.webServer>
     <modules runAllManagedModulesForAllRequests="true"/>
  </system.webServer>
</configuration>

Before you do anything else you need to read about, understand and start utilizing parameterized queries. This is a textbook example of sql injection. — Sean Lange
– Sean Lange, Commented Mar 4, 2016 at 19:59
Your query should be inserting the literal text "+TextBox1.Text+". Are you getting an exception, or just nothing inserted at all? — juharr
– juharr, Commented Mar 4, 2016 at 19:59
This looks like a great opportunity to familiarize yourself with the use of a debugger. With it you can step through your code, line by line, as it executes. This allows you to examine the actual runtime behavior and values and see more specifically what's actually going on. You'll find this process to be indispensable when writing software. — David
– David, Commented Mar 4, 2016 at 20:03

Sean Lange · Accepted Answer · 2016-03-04 20:08:35Z

4

You should become familiar with the USING statement. It makes database work a lot less painful as it will handle disposing of your objects cleanly. Also, you should have your connection string in the web.config instead of hard coded in here.

This is untested but should be pretty close to what you need. I have no idea what the real datatype is for Type so I guessed.

using(SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\GERP\gerp_support\App_Data\Database1.mdf;Integrated Security=True;User Instance=True"))
{
    con.Open();
    using(SqlCommand cmd = con.CreateCommand())
    {
        cmd.CommandType = CommandType.Text;
        cmd.CommandText = "insert into service_type (type) values(@Type)";
        cmd.Paramters.Add("@Type", SqlDbType.VarChar, 10).Value = TextBox1.Text;
        cmd.ExecuteNonQuery();
    }
}

answered Mar 4, 2016 at 20:08

Sean Lange

33.7k3 gold badges28 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

rajib Over a year ago

Thank you for the help. Tried with your code. But same result, no data inserted into DB. Note: Matched with datatype

Sean Lange Over a year ago

I didn't the cause of the problem. I did however fix the problem that you were going to find once you got the original issue sorted out. Why are you adding the event handler in the code? Why not set that up already?

rajib Over a year ago

Can u pls share updated code of mine tht will work. As I m beginner, i could easily understand then, what i m missing. Thank you

Sean Lange Over a year ago

Get rid of this line in your Page_Load event. "Button1.Attributes.Add("OnClick", "Button1_Click");". Then open the designer and go the events tab of the properties window with the button selected. Then in the Click event window select Button1_Click

rajib Over a year ago

Thanks a lot Sean :) . At last i could insert data in DB.

Ashkan Mobayen Khiabani · Accepted Answer · 2016-03-04 20:07:40Z

0

You are missing " before and after TextBox1.Text

 protected void Button1_Click (object sender, EventArgs e)
        {
            con.Open();
            SqlCommand cmd = con.CreateCommand();
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = "insert into service_type (type) values('"+TextBox1.Text.Replace("'","''")+"')";

            cmd.ExecuteNonQuery();
            con.Close();
        }

edited Mar 4, 2016 at 20:07

answered Mar 4, 2016 at 20:00

Ashkan Mobayen Khiabani

34.3k35 gold badges111 silver badges187 bronze badges

4 Comments

Sean Lange Over a year ago

This is only perpetuating the vile practice of sql injection. You need to parameterize these queries.

Igor Over a year ago

Here is my value for your text box. TextBox1.Text = "Hi'); DROP TABLE service_type; 'EMPTY"

Steve Over a year ago

If you really want to get some rep from this kind of answers you should do more that just fixing a simple typo. Sql Injection is a thing that you should always try to address.

Sean Lange Over a year ago

NO. Using replace is barely any better than skipping it entirely. The ONLY way to make this safe is using parameters.

Collectives™ on Stack Overflow

Data not inserting into DB

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related