November 14, 2016 at 3:03 pm
I'm trying to wrap my head around what approach to use in building an application that allows daily import of data and data viewing and entry (update of existing data). The part I need some ideas for is this: The data comes in as event rows that revolve around a particular visit. I want to allow users to see all data related to one visitID on one .NET web page so that, for example, if they enter some notes they will see those notes on that same page. The system they had when I got here is in Access. Each eventID row is separate so if they enter notes on one of the forms it doesn't appear in the other related rows that have that same visit ID. There's a lot more to this application than this but this highlights the crux of my issue I think.
The part I cannot visualize is how to display the data on one .NET webpage that they can view and update. I know I'm being a bit vague but I am looking for an idea / some guidance in general. I'm not a programmer but I have some experience with SSIS, SSRS, .NET web page development, etc. but mostly I'm a DBA but they don't have anyone else to do this.
Example rows would look something like this:
VisitID status statusDate EntryLocation ExitLocation factoid1 factoid2 [...]
1 entry 9/9/2009 here there blue red
1 exit 9/12/2009 near far teal orange
5 exit 10/1/2015 west east grey ecru
999 entry 9/18/2015 south jones white NULL
The data as it comes in from an external source is partially denormalized, for example, columns for EntryLocation and ExitLocation.
Please let me know if I need to add more information to help you to help me.
Thanks.
November 16, 2016 at 11:02 am
Did this ever work in Access? If so, I would look at the RowSource properties of the various forms involved, so you know what's coming from where. Once you understand that, you should be able to create stored procedures to return the same data, and then others to do the CRUD stuff.
November 16, 2016 at 2:13 pm
Thanks for responding. No, it never worked in Access. The AccessDB shows each row (event) as a separate page. Some of the users hand-keyed the notes onto each event for a given user but that's not a good solution for them. If all else fails, I can have one webform page for each person with individual rows in a gridview. Not ideal. Not elegant for sure. I could put the notes column's data on the parent page with the rest of the person's data so when they page through the gridview pages they still see the notes. As you can tell from my description 1) I've only basic .NET experience and 2) by my use of webform instead of using MVC or Entity Framework I'm old school. Thhis is probably the only time I'll need to use .NET in my role so I figure webforms are good enough.
I was just hoping that there would be some pointers to how I could avoid just having a master/detail type webform with individual events in the . If someone knows, please just give me some keywords to google or somesuch and I will investigate further.
Thanks again.
November 17, 2016 at 11:02 am
pharmkittie (11/16/2016)
Thanks for responding. No, it never worked in Access. The AccessDB shows each row (event) as a separate page. Some of the users hand-keyed the notes onto each event for a given user but that's not a good solution for them. If all else fails, I can have one webform page for each person with individual rows in a gridview. Not ideal. Not elegant for sure. I could put the notes column's data on the parent page with the rest of the person's data so when they page through the gridview pages they still see the notes. As you can tell from my description 1) I've only basic .NET experience and 2) by my use of webform instead of using MVC or Entity Framework I'm old school. Thhis is probably the only time I'll need to use .NET in my role so I figure webforms are good enough.I was just hoping that there would be some pointers to how I could avoid just having a master/detail type webform with individual events in the . If someone knows, please just give me some keywords to google or somesuch and I will investigate further.
Thanks again.
Unless they are on some ancient history version of MS Access, there's probably no reason that the Access forms couldn't be re-worked to provide visibility to every row with the same VisitID. That said, your primary goal with your .NET page will effectively be that same re-work, just in a different medium. The row-source will need the same re-work that the Access control row-source would have needed. Within Access, you might well have needed to use some kind of ActiveX Grid control, like MSFlexGrid, as opposed to whatever is currently in use. Once you come up with a modified row-source query to pull all the data for the same VisitID, that's probably most of the database work. Much of the rest might well be able to be copied from Access. Let us know if you have trouble figuring out the query, and provide some test data with table create statements and inserts for some test data, along with expected results, and you'll get help pretty quickly.
Edit: If it turns out you're migrating MS Access data up to SQL Server, be sure to look at Microsoft's SSMA product, which has a version for almost all versions of Access, and by version of SQL Server, so the number of versions of SSMA is quite large. It's free as of the last time I checked it out, and it's well worth dealing with. Even if you're going to have to change data structures up in SQL Server, it's quite useful to migrate the data as is, and then SSMA can change the existing tables in the Access database to become linked tables to the SQL Server versions. After any changes to the table structure, you then use Access' Linked Table Manager to refresh that connection and the structure changes show up in MS Access. Very useful....
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 17, 2016 at 7:23 pm
Thanks sgmunson. I think what I'll do to make my question much easier to answer is build a quick-and-ugly .NET webpage to show the problem I'm having in thinking about how to display the events related to a visit. It may take me a while to post again because I may not be able to get back to this tomorrow because of other projects but when I have it I will post again with enough detail to make it clear what I need help with.
The Access DB doesn't do most of what the users want so I will be changing almost everything.Thanks again.
November 23, 2016 at 7:55 am
pharmkittie (11/17/2016)
Thanks sgmunson. I think what I'll do to make my question much easier to answer is build a quick-and-ugly .NET webpage to show the problem I'm having in thinking about how to display the events related to a visit. It may take me a while to post again because I may not be able to get back to this tomorrow because of other projects but when I have it I will post again with enough detail to make it clear what I need help with.The Access DB doesn't do most of what the users want so I will be changing almost everything.Thanks again.
I just re-read your initial post, and if you're not used to designing web pages or working with object-oriented programming languages because you're used to being a DBA, you might be struggling just to understand how the basics operate. Web pages are what is known as "state-less". It means that they generally do not hold on to ANY information that isn't kept in a "control" that is part of the web page... Thus gone is the concept of creating a variable and then being able to use the value of that variable to do things with, that so many of us got used to by learning the BASIC language, or FORTRAN, or even COBOL. VBA, which you can use within MS Access, now provides all the object-oriented abilities, but once the code that you attach to a given event for a given control runs, any values it was using are gone unless they are preserved within the control. It's a serious paradigm-shift. Only the controls retain any information. Your program code is largely there to implement what the user wants done when they click on something. Similarly, MS Access forms operate on the same concept. There is no "master program"... just code to operate when an "event" occurs. Data is kept in tables or in the various controls you design into a "form". That doesn't mean that you can't write your own functions or subroutines that code associated with a given form or control can call or execute. Recent versions of MS Access can have a form set up to use tabs, which is a convenient way to re-use space. Think of it as similar to opening multiple tabs in a browser. Each tab could display different elements, and in your case, possibly specific events or types of events that are associated with a given VisitID. Does that help?
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply