Question Database command Help please

Vegito_ZA

Member
Joined
Mar 4, 2016
Messages
8
Programming Experience
Beginner
Hi,

I am currently in the process of teaching myself VB.net. My first challenge was to write as program that stores order info. The app must use a centralised database and me able to run from several terminals at once. So I thought I would skip the wizard and use manual commands to connect to the database, this way I could incorporate a settings menu that could enable the user to select the path to the database and work the path into some code. Sadly I have been blackballed and I am stuck. I can only find limited info on manual database connection commands and tried using SQL commands. I would appreciated any help on what I am doing wrong and please keep in mind that I am very very new to VB.

Here is the section of my code relating to my problems:
Please Note: All variables were declared.

VB.NET:
[COLOR=#000000][FONT=Consolas]Dim sqlConnection1 AsNew System.Data.SqlClient.SqlConnection("Provider=Microsoft.ACE.OLEDB.12.0;DataSource=|DataDirectory|\My Project\Special Order Database.accdb")[/FONT]

[FONT=Consolas]Dim cmd As New System.Data.SqlClient.SqlCommand[/FONT]
[FONT=Consolas]        cmd.CommandType = System.Data.CommandType.Text[/FONT]
[FONT=Consolas]Dim order_ref As Integer = 1000[/FONT]
[FONT=Consolas]Dim columns As String = "Order Reference,Customer Name, Contact Number, Part Requested, Supplier, Date Ordered, DepositAmount, Deposit Method, Part Ordered, Order Status, Order Notes"[/FONT]
[FONT=Consolas]Dim values As String = order_ref + "," + customer_name + ", " +contact_number + ", " + item_requested + "," + supplier + ", " +order_date + ", " + deposit + "," + deposit_method + ", " +part_ordered_yet + ", " + order_status + "," + order_notes[/FONT]
[FONT=Consolas]        cmd.CommandText = "INSERT INTO Special Order (" + columns + ")VALUES (" + values + ");"[/FONT]
[FONT=Consolas]        cmd.Connection = sqlConnection1[/FONT]

[FONT=Consolas]        sqlConnection1.Open()[/FONT]
[FONT=Consolas]        cmd.ExecuteNonQuery()[/FONT]
[FONT=Consolas]        sqlConnection1.Close()[/FONT]
 [/COLOR]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,397
Location
Sydney, Australia
Programming Experience
10+
If you want the user to be able to select the database location then don't hard-code it. You need to store the location external to the application and read it at run time. The logical option is to use application settings. Open the Settings page of the project properties and add a setting there of type String named something like DatabaseFilePath. You can then build your connection string at run time like so:
Dim builder As New OleDbConnectionStringBuilder With {.Provider = "Microsoft.ACE.OLEDB.12.0",
                                                      .DataSource = My.Settings.DatabaseFilePath}

Using connection As New OleDbConnection(builder.ConnectionString)
    'Use connection here.
End Using
You can allow the user to edit the path like this:
Using dialogue As New OpenFileDialog With {.FileName = My.Settings.DatabaseFilePath}
    If dialogue.ShowDialog() = DialogResult.OK Then
        My.Settings.DatabaseFilePath = dialogue.FileName
    End If
End Using
Note that the setting will have User scope, which means that each individual Windows user will have their own value. You can use Application scope but then the setting is read-only in code.

Also, that's a bad way to write SQL code for a number of reasons. Firstly, string concatenation makes it harder to read and thus more error-prone. Secondly, using string concatenation to insert values into SQL code introduces all sorts of issues, the most serious of which could allow a malicious user to delete everything in your database. You should use XML literals to write SQL code and you should use parameters to insert values. That would look something like this:
Dim sql = <sql>
              INSERT INTO [Special Order]
              (
                  [Order Reference],
                  [Customer Name]
              )
              VALUES
              (
                  @OrderReference,
                  @CustomerName
              )
          </sql>

Using connection As New OleDbConnection(builder.ConnectionString),
      command As New OleDbCommand(sql.Value, connection)
    With command.Parameters
        .AddWithValue("@OrderReference", order_ref)
        .AddWithValue("@CustomerName", customer_name)
    End With

    '...
End Using
The SQL is eminently readable and the parameters reduce the possibility of syntax errors and avoid SQL injection altogether. Note also that I have wrapped the table and column names in brackets. That is required because you have included spaces in the names. That is just a bad idea and should be avoided to begin with but if you are going to insist on such a bad naming convention, you must use brackets in your SQL.

For more information on why and how to use parameters, follow the Blog link in my signature below and check out my post on Parameters In ADO.NET.
 

Vegito_ZA

Member
Joined
Mar 4, 2016
Messages
8
Programming Experience
Beginner
Firstly, thank you very much for the positive criticism rather than negative comments. Most of the tutorials I read didn't explain why things were done a certain way and every example was done totally different to the previous one. I appreciate your explanation ALOT. I realise now that this project might have been a bit ambitious for a beginner, but now that I have started I want to see it through. As for the bad naming, the reason: I planned to use a grid view to recall the stored data and I wanted the headers to be properly spaced, but after your comment, I will be exploring a different way to display the data as it makes perfect sense. Thanks Again
 
Last edited:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,397
Location
Sydney, Australia
Programming Experience
10+
As for the bad naming, the reason: I planned to use a grid view to recall the stored data and I wanted the headers to be properly spaced, but after your comment, I will be exploring a different way to display the data as it makes perfect sense.

What you're talking about is a presentation issue and should be handled in the presentation code. The database shouldn't have to care about presentation issues.
 

Vegito_ZA

Member
Joined
Mar 4, 2016
Messages
8
Programming Experience
Beginner
As I said, I am very new to VB. I did however download some stuff from your blog that I found very informative and it will go a long way to helping me understand VB better. Thanks.
 
Top Bottom