How to fix the connectionstring property has not been initialized

Question

I am trying to create a user and insert the data into a MS Access database, but I get an error:

The connectionString property has not been initialized

whenever I click on the button.

I have tried all possible codes on Connection string but the challenge still persist.

Try
    Dim sqlconn As New OleDb.OleDbConnection
    Dim sqlquery As New OleDb.OleDbCommand
    Dim connString As String

    connString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PavilionDB.mdb"
    sqlquery.Connection = sqlconn
    con = New OleDbConnection(conString)

    con.Open()
    Dim cmd As OleDbCommand = New OleDbCommand(sql, con)
    sqlquery.CommandText = "INSERT INTO member(mmbr_id, name, gender, address, phone, join_date, acc_no) VALUES (@txtNewID, @txtName, @txtGender, @txtAddress, @txtPhone, @txtPhone, @)txtAccNo"
    sqlquery.Parameters.AddWithValue("New ID", txtNewID.ToString)
    sqlquery.Parameters.AddWithValue("Name", txtName.ToString)
    sqlquery.Parameters.AddWithValue("Gender", txtGender.ToString)
    sqlquery.Parameters.AddWithValue("Address", txtAddress.ToString)

    Dim da As New OleDbDataAdapter(cmd)
    Dim ds As New DataTable
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "con")
    Return
    sqlquery.ExecuteNonQuery()
    sqlconn.Close()

Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Possible duplicate of [How to fix "The ConnectionString property has not been initialized"](https://stackoverflow.com/questions/1007786/how-to-fix-the-connectionstring-property-has-not-been-initialized). Also look closer at your connection object/s... — Trevor, Apr 08 '19 at 14:27
Why are you creating two different `SqlConnection` objects? Without looking closely, it seems rather likely that you are setting the `ConnectionString` of one and then trying to use the other one. Such mistakes occur when your code is disorganised. — jmcilhinney, Apr 08 '19 at 14:29
You're also creating two `SqlDataAdapter` objects, even though you don't need any. Your code is all over the place. Create the connection, create the command, add the parameters, call `ExecuteNonQuery`. That's it. — jmcilhinney, Apr 08 '19 at 14:31
I doubt this is causing your error, but you have a mistake in your query. The parentheses at the end is in the wrong place. Question: when you run this code with the debugger, what line is the error being thrown on? — Nick, Apr 08 '19 at 14:38
there is no error but when I click on the button, that is when the connection error pops up — khadies, Apr 08 '19 at 14:59

jmcilhinney · Answer 1 · 2019-04-15T23:52:50.867

0

That code really is a mess. It should be reduced to this and that will fix your issue:

Try
    Using connection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PavilionDB.mdb"),
          command As New OleDbCommand("INSERT INTO member (mmbr_id, name, gender, address, phone, join_date, acc_no) VALUES (@mmbr_id, @name, @gender, @address, @phone, @join_date, @acc_no)", connection)
        With command.Parameters
            .AddWithValue("@mmbr_id", txtNewID.Text)
            .AddWithValue("@name", txtName.Text)
            .AddWithValue("@gender", txtGender.Text)
            .AddWithValue("@address", txtAddress.Text)
            'Set other parameters here.
        End With

        connection.Open()
        command.ExecuteNonQuery()
    End Using
Catch ex As Exception
    MessageBox.Show(ex.Message)
End Try

Ideally, you would be using Add rather than AddWithValue too, but that's another conversation.

edited Apr 15 '19 at 23:52

answered Apr 08 '19 at 14:42

jmcilhinney

50,448
5
26
46

thanks for your effot jmcilhinney, but still your code did not work either – khadies Apr 08 '19 at 15:01
i get a return "Syntax error in INSERT INTO statement" – khadies Apr 08 '19 at 15:19
Did you set the rest of the parameters? You should have 7 .AddWithValue lines inside the with. – Mary Apr 08 '19 at 16:28
Paste your query in Sql Server Management System to see if you can get more help on what is wrong with your query. Every table name and field name must exactly match the names in the database. – Mary Apr 08 '19 at 16:44
@khadies In your post, you have connString and conString. Not the same thing. – LarsTech Apr 08 '19 at 16:47
Now I see it! The above code is missing the final ) in the query string. Should be ` @acc_no)",connection)` at the end of the command constructor. – Mary Apr 08 '19 at 16:47
@Mary you see Jim has said to `Set other parameters here.` also note he has the `Using` pattern and doesn't need to explicitly close or dispose of the connection object. – Jeremy Thompson Apr 16 '19 at 01:36

score 0 · Answer 2 · edited Jun 08 '19 at 10:42

The trouble here is that you have 2 commands in your code, namely cmd and sqlquery. You define sqlquery at the beginning of the code, then initialize cmd with the connection and a SQL variable that I cannot determine the source of.

What you need to do is redo this part and get remove the extra references that seem to be the source of the problem. Consider the following...

