Question Connection property has not been initialized

[Systematic]

Please Help am having this error: "ExecuteNonQuery: Connection property has not been initialized"
Imports System.Data.SqlClient
Public Class Form1
Dim cm As New SqlConnection("Data Source=KHQ01WX12F4;Initial Catalog=VEHICLEDATA;User ID=sa;Password=MasaPIMS2008")
Dim cmd As New SqlCommand


Dim dr As SqlDataAdapter
Private Sub SAVE_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SAVE.Click
cm.Open()
cmd.CommandText = "insert into tblMyDetails (DateOpened,ourRef) values ('" & txtName.Text & "','" & txtSurname.Text & "')"
cmd.ExecuteNonQuery()
MessageBox.Show("Record saved Successfully")
cm.Open()
End Sub
End Class

 

[jmcilhinney]

You've created a connection object but nowhere have you told the command to use it.

 

[IanRyder]

Hi,

The reason why you are getting this error is due to the fact that you have not associated the SQLConnection with the SQLCommand, so even though you are opening the SQLConnection the SQLCommand does not know to use that particular connection.

What you need to say is:-

Dim cm As New SqlConnection("Your Connection String")
Dim cmd As New SqlCommand()
 
cmd.CommandText = "insert into tblMyDetails (DateOpened,ourRef) values ('" & txtName.Text & "','" & txtSurname.Text & "')"
cmd.Connection = cm
cm.Open()
cmd.ExecuteNonQuery()
MessageBox.Show("Record saved Successfully")
cm.Close()

Notice a few things here. Firstly, we assign the SQLConnection to the SQLCommand variable and then call Open on the connection before calling ExecuteNonQuery on the command object and finally we Close the connection, not Open it again.

You can also assign the SQL Query and the Connection object using one of the overloads of the SQLCommand class. i.e:-

Dim myConnectionString As New SqlConnection("Data Source=IANVAIO\SQLEXPRESS;Initial Catalog=NORTHWIND;Integrated Security=True")
Dim myInsertCommand As New SqlCommand("Insert Into MyTable (FieldName1, FieldName2) VALUES (Value1, Value2)", myConnectionString)

Just a couple of other points for you:-

1) When positing to a Public Forum I suggest that you do not post sensitive information such as passwords.
2) For ease of reading your own code later, please ensure that you use descriptive names for your variables otherwise this can start to get confusing.
3) Have a read up on adding Parameters to SQLCommand objects rather than using string concatenation.
4) When posting again to the forum, please use the Advanced button to add Code tags to your code for readability.

Hope that helps.

Cheers,

Ian

 

[Systematic]

thanks a lot Ian that have solved my problem and again thanks for the advice