March 12, 2008 at 12:32 am
I have SQL Server 2005 running on a server w/SBS 2003.
I have created a test web application with Visual Web Developer using the controls available in that program to create pages of database records that can be updated.
Having gone through the initial tutorials, I created some .aspx pages that display data from my database tables, however the update, insert and delete functions do nothing. The grid view goes into edit mode when the edit button/link is pressed, but when data is typed or changed and the update button/link is clicked, nothing changes.
It is my belief that some SQL database setting is preventing these updates as the code seems to be as the examples and tutorials suggest. The SQL Datasource is trying to use Windows Authentication as opposed to SQL Authentication. I think I can find out how to change this if needed.
The .aspx pages are at the http://www.root (inetpub) level. Since they are outside the folders which, as an example, SharePoint uses, no login is requested when the pages are requested from the server. No sign on to the server is happening.
Is this why the data is not being updated? If so, how do I rectify this?
I hope I have provided enough information for someone to help.
Thanks,
HN
March 12, 2008 at 12:50 am
If you have access to SQL Profiler, run it against the SQL server while testing out your web page. You'll be able to see if the delete, insert or update commands are going to the server.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 12, 2008 at 9:57 pm
i have looked and do not have this program. (Profiler) where can I get it?
Thanks,
March 12, 2008 at 11:08 pm
You should double-check the nullability of the parameters used in the ASP:SQLDataSource object. The default behavior is to abort the action if any of them are null (meaning - it doesn't even attempt the insert/update/etc...)
Also - you need to make sure that the DataKeyNames property is set to the name of the Primary Key field in your table.Without that - the automated wireup for updates.deletes, etc... doesn't work.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 13, 2008 at 12:53 am
Any machine that has the SQL management tools on it (Management studio and the like) will have profiler. You can profile the server from any machine that can see it.
To trace the server, you'll need sysadmin rights, or someone who has sysadmin rights to run it for you.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 13, 2008 at 1:34 am
Please explain how to do these two suggestions.
Where is it that I should look to check null-ability of the datasource parameter?
And where for the DataKeyName information?
Thanks,
March 13, 2008 at 6:50 am
- The controls have a property called DataKeyNames. It need to be set to the name of the column which is the primary key in the table you're updating.
- If you look at the SQLDataSource while in Source view, trying to modify the parameters should show you thing that allow you to assign def values. Each of the operations (insert/update/delete/select) should also have a flag stating something to the effect of "cancel on null parameter". Meaning - it at the time when the operation is to run, any parameter is null - then don't bother to run.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 8:57 am
Matt,
Thanks.
The DataKeyName is set to the primary key which is "Student_id"
After reviewing the control (GridView1) in source mode I do find something called "UpdateParameters" In that section there are only the following kinds of statements:
A line like this one appears for each field in the Student_main table.
The last item is:
Do I need to insert some additional parameter?
HN
March 14, 2008 at 9:03 am
Trying this again since my examples did not get into my last post. This time I am omitting the symbols that begin and end these parameters.
I have the following:
UpdateParameters
asp:Parameter Name="PA_id" Type="String" /
... more like this for each field in the table and then the ending
asp:Parameter Name"Student_id" Type"Int32" /
No other parameters are included there.
March 14, 2008 at 9:13 am
Your parameters got "whacked" when you pasted it. You need to paste them into a text editor and run a search and replace for the brackets.
Replace < with <
Replace > with >
That being said - try assigning a default value to each of the update parameters. Also - try adding this to the main Asp:SQLDataSource tag:
CancelSelectOnNullParameter="false"
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 14, 2008 at 9:18 am
Also,
I only have one primary key Student_id. As required, it is included in the select statement. It does not show (in VWD) as Primary Key but in SQL Server Mgmt Studio, it is certainly set to be the primary key.
So the code generated includes the Insert, Update and Delete statements.
The Student_id field is indicated as not null also.
I am lost as to what to try to get this to work as it should.
March 14, 2008 at 9:48 am
I have the following:
UpdateParameters
asp < Parameter Name="PA_id" Type="String" />
... more like this for each field in the table and then the ending
asp < Parameter Name"Student_id" Type"Int32" />
No other parameters are included there.
The primary key does not allow nulls.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply