GridView control and SQL data source

[chump2877]

So I've popluated a GridView control with an SQL data source, but I can only update the records if "use optimistic concurrency" is turned off...If that feature is on, the records are not updated when you hit the update button for a given row in the GridView...

This the relevant .aspx page code:


<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:NorthwindConnectionString %>"DeleteCommand="DELETE FROM [Customers] WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND [ContactName] = @original_ContactName AND [ContactTitle] = @original_ContactTitle AND [Address] = @original_Address AND [City] = @original_City AND [Region] = @original_Region AND [PostalCode] = @original_PostalCode AND [Country] = @original_Country AND [Phone] = @original_Phone AND [Fax] = @original_Fax"InsertCommand="INSERT INTO [Customers] ([CustomerID], [CompanyName], [ContactName], [ContactTitle], [Address], [City], [Region], [PostalCode], [Country], [Phone], [Fax]) VALUES (@CustomerID, @CompanyName, @ContactName, @ContactTitle, @Address, @City, @Region, @PostalCode, @Country, @Phone, @Fax)"SelectCommand="SELECT * FROM [Customers]"UpdateCommand="UPDATE [Customers] SET [CompanyName] = @CompanyName, [ContactName] = @ContactName, [ContactTitle] = @ContactTitle, [Address] = @Address, [City] = @City, [Region] = @Region, [PostalCode] = @PostalCode, [Country] = @Country, [Phone] = @Phone, [Fax] = @Fax WHERE [CustomerID] = @original_CustomerID AND [CompanyName] = @original_CompanyName AND [ContactName] = @original_ContactName AND [ContactTitle] = @original_ContactTitle AND [Address] = @original_Address AND [City] = @original_City AND [Region] = @original_Region AND [PostalCode] = @original_PostalCode AND [Country] = @original_Country AND [Phone] = @original_Phone AND [Fax] = @original_Fax"ConflictDetection="CompareAllValues"OldValuesParameterFormatString="original_{0}">
<DeleteParameters>
<asp:ParameterName="original_CustomerID"Type="String"/>
<asp:ParameterName="original_CompanyName"Type="String"/>
<asp:ParameterName="original_ContactName"Type="String"/>
<asp:ParameterName="original_ContactTitle"Type="String"/>
<asp:ParameterName="original_Address"Type="String"/>
<asp:ParameterName="original_City"Type="String"/>
<asp:ParameterName="original_Region"Type="String"/>
<asp:ParameterName="original_PostalCode"Type="String"/>
<asp:ParameterName="original_Country"Type="String"/>
<asp:ParameterName="original_Phone"Type="String"/>
<asp:ParameterName="original_Fax"Type="String"/>
</DeleteParameters>
<UpdateParameters>
<asp:ParameterName="CompanyName"Type="String"/>
<asp:ParameterName="ContactName"Type="String"/>
<asp:ParameterName="ContactTitle"Type="String"/>
<asp:ParameterName="Address"Type="String"/>
<asp:ParameterName="City"Type="String"/>
<asp:ParameterName="Region"Type="String"/>
<asp:ParameterName="PostalCode"Type="String"/>
<asp:ParameterName="Country"Type="String"/>
<asp:ParameterName="Phone"Type="String"/>
<asp:ParameterName="Fax"Type="String"/>
<asp:ParameterName="original_CustomerID"Type="String"/>
<asp:ParameterName="original_CompanyName"Type="String"/>
<asp:ParameterName="original_ContactName"Type="String"/>
<asp:ParameterName="original_ContactTitle"Type="String"/>
<asp:ParameterName="original_Address"Type="String"/>
<asp:ParameterName="original_City"Type="String"/>
<asp:ParameterName="original_Region"Type="String"/>
<asp:ParameterName="original_PostalCode"Type="String"/>
<asp:ParameterName="original_Country"Type="String"/>
<asp:ParameterName="original_Phone"Type="String"/>
<asp:ParameterName="original_Fax"Type="String"/>
</UpdateParameters>
<InsertParameters>
<asp:ParameterName="CustomerID"Type="String"/>
<asp:ParameterName="CompanyName"Type="String"/>
<asp:ParameterName="ContactName"Type="String"/>
<asp:ParameterName="ContactTitle"Type="String"/>
<asp:ParameterName="Address"Type="String"/>
<asp:ParameterName="City"Type="String"/>
<asp:ParameterName="Region"Type="String"/>
<asp:ParameterName="PostalCode"Type="String"/>
<asp:ParameterName="Country"Type="String"/>
<asp:ParameterName="Phone"Type="String"/>
<asp:ParameterName="Fax"Type="String"/>
</InsertParameters>
</asp:SqlDataSource>
<asp:GridViewID="GridView1"runat="server"AllowPaging="True"AllowSorting="True"AutoGenerateColumns="False"BackColor="#DEBA84"BorderColor="#DEBA84"BorderStyle="None"BorderWidth="1px"CellPadding="3"CellSpacing="2"DataKeyNames="CustomerID"DataSourceID="SqlDataSource1">
<FooterStyleBackColor="#F7DFB5"ForeColor="#8C4510"/>
<Columns>
<asp:CommandFieldButtonType="Button"ShowDeleteButton="True"ShowEditButton="True"/>
<asp:BoundFieldDataField="CustomerID"HeaderText="CustomerID"ReadOnly="True"SortExpression="CustomerID"/>
<asp:BoundFieldDataField="CompanyName"HeaderText="CompanyName"SortExpression="CompanyName"/>
<asp:BoundFieldDataField="ContactName"HeaderText="ContactName"SortExpression="ContactName"/>
<asp:BoundFieldDataField="ContactTitle"HeaderText="ContactTitle"SortExpression="ContactTitle"/>
<asp:BoundFieldDataField="Address"HeaderText="Address"SortExpression="Address"/>
<asp:BoundFieldDataField="City"HeaderText="City"SortExpression="City"/>
<asp:BoundFieldDataField="Region"HeaderText="Region"SortExpression="Region"/>
<asp:BoundFieldDataField="PostalCode"HeaderText="PostalCode"SortExpression="PostalCode"/>
<asp:BoundFieldDataField="Country"HeaderText="Country"SortExpression="Country"/>
<asp:BoundFieldDataField="Phone"HeaderText="Phone"SortExpression="Phone"/>
<asp:BoundFieldDataField="Fax"HeaderText="Fax"SortExpression="Fax"/>
</Columns>
<RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
<SelectedRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
<PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
<HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
</asp:GridView>


