Register Form Issues VB.Net

Question

I am having an issue when I am setting up this Register form. My current code is this:

Public Class Form2
Dim con As New OleDb.OleDbConnection

Dim dbProvider As String
Dim dbSource As String
Dim MyDocumentsFolder As String
Dim TheDatabase As String
Dim FullDatabasePath As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim DBTest1 As String
Dim DBTestP1 As String
Dim cmd As New OleDbCommand(sql, con)
Dim connStr As String

Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim connection As New OleDb.OleDbConnection(connStr)
    dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"

    TheDatabase = "\Robocopy_Test.accdb"
    MyDocumentsFolder = "C:\Users\Dan\Desktop\WindowsApplication2"
    FullDatabasePath = MyDocumentsFolder & TheDatabase

    dbSource = "Data Source = C:\Users\Dan\Desktop\WindowsApplication2\Robocopy_Testaccdb1.accdb"

    con.ConnectionString = dbProvider & dbSource

    con.Open()
    sql = "SELECT * FROM Robocopy"
    da = New OleDb.OleDbDataAdapter(sql, con)
    'da.Fill(ds, "Robocopy")
    MessageBox.Show("Databse is Open")
    DBTest1 = DBTest.Text
    DBTestP1 = DBTestP.Text
    'DBTest.Text = ds.Tables("Robocopy").Rows(0).Item(1)
    'DBTestP.Text = ds.Tables("Robocopy").Rows(0).Item(2
    sql = "INSERT INTO Robocopy(username,password) VALUES('" & DBTest1 & "','" & DBTestP1 & "')"
    cmd.Connection = connection
    connection.Open()
    cmd.CommandText = sql
    da.InsertCommand = cmd
    da.InsertCommand.ExecuteNonQuery()
    connection.Close()
    'With cmd.Parameters
    '.AddWithValue("usernamer", DBTest.Text)
    '.AddWithValue("password", DBTestP.Text)
    '.AddWithValue("email", txtsub.text)
    '.AddWithValue("contactnum", txtau.text)
    'End With
    'cmd.ExecuteNonQuery()
End Sub

Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    con.Close()
    MessageBox.Show("Database Is now Closed")
End Sub

End Class

I am having the issue at connection.open(). The error that I am having is The ConnectionString property has not been initialized. I have been trying for the past hour to find different ways to write to the database but to no prevail and I cannot figure this out.

