ExecuteNonQuery: Connection property has not been initialized.

[Morn]

I am trying to do the simplest thing in VB.NET 2005.

I am tying to connect to a database and make some values write into a table (the code is below)

Every time I run the application I get the error 'ExecuteNonQuery: Connection property has not been initialized.' from the command.ExecuteNonQuery() command.

If i don’t put it in, there is no error but no data is passed to the database, if I do then it fails and doesn’t run.

Any Ideas would be grateful



Please keep it simple, first time I have ever needed to play with programing so you could say that I a newbie.

Private Sub newcusnextButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _
Handles newcusnextButton.Click
 
Dim title As String = titleList.Text
Dim firstname As String = firstnameText.Text
'Dim surname As String = surnameText.Text
'Dim houseNo As String = housenoText.Text
'Dim address1 As String = address1Text.Text
'Dim address2 As String = address2Text.Text
'Dim county As String = countyComboBox.Text
'Dim postcode As String = postcode1Text.Text
'Dim DofBday As Integer = dayList.Text
'Dim DofBmonth As String = monthList.Text
'Dim DofByear As Integer = yearText.Text
 
Dim connect As New OleDb.OleDbConnection
connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"
 
 
connect.Open()
 
MsgBox("A Connection to the Database is now open")
 
Dim command As New System.Data.OleDb.OleDbCommand
Dim sql
 
 
 
 
sql = ("INSERT into tblCustomer(title, firstname, surname, houseno, address1, address2, county, postcode, DofB)" _
& "Values ('" & title & "' , '" & firstname & "'")
 
 
command.CommandText = sql
command.ExecuteNonQuery()
 
connect.Close()
 
 
MsgBox("The Connection to the Database is now Closed")

 

[JohnH]

You have created a connection. You have created a command. You have not provided any link between them. Command can be constructed and specified connection directly (new oledbcommand(stringsql,conn)), or you can set the .Connection property for the command.

 

[Morn]

Sorry about the posting problem,

I have looked at the links you have given me, and to be honest can not figure out where to use them.

I understand that I need to connect the sql command and the connection together, but based on my coding I just really can not see how to do it.

Could you please illustrate it using my example.

I am truly grateful

Dim connect As New OleDb.OleDbConnection

        connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"
        connect.Open()

        MsgBox("A Connection to the Database is now open")
        Dim sql

        sql = "INSERT into tblCustomer(title, firstname) Values (" & title & " , " & firstname & ""

        Dim command As New System.Data.OleDb.OleDbCommand(sql)

        command.CommandText = sql

        command.ExecuteNonQuery()
        connect.Close()

        MsgBox("The Connection to the Database is now Closed")

 

[vis781]

Dim connect As New OleDb.OleDbConnection
  connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"
   sql = "INSERT into tblCustomer(title, firstname) Values (" & title & " , " & firstname & ")"
 
 Dim command As New System.Data.OleDb.OleDbCommand(sql, connect)
 
              connect.Open()
 
        MsgBox("A Connection to the Database is now open")
        Dim sql
 
 
         command.ExecuteNonQuery()
        connect.Close()
 
        MsgBox("The Connection to the Database is now Closed")

 

[Morn]

I think that I am getting there, the above code caused a new error that told me to edit my INSERT statement so that it was correct.

Once I did this it went back to the old error which this time says:

'No value given for one or more required parameters.'

Here’s the code that I have now:

Dim title As String = titleList.Text
        'Dim firstname As String = firstnameText.Text
        'Dim surname As String = surnameText.Text
        'Dim houseNo As String = housenoText.Text
        'Dim address1 As String = address1Text.Text
        'Dim address2 As String = address2Text.Text
        'Dim county As String = countyComboBox.Text
        'Dim postcode As String = postcode1Text.Text
        'Dim DofBday As Integer = dayList.Text
        'Dim DofBmonth As String = monthList.Text
        'Dim DofByear As Integer = yearText.Text

        Dim connect As New OleDb.OleDbConnection
        connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"

        Dim sql
        sql = "INSERT INTO cust VALUES(" & title & ")"

        connect.Open()

        MsgBox("A Connection to the Database is now open")

        Dim command As New System.Data.OleDb.OleDbCommand(sql, connect)

        command.ExecuteNonQuery()
        connect.Close()

        MsgBox("The Connection to the Database is now Closed")

Help

 

[vis781]

Dim title As String = titleList.Text
    
        Dim connect As New OleDb.OleDbConnection
        connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"

        Dim sql as String = "INSERT INTO cust VALUES (?)"
Dim command As New System.Data.OleDb.OleDbCommand(sql, connect)
 
Command.Parameters.AddWithValue("@Title", title)

             connect.Open()

        MsgBox("A Connection to the Database is now open")

        
        command.ExecuteNonQuery()
        connect.Close()

        MsgBox("The Connection to the Database is now Closed")

Try that. I've used a parameter rather than the string concatenation as you have been using before. It's the preferred way to do the whole SQL thing.

 

[Morn]

Thank you so much, you have saved my life. I now need to edit it but I think i will be ok to move on from here.

Can I ask one last favour on this topic?

Could you possibly annotate the main points in the code so that I may reaserch them.

For instance:

command.Parameters.AddWithValue("@Title", title)

Why the @Title then title again.

Sorry to be cheeky and ask so much.

Also do i just add a new command parameter line for each variable that I want to store in the DB?
Thanks again
Graham

 

[Morn]

OK the last question I think on this as i managed to peice togeather much of this myself.

How can I use this method to so that it ignores my autonumber field in teh data base. Once i have that sorted im all good.

Cheers
Graham

 

[vis781]

If you don't add the column in your SQL statement then access will just generate the number for you.

 

[Morn]

Having followed this through to the T and finally getting the logic behind it I am still stuck on how to make Access insert an auto number into a customerID field.

I have tried to just leave it so that nothing is entered in the sql statement and that fails to work.

If I add an extra ? at the beginning so that there are enough DB fields specified it throws an error saying that there is no data in the parameter to input, if I add the parameter then it throws an error saying that it has no data to input and if I add a variable as a integer and reference it to a parameter I get a error saying that I am trying to create to many entries for the same Primary key.

I you could help me here I would be grateful.

Thanks
Graham

'creates storage variables for the input fields
        Dim title As String = titleList.Text
        Dim firstname As String = firstnameText.Text
        Dim surname As String = surnameText.Text
        Dim houseNo As String = housenoText.Text
        Dim address1 As String = address1Text.Text
        Dim address2 As String = address2Text.Text
        Dim county As String = countyComboBox.Text
        Dim postcode As String = postcode1Text.Text
        Dim DofBday As Integer = dayList.Text
        Dim DofBmonth As String = monthList.Text
        Dim DofByear As Integer = yearText.Text
        Dim telephone As String = telephoneText.Text

        ' Creates the OleDb connection
        Dim connect As New OleDb.OleDbConnection
        connect.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Holiday.mdb"

        'Creates a variable to store the sql ? says look for the value in the parameter
        Dim sql As String = "INSERT INTO tblCustomer VALUES (?,?,?,?,?,?,?,?,?,?,?,?)"

        'Creates a variable command so that the sql and connection variables are run
        Dim command As New System.Data.OleDb.OleDbCommand(sql, connect)

        'insted of using a concat use the param value
        command.Parameters.AddWithValue("@Title", title)
        command.Parameters.AddWithValue("@firstname", firstname)
        command.Parameters.AddWithValue("@surname", surname)
        command.Parameters.AddWithValue("@houseNo", houseNo)
        command.Parameters.AddWithValue("@address1", address1)
        command.Parameters.AddWithValue("@address2", address2)
        command.Parameters.AddWithValue("@county", county)
        command.Parameters.AddWithValue("@postcode", postcode)
        command.Parameters.AddWithValue("@DofBday", DofBday)
        command.Parameters.AddWithValue("@DofBmonth", DofBmonth)
        command.Parameters.AddWithValue("@DofByear", DofByear)
        command.Parameters.AddWithValue("@telephone", telephone)

        'opens connection
        connect.Open()

        MsgBox("A Connection to the Database is now open")

        'runs the command variable
        command.ExecuteNonQuery()

        'closes connection
        connect.Close()

        MsgBox("The Connection to the Database is now Closed")

       

        Me.Close()

 

[vis781]

You don't have to Access will do that automatically as long as you have column's datatype set to AutoNumber. When the insert command executes the data will be inserted into the access table and the value for the Autonumber field will be generated automatically, as the name suggests.

 

[Morn]

Sorry to labour this point, if i do as you suggest then i get this error:

'Number of query values and destination fields are not the same.'

Thus to me that is saying that there needs to be a value to enter into the customerID field even though as you mention it should automatically generate.

The code I am using is exactly as shown above.

Thanks again for your continued help

Graham