I was doing this in VS2005's built in development server, so I wondered if maybe I moved the project onto an IIS server, i would have different results...I tried the page on the server, but this time the page wouldn;t even render and I got the following error in my web browser:


Server Error in '/SQLTesting' Application.

Cannot open database "Northwind" requested by the login. The login failed.
Login failed for user 'RANDY\ASPNET'.

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: Cannot open database "Northwind" requested by the login. The login failed.
Login failed for user 'RANDY\ASPNET'.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:

[SqlException (0x80131904): Cannot open database "Northwind" requested by the login. The login failed.Login failed for user 'RANDY\ASPNET'.]......


Thank you for any help.

[nikkiH]

Can't help you with the first one, but the login issue is because your dev machine knows who you are. The IIS server you moved it to is trying to use the .NET app credentials instead of yours. You can 1. create the account for SQL Server to match the IIS account, or 2. Set the directory in IIS up to use a different account for requests like this and use trusted connection, or 3. change your connection string to use a specific login and password to sql server.

[chump2877]

Can't help you with the first one, but the login issue is because your dev machine knows who you are. The IIS server you moved it to is trying to use the .NET app credentials instead of yours. You can 1. create the account for SQL Server to match the IIS account, or 2. Set the directory in IIS up to use a different account for requests like this and use trusted connection, or 3. change your connection string to use a specific login and password to sql server.