Try
    Dim conString As String
    Dim text = "INSERT INTO member(mmbr_id, name, gender, address, phone, join_date, acc_no) VALUES (@txtNewID, @txtName, @txtGender, @txtAddress, @txtPhone, @txtPhone, @)txtAccNo"

    conString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\PavilionDB.mdb"
    Using sqlCon = New OleDbConnection(conString), sqlCmd = New OleDbCommand(text, sqlCon)
        With sqlCmd.Parameters
            .AddWithValue("New ID", 234234)
            .AddWithValue("Name", "Fabulous")
            .AddWithValue("Gender", "Male")
            .AddWithValue("Address", "127.0.0.1")
        End With

        sqlCon.Open()
        sqlCmd.ExecuteNonQuery()
    End Using
Catch ex As Exception
    MsgBox(ex.Message)
End Try

I couldn't tell whether txtNewID and the other similarly named variables are text boxes or not. If they are text boxes, you need the .Text property which gets the content in a string. You'll need to replace the literals I used in my test environment to get to it.

Ensure everything, including your connection string and query are correct for this to run smoothly. In your current case, you are getting the connection string related error because you are attempting to execute the query on a connection you didn't associate with the connection.

score 0 · Answer 3 · edited Apr 16 '19 at 01:52

0

Here is simple code for what you mostly asked for, in addition to the whole open source on this link: enter link description here

Here is the tested code:

    Private Sub BtnAddNewRec_Click(sender As Object, e As EventArgs) Handles BtnAddNewRec.Click

    Dim con As New OleDbConnection
    Dim cmd As New OleDbCommand
    Try
        con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\db1.mdb"
        con.Open()

        cmd.Connection = con
        cmd.CommandText = "INSERT INTO Tbl1 (mmbr_id, name, gender, address) VALUES (@txtNewID, @txtName, @txtGender, @txtAddress)"

        cmd.Parameters.AddWithValue("@txtNewID", txtNewID.Text)
        cmd.Parameters.AddWithValue("@txtName", txtName.Text)
        cmd.Parameters.AddWithValue("@txtGender", txtGender.Text)
        cmd.Parameters.AddWithValue("@txtAddress", txtAddress.Text)
        cmd.ExecuteNonQuery()

        MsgBox("Record Added Successfully!")

    Catch ex As Exception
        MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
    Finally
        con.Close()
    End Try

End Sub

I hope this helps:

edited Apr 16 '19 at 01:52

Jeremy Thompson

61,933
36
195
321

answered Apr 16 '19 at 01:22

MAMPRO

69
6

You should use the `Using` pattern, currently you close your connection but dont dispose of it. You have to explicitly dispose all UnManaged Objects, and Connection and Commands are unmanaged. – Jeremy Thompson Apr 16 '19 at 01:32
Hi bro, but what do you mean by "all UnManaged Objects". I just used the "con.Open()" for the "ConnectionString" and then close it at last. If you mean something else, please let me know ^_^. – MAMPRO Apr 16 '19 at 01:43
Goto the Definition of an Object, eg a `OleDbConnection`, you will see in the .Net framework it implements an interface `IDisposable`. Any object that implements this interface must be explicitly disposed of, or you can simply use the `Using` pattern. – Jeremy Thompson Apr 16 '19 at 01:56
Hi again bro, I think that will be manage by default by the .NET garbage collector, but thanks a lot for sharing ^_^. – MAMPRO Apr 16 '19 at 02:20
So you think unmanaged resources are cleared up by the `GC`? Please read up on the topic: https://learn.microsoft.com/en-us/dotnet/standard/garbage-collection/fundamentals **If your managed objects reference unmanaged objects by using their native file handles, you have to explicitly free the unmanaged objects, because the garbage collector tracks memory only on the managed heap.** – Jeremy Thompson Apr 16 '19 at 02:52
sorry bro, but going back to the Definition such as "OleDbConnection", and then where can I see that "IDisposable", cos I think you info has a great value and frankly I don't know it and how to use it. If you have time, please complete your favor and tell me how and I really appreciate your time and effort bro, otherwise ignore it. Thanks a lot bro ^_^ – MAMPRO Apr 16 '19 at 03:10
Right click on the `OleDbConnection` variable and choose "Goto Definition". Because this is inside the .Net Framework you'll only see meta data, but you can inspect to see a object/classes Interfaces and Inheritance. To see this click [full .Net Framework code](https://referencesource.microsoft.com/) and go here: https://referencesource.microsoft.com/#System.Data/System/Data/OleDb/OleDbConnection.cs,2301726da0e82e9c then click on IDbConnection and you will see `public interface IDbConnection : IDisposable {` – Jeremy Thompson Apr 16 '19 at 03:15
Thanks a lot brother, I really appreciate your time and effort. Millions thanks for sharing. ] thanks , thanks [ – MAMPRO Apr 16 '19 at 03:18

How to fix the connectionstring property has not been initialized

3 Answers3