Connect to SQL Server and insert values

Question

I'm new to c# and .net so I stacked into inserting data into database.

First I created new project - asp.net web application (v4.6.1). Then I selected web forms template.

My web.config file automatically added this lines

<connectionStrings>
    <add name="DefaultConnection" 
         connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WebApplication7-20161108020707.mdf;Initial Catalog=aspnet-WebApplication7-20161108020707;Integrated Security=True"
         providerName="System.Data.SqlClient" />
</connectionStrings> 

So that is my connection to database.

I started Microsoft SQL Server Management Studio. On left side I have object explorer where I see my server (my computer - I use windows authentication). There is folder called databases. I right-clicked on it and selected new database... I created new database called test. Then I expanded test database and right-clicked on tables folder and selected new->table. I added 2 columns. id (int) - not null and name (nchar(255)) - not null. I added id column as is identity. I saved table and name it testing.

Now in Visual Studio I created Default.aspx page.

<%@ Page Title="Home Page" Language="C#" MasterPageFile="~/Site.Master" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication7._Default" %>

<asp:Content ID="BodyContent" ContentPlaceHolderID="MainContent" runat="server">

<asp:TextBox ID="someBox" runat="server" placeholder="enter something"> </asp:TextBox>

<asp:Button ID="clickMe" runat="server" text="click" onclick=clickMe_click />

</asp:Content>

And I added into 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.Data.SqlClient;
using System.Configuration;
using System.Web.Configuration;
using System.Data;

namespace WebApplication7
{
  public partial class _Default : Page
  {
      protected void clickMe_click(object sender, EventArgs e)
      {
        string txtBox = someBox.Text;

        System.Data.SqlClient.SqlConnection sqlConnection1 =
           new System.Data.SqlClient.SqlConnection("DefaultConnection");

        System.Data.SqlClient.SqlCommand cmd = new System.Data.SqlClient.SqlCommand();
        cmd.CommandType = System.Data.CommandType.Text;
        cmd.CommandText = "INSERT testing (name) VALUES (txtBox)";
        cmd.Connection = sqlConnection1;

        sqlConnection1.Open();
        cmd.ExecuteNonQuery();
        sqlConnection1.Close();
     }
  }
} 

I also tried with this code inside click function:

SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString());

SqlCommand cmd = new SqlCommand();
cmd.CommandText = "INSERT INTO testing (name) VALUES (@name)";
cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = txtBox;
cmd.Connection = conn;

conn.Open();
cmd.ExecuteNonQuery();
conn.Close();

Page refresh and when I refresh table into SQL Server Management Studio and make query:

select * from testing;
GO

It return 0 rows affected and shows empty table.

What am I doing wrong?

---

edit:

 <connectionStrings>
<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WebApplication3-20161105080932.mdf;Initial Catalog=aspnet-WebApplication3-20161105080932;Integrated Security=True"
  providerName="System.Data.SqlClient" />
 </connectionStrings>

aspnet-WebApplication3-20161105080932 is my database name in server object explorer. Inside db I created table testing.

Here is my cs code:

 protected void clickMe_click(object sender, EventArgs e)
    {
        string txtBox = someBox.Text;
        using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString()))
        {
            string sql = "INSERT INTO testing (name) VALUES (@name)";
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.Parameters.Add("name", SqlDbType.NVarChar).Value = txtBox;
            int rowsAffected = cmd.ExecuteNonQuery();
            conn.Close();
        }
    }

But nothing happens.

Answer 1

This is a local database. In Visual Studion, go to View > SQL Server Object Explorer > (LocalDb)\MSSQLLocalDB > aspnet-WebApplication7-20161108020707 to see the contents of your database. No need to start Microsoft SQL Server Management Studio.

<add name="DefaultConnection" connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WebApplication7-20161108020707.mdf;Initial Catalog=aspnet-WebApplication7-20161108020707;Integrated Security=True"
  providerName="System.Data.SqlClient" />
</connectionStrings>

Use the using(...) block for proper disposal when creating a connection to the database.

        string txtBox = someBox.Text;
        using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString))
        {
            using(SqlCommand cmd = new SqlCommand())
            {
                cmd.CommandType = CommandType.Text;
                cmd.CommandText = "INSERT testing (name) VALUES (@name)";
                cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = txtBox;
                cmd.Connection = conn;
                conn.Open();
                int rowsAffected = cmd.ExecuteNonQuery();
            }
        }

Sample Output:

Answer 2

Here is some cleaner code for doing SQL queries (Just my opinion)

 using(SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DefaultConnection"]))
 {
     string sql = "INSERT INTO testing (name) VALUES (@name)";
     SqlCommand cmd = new SqlCommand(sql, conn);
     cmd.Parameter.Add(New SqlParameter("@name", txtBox.Text));
     int rowsAffected = cmd.ExecuteNonQuery();
     conn.Close();
 }

You could also get the number of columns affected by getting the return value of the cmd.ExecuteNonQuery.

I would check into the connection string here is a link Here.

Answer 3

Looks like you are checking in the wrong place. From your connection string it's clear that you are using a local database as seen below but you are probably checking in some other DB in SSMS

connectionString="Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\aspnet-WebApplication7-20161108020707.mdf;

Again, your second posted ADO.NET code is correct but you are missing the below line of code

cmd.CommandType = System.Data.CommandType.Text;