[In response to Steve My code after editing and still the same error

Imports System.Data.OleDb

Public Class Form2
Dim connection As New OleDb.OleDbConnection

Dim dbProvider As String
Dim dbSource As String
Dim MyDocumentsFolder As String
Dim TheDatabase As String
Dim FullDatabasePath As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim DBTest1 As String
Dim DBTestP1 As String
Dim cmd As New OleDbCommand(sql, connection)
Dim connStr As String

Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    Dim connection As New OleDb.OleDbConnection(connStr)
    dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"

    TheDatabase = "\Robocopy_Test.accdb"
    MyDocumentsFolder = "C:\Users\Dan\Desktop\WindowsApplication2"
    FullDatabasePath = MyDocumentsFolder & TheDatabase

    dbSource = "Data Source = C:\Users\Dan\Desktop\WindowsApplication2\Robocopy_Testaccdb1.accdb"

    Me.connection.ConnectionString = dbProvider & dbSource

    Me.connection.Open()
    sql = "SELECT * FROM Robocopy"
    da = New OleDb.OleDbDataAdapter(sql, connection)
    'da.Fill(ds, "Robocopy")
    MessageBox.Show("Databse is Open")
    DBTest1 = DBTest.Text
    DBTestP1 = DBTestP.Text
    'DBTest.Text = ds.Tables("Robocopy").Rows(0).Item(1)
    'DBTestP.Text = ds.Tables("Robocopy").Rows(0).Item(2
    sql = "INSERT INTO Robocopy(username,password) VALUES('" & DBTest1 & "','" & DBTestP1 & "')"
    cmd.Connection = connection
    connection.Open()
    cmd.CommandText = sql
    da.InsertCommand = cmd
    da.InsertCommand.ExecuteNonQuery()
    connection.Close()
    'With cmd.Parameters
    '.AddWithValue("usernamer", DBTest.Text)
    '.AddWithValue("password", DBTestP.Text)
    '.AddWithValue("email", txtsub.text)
    '.AddWithValue("contactnum", txtau.text)
    'End With
    'cmd.ExecuteNonQuery()
End Sub

Public Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
    connection.Close()
    MessageBox.Show("Database Is now Closed")
End Sub
End Class

Why setup the adapter with the con variable and then set a different connection in the command used as insert command of the adapter? Could you try to recreate the adapter with the correct connection before executing the insert? — Steve, Nov 09 '15 at 23:16
@Neolisk the connection.open() line after cmd.connection = connection --- I am currently editing the code to what I think steve means — DaMrZ, Nov 09 '15 at 23:28
@Steve I have edited the code to what I think you mean Although I am still receiving the same error — DaMrZ, Nov 09 '15 at 23:31
The remove everything not relevant to `connection.open()` - should help understand your issue. — Victor Zakharov, Nov 09 '15 at 23:39

Steve · Answer 1 · 2015-11-10T08:16:20.480

Global variables could be very ....evil. Expecially if you name them with the same name of a local variable.

Me.connection is not the same variable connection declared as local variable inside the sub. You set the connection string on the global variable then use the local variable without any connection string

Change these two lines

Me.connection.ConnectionString = dbProvider & dbSource
Me.connection.Open()

removing the Me.

connection.ConnectionString = dbProvider & dbSource
connection.Open()

and don't open the connection two times.

In any case, you don't need the adapter at all to execute an insert command

Public Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    dbProvider = "Provider=Microsoft.ACE.OLEDB.12.0;"
    dbSource = "Data Source = C:\Users\Dan\Desktop\WindowsApplication2\Robocopy_Testaccdb1.accdb"
    Dim connStr = dbProvider & dbSource    

    DBTest1 = DBTest.Text
    DBTestP1 = DBTestP.Text
    sql = "INSERT INTO Robocopy(username,[password]) VALUES('" & DBTest1 & "','" & DBTestP1 & "')"

    Using connection = New OleDb.OleDbConnection(connStr)
    Using cmd = new OleDb.OleDbCommand(sql, connection )    
       connection.Open()
       cmd.ExecuteNonQuery()

       'With cmd.Parameters
          '.AddWithValue("usernamer", DBTest.Text)
          '.AddWithValue("password", DBTestP.Text)
          '.AddWithValue("email", txtsub.text)
          '.AddWithValue("contactnum", txtau.text)
       'End With
       'cmd.ExecuteNonQuery()
    End Using
    End Using
End Sub

I see also that you have commented out the Parameterized approach to your query. Please do yourself a favour and restore as soon as possible the parameters logic. It is a lot more safe and avoids numerous errors

Finally Password is a reserved keyword in Access.Use square brakets around it otherwise you will see an unexplicable "Syntax Error" in your insert command

Thank you for your reply it looks brilliant so far. I am having an isue once I have imported your code--- On the line Using cmd = new OleDb.OleDbCommand(sql, connStr) connStr has the error Value of type 'String' cannot be converted to 'OleDbConnection'. Thanks for your help in advance — DaMrZ, Nov 10 '15 at 00:10
Thank you for the fix. In regards to the insert, with the password is it possible to add encryption? or hide its text somehow? — DaMrZ, Nov 10 '15 at 11:51
ConnectionString information should be stored in the app.config file and the classes that manage this file have methods for encryption http://www.codeproject.com/Articles/20398/Encrypt-and-Decrypt-ConnectionString-in-app-config (sorry still C# but I hope you get the idea and what to search for) — Steve, Nov 10 '15 at 12:05
While for protecting the user passwords stored in a database you should search hashing passwords http://stackoverflow.com/questions/2138429/hash-and-salt-passwords-in-c-sharp — Steve, Nov 10 '15 at 12:07
Thank you very much for the resources that you have supplied me with. I will begin to look into this. — DaMrZ, Nov 10 '15 at 12:11

Register Form Issues VB.Net

1 Answers1