May 20, 2013 at 1:42 pm
Thanks Andrew. I am learning to use SSMS and find it managable, but coming over from Access, there is a bit of a learning curve as I shift my thinking into all that is SQL server. I will need to learn t-sql and stored procedures as well. Are there any good books or online training materials that you would recommend?
In an ideal world, I would start with a brand new database and rework the tables, indexes and other items, but the users want the access database application to function as it has in the past. Part of that means making historical data available for the purposses of year to year comparisons. The good thing is that 10 years worth of data is only 69MB.
I'm getting into SQL Server and .net because Sharepoint's access services falied to provide a viable solution to add a web front end to an access application.
May 20, 2013 at 1:44 pm
I guess I've been lucky. I've been using Access Since 1997 and have yet to find a request for data that I couldn't handle with Access's query builder.
May 20, 2013 at 1:50 pm
There is so very much more that SQL Server can do for you. I use the SQL Books Online, that is the very Best in my opinion. It uses the same databases that come with SQL Server, or you can download them as well.
That is the thing we are all trying to tell you. SQL Server will not work the same as what you are used to in Access. But the end users will not see a difference. That is what the different layers are all about. Just because you change some things in the database, the end users will not ever know that. For example, you could place every row into its own table, and the front-end would never know that. They cannot see the tables.
Andrew SQLDBA
May 20, 2013 at 2:03 pm
Your right I could redesign some of the backend structure but then I might give the DBA a nightmare of a redesign with regards to the forms, queries and reports in Access. also, getting the existing data to fit neatly into a redesign might also be a challenge. Anyways, I will discuss this with her before I begin building anything for production.
Also I'll check out the recommended learning material.
May 22, 2013 at 3:33 am
while accessing tables from two different databases u can use in ur queries databasename.tablename to qualify. As the servers are linked u can also use tables from other servers by qualifying it with server name and tables name
May 22, 2013 at 6:42 am
SSC Rookie,
Does that hold true if I'm accessing the SQL server from a .net application? I would think that I'd need to specify a connection string for each server and then only be able to query one server at a time. Maybe I'm missing something.
Also, I'm starting to learn to develop .net applications to run in sharepoint 2010. I plan to leverage Sharepoint's SQL Server to hold my DBs. This way everything will be in the same place. I can then replace the tables in Access DB to point to SQL server and everyone will be able to work with the same data. It won't matter if they access it from within Sharepoint or from Access.
Mike
May 22, 2013 at 3:58 pm
Mike
Guess where Sharepoint stores the data? You guessed it, SQL Server. You do not need Sharepoint in the middle of things. Use the same database in SQL Server that, if you want, Sharepoint is using. Sharepoint uses SQL Server Database, It is not intertwined in Sharepoint. Look at the connection string in a SP front-end, guess what, it is pointing at a SQL Server Database. How would you plan on maintaining a Sharepoint database? Guess what, in SQL Server.
I do not know how else to explain this to you. Use SQL Server for your database. Stop making things more complicated. You will end up with a huge maintenance headache.
Create yourself a database in SQL Server, add the tables from all the Access Databases. Pump all the data over from Access to SQL Server. As long as there are not two table with the same name, you are fine. If there happens to be, look at the data in the tables. If there are similar columns, add all that data to the same table.
Use Stored Procedures for ALL your queries. In the sproc, if needed, query the data from another database.
Andrew SQLDBA
May 23, 2013 at 6:57 am
Andrew,
That is what I'm planning to do. I'm moving my data from access to the SQL server used by sharepoint. I'm then going to build my web applications using vb.net and run them on the Sharepoint server. That way they will have a url which makes sense to everyone. After I finish moving the data, I'll remove the tables in access and point the db to SQL server.
As far as storing the queries for my web pages as stored procedures, why would I want to do that? An application could have 30 or more queries. I thought the idea behind stored procedures was for handling events such as "before update" and "after update". Correct me if I am wrong. Again I have something new to learn.
On to a developer question that you may not know. I've been researching biulding .net applications and have come to a good understanding. I have a strong background in clasic ASP. I can query a database (access & Sql Server) and write to a them using .net pages. What I don't know is if in visual studio I need to generate sharepoint projects or if vb.net projects will do just fine. I'm leaning towards sharepoint projects because of the ability to deploy an application, but I'm not creating web parts. My first project is to take a 12 page paper form that gets mailed to users and generate a web form that matches the paper form. It almost seems like I could build a stand alone web application and run it from within sharepoints _layouts folder. Do you know about this? Also Do I need a SP project in order to be able to access sharepoint and get the currently logged in user?
Earlier this year, I tried using Access services to achieve this and nearly tore my hair out because I'm a code guy and access services is not code based. Also, performance was a nightmare. We really turned a lot of people off to the idea of using the web. I don't want to repeat that experience. Luckily I have the rest of the year to develop and test a new solution.
May 23, 2013 at 12:05 pm
Mike
I am not sure where you got your information about stored procedures (sprocs). They are stored queries, and can do so many things. You can code a trigger to perform an after and before update. Sprocs are used to insert, update, delete and select data, plus so much more. How would you code a query in the front-end to insert a new row into multiple tables that are related? And if anything happened that one table was locked, how would you roll it all back? With a single sproc, you can easily do that and return an error message to the front-end to trigger a method. Sprocs are very secure, and make your front-end app very secure by adding another layer between the front-end and the back-end. You setup a SQL user, and grant that use absolutely no permissions. You only give that user Execute permissions to the srocs needed for the front-end. I have coded front-end web apps that use well over 1000 sprocs. You can completely change the query without ever having to touch the front-end to accept the new data. You have one place to keep all queries. If you simply have to add one column to one table, how would you go about knowing what pages you would have to change on the front-end? In SQL Server, use sp_depends. It will tell you every object that uses that table. You would then script out those sprocs, perform a find and replace and then alter all the sprocs, or you could drop and create them all again, with one simple query. Then in another query grant the execute permissions back to the SQL User. Very simple to do. I can do things like that in minutes where it would take you possibly days to change all that front-end code.
I do not use Share Point, and for a good reason. I simply use a web app running on a web server. Simple. I do not code my OOP using VB, I use C#. Almost the same thing really. I have never had an issue deploying(copying) all the pages to a webserver. As long as they run on my server at home, they will run on the web server. I have heard stories about people trying to code things for SP, and it never working as planned. A simple web app with a Database back-end running on a webserver, always works. And it is very simple to install and configure IIS. So much easier than SP. SP was designed for folks to share documents, pages, files, etc... that have no programming experience. For that lack of experience, you pay a price. I have created many .NET web apps, and never once used SP. I really do not feel that adding another server in the middle will do anything but cause issues.
As for you form, I would break that down into multiple pages, store the data in a table, which would allow the user to walk away and finish the form later if needed. Once the user has completed all the required fields and you have verified all the data, then insert the new row into all the different tables. Never, ever insert a new rows from page to page. What would happen if the user never completes all the form fields? You would have an incomplete record. Notice that I use the term "row" and use the term "record" differently. A single row in a table does not make up a complete record in a database.
Andrew SQLDBA
May 23, 2013 at 12:47 pm
OK, So I was wrong about stored Procedures. They sound very much like the thing to use. How though do I code them if queries require parameters from the front end such as a value from a drop down box? I’m used to writing SQL as a concatenated string and interjecting parameters into the string on the fly. I’ll have to research this. Maybe there’s a way to send the parameter values when calling the procedure.
As far as using SharePoint, I’d rather not. But the server that the company uses for the intranet is a SharePoint site. That said, users will log into SharePoint to get access to my app. Once they get there I want to authenticate them based upon how they logged into SharePoint. At present I’m thinking of using a page on the sp site which invokes some JavaScript to read the innerHTML of the user menu to retreive the username and pass the username to my app. From there it’s straight .net.
As I understand it, I can load .net applications in the _layouts folder of a sharepoint site and run it as a stand alone application. I believe I have to do someting in central admin to tell sharepoint not to manage my code and have IIS handle it. I will not know for sure till I have a test environment to try this in. At present the SP site is being moved to a new server and the old server becoming my development environment. Till then I'm using my win 7 box.
The form will be 12 interactive pages with navigation buttons (next and Previous) on the bottom . There will also be a save button on each page that will save each page as the users fill them out. This way multiple users can complete the data request and the form will always open displaying any saved data. Of course I will validate data before it is saved.
May 23, 2013 at 6:55 pm
Mike
Yes, they are. And everyone should use them. Sprocs can and do accept input parameters and can return data using output parameters. For the ID value form a DropDownList, you simply place that value into a variable. Then call the method that actually does the insert, meaning, the method that executes the sproc, and there you place the value that came into the method, into the input parameter. If that makes any sense. I know C#, I would never use VB. I think that C# is cleaner and is more robust.
protected void InsertContactUsData(int titleID)
{
SqlCommand SQLInsertCommand;
SqlConnection SQLDBConn = new SqlConnection(SQLDBConnectionString());
Int32 rowsAffected;
try
{
SQLDBConn.Open();
SQLInsertCommand = new SqlCommand("INS_PersonTitle", SQLDBConn);
SQLInsertCommand.CommandType = CommandType.StoredProcedure;
SqlParameter TitleID = SQLInsertCommand.Parameters.Add("@TitleID", SqlDbType.Int);
InsertSuccess.Direction = ParameterDirection.Input;
SQLInsertCommand.Parameters["@TitleID"].Value = titleID;
rowsAffected = SQLInsertCommand.ExecuteNonQuery();
SQLDBConn.Close();
}
catch (System.Data.SqlClient.SqlException ex)
{
string msg = "Fetch Error:";
msg += ex.Message;
throw new Exception(msg);
}
finally
{
if (SQLDBConn != null)
{
SQLDBConn.Close();
}
}
That is the way that I take a value from a DropDownList and insert into a table using a sproc from an ASP.NET page. Of course this is a method in the code behind page. There is a front-end page that actually shows the DropDownList. And another method that actually fills the DropDowList with values and text from the PersonTitle table.
Have no idea about Sharepoint. Never used it, and will never use it.
If you have a "Next", "Previous" and "Save" button. What will happen if the use only select the "Next" button. I would code that on the page unload to write the data to a table. Not to the final table, just a short term storage table. I would not have a "Save" button on any page. Force the user to give you want is expected, and do not let them leave that page until they have. Once the form is satisfied, then they are able to proceed to the next page.
Andrew SQLDBA
May 24, 2013 at 6:54 am
The problem with not having a save button on each page is that of the 12 pages multiple people may fill out the information. For Instance:
Person 1 - fills pages 1 & 2
Person 2 - fills pages 3 - 8
Person 3 - fills pages 9 - 12
Also users want the ability to be able to save their progress so they can pause and continue later. That is why I can't force users to complete the entire 12 pages before saving the info. I need to capture what they provide. Basicly I need to mirror the paper form as is possible. It's better to capture partial pages than not at all because one field would be left blank. I know this is far from best practice, but it is what the folks in the other department want.
To answer the question of next without saving, I'm going to perform the save function on button press before deciding whether or not to move to another page. The only difference is whether the users stay on the same page or move to another.
Oh, and in regards to my using VB vs C#, I do it because it is what I know. I learned VB in college and have been using it ever since. Why learn another language if I don't have to?
I will definatly look into stored procedures and they seem like the logical way to code applications.
Viewing 12 posts - 16 through 26 (of 26 total)
You must be logged in to reply to this topic. Login to reply