Getting a SQL Database Error...any ideas SQL experts?

I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...

Here is the code I am using:

<%@. Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Configuration" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">

<script runat="server">

Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)

Dim CWZ As CreateUserWizard
CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)

CreateUserProfile(CWZ.UserName)

Private Sub CreateUserProfile(ByVal UserName As String)

Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@.UserID)", con)
cmd.Parameters.AddWithValue("@.UserID", UserName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using

End Sub

</script>

...and here is the error and stack trace (the offending Line 49 is in bold):

Server Error in '/Site_Dev' Application.
------------------------

An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Source Error:

Line 47: cmd.Parameters.AddWithValue("@.UserID", UserName)
Line 48: Using con
Line 49: con.Open()
Line 50: cmd.ExecuteNonQuery()
Line 51: End Using

Source File: C:\Users\mdcragg\Documents\Website\Site_Dev\User_Create.aspx Line: 49

Stack Trace:

[SqlException (0x80131904): An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.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) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ASP.user_create_aspx.CreateUserProfile(String UserName) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:49
ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:30
System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102

------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312

Looking around potential solutions seem to be:

1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')

Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.

2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.

3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.

4. You may want to disable the "User Instance=true" and try.

5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them andinstall the released versions.

6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)

7. Make sure your connection string is correct. It must be something like below:

"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"

When you useIntegrated Secuiryt=SSPI in the connection string, make sure you are using<identity impersonate="true" /> in the<system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.

Getting a single value from SqlDataSource

Hi,

I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1.

DDL1 Selected data field todisplay is ProductName
DDL1 Selected data field tovalue is ProductCost

I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1.

Happy Coding

Hi

You will be able to obtain only the values that you bind to the drop down list. if you want non bound value then you will have to fire another query based on what you select from the drop down.

For example,

select productid from Table_name where product name = 'prodname you selected from ddl' and productcost= cost you got from ddl.

Then use productid as you need

HTH

Getdate() default value does not get created

I have a table in which a field named DateCreated has the default value
(getdate()). THis has always worked before in other apps, it would naturally
write the computer's date time whenever a new record was created. I can't
figure out why all of a sudden the field in this table no longer get
populated with the default value.
Anyone have any ideas on where to start looking? I'm stumped.
BobAn INSERT that references the column will override the default, even
if it is assigned NULL. Any INSERT without the (column list) after
the table name will have this problem. The INSERT must use an
explicit column list, and the column must not appear in that list, for
the default to be applied.
Roy Harvey
Beacon Falls, CT
On Fri, 16 Jun 2006 15:57:44 -0400, "Bob" <bdufour@.sgiims.com> wrote:

>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would naturall
y
>write the computer's date time whenever a new record was created. I can't
>figure out why all of a sudden the field in this table no longer get
>populated with the default value.
>Anyone have any ideas on where to start looking? I'm stumped.
>Bob|||Can you show the table structure (CREATE TABLE, not a hand-written list of
columns) and the actual insert statement that fails to generate the correct
value?
"Bob" <bdufour@.sgiims.com> wrote in message
news:%239SVj8XkGHA.4528@.TK2MSFTNGP05.phx.gbl...
>I have a table in which a field named DateCreated has the default value
>(getdate()). THis has always worked before in other apps, it would
>naturally write the computer's date time whenever a new record was created.
>I can't figure out why all of a sudden the field in this table no longer
>get populated with the default value.
> Anyone have any ideas on where to start looking? I'm stumped.
> Bob
>|||Aaron Bertrand [SQL Server MVP] wrote:
> Can you show the table structure (CREATE TABLE, not a hand-written list of
> columns) and the actual insert statement that fails to generate the corre
ct
> value?
>
Also any triggers that are on the table...

Get zodic sign in sql server

I have a field in table A named tbl_dob.It is date of birth of member. I want to find zodic sign from this tbl_dob.How I get it.Please help me.

You'll have to create a table to hold the zodiac signs and their date ranges. You can then join to this table to find which sign the date falls into.

sql

Get the Highest value.

Hi! i have a column named number(int),ref_number(char),date(date),creator(ch ar)...what would be my query string, if posible, to get the highest value for clumn 'number'? by the way im using SQL Desktop Engine... any inputs will be greatly appreciated...thanks in advanace!select max(number) as m from daTable|||thanks a bunch!!!!|||select max(number) as m from daTable
Truly, an elegant piece of coding. Sheer genious for its blend of brevity and functionality. I shall have to use this in my next project.

get text after a delimiter

In a table I have a column named "Installment" which stores
installment like 9/15, 2/10 etc., where first number is the last
installment deducted and the last number is the total number of
installments. I want to pick any text after "/" using SQL Query. Which
function to use? I am using SQL Server 2005 Express.See funtions LEFT, RIGHT, CHARINDEX, PATINDEX, REPLACE, PARSENAME in BOL.
Example:
select parsename(replace('2/10', '/', '.'), 1)
go
AMB
"RP" wrote:
> In a table I have a column named "Installment" which stores
> installment like 9/15, 2/10 etc., where first number is the last
> installment deducted and the last number is the total number of
> installments. I want to pick any text after "/" using SQL Query. Which
> function to use? I am using SQL Server 2005 Express.
>

Get PK for inserted record in SQLdatasource

I have a table named invoice that contains the following columns

-invoiceno - Primary key and is set to autonumber
-customerno
-incoicedate

and on my VB code i did the following InsertCommand

SqlDataSource1.InsertCommand = "INSERT INTO invoice(customerno, invoicedate) VALUES('" & Session("UID") & "', GetDate()) "
SqlDataSource1.Insert()

My Question is how do i get the Primary Key Value it generated during the insert operation(invoice['incoiceno'])? Besides the creationg a stored procedere like the one in the MSDN Library

Well, since you really aren't using the sqldatasource as a datasource, just do it manually.

Dim conn as new sqlconnection("{Your connect string}")
conn.open
dim cmd as new sqlcommand("INSERT INTO invoice(customerno, invoicedate) VALUES(@.customerno,getdate()) SELECT @.newid=SCOPE_IDENTITY()",conn)
cmd.parameters.add("@.customerno",sqldbtype.uniqueidentifier).value=session("UID")
cmd.parameters.add("@.newid",sqldbtype.int).direction=output
cmd.executenonquery()
dim newid as integer=cmd.parameters("@.newid").value
conn.close

I believe you can also try to create a parameter on the insert with a direction of output as well, but I think you have to actually pull the value in sqldatasource1_inserted by referencing e.command.parameters("@.newid").value, but you can try and see if you can pull it directly after your insert too.