Trouble opening SQL connection to insert textbox values

Question

First off, I'd like to say that this is the most brilliant forum I've encountered in my programming journey, and I've been google-fishing for all the help I can get for the last three months. Great support, and even greater style #necromancer badge.

Enough with the flattery.

I'm doing a practice project, insurance website. Right now, I need to get user input from the textboxes into the database. I have seen plenty of related questions here, but I'm getting an error message on my connection I haven't found on any of the other posts, and I'm so ignorant it's difficult to apply examples that don't fit exactly what I'm doing. (As a side note, my trainer specifically wants the most basic form of this code, and as such told me not to worry about parameterizing the queries for security or to use a try-catch block for exceptions, but many thanks to the answers here for those pointers)

The error message I get is "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed."

Am I getting my syntax wrong? Am I using the 'TextBox1.Text' value right? Am I just too stupid to be doing this?

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

public partial class SubmissionPage : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        String connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\aspnetdb.mdf;Integrated Security=True;User Instance=True";
        SqlConnection sqlConn = new SqlConnection(connectionString);

        sqlConn.Open();

        String thisQuery = "INSERT INTO Customer (" + " Name, SIC_NAIC, Address, City, State, Zip, " + ") VALUES (" + " @TextBox1.Text, @RadioButtonList1.SelecedItem, @TextBox2.Text, @DropDownList1.SelectedItem, @TextBox3.Text" + ")"; 
        SqlCommand thisCommand = new SqlCommand(thisQuery, sqlConn);

        thisCommand.ExecuteNonQuery();
        sqlConn.Close();
    }
}

Answer 1

Ok guys, first of all I'm overwhelmed by the helpfulness I found here. I'll be a stackoverflow-er for life. Thank you all for taking your time and experience to aid a total stranger. I couldn't have gotten this done without it.

I wanted to post the code I ended up with, just so as to have a record of what actually worked for me in case someone with the same issue needs to see the end result. As per @yetanothercoder's recommendation, I placed this connection string in my webconfig file, and it looked like this (from ?xml version... to configuration is just to show where I placed the code, since I had wondered about that myself, the connection string is wrapped between the tags):

<?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="MyConnectionString" connectionString="Data Source=The-Crushinator\SQLEXPRESS;AttachDbFilename=|DataDirectory|\ArgonautSubmission.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>

And, although my trainer assured me that the using block @yetanothercoder suggested should be fine, it wasn't working for me, so I used the example from @chaps' answer, remembering to put in the TextBox4.Text value forgot. The code looks like this:

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

public partial class SubmissionPage : System.Web.UI.Page
{



    protected void Page_Load(object sender, EventArgs e)
    {

    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
        String thisQuery = "INSERT INTO Customer (Name, SIC_NAIC, Address, City, State, Zip) VALUES ('" + TextBox1.Text + "', '" + RadioButtonList1.SelectedItem.Text + "', '" + TextBox2.Text + "', '" + TextBox3.Text + "', '" + DropDownList1.SelectedItem.Text + "', '" + TextBox4.Text + "')";

        using (SqlConnection sqlConn = new SqlConnection(connectionString))
        {
            sqlConn.Open();

            using (SqlCommand command = new SqlCommand(thisQuery, sqlConn))
            {
                command.ExecuteNonQuery();
            }
        }
    }
}

Next came the more convoluted part. To rid myself of the dreaded "Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed" error message, I followed this link from @yetanother coder, and found that I needed to install Sql Server Management Studio Express. I had to use Microsoft Web Platform Installer because when I tried to follow the instructions at msdn.com for downloading ssmse, I kept getting a cyclical error. I installed ssmse, opened up the query window with the 'new query' button, and executed this command

exec sp_configure 'user instances enabled', 1. 

Go Reconfigure

then I restarted sql server, added a new database to my asp.net project, and BAM! It worked! User info was saved into the database, where it was supposed to go! I had been so conditioned to expecting failure from my code that it felt like watching my first rocket make it into orbit. Awesome. Thanks again, everyone, and I hope this helps someone in a similar situation.

Answer 2

First of all, if your mdf resides at App_Data, then your connection string is all wrong.
Put the following in your web.config

<connectionStrings>
    <add name="MyConnectionString" connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\aspnetdb.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>

Now call it like

string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

Here is a fix for your error. But you should have an instance of SQL Express installed, which I supposecomes standard with Visual Studio.

http://www.aspdotnetfaq.com/Faq/fix-error-Failed-to-generate-a-user-instance-of-SQL-Server-due-to-a-failure-in-starting-the-process-for-the-user-instance.aspx

And while you are at it, please alter your Button_Click event like this

protected void Button1_Click(object sender, EventArgs e)
{

    string connectionString = System.Configuration.ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
    string sql = "INSERT INTO Customer (Name, SIC_NAIC, Address, City, State, Zip) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')";

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        string query = String.Format(sql, 
            TextBox1.Text, 
            RadioButtonList1.SelectedItem.Text, 
            TextBox2.Text, 
            DropDownList1.SelectedItem.Text, 
            TextBox3.Text, 
            "000000");
        using (SqlCommand command = new SqlCommand(query, connection))
        {
            command.ExecuteNonQuery();
        }
    }
}

Also keep in mind that you cannot insert RadioButtonList1.SelecedItem or DropDownList1.SelectedItem to database. You must append either .Text or .Value to it as per your requirement.

Answer 3

Check this and use sql parameters:

 using (var conn = new SqlConnection("Data Source=.\SQLEXPRESS;Initial Catalog=aspnetdb;Integrated Security=True;Connect Timeout=30;User Instance=True"))
            {

                conn.Open();
                using (var cmd = new SqlCommand("INSERT INTO Customer (Name,SIC_NAIC) VALUES (@Name,@SIC_NAIC)",conn))
                {
                    try
                    {
                        cmd.Parameters.Add("@Name", SQlDbType.VarChar, 50).Value = TextBox1.Text;
                        cmd.Parameters.Add("@SIC_NAIC", SQlDbType.VarChar, 50).Value = RadioButtonList1.SelecedItem.ToString();



                        cmd.ExecuteNonQuery();


                    }
                    catch (Exception)
                    {
                        {
                                                      }

                        throw;
                    }
                    finally
                    {
                        if (conn.State == ConnectionState.Open) conn.Close();
                    }
                }

Make sure you have downloaded sqlmanagement studio 2008 express.. and then attach asp.netdb on it and change your sql connectionstring.

sql ms

Regards

Answer 4

In addition to answers regarding SQL database instances, your SQL query looks wrong. Try changing your embedded query to:

String thisQuery = "INSERT INTO Customer (Name, SIC_NAIC, Address, City, State, Zip) VALUES ('" + TextBox1.Text + "', '" + RadioButtonList1.SelecedItem.Text + "', '" + TextBox2.Text + "', '" + DropDownList1.SelectedItem.Text + "', '" + TextBox3.Text + "', '" + TextBoxZip.Text + "')";

This will format the SQL statement from the values on your form.

NOTE: Assuming field SIC_NAIC and State are storing text values and an additional field for Zip (TextBoxZip).

Answer 5

could be due to permission issue, check eventlog. you may try removing User Instance=True from connection string

Answer 6

This error is often caused because the parent instance (for whatever reason) can't copy the system databases to the users local isolated storage folders. Sometimes it is because of a previous install of SQL Express has left files in that directory. Full story here http://social.msdn.microsoft.com/Forums/en/sqldatabaseengine/thread/f5eb164d-9774-4864-ae05-cac99740949b