Standalone Database and Connection String

[kaiser]

Hello,

I have been programming in Access with VBA and jetSQL for years and I am making the leap to VB.net so I apologize for my ignorance up front.

I have a stand alone database with a table named "tbl_Settings". I am trying to make a connection to this table to run a SQL statement. I have looked around trying to find a good example how to do this with no luck. Below is th code I got thus far:

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

Dim con As New OleDb.OleDbConnection(strConn) 'set connection

Dim cmdSQL As IDbCommand = con.CreateCommand() 'create a new command

Dim strSQL As String = "SELECT Source_Drive FROM tbl_Settings" 'Create query

cmdSQL.CommandText = strSQL 'bind the command to execute the max query

con.Open() 'Open connection

'WHAT SYNTAX IS USED TO EXECUTE THIS QUERY?

con.Close() 'Close connection

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

I need help on the bold area's of this post.

Thank you in advance.

 

[giadich]

if you are getting a single value then use .ExecuteScalar

 

[kaiser]

Thank you for your help.

 

[cjard]

Hello,

I have been programming in Access with VBA and jetSQL for years

OK, the first thing you need to do is dump that knowledge I'm afraid. Data access has moved on.. Click the DW2 link in my signature, then click the .NET 3.0 link (DW2 is 2.0) in the top right grey box to make sure you have the latest advice, then have a read of topic "Creating a simple data application" and go from there...

A simple bit of advice I can give you for starters:

If youre writing ANY sql in a string variable in code (like Dim strSql as String = "SELECT * FROM ...") then youre doing it wrong/old way.. Revise the modern way

 

[giadich]

If youre writing ANY sql in a string variable in code (like Dim strSql as String = "SELECT * FROM ...") then youre doing it wrong/old way.. Revise the modern way

i agree that would be the old way but disagree that it's the wrong way. There are many ways to archieve the same objective. Of course how you do that will have a consequence in performance, security... but say if your objective is pulling some data from db and throw it on the screen then there isn't anything wrong with dynamic sql nor with stored procedure nor view for that matter.

But yes, as technology moves forward, we should adapt to the new, modern way.

 

[cjard]

i agree that would be the old way but disagree that it's the wrong way.

The IDE still writes code like this for you, so saying it's wrong goes merely as far as saying that youre using a tool incorrectly.

There are many ways to archieve the same objective.

Actually, when it all comes down to it, there is only one and there are correct and incorrect applications of that. String concatenation and shoehorning everything into a string representation is the biggest mistake and people can sometimes never understand why it's wrong, even though they can clearly explain why they Dim i as Integer, - why do they put everything into a SQL string, if they understand when to use an integer variable?

Of course how you do that will have a consequence in performance, security...

"SELECT * FROM table WHERE column = " & textbox.Text has NO advantages, at all, ever. Not for performance, nor security, nor anything else. It is absolutely 100% the wrong way to do anything (and i know this discussion isnt about that so much) for reasons given in PQ. Dynamic SQL can be created a far better way that lets the database optimize and precompile the statement, or parameterization can be concatted on, but values should never ever be concatenated, ever.

there isn't anything wrong with dynamic sql nor with stored procedure nor view for that matter.

Dyn sql covered above, sproc are great because the force use of parameterisation anf views are actually just normal SQL selects like any other table, it's just that the database will expand the view and its use and recompile and re-plan the query