By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,870 Members | 1,224 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,870 IT Pros & Developers. It's quick & easy.

UPDATE command will not work on MS SQL 2005 Express database

P: n/a
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("ReportingC onnectionString").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("newPhoneNumbe r", 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=|DataDirector y|\Reporting.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Jun 3 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
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())



"TJ" <tj@tj.com> wrote in message news:44******@dnews.tpgi.com.au...
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("ReportingC onnectionString").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("newPhoneNumbe r", 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=|DataDirector y|\Reporting.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Jun 3 '06 #2

P: n/a
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

"TJ" <tj@tj.com> wrote in message news:44********@dnews.tpgi.com.au...
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())



"TJ" <tj@tj.com> wrote in message news:44******@dnews.tpgi.com.au...
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("ReportingC onnectionString").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("newPhoneNumbe r", 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=|DataDirector y|\Reporting.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
Jun 3 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.