Getting error while trying to update records in SQL local database in App_code folder

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

Hi i am trying to execute a simple insert operation in database located localy in App_Data folder, but i am getting error:

An attempt to attach an auto-named database for file App_Data\studentdata.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

C#

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

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

    }
    protected void SubmitButton_Click(object sender, EventArgs e)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString= @"Data Source=.\SQLEXPRESS;" + @"AttachDbFilename=App_Data\studentdata.mdf;" + "Integrated Security=True;" +" User Instance=True;";

        SqlCommand cmd = conn.CreateCommand();
        cmd.CommandType = CommandType.Text;
        cmd.CommandText="Insert IN TO class10(subject,chapter_name,chapter_number,exercise_number,url) VALUES ('DropDownList1.SelectedValue','ChapterBox.Text','TextBox1.Text','TextBox2.Text' )";
        cmd.Connection = conn;
        conn.Open();
        int updt = cmd.ExecuteNonQuery();
        conn.Close();
  


    }
}

and here is the aspx

XML

<%@ Page Language="C#" AutoEventWireup="true" CodeFile="addvideourl.aspx.cs" Inherits="admin_addvideourl" %>

<!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></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>

        Select Subject
        <asp:DropDownList ID="DropDownList1" runat="server">
            <asp:ListItem>Math</asp:ListItem>
            <asp:ListItem>Science</asp:ListItem>
            <asp:ListItem>English</asp:ListItem>
        </asp:DropDownList>

    </div>
    <p>
        Enter the chapter name&nbsp;&nbsp;&nbsp;&nbsp;
        <asp:TextBox ID="ChapterBox" runat="server"></asp:TextBox>
    </p>
    <p>
        Enter the exercise number
        <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
    </p>
    <p>
        Enter the Video URL Here
        <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
    </p>
    <asp:Button ID="SubmitButton" runat="server" Text="Submit"
        onclick="SubmitButton_Click" />
    </form>
</body>
</html>

Please help if i am missing any particular operation in SQL connection or do i need to check anything for SQL express settings on machine.

Error details:

SQL

[SqlException (0x80131904): An attempt to attach an auto-named database for file App_Data\studentdata.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
   System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +5009598
   System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning() +234
   System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +2275
   System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +35
   System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, TimeoutTimer timeout, SqlConnection owningObject) +183
   System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(ServerInfo serverInfo, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, TimeoutTimer timeout) +239
   System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, TimeoutTimer timeout, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +195
   System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +232
   System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +185
   System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +33
   System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +524
   System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
   System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +479
   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +108
   System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +126
   System.Data.SqlClient.SqlConnection.Open() +125
   admin_addvideourl.SubmitButton_Click(Object sender, EventArgs e) in c:\Documents and Settings\Administrator\My Documents\Visual Studio 2010\WebSites\StudentWeb\admin\addvideourl.aspx.cs:25
   System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
   System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563

Posted 31-Oct-12 6:01am

Ashish Raj Srivastava

Updated 31-Oct-12 6:05am

v2

Add a Solution

6 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Answer 1 · 2012-10-31T06:08:00

Solution 1

The error message is quite clear. The database you are attempting to attach to is already attached in SQLEXPRESS so you can't do it again. If the db is already attached, just connect to the instance instead of trying to reattach it.

Posted 31-Oct-12 6:08am

fjdiewornncalwe

Comments

Ashish Raj Srivastava 31-Oct-12 12:25pm

Hi Thanks for the reply, ya now i tried opening database using connection string

@"Data Source=.\SQLEXPRESS;" + @"Database=App_Data\studentdata.mdf;" + "Integrated Security=True;" +" User Instance=True;";

now it gives me Login error:

ystem.Data.SqlClient.SqlException: Cannot open database "App_Data\studentdata.mdf" requested by the login. The login failed.
Login failed for user 'ADMIN-929C9B7EA\Administrator'.

However ADMIN-929C9B7EA\Administrator is the owner of database as showing in VS Server explorer and ADMIN-929C9B7EA\Administrator is local admin on machine as well.

_Natula · Answer 2 · 2012-10-31T06:11:00

Solution 2

This could be the issue

