Link to home
Create AccountLog in
Avatar of shwaqar82
shwaqar82

asked on

Implementing DB using MS Access in ASP.Net

Hi Experts
Im implementing MS Access  in ASP.Net using VB.Net. Im trying to implement a database first time and i need step by step guidance.
Lemme tell you what exactly im trying to implement. I need to develop a page that allow an administrator to view all the fields in the database in gridview. This page should allow an administrator to insert update or delete the records. Sounds simple to you but not to me as i tried to do that but didn't get 100% success.

Now the DB name is thestory.mdb and the fields inside it are storyid, traveldate, country, city, hotelname, starrating, roomtype, storytext. storyid is my primary key.
Please help me out .....

Best Regards
Shaukat Waqar
Avatar of NauticalNonsense
NauticalNonsense
Flag of United States of America image

Hi Shaukat,

I, like many developers, build everything in SQL, so if you have access to using SQL, your life will be a lot easier.

There is a Data Access Layer provider called SubSonic.

http://www.codeplex.com/actionpack

View the demo, and look at the scaffold demonstration at the end. It does pretty much EVERYTHING for you. It's not the prettiest (you can gussy it up with CSS) but it is simply the easiest, most amazing thing I've ever seen.

And if you have to use access, my condolences. :)  Post what code you have and where it's bombing out for the Access folk out there.

Good luck.
Avatar of shwaqar82
shwaqar82

ASKER

the probelm is that im not famaliar with using MS SQL. I wish i will start using MS SQL. I tried to install MS SQL Express edition which is a free version availible online. But i got confuse during the installation b/c it grays out 'Native Client' option in order to install that service on my machine. That would be great if you guide me through the installation process and we can go from there.
Lemme give you the detail of my machine. Im using Windows XP Professional Edition. And have Dot Net Framework 2.0 and Service Pack 2.0.
Shaukat, I have never had installation problems with SQL 2005, but you will need a bigger version of the software to run it on a production server. For SQL Express, you have to be logged in as an Admin on the local machine you're installing it on, and for me, it was a Next->Next->Next->Next->Finish kind of thing.

It sounds like you might be limited to Access at this point in time, I just mention the actionpack because it makes EVERYTHING incredibly easy.

If you go the Access route, post your code here and where it is giving you problems. That will give everyone something to go on.
Hi
I have an administrator access to my local machine and its not the matter of next and next but its the matter of not fully aware of how to use the software. During the installation setup for MS SQL Server Express Edition, im getting the following message on 'Ready to Install' window.

The following components will be installed:

SQL Server Database Services (Upgrade)
(Database Services)
(Replication)
Warning: Setup found that the following components that already exist are at a different service pack level than the components being installed.
Components: Microsoft SQL Server 2005 Tools Express Edition
After completing setup, you must download and apply the latest SQL Server 2005 service pack to all the components.

My concern is why this setup is not installing 'Components: Microsoft SQL Server 2005 Tools Express Edition' . What i need to do to overcome this problem. Right now do i have to click install button in order to install just SQL Server Database Services or what.......?
Any help please and thank you
 
ASKER CERTIFIED SOLUTION
Avatar of NauticalNonsense
NauticalNonsense
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer
Okie i did install watever service i allow me to install. Now im installing 'Microsoft SQL Server 2005 Service Pack 2'. There are 3 downloads availible which one i need to install:
1. SQLServer2005SP2-KB921896-IA64-ENU.exe        
2. SQLServer2005SP2-KB921896-x64-ENU.exe              
3. SQLServer2005SP2-KB921896-x86-ENU.exe
 
x86-enu is the english XP one ... the others are 64 bit machines.
okie ..i figure it out ..can you elaborate your statement "your Management Tools are a higher version than the SQL you're installing". What does that means and with "x86-enu" installation whats gona happen. Is that the latest release for SQL or ........?
That service pack should upgrade everything to the same version.
it means i dont need to do anything manually after installing this..rite.......? well im asking this question coz downloading is still in progress..lolz ...temme do i need anything else after that for creating and managing database in SQL........?
well now im getting an error in installing the software. It says: 'SQLServer2005SP2-KB921896-x86-ENU.exe is not a valid Win32 application' .....hard luck dun know wat i need to do.......

