OleDbConnection Question

[brett]

I have a piece of code that i kinda hobbled together from examples i
found out on the net. I am wondering if i have some extra bits that i
don't necessarily need.

I have the following code:

Public Sub updatelog_table(ByVal [string] As String)
'*************** database **************
Using connection As New OleDbConnection(con)
Try
Dim command As New OleDbCommand("INSERT INTO
log([Note],DateAdded) VALUES ('" & [string] & "','" & Now() & "')")
command.Connection = connection
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Error inserting records into log_Table: " &
ex.Message)
End Try
End Using
'*************** database ************
End Sub

"con" is a string variable defined elsewhere in the program. It
contains provider and data source info.

I am not explicitly using connection.close() anywhere and i am
wondering if the connection.open is required or if the
command.connection = connection bit does the same thing. I though
that by using the "using" statement it would dispose of the connection
when it was finished, but i have no idea.

Can anyone offer a few words on the subject?

Thanks,
Brett

 

[Armin Zingler]

Am 13.08.2010 17:35, schrieb brett:

I have a piece of code that i kinda hobbled together from examples i
found out on the net. I am wondering if i have some extra bits that i
don't necessarily need.

I have the following code:

Public Sub updatelog_table(ByVal [string] As String)
'*************** database **************
Using connection As New OleDbConnection(con)
Try
Dim command As New OleDbCommand("INSERT INTO
log([Note],DateAdded) VALUES ('" & [string] & "','" & Now() & "')")

If you don't want to worry about value formatting insided SQLs anymore,
do yourself a favor and make use of the command's Parameters property:

Dim command As New OleDbCommand("INSERT INTO log([Note],DateAdded) VALUES (?,?)")

'something like this (watch out for programming-in-a-news-reader flaws)

with command.parameters
.addwithvalue("note", [String])
.addwithvalue("dateadded", datetime.now)
end with

command.Connection = connection
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Error inserting records into log_Table: " &
ex.Message)
End Try
End Using
'*************** database ************
End Sub

"con" is a string variable defined elsewhere in the program. It
contains provider and data source info.

I am not explicitly using connection.close()

It's closed at "End Using", which disposes the connection.

anywhere and i am
wondering if the connection.open is required or if the
command.connection = connection bit does the same thing.

Why not try it? connection.open is required. The assignment is only an
assignment. See the documentation of command.ExecuteNonQuery() stating
that you'll get an InvalidOperationException otherwise.

I though
that by using the "using" statement it would dispose of the connection
when it was finished, but i have no idea.

That's right.

Can anyone offer a few words on the subject?

No.

 

[Tom Shelton]

brett wrote on 8/13/2010 :

I have a piece of code that i kinda hobbled together from examples i
found out on the net. I am wondering if i have some extra bits that i
don't necessarily need.

I have the following code:

Public Sub updatelog_table(ByVal [string] As String)
'*************** database **************
Using connection As New OleDbConnection(con)
Try
Dim command As New OleDbCommand("INSERT INTO
log([Note],DateAdded) VALUES ('" & [string] & "','" & Now() & "')")
command.Connection = connection
connection.Open()
command.ExecuteNonQuery()
Catch ex As Exception
MsgBox("Error inserting records into log_Table: " &
ex.Message)
End Try
End Using
'*************** database ************
End Sub

"con" is a string variable defined elsewhere in the program. It
contains provider and data source info.

I am not explicitly using connection.close() anywhere and i am
wondering if the connection.open is required or if the
command.connection = connection bit does the same thing. I though
that by using the "using" statement it would dispose of the connection
when it was finished, but i have no idea.

Can anyone offer a few words on the subject?

Thanks,
Brett

The connection.open is necessary. And the Using will dispose of the
connection automatically, so you do not need to explicitly call close
on the connection object.

 

[brett]

Thanks Guys! That was quick and painless I hope.

Armin,
That trick about the command.parameters makes life a lot easier.
If i were to expand my query to include 3 or 4 items, would i then use
something like

Dim command As New OleDbCommand("INSERT INTO log([Note],DateAdded,
item3, item4) VALUES (?,?,?,?)")

Note the two extra "?"

Thanks,
Brett

 

[brett]

I'm getting a dataType error now when inserting into the database.

Public Sub updatelog_table(ByVal [string] As String)

Could it be because "[string]" is a string value? I looked at the
addwithvalue tool tip and it says the value need to be and object.

I suppose i can take out the "as string" portion to fix this but im
not sure if that is actually needed.

 

[Armin Zingler]

Am 13.08.2010 18:26, schrieb brett:

Thanks Guys! That was quick and painless I hope.

Armin,
That trick about the command.parameters makes life a lot easier.
If i were to expand my query to include 3 or 4 items, would i then use
something like

Dim command As New OleDbCommand("INSERT INTO log([Note],DateAdded,
item3, item4) VALUES (?,?,?,?)")

Note the two extra "?"

Yep, together with two additional .AddWithValue calls.

 

[Armin Zingler]

Am 13.08.2010 19:29, schrieb brett:

I'm getting a dataType error now when inserting into the database.

Public Sub updatelog_table(ByVal [string] As String)

Could it be because "[string]" is a string value? I looked at the
addwithvalue tool tip and it says the value need to be and object.

Everything is derived from System.Object.
System.String also. The type is 'Object' because other types
are allowed, too, like DateTime, Integer, etc. (which are also
derived from Object).

I suppose i can take out the "as string" portion to fix this but im
not sure if that is actually needed.

No, keep it like it is.

What are the data types in the database?

 

[brett]

Am 13.08.2010 19:29, schrieb brett:

I'm getting a dataType error now when inserting into the database.

Public Sub updatelog_table(ByVal [string] As String)

Could it be because "[string]" is a string value?  I looked at the
addwithvalue tool tip and it says the value need to be and object.

Everything is derived from System.Object.
System.String also. The type is 'Object' because other types
are allowed, too, like DateTime, Integer, etc. (which are also
derived from Object).

I suppose i can take out the "as string" portion to fix this but im
not sure if that is actually needed.

No, keep it like it is.

What are the data types in the database?

i have text and date/time

 

[Armin Zingler]

Am 13.08.2010 20:05, schrieb brett:

i have text and date/time

The order of the parameters is important. The first '?' is associated
with the first parameter in the Parameters collection, and so on. If
the order is correct, I don't see the cause.

What's the exact exception message? If you catch the exception,
also look for the exception's InnerException property. If it's
not Nothing, post that message, too.

 

[brett]

I figured out what the problem was.
I found this post: http://forums.asp.net/t/1079456.aspx
and it said that if i change the "datetime.now" to
"datetime.now.tostring" then it will work. Sure enough it did.

Although i am a little confused, what about the data types? My only
guess is that the end value of, lets say for example, the date is the
only thing needed as when its converted to the SQL command, its just a
long string passed to the database, which then reads the string and
converts it back to whatever it needs.

do i have the right thinking or maybe im way off. either way,
tostring appended to the end seems to fix it.

thanks,
brett

 

[Cor]

Brett,

The command executeNonQuerry, the command.DataReader and the
command.ExecuteScalar are very basic commands of AdoNet.

They need the open of the connection, while the TableAdapter, DataAdapter,
Linq to SQL and Linq to EF have that build in.

The dispose method of the connection has an override to the close of the
connection. So probably that gives your confusion. (The close of the
connection has an override of the dispose of the connection which removes
the connectionstring). All done because of the often wrong given information
about the dispose method, that it should release objects, which it does not.

I hope this gives some information which you need.

Cor