UPDATE command will not work on MS SQL 2005 Express database

T

TJ

Hi All,

I am having some trouble.

I have created a database via the new database option inside VWD2005.
Then and table or two.

I have been able to perform INSERT and SELECT operations on the table, BUT cannot do an UPDATE.
After the update command it says 1 row updated, but the data does not change.
At one point I receieved a message stating something like (heavily paraphrased...):
"Permissions do not allow updating to be performed"

Interestingly enough when I attached to gridview and enable updating it wont allow that to do it either!

I've tried creating clean app, new database, new tables etc and it still happens.


Is this a simple setting, or how I am doing the UPDATE (code below) or the fact that the database is set as a "user" database.
Not a multi-access one. Changin User Instance to False comes up with error anyway. Know how to get around that?

Thanks in advance for your assistance.

Steve


My Update Code:

Dim userDataSource As New SqlDataSource
Dim userResult As New System.Data.DataView
userDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()
'set the way we will perform select queries
userDataSource.UpdateCommandType = SqlDataSourceCommandType.Text

UserDataSource.UpdateCommand = "UPDATE site_info SET UserName = @UserName, First_Name = @newFirstName, Last_Name = @newSurname, Phone_Number = @newPhoneNumber, Fax_Number = @newFaxNumber WHERE UserName = @UserName"
userDataSource.UpdateParameters.Add("UserName", User.Identity.Name.ToString)
userDataSource.UpdateParameters.Add("newFirstName", firstnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newSurname", surnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newPhoneNumber", phoneTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newFaxNumber", faxTextBox.Text.ToString)
Try
MsgBox(userDataSource.Update())
Catch ex As Exception
MsgBox(ex.Message)
End Try


userDataSource = Nothing
userResult = Nothing




My Connection String:

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

TJ

AND, further to what I have described below, I seem unable to change user emails via the membership object.
User is definately logged in and I can retrieve the email.
It seems like this UPDATE problem is for the ASPNETDB database as well...Anyone had similar experiences?

As a side note, has anyone tried expanding the ASPNETDB Membership page to add in different data? Like phone number and first and last name.
This would save have to relate my own table to the username.
I added a few columns myself, which it let me do, but I could not update the records.
Is it perhaps this that began all the problems?
I.e. it locked me out?

Changing Membership Code

Membership.GetUser(User.Identity.Name).Email = emailTextBox.Text

Membership.UpdateUser(Membership.GetUser())







Hi All,

I am having some trouble.

I have created a database via the new database option inside VWD2005.
Then and table or two.

I have been able to perform INSERT and SELECT operations on the table, BUT cannot do an UPDATE.
After the update command it says 1 row updated, but the data does not change.
At one point I receieved a message stating something like (heavily paraphrased...):
"Permissions do not allow updating to be performed"

Interestingly enough when I attached to gridview and enable updating it wont allow that to do it either!

I've tried creating clean app, new database, new tables etc and it still happens.


Is this a simple setting, or how I am doing the UPDATE (code below) or the fact that the database is set as a "user" database.
Not a multi-access one. Changin User Instance to False comes up with error anyway. Know how to get around that?

Thanks in advance for your assistance.

Steve


My Update Code:

Dim userDataSource As New SqlDataSource
Dim userResult As New System.Data.DataView
userDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()
'set the way we will perform select queries
userDataSource.UpdateCommandType = SqlDataSourceCommandType.Text

UserDataSource.UpdateCommand = "UPDATE site_info SET UserName = @UserName, First_Name = @newFirstName, Last_Name = @newSurname, Phone_Number = @newPhoneNumber, Fax_Number = @newFaxNumber WHERE UserName = @UserName"
userDataSource.UpdateParameters.Add("UserName", User.Identity.Name.ToString)
userDataSource.UpdateParameters.Add("newFirstName", firstnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newSurname", surnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newPhoneNumber", phoneTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newFaxNumber", faxTextBox.Text.ToString)
Try
MsgBox(userDataSource.Update())
Catch ex As Exception
MsgBox(ex.Message)
End Try


userDataSource = Nothing
userResult = Nothing




My Connection String:

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

Mark Fitzpatrick

Have you made sure that the ASPNet user account has write permissions to the database file? By default it probably only has read/execute. WIthout write permissions it can't update the database file.

Hope this helps,
Mark Fitzpatrick
Microsoft MVP - FrontPage

AND, further to what I have described below, I seem unable to change user emails via the membership object.
User is definately logged in and I can retrieve the email.
It seems like this UPDATE problem is for the ASPNETDB database as well...Anyone had similar experiences?

As a side note, has anyone tried expanding the ASPNETDB Membership page to add in different data? Like phone number and first and last name.
This would save have to relate my own table to the username.
I added a few columns myself, which it let me do, but I could not update the records.
Is it perhaps this that began all the problems?
I.e. it locked me out?

Changing Membership Code

Membership.GetUser(User.Identity.Name).Email = emailTextBox.Text

Membership.UpdateUser(Membership.GetUser())







Hi All,

I am having some trouble.

I have created a database via the new database option inside VWD2005.
Then and table or two.

I have been able to perform INSERT and SELECT operations on the table, BUT cannot do an UPDATE.
After the update command it says 1 row updated, but the data does not change.
At one point I receieved a message stating something like (heavily paraphrased...):
"Permissions do not allow updating to be performed"

Interestingly enough when I attached to gridview and enable updating it wont allow that to do it either!

I've tried creating clean app, new database, new tables etc and it still happens.


Is this a simple setting, or how I am doing the UPDATE (code below) or the fact that the database is set as a "user" database.
Not a multi-access one. Changin User Instance to False comes up with error anyway. Know how to get around that?

Thanks in advance for your assistance.

Steve


My Update Code:

Dim userDataSource As New SqlDataSource
Dim userResult As New System.Data.DataView
userDataSource.ConnectionString = ConfigurationManager.ConnectionStrings("ReportingConnectionString").ToString()
'set the way we will perform select queries
userDataSource.UpdateCommandType = SqlDataSourceCommandType.Text

UserDataSource.UpdateCommand = "UPDATE site_info SET UserName = @UserName, First_Name = @newFirstName, Last_Name = @newSurname, Phone_Number = @newPhoneNumber, Fax_Number = @newFaxNumber WHERE UserName = @UserName"
userDataSource.UpdateParameters.Add("UserName", User.Identity.Name.ToString)
userDataSource.UpdateParameters.Add("newFirstName", firstnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newSurname", surnameTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newPhoneNumber", phoneTextBox.Text.ToString)
userDataSource.UpdateParameters.Add("newFaxNumber", faxTextBox.Text.ToString)
Try
MsgBox(userDataSource.Update())
Catch ex As Exception
MsgBox(ex.Message)
End Try


userDataSource = Nothing
userResult = Nothing




My Connection String:

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