cmd.CommandText="Insert IN TO class10(subject,chapter_name,chapter_number,exercise_number,url) VALUES ('DropDownList1.SelectedValue','ChapterBox.Text','TextBox1.Text','TextBox2.Text' )";

No space, Should be "Insert INTO......."

Posted 31-Oct-12 6:11am

_Natula

Comments

Ashish Raj Srivastava 31-Oct-12 12:26pm

Hi Thanks for this correction, i corrected it but still having issue on opening the connection.

it results with login failed after making changes as suggested by Marcus.

AshishChaudha · Answer 3 · 2012-10-31T19:01:00

Solution 4

Try this

C#

cmd.CommandText="Insert into class10(subject,chapter_name,chapter_number,exercise_number,url) VALUES ('"+ DropDownList1.SelectedValue+"','"+ChapterBox.Text.Trim()+"','"+TextBox1.Text.Trim()+"','"+TextBox2.Text.Trim()+"' )";

Note : Use Parameterzed Query, Follow the URL
http://forums.asp.net/t/1374619.aspx/1[^]

Thanks

Posted 31-Oct-12 19:01pm

AshishChaudha

Comments

Ashish Raj Srivastava 1-Nov-12 11:48am

Hi Ashish, thanks for reply

The error i am getting is seems while even i am trying to open the connection, though i tried with the new query parameter, but it is still not opening the connection, once i can open the connection it may tell me if i should have any issues with query that i am passing throgh the connection.

conn.open(); gives me login error.

Ashish Raj Srivastava · Answer 4 · 2012-11-07T09:24:00

finally i fixed this using connection string in Web.config file using an extra attribute providerName="System.Data.SqlClient"

so my web.config file looks like :

XML

<configuration>

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

and connection in C# file looks like :

conn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
con= new SqlConnection(conn);

SqlCommand cmd = con.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Insert INTO class10(subject,chapter_name,chapter_number,excercise_number,url) VALUES ('" + DropDownList1.SelectedValue + "','" + ChapterBox.Text.Trim() + "','" + Chapter_NumberBox.Text.Trim() + "','" + TextBox1.Text.Trim() + "','" + TextBox2.Text.Trim() + "' )";
cmd.Connection = con;
con.Open();
int updt = cmd.ExecuteNonQuery();
con.Close();

but still i am not able to call connection directly from C# code behind file even using the providerName="System.Data.SqlClient"

below is what i am trying now: .

SqlConnection con = new SqlConnection();

con.ConnectionString = @"Data Source=admin-929c9b7ea\SQLEXPRESS;" + @"Initial Catalog=App_Data\studentdata.mdf;" + @"Integrated Security=SSPI;" + @"providerName=System.Data.SqlClient";

If somebody can help on this that how can i use providername attribute in C# file directly....

footballpardeep · Answer 5 · 2012-10-31T06:17:00

Solution 3

select your database in appdata press F4 , that will open it's properties, copy connections string from there, and try to use that in your solution.

Posted 31-Oct-12 6:17am

footballpardeep

Comments

Ashish Raj Srivastava 31-Oct-12 12:37pm

after selecting database in App_Data Folder, properties window do only have two properties.

File Name and File Path.

However i already tried connection string which i got after selecting the database in server explorer, that even fireup with same errors.

footballpardeep 31-Oct-12 12:57pm

Try to use InitialCatalog="databsename", in connection string.

Ashish Raj Srivastava 1-Nov-12 11:45am

Hi pradeep, i tried after replacing the Database to Initial Catalog field, still its not opening the connection.

Ashish Raj Srivastava · Answer 6 · 2012-11-06T12:33:00

I am still on the same page...some one please help me...i searched a lot about this error and yet no solution working for me...i checked few forums where it is mentioned as User login kinda error, and asked to set User default database to the database i am connecting to via SQL management Studio , But in My case i am connecting to Database located in App_Data Folder on run time and there is no instance of this particular Database in SQL management studio ....

Help me...it seems this error is really a bugging one for every beginner in VS Database connections.

Getting error while trying to update records in SQL local database in App_code folder

6 solutions

Solution 1

Solution 2

Solution 4

Solution 6

Solution 3

Solution 5

Add your solution here

Preview 0