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();
    }
}

Can you login to the sql server manually as the web user and do the query you are trying to do? Do you get an error there? — CtrlDot
– CtrlDot, Commented Mar 28, 2011 at 4:19
Did you try declaring variables for each of your input values and then use them in your query? These variables can also aid in debugging — WorldIsRound
– WorldIsRound, Commented Mar 28, 2011 at 4:21
@WorldIsRound declaring variables for the input values: Would that look like this? --> new var TextBox1.Text — Ace Troubleshooter
– Ace Troubleshooter, Commented Mar 28, 2011 at 4:30
@CtrlDot: I cannot, as I don't have SQL server manager. Simply put, I've had one hellacious time trying to download and install it, with no luck. So, no. But thank you both, for your responses. I hope to pay all this kindness forward in the future. — Ace Troubleshooter
– Ace Troubleshooter, Commented Mar 28, 2011 at 4:32
@Brazos , just a suggestion , you shouldn't insert values that way , it can lead to sql injection , use ado.net sqlcommand — kobe
– kobe, Commented Mar 28, 2011 at 4:53

Crimsonland · Accepted Answer · 2011-03-28 07:37:44Z

2

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

edited Mar 28, 2011 at 7:37

answered Mar 28, 2011 at 6:01

Crimsonland

2,1943 gold badges25 silver badges43 bronze badges

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

1 Comment

Ace Troubleshooter Over a year ago

I've had a hell of a time trying to download sms for some reason. Problems each step of the way. Is that common with windows 7?

Konstantin Chernov · Accepted Answer · 2011-03-28 04:33:38Z

0

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

answered Mar 28, 2011 at 4:33

Konstantin Chernov

1,9462 gold badges22 silver badges40 bronze badges

Comments

Brijesh Mishra · Accepted Answer · 2011-03-28 04:37:35Z

0

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

answered Mar 28, 2011 at 4:37

Brijesh Mishra

2,7581 gold badge21 silver badges36 bronze badges

Comments

striped.dynamite · Accepted Answer · 2011-03-28 05:21:03Z

0

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).

edited Mar 28, 2011 at 5:21

answered Mar 28, 2011 at 4:41

striped.dynamite

1063 bronze badges

3 Comments

striped.dynamite Over a year ago

He is doing a simple example. Didn't want to complicate his understanding as he mentioned not to worry about parameterised queries... Also, Siri has mentioned this in the comments on the original question.

Ace Troubleshooter Over a year ago

Ah yes, @marc_s, but I mention in my question that my trainer specifically forbid me to paramaterize, in the interest of pursuing a simple grasp on the fundamentals.

Ace Troubleshooter Over a year ago

Please +1 the answer, @yetanothercoder; I'm unable to do so until I'm a level 15 member, and it merits points.

codeandcloud · Accepted Answer · 2011-03-28 17:13:28Z

0

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.

edited Mar 28, 2011 at 17:13

answered Mar 28, 2011 at 4:45

codeandcloud

55.5k47 gold badges169 silver badges259 bronze badges

10 Comments

Ace Troubleshooter Over a year ago

Ah, thank you. So my value "RadioButtonList1.SelectedItem" should be changed to "RadioButtonList1.Value", or "RadioButtonList1.SelectedItem.Value"?

codeandcloud Over a year ago

yes. thats correct. i have added something else to it. give it a try

Ace Troubleshooter Over a year ago

@myself: You specifically show that in your answer. Facepalm.

codeandcloud Over a year ago

changing the connectionstring

Ace Troubleshooter Over a year ago

So I should put your first snippet in the html source page, then call the string under the protected void Page_Load(object sender, EventArgs e) block, then alter the click event accordingly? What is the reason behind your insertion method? Does it have a name? Clarification: I meant facepalm on me, because I asked a question you showed in your original answer.

|

Ace Troubleshooter · Accepted Answer · 2011-03-28 22:30:55Z

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.

Collectives™ on Stack Overflow

Trouble opening SQL connection to insert textbox values

6 Answers 6

1 Comment

Comments

Comments

3 Comments

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

1 Comment

Comments

Comments

3 Comments

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related