any help...?

Shaukat, I have no idea why that would bomb on you... I've never had any problem installing SQL stuff. You can try uninstalling all of the SQL stuff (tools included) and just running the original install (SP0) ....
well it might be coz its not downloaded properly......it suppose to be 228MB but i have only 148MB downloaded...im downloading it again ...let see it helps or not ..i tried uninstalling n installing again several times..it does not help
here you go i did that finally with your help nonsense ... thanks alot....now come to my question ..i need to create database in SQL using SSMSE ...how can i make new database in it
You can make a database in the SQL Tools, or do it directly from Visual Studio...  in SSMSE connect to the server, right click database, new database, then add your table.
kool stuff ..thanks nauticalNS ...now i created the table name 'theStory' having table 'storytable'. I have the following fields in this table:
storyid(datatype = unqueidentifier) [storyid is my primary key]
country(datatype = text)
city(datatype = text)
hotel(datatype = text)
star(datatype = text)
date(datatype = datetime)
room(datatype = text)
story(datatype = text)

now i need to fill few rows in the table so i can check if its working properly then the last thing coms up which is my actual question for allowing the administrator to insert update n delete the records from page....
Any help for how to add records in the table using SSMSE
Databases > Your Database > Tables > theStory > Right Click, Open, add where it's got nulls.

After you have that, take the demo on Subsonic at the link I provided, and take a look at that. Watch how he installs it, and take the same from the samples he provides.

After you have it installed, you can do the Scaffold he shows at the end. It's pretty sweet.
i didn't see any demo to watch ...where is dat video in the page......?
my bad i found out the demo :)
well nautical its good but i will prefer to do it manually through grid view in order to understand the classes.... can you help me with that
I'm heading out for the day ... do some googling on updateable gridview and start playing around. there are a ton of tutorials out there on updating gridviews, connecting to SQL databases, etc. Plus, you may have some luck with the drag and drop controls in the VS IDE, although I haven't used them in 2005, from some demos I've seen, they look pretty good.

Nonetheless, welcome to SQL... it's a MUCH better place to be. I have a couple clients who I inherited that are Access/Asp, and it's awful.
finally i got it to work...yahoooo!!!!....well but again i need help in deployment coz i dunno wat i need on my server to deploy this project. i have MS Server 2003 version and we are using both Framework 1.0 n 2.0. What else i need on my server to make it work coz right now its giving me an error message that says:
An error has occurred while establishing a connection to the server.  When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

This error might be coz i dont have SQL Server installed on my server ....
Correct. You'll need SQL in your production environment. You can do a Backup/Restore to the production database and that should work. You might need to create a user, or set up authentication differently than on your local machine (usually web.config file).

Congrats, glad you were able to make the quantum leap to SQL in a couple days. Welcome aboard :)
thanks nautical
can u elaborate more about your statement " You can do a Backup/Restore to the production database and that should work. You might need to create a user, or set up authentication differently than on your local machine (usually web.config file) "
What you mean by Backup/Restore.
Before playing anything on the server im curious to know what are the right things to be installed on production server.
Coz its a Windows Server 2003 and for and on this OS if i installed MS SQL Server 2005 would it be matter. Secondly the simple application i made now is on 2.0 framework and im gona utilize it on 1.0 in production server. Lolz sounds weird isn't it :).
Any help regarding this please.......?
You need to have SQL Server 2005 on your production machine.

You can do a backup from the management tools in SQL to a file (go to your database, right click, backup should be in there)... Then, take that file and do a Restore on your production machine from it.

You'll need to change the settings in the web.config file to point to the production SQL on the production server.

If you used a gridview, you have to run it in framework 2.0. Gridview in new in 2.0, replacing Datagrid in 1.0 apps.

Your production server can handle multiple .Net frameworks. You'll need to install 2.0 on it, and then in IIS set up that website, hit the ASPNET tab, select 2.0, and off you go.