It came to me that the [ID] autonumber index field in my Access 2000 Database name may be a reserved word in ASP.NET. I was looking at the paramiters in the GridView and noticed (ID) was used. So I changed the name in the table to RecordID and then in the GridView I changed the AccessDataSource1 to reflect the change. I ran the program and it all now works. So another note for my notebook. Do not name an index autonumber record "ID" or it will not work with ASP.NET.
Thanks everyone for your help.
Have a good weekend.
Mike
Friday, August 10, 2007
Back on the Forums
Today I went back to the forums.asp.net and submitted the problem. I am amazed at the generosity and helpfullness of the programmers community. Most are very helpfull and there would be no way I could progress without there help. So my hats off to you guys! Great Job.
Since I can not enter html in my blog I can not past the post into the blog. But better yet here is the link to the forum post.
My Post on this issue in FORUMS.ASP.NET
I have one suggestion so far but it did not work. I still believe the problem is the variable is not passing to the [ID]=? But I will continue to try there solutions.
Since I can not enter html in my blog I can not past the post into the blog. But better yet here is the link to the forum post.
My Post on this issue in FORUMS.ASP.NET
I have one suggestion so far but it did not work. I still believe the problem is the variable is not passing to the [ID]=? But I will continue to try there solutions.
Wednesday, August 8, 2007
Getting Closer to the Answer
OK, So I started experimenting in the code. I tried intentinally entering the wrong field number in each line of the code to see what the program was reading and to force it to blow up. The results were educational. I found that
line1 DataFile="~/App_Data/PhoneList.mdb" DeleteCommand="DELETE FROM [PhoneListTable] WHERE [ID] = ? AND [FirstName] = ? AND [LastName] = ? AND [ShowInPhoneList] = ?"
line 2InsertCommand="INSERT INTO [PhoneListTable] ([ID], [FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?, ?)"
line 3 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [ID], [FirstName], [LastName], [ShowInPhoneList] FROM [PhoneListTable]"
UpdateCommand="UPDATE [PhoneListTable] SET [FirstName] = ?, [LastName] = ?, [ShowInPhoneList] = ? WHERE [ID] = ?">
If you notice at the end of line 3 if I enter a value for WHERE [ID] = ? and make it WHERE [ID] = 2 then when I do an edit and change the data for that record and then click on UPDATE it updates the record that has the [ID] = 2. Even if I click on record [ID] = 1 it still updates record 2. So I now know it is not a permissions problem but a code problem. I also now know that I can make it work but I just have to figure out how to pass the variable that I click on into the [ID] = ?
Watch for future post.
line1 DataFile="~/App_Data/PhoneList.mdb" DeleteCommand="DELETE FROM [PhoneListTable] WHERE [ID] = ? AND [FirstName] = ? AND [LastName] = ? AND [ShowInPhoneList] = ?"
line 2InsertCommand="INSERT INTO [PhoneListTable] ([ID], [FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?, ?)"
line 3 OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [ID], [FirstName], [LastName], [ShowInPhoneList] FROM [PhoneListTable]"
UpdateCommand="UPDATE [PhoneListTable] SET [FirstName] = ?, [LastName] = ?, [ShowInPhoneList] = ? WHERE [ID] = ?">
If you notice at the end of line 3 if I enter a value for WHERE [ID] = ? and make it WHERE [ID] = 2 then when I do an edit and change the data for that record and then click on UPDATE it updates the record that has the [ID] = 2. Even if I click on record [ID] = 1 it still updates record 2. So I now know it is not a permissions problem but a code problem. I also now know that I can make it work but I just have to figure out how to pass the variable that I click on into the [ID] = ?
Watch for future post.
Problem writing to PhoneList.mdb file
Well I am back on the problem today of not being able to update the PhoneList.MDB file.
I moved the application and the datafile to my IIS Server and tested there. I found that my web.config file was working and that trace was running. However the mass amount of information provided no light on why the table is not updating. There was no difference in how the application ran on my local workstation and the web server. I will take a close look at the permissions but I do not think that is the problem.
I was told on the forums.asp.net site that Visual Studio Express generates incorrect code and trys to write to the autonumber field. Visual Studio Express generated the following line.
InsertCommand="INSERT INTO [PhoneListTable] ([ID], [FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?, ?)"
But that code is not correct because [ID] is an autonumber field and can not be written to. So I removed the [ID] and one of the ? marks. The new line looks like this.
InsertCommand="INSERT INTO [PhoneListTable] ([FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?)"
I just checked the table and all the fields to make sure that REQUIRED was set to NO. All the fields in the table are correct.
I also removed the lines with the [ID] in them from the
I checked the permissions for the App_Data folder and all accounts have full rights. I even gave Anonymous login with full rights. This still did not resolve the problem so I continue to research what the problem is. I believe it is still something in the code.
I moved the application and the datafile to my IIS Server and tested there. I found that my web.config file was working and that trace was running. However the mass amount of information provided no light on why the table is not updating. There was no difference in how the application ran on my local workstation and the web server. I will take a close look at the permissions but I do not think that is the problem.
I was told on the forums.asp.net site that Visual Studio Express generates incorrect code and trys to write to the autonumber field. Visual Studio Express generated the following line.
InsertCommand="INSERT INTO [PhoneListTable] ([ID], [FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?, ?)"
But that code is not correct because [ID] is an autonumber field and can not be written to. So I removed the [ID] and one of the ? marks. The new line looks like this.
InsertCommand="INSERT INTO [PhoneListTable] ([FirstName], [LastName], [ShowInPhoneList]) VALUES (?, ?, ?)"
I just checked the table and all the fields to make sure that REQUIRED was set to NO. All the fields in the table are correct.
I also removed the lines with the [ID] in them from the
I checked the permissions for the App_Data folder and all accounts have full rights. I even gave Anonymous login with full rights. This still did not resolve the problem so I continue to research what the problem is. I believe it is still something in the code.
Tuesday, August 7, 2007
First Project - Office Phone Book
The Office Phone Book Project
This project has been on my desk for months. I keep putting it off becuase I want it to be a good test of ASP.NET database driven web site. The database in phonebook data is already in an Access 2000 MDB.
I start Microsoft Web Developer 2005 Express Edition or "Web Developer" for short. From the main screen I create a new Web Site. Location listed below.
"My Documents\Visual Studio 2005\Projects\PhoneBook" and I select ASP.NET web site. Web Developer then creates the following files and folders.
Phonebook\App_Data
Default.aspx
Default.aspx.vb
PhoneBook.sln
web.config
PhoneBook\App_Data\Aspnetdb.mdf
PhoneBook\App_Data\aspnetdb_log.ldf
PhoneBook\App_Data\web.config
I copied our PhoneList.mdb file that contains all of our phone book data from our network to my workstation and placed it in the following location.
PhoneBook\App_Data\phonelist.mdb
(1) Now in my Web Developer I drug a GridView onto my Default.aspx work serface.
(2) I then selected the GridView Tasks and under 'choose data source' I selected
(3) I then selected Access Database as the database type and let the Developer specify the default ID for the data source as 'AccessDataSource1'
(4) Then I selected the path to the Access 2000 database phonelist file. The location is; '~/App_Data/PhoneList.mdb'.
(5) Now the wizard wants to know what data I would like to use and from what table. I select the PhoneListTable and then select the following fields.
[ID] - This is the autonumber index field in my table.
[DID_Number]
[FirstName]
[LastName]
[ShowInPhoneList] = This is a boolian Y/N check box.
(6) I then click on the Advance Tab. Since I selected an index field [ID] The developer is going to allow me to 'Generate INSERT, UPDATE, and DELETE Statements' So I check this box. I also check the box 'Use Optimistic Concurrancy' so that updates by mutiple users in the same record would be possible without data collisions.
(7) After clicking on NEXT I test the query and find that my data does appear.
(8) I am now taken back to the GridView which has been updated to now show the fields that I selected above. I now check 'Enable Paging', 'Enable Sorting' and 'Enable Editing'.
I am now ready to test my database web page. I click on RUN to test my application. I do not get any errors in my Web Developer and the web page with my data displayed in a table works perfectly. However when I click on Edit a record and try editing or adding data to any field and the click on Save nothing happens.
So my question is what went wrong. I tested the connection and that passed. The web page displays the data with no problem. So why can I not update a record?
I stop the application and then click on the Database Explorer. I right click on PhoneBookTable and select Display Data.
The Web Developer now shows a complete list of my data. I test editing a field and it works perfectly within the Web Developer. But from a web page nothing happens when I edit a record. The record shows the data changes in the field but when I click on UPDATE the data disapears or reverts back to the original data.
This project has been on my desk for months. I keep putting it off becuase I want it to be a good test of ASP.NET database driven web site. The database in phonebook data is already in an Access 2000 MDB.
I start Microsoft Web Developer 2005 Express Edition or "Web Developer" for short. From the main screen I create a new Web Site. Location listed below.
"My Documents\Visual Studio 2005\Projects\PhoneBook" and I select ASP.NET web site. Web Developer then creates the following files and folders.
Phonebook\App_Data
Default.aspx
Default.aspx.vb
PhoneBook.sln
web.config
PhoneBook\App_Data\Aspnetdb.mdf
PhoneBook\App_Data\aspnetdb_log.ldf
PhoneBook\App_Data\web.config
I copied our PhoneList.mdb file that contains all of our phone book data from our network to my workstation and placed it in the following location.
PhoneBook\App_Data\phonelist.mdb
(1) Now in my Web Developer I drug a GridView onto my Default.aspx work serface.
(2) I then selected the GridView Tasks and under 'choose data source' I selected
(3) I then selected Access Database as the database type and let the Developer specify the default ID for the data source as 'AccessDataSource1'
(4) Then I selected the path to the Access 2000 database phonelist file. The location is; '~/App_Data/PhoneList.mdb'.
(5) Now the wizard wants to know what data I would like to use and from what table. I select the PhoneListTable and then select the following fields.
[ID] - This is the autonumber index field in my table.
[DID_Number]
[FirstName]
[LastName]
[ShowInPhoneList] = This is a boolian Y/N check box.
(6) I then click on the Advance Tab. Since I selected an index field [ID] The developer is going to allow me to 'Generate INSERT, UPDATE, and DELETE Statements' So I check this box. I also check the box 'Use Optimistic Concurrancy' so that updates by mutiple users in the same record would be possible without data collisions.
(7) After clicking on NEXT I test the query and find that my data does appear.
(8) I am now taken back to the GridView which has been updated to now show the fields that I selected above. I now check 'Enable Paging', 'Enable Sorting' and 'Enable Editing'.
I am now ready to test my database web page. I click on RUN to test my application. I do not get any errors in my Web Developer and the web page with my data displayed in a table works perfectly. However when I click on Edit a record and try editing or adding data to any field and the click on Save nothing happens.
So my question is what went wrong. I tested the connection and that passed. The web page displays the data with no problem. So why can I not update a record?
I stop the application and then click on the Database Explorer. I right click on PhoneBookTable and select Display Data.
The Web Developer now shows a complete list of my data. I test editing a field and it works perfectly within the Web Developer. But from a web page nothing happens when I edit a record. The record shows the data changes in the field but when I click on UPDATE the data disapears or reverts back to the original data.
Let the Journey Begin - or ASP.net 101
OK so let the games begin. Start with a new computer on my desk. HP Compaq with an AMD Athlon X2 and 4gbt of RAM. Since I work for the government this machines must meet certin requirements. One of the requirements is that the machine must be running Windows XP Professional and it is connected to the domain. Since I am an Administrator (IT GOD!!!) I have full rights and permissions to the desktop and the network.
I start with a standard configuration of Windows XP Professional. I am also running Microsoft Office 2000 Professional with Access 2000 and Outlook 2003. I download and install Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 but do not expect to use SQL SERVER as most of my database work needs to be in Access 2000 MDB files.
A note: I do not have IIS Services installed this machine as it is not going to be a web server. My web server is a dedicated server in my computer room. I will be doing all the development work on my desktop development system and then transfering the files to the network IIS Server.
My goal is to develop web pages that are database related. What I mean is most of my needs for web pages in ASP.NET is access to Microsoft Access 2000 database tables. I need not only to display information but also to edit, add, delete, and update table data.
I start with a standard configuration of Windows XP Professional. I am also running Microsoft Office 2000 Professional with Access 2000 and Outlook 2003. I download and install Microsoft Visual Web Developer 2005 Express Edition and Microsoft SQL Server 2005 but do not expect to use SQL SERVER as most of my database work needs to be in Access 2000 MDB files.
A note: I do not have IIS Services installed this machine as it is not going to be a web server. My web server is a dedicated server in my computer room. I will be doing all the development work on my desktop development system and then transfering the files to the network IIS Server.
My goal is to develop web pages that are database related. What I mean is most of my needs for web pages in ASP.NET is access to Microsoft Access 2000 database tables. I need not only to display information but also to edit, add, delete, and update table data.
Introduction
This web blog serves many purposes. One of the purposes of the blog is to provide me a diary of my journey and frustrations in migrating to ASP.net. I am a veteran ASP 2.0 programmer who has been convinced by fellow programmers and friends that I need to transition to ASP.net. After a number of failed attempts I found that Microsoft was giving away "Visual Web Developer Express Edition 2005" and "Microsoft SQL Server 2005". Both appear to be wonderful tools and I can only assertain that the reson for this giveaway was to further push old ASP progammers such as myself into the future DOT net world.
I have made a number of failed attempts of getting started with ASP.NET. I purchased my weight in ASP books and found most more confusing than helpful. Then I located a number of free vidoes on the internet that appeared to be just what the doctor ordered. So here is my journey.
Links of Interest.
Microsoft Visual Studio Express Edition 2005 Web Site
Learn Visual Studio.NET Videos
I have made a number of failed attempts of getting started with ASP.NET. I purchased my weight in ASP books and found most more confusing than helpful. Then I located a number of free vidoes on the internet that appeared to be just what the doctor ordered. So here is my journey.
Links of Interest.
Microsoft Visual Studio Express Edition 2005 Web Site
Learn Visual Studio.NET Videos
Subscribe to:
Posts (Atom)