This will be very difficult to explain, however.. I will do the best I can to ellaborate on the subject if anyone is willing to help. Right now I am working on a Final Project for my degree in CS/Programming. We are working on a Survey Software Disto that the teachers can use to create surveys, add questions, pull statistics and have future scalability. Right now the features for scalability are: 1. Create and add your own questions. 2. Build your own survey. As small as those 2 ideas seem, it is not easy and as far as the data model goes for the database, well we're still working on it..we're in the 3rd week now. Anyways, becuase of the skill set of our class/team we decided to use Visual Basic and Access. My first thoughts were Java and Oracle, but unfortunetly the skill set for some students just wasn't there. So please don't bother making any suggestions on Language, Database or anything of that nature, just help to the topic. :) Next point. Yesterday I was thinking that when you pull a query in Visual Basic you have a block of code for each button you create. So ex: Student Search Button -> Code( Grab all students, from database ) -> ect...(retrieve) So with this said it will invlove n amount of blocks of code for n buttons. Like so: 1. Student Average All Search Button -> Code-> ect...(retrieve) 2. Student Survey of a Rank(3) Search Button -> Code-> ect...(retrieve) 3. Students Survey of a Rank(5) Search Button -> Code -> ect...(retrieve) 4. Student Survey of a Rank(1) -> Code -> ect...(retrieve) So with 4 buttons it involves 4 different blocks of code, me thinking in a much simpler matter thought why not do this... Have one block of code that will retrieve a .sql and run the sql statement so that all buttons use the same code but retrieve a different .sql block. So for ex: Student Average All Search Button -> Block_A_Code ( calls average.sql ) Student Survey of a Rank(3) Search Button -> Block_A_Code ( calls Rank_3.sql ) etc.. They all use the same code but grab a different .sql script and run it. Do you see this as being possible and managable? If you need me to clarify I can even make some models of my idea. This is mostly learning for myself, most likely we will only have 3 - 5 different survey stats. However say we wanted to update or changes a statistic and have to alter the SQL? I would have to go in the code and change that as well, but with my idea you simply change the .sql script and its done since the Block_A_Code is reusable. Also, say he wants to add different types of Stats in the future? He hires us again as his developers and all we have to do is add new buttons, and new SQL scripts very little code. Hope that is enough info for you if now, I can give more, thanks to any one who can help or has seen this done before.

Someone has to have an idea right?

I honestly can't see the problem with that. Ultimately it's your call. If you expect the program to change frequently then there would be no problem with the SQL scripts idea.

Yes this is feasible. I think you are discovering for yourself features like identifying common functionality, making things generic, reusability, late-binding (getting the SQL script at the last moment at runtime, not at VB compile), data driven programming (almost treating the SQL script as data not code, placing questions in the database rather than hardcoding them), etc.

Quite often VB programmers will have generic functions which populate grid controls with the result set from SQL query, e.g.:
PopulateGrid( ctrlGrid, strSQLQuery )

For your type of application, the less you hardcode, and the more generic you make the application, the more flexible and future-proof it will be.

You may also want to think about parameterised queries, i.e. you pass the parameter 3, 5, 1 into the same query rather than having separate queries for rank(3), rank(5), rank (1) etc.

p.s. buttons for each query isn't the most scale-able and future proof of UI designs, how about a drop down list box containing all queries with a 'go' button next to it?

Good luck with the project, it sounds like a good one.

Yeah, I'll update you guys and let you see the final documentation. All the datamodels, diagrams, users manual's stuff like that. We built in a way so that the Admin or Moderator can add questions to surveys, then go back and choose there questions however the stats are always correct because the rank(1 - 5) is always the same, if we do the math with the same amount of questions.

Anyways, it has a lot of built in features but also plenty of room for scalability. 5 weeks for the software I think we're doing a hell of a job, got out of class yesterday at around 11:30 programming the login and class choice so that the students cannot take the same survey for the same class.

Anyway, i'll keep you guys updated and if you see the any pseudo code for that algorithm let me know!!

Thanks

Well the code is Completely seperate from actual SQL. the code simply grabs the .sql and runs the query. The code is completely re-usable, allowing ANY SQL to be ran in it to obtain the required results.

All the button does is passes a value to say which .sql script to grab. Instead of having 10 blocks of unique code for 10 differnt command buttons. Yes it works, but it's long and ugly. Opening and closing all the time with differnt code.

If you have one block that opens and closes and retrieves the data it is so much cleaner.

-----------------------------------------------------------------------------

Basically I want to run a query for all the students who have taken the survey.
It retrieves there Student_ID and the Survey's they took.

I also have a query that runs all the Instructors that are in the database.

That right there would have 2 differnt cmdButtons: one named cmdStudentSurvey and one named cmdIntstructor.

When I click cmdStudentSurvey I pass a variable to the global function that runs any SQ script. So I set global variable ( in cmdStudentSurvey ). So ValueSQL has the value of 1. My function GetSQLANDRUN ( horrible names but lets move on ) will grab the ValueSQL and say oh your = 1, well then I'm going to grab the StudentSurvey.SQL and run your query. All the SQL scripts can be stored in one folder and ready for change if necessary.

Mind any mistakes, last night was the first night I coded in VB for about 5-6 months
--------------------------------CODE------------------------------------------
'Sorry I forgot how to show code on these forums
Option Explicit

public ValueSQL As Integer

---------------------------------------------------------------

Private cmdStudentSurvey_Click()
ValueSQL = 1
End Sub
---------------------------------------------------------------
Private cmdInstructor_Click()
ValueSQL = 2
End Sub

------------------------------SOME PROCEUDRE------------------------------------

' need to mind my get's i haven't coded gets in VB for a while, so there will be mistakes, but basically


Public GetSQLANDRUN_Get(ByValue intValueSQL) ' Something like this
Select Case ValueSQL
Case 1
'Grab (StudentSurvey.sql)
'Right here I could even set my rsRecordset to the value returned.
'Run a block of code that is re-usable ( stored in its own function )
Case 2
'Grab (Instructor.sql)
'Run the SAME block of code that is re-usable ( stored in its own function )
End Select
End Sub

--------------------------------------------------------------------------------
This is merely the pseudo code and I haven't quite nailed the whole thing, but if you think there is a cleaner way please, I am open to constructive critisism, I am learning to Optomize my code. Thanks!

Always beware of switch/select case statements. They sometimes alert you to the fact that you are doing something in a non-oo or non-reusable or non-flexible or non-future-proof way.

Instead of passing in a number to:
GetSQLANDRUN_Get(ByValue intValueSQL)
and determining the filename from the number via a switch/select case statement...

Why not just pass in the filename and remove the switch/select case statement:
GetSQLANDRUN_Get(ByValue strSQLFileName)
GetSQLANDRUN_Get could then run any SQL query file and present the results in some grid (I presume?). You could also pass the grid control in as a parameter, that way the code for this function can be moved outside of the form.

I would scan the directory where the SQL files are kept, and present them in a dropdown list, with a button 'Go' next to the dropdown. That way new query files can be added without having to change the VB code (i.e. no increasing the size of the switch/select case statement necessary since there isn't a switch/select case statement).

The switch I just thru in there, sorta came up with it as I was thinking of a possible way the code could flow.

EDIT: How will the function determine which .SQL file to get if it is not flagged?

The filenames of SQL files are read in from a directory, put in a dropdown list, when an item in the list is selected and the go button pressed, code behind the go button can find out what is the text of the currently selected item in the dropdown, and pass this to the Get function.