KondukterCRO KondukterCRO - 27 days ago 19
ASP.NET (C#) Question

Connect to SQL Server and insert values

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" />

</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

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:

Code

Data

Comments