I guess which one of the three would you recommend - and what are the steps to implementing it? The IIS server is running on my development machine right now, as I am using it for testing. I'm new to administrating IIS and MSSQL, so I need a little hand holding with this.

Thanks.

[nikkiH]

Just to be sure it is working at the basic level, change your connection string to use your SQL login and password, and set trusted connection to false. This is the most basic type of connection so there should be little that can go wrong there. This is your SQL login/pass, not your windows account.

Once that works, next step is to change IIS to use your (assuming this is the one working on the dev box) windows login and password instead. Take it out of your connection string, set trusted connection to true, and set IIS to use it:

IIS 5 -> Navigate to your site definition
Right click -> properties
Directory Security tab -> anonymous access -> edit button
Put the windows account and password there that will be configured for SQL Server (your own, to start with and make sure this works)

Once your login works, you should really create a separate account for web apps. Set it up in SQL Server to have strict permissions. Then use that account. No need to risk something hijacking 'sa' or another superuser account.

[chump2877]

Just to be sure it is working at the basic level, change your connection string to use your SQL login and password, and set trusted connection to false. This is the most basic type of connection so there should be little that can go wrong there. This is your SQL login/pass, not your windows account.


Can you give an example of a valid connection string? Is trusted connection part of the connection string? This is my current connection string:

Data Source=RANDY\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True

Also, I assume this connection string goes in the application's web.config? And I don;t know that I currently have a SQL login and password....I login to SQL Server Management Studio, but the username and password are empty and disabled (it's using Windows Authentication)...How do you create a new SQL Server login/password?


Once that works, next step is to change IIS to use your (assuming this is the one working on the dev box) windows login and password instead. Take it out of your connection string, set trusted connection to true, and set IIS to use it:


I think IIS is using Windows Authentication as well. When I go to ASP.NET configuration Settings dialogue box for IIS, it says that it is. Also, the connection string in there is:


Data Source=RANDY\SQLEXPRESS;Initial Catalog=Northwind;Integrated Security=True


It's identical to my web.config file.


IIS 5 -> Navigate to your site definition
Right click -> properties
Directory Security tab -> anonymous access -> edit button
Put the windows account and password there that will be configured for SQL Server (your own, to start with and make sure this works)


Right now, this dialogue is configured to use Anonymous access...The user name is IUSR_RANDY and the password is there, but hidden..."Allow IIS to control password" is checked....nothing else is set in that dialogue box...

Thank you for your help.

[nikkiH]

What are you using to get to SQL? That looks like SQL Express? Not SQL Server?
The information store that has Northwind isn't on your local machine, is it?

[chump2877]

What are you using to get to SQL? That looks like SQL Express? Not SQL Server?
The information store that has Northwind isn't on your local machine, is it?


I have SQL Express and SQL Server Management Studio Express installed on my development machine....I actually have the full version of SQL Server, but it's not installing properly...so i gave up and went with the express versions for now...You should still be able to add SQL server accounts with the express version, but I don;t know how...

The NorthWind database is on my development machine...Everything is installed on my dev machine right now...

I need to get the current setup at least working for now, so i can do some testing now...

-------------------------------------------------------------------------

By the way, I figured out my first problem, in case anyone has the same problem:

