Connection property has not been initialized

[SteveHi]

Hello

In some posts I have read on other forums, those with database connection problems (I am thinking about Access) sometimes get the error: 'Connection property has not been initialized' which might translate as 'you have not kick-started your connection'. One reason for that, according to a number of replies I have come across, is that 'you have not set the connection property of the command object' ('you have not told command how to connect').

So would something like this:

Dim conn As New OleDbConnection
Dim OleDbConnection As New OleDbConnection
Dim cmd As New OleDbCommand

conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|myDatabase.mdb;"

conn.Open()

cmd = New OleDbCommand("SELECT strName FROM school WHERE strName=@strName", conn)

cmd.Parameters.AddWithValue("@strName", UserName.Text)

cmd.Connection = conn

cmd.ExecuteNonQuery()

conn.Close()

a) initialise the connection property and b) set the connection property of the command object?

Many thanks.

[techgnome]

a) yes, b) yes, but since you passed it to the constructor there's no need to set it again.
BUT...

I wouldn't use ExecuteNonQuery in this case. The ExecuteNonQuery is for action non-query queries... Inserts, Updates, Deletes... queries that do not return data. Your query does, so it should use the Execute method and return the data.

Also, look into using the Using clause....

Using conn As New OleDbConnection
    conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|myDatabase.mdb;"
    Using cmd As New OleDbCommand("SELECT strName FROM school WHERE strName=@strName", conn)
        cmd.Parameters.AddWithValue("@strName", UserName.Text)
        conn.open()
        cmd.ExecuteNonQuery() <-- obviously, change this as previously mentioned.
    End Using
End Using

-tg

[dee-u]

@TG: Execute? Shouldn't it be ExecuteReader?

[techgnome]

Probably. I don't use ADO.NET directly much any more - I have to go through our own data access assemblies. And as I think about it, Execute is off the Adaptor isn't it?

-tg

[SteveHi]

Many thanks, TG for showing the Using clause. I will try it.

Is it really correct to say that the code I posted (as you did with 'Using') 'returns data'? Doesn't it just check verify that one email address (the one typed in by the user) corresponds to another email address (the one the user registered with and lives in the database table).

[techgnome]

Of course it returns data ... that's what the SELECT statement does... selects data out of a table and returns it.

-tg

[SteveHi]

I know what you mean - it selects data and uses that data to perform other actions such as, in this case, to send an email. I suppose I meant 'display it'.

[NeedSomeAnswers]

The display of data has nothing to do with selecting it from a database.

What TG was saying was if you are running a query against a database which uses a SELECT clause e.g.

SELECT COUNT(c.email) From Contact c WHERE c.email = 'emailaddress'

Then you would use the ExecuteReader command as , if on the other hand you are carrying out Delete, Update or Insert command then you use ExecuteNonQuery as these operations do not return you a dataset. (although they can return you a return parameter which some use to return a record count or the inserted record id for instance.)