Sign in to follow this  
_jinx_

VB and SQL idea

Recommended Posts

_jinx_    139
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.

Share this post


Link to post
Share on other sites
Sailorstick    142
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.

Share this post


Link to post
Share on other sites
abstractworlds    194
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.

Share this post


Link to post
Share on other sites
_jinx_    139
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

Share this post


Link to post
Share on other sites
Structural    328
Hmmmm... working with an app that retrieves SQL statements through functions and have the logic behind the button execute it, I can say it's horribly backwards.

You goal should be to separate any database-specific stuff from your forms (ie: logic behind the buttons).

How I'd do it... quick and dirty...
Hmmmm... use a function called "getStudentSurveyByRank(rank as long, resultSet() as variant) as long" where the resultset (2D array) is dimmed and filled by the function which queries the database and the funcion returns the number of results found.

Then I'd make a sub "printResult(resultset as variant(), results as long)" that would put the results on screen in whatever format you want.

This all would require me to have two lines of code to retrieve/show the result behind every button.

Ofcourse, I wouldn't REALLY do it like that, as I'd attempt to use some fancy OO stuff in VB and be stuck on some arbitrary problems, but that's where I'd start.

Share this post


Link to post
Share on other sites
_jinx_    139
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!

Share this post


Link to post
Share on other sites
abstractworlds    194
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).

Share this post


Link to post
Share on other sites
_jinx_    139
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?

Share this post


Link to post
Share on other sites
abstractworlds    194
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.

Share this post


Link to post
Share on other sites
_jinx_    139
Do you know any tut's that have reading files specifically .sql files or something related to access;

I guess that will work. Not what I intended but, its another route. I will have to approach my team mates on the GUI of it, but my main concern is the code.

Share this post


Link to post
Share on other sites
abstractworlds    194
I'm guessing that your SQL file is nothing more than a text file with some SQL query text in it (e.g. select * from tablename). You have to load the contents of the file into a VB string and pass the VB string to some Access database query function.

There's plenty of ways of doing all this, depending on what version of VB you are using, and there are many websites offering vb samples and tutorials, just google them, here's one for files and directories:
www.vb-helper.com/index_files_and_directories.html

You will also have to check the Access documentation for what format is required for the SQL query strings you pass to access, sorry I dont know access.

Share this post


Link to post
Share on other sites
_jinx_    139
Well the SQL will be written from the Access db so thats set. I just wasnt sure on how to read in a flat text file from VB since the books I have never really went into it, seems like this really may be possible. Just pass the string and make the

rsRecordset.Open strSQLObtained, etc...

Seems so much easier the only thing is, the rsRecordset will change a lot so I will have to work with that thanks for the link.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this