There is a known bug in ASP.NET, documented here: http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937 (http://connect.microsoft.com/VisualStudio/feedback/ViewFeedback.aspx?FeedbackID=93937)

A solution is provided here: http://connect.microsoft.com/VisualStudio/feedback/Workaround.aspx?FeedbackID=93937 (http://connect.microsoft.com/VisualStudio/feedback/Workaround.aspx?FeedbackID=93937)

The fix is something that you have to do manually....And for lengthy queries this can be tedious...

I figured I could write a regex to do this quickly...I tried using the Find and Replace feature in VS2005, but it didn;t seem to accept a syntactically legal regex pattern...

So I created a simple Console Project that reads the .aspx file in, modifies the file accordingly, and then writes it back to the file, overwriting the existing contents. I added the project to my web application solution and ran it from there. When the console program completes, you'll be prompted to refresh the .aspx file if it is open in VS2005. After it refreshes, your entire file (any and all UPDATE and DELETE queries in the file) has been modified.

The Edit (and I assume Delete -- haven;t tried this yet) features work OK now. I have attached the Console project to this post, in case others would like to use it (as I can only assume that others will have this problem as well).

[nikkiH]

Very interesting.

So you're still having the issue connecting to the SQL store from production, right?
Did you try "Integrated Security=SSPI" in the conn string?

[chump2877]

So you're still having the issue connecting to the SQL store from production, right?

Yes.

Did you try "Integrated Security=SSPI" in the conn string?

I tried changing this in both the app's web.config and in the IIS virtual directory (although the IIS setting seems to change when I change the web.config -- I guess that makes sense)....

So I'm still having the previous problem and getting the previous error message when loading the page in IIS....

I don;t know if it helps any, but the connection string in my global IIS configuration is as follows:


data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true


and in the machine.config:


<connectionStrings>
<add name="LocalSqlServer" connectionString="data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true" providerName="System.Data.SqlClient" />
</connectionStrings>

[SouthwaterDave]

Getting ASP.NET/IIS/SQL Server security right is tricky but I find the following entries in the web.config file on my development computer the easiest option:
<configuration>
<system.web>
<authentication mode="Windows"/>
<identity impersonate="true"/>
</system.web>
</configuration>
I installed SQL Server Express on my development computer and I am the system administrator.

Read or write access to the file system or the registry is OK too.

[chump2877]

Thanks Dave, I may try that eventually....

IIS was causing problems with my installation of XP Pro, so I decided to restore a previous system state (before the IIS installation).

I had previously installed IIS v.5.1, the version that comes with XP Pro...

But after I installed IIS, I gradually noticed some strange things going on:

1) Shortcuts for Outlook, Internet Explorer, and Windows Media Player (among other things) were GONE, and the executable files for those programs where nowhere to be found in Windows Explorer...At first, I thought everything had disappeared, but when I went to Start->Run, and entered the necessary commands, I found that all of these programs were indeed still installed and completely functional on my system. But, at first glance, it sure looked like they were gone....Wierd...

2) Certain startup programs and icons in my system tray (bottom right of OS) were missing...Foremost, my Volume Control was GONE....i had to go into the control panel if I wanted to adjust the volume of the computer....it was nuts

So, obviously, installing IIS over my XP Pro installation did a number on my registry or something to that effect...

So now, I am pressed to find another way to install IIS without screwing up the operating system in the process....

Is there a way to install IIS without causing all of these OS problems? Without having to reinstall everything (the OS included) from scratch?

One idea that comes to mind is to create a new virtual machine using Microsoft Virtual PC, and install a fresh copy of XP Pro and IIS to the virtual machine. Then I can could test ASP.NET web apps on IIS in my virtual operating system....But I don;t know if this would work (for example, can you map virtual directories in the virtual OS to directories on my physical drives? Or do I need to copy all VS2005 projects to the virtual operating system? Would I need to install VS2005 and/or SQL Server on the virtual machine?)....I'd rather get some input on this idea before implementing it -- since creating a new virtual machine takes awhile to set up....

Any ideas would be helpful here. Once I get this squared away, I can get back to adjusting permissions/authentication/connection strings to get IIS to connect successfully to SQL Server...

[nikkiH]

Personally, I'd rather just reinstall the OS and everything from scratch rather than use a virtual machine.
Then again, I play a lot and have had to reinstall a few times, so reinstalling is easy for me due to all the practice. :D