|
|
|
| Title | Get and set a single database value in VB .NET |
|---|
| Keywords | VB .NET, database, update, get, set |
|---|
| Categories | Database, VB.NET |
|---|
|
|
|
When the program starts, it initializes an OleDbConnection object. Note that this defines its connection string but doesn't open it.
|
|
Private m_Connection As OleDbConnection
' Initialize the connection object. Note that
' this defines but doesn't open the connection.
Private Sub Form1_Load(ByVal sender As System.Object, ByVal _
e As System.EventArgs) Handles MyBase.Load
Dim file_name As String = Application.StartupPath
file_name = file_name.Substring( _
0, file_name.LastIndexOf("\"))
file_name &= "\Books.mdb"
m_Connection = New OleDbConnection( _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & file_name & ";" & _
"Mode=Share Deny None")
End Sub
|
|
|
When the user clicks Get, the program builds an OleDbCommand object to execute an SQL SELECT statement. It opens the connection, executes the statement, and closes the connection. Because this statement returns a single value, the program can use the ExecuteScalar method, which is relatively fast.
|
|
' Get the URL for the book Visual Basic .NET
' Database Programming.
Private Sub btnGet_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnGet.Click
' Make a command to get the data.
Dim db_command As New OleDbCommand( _
"SELECT URL FROM Books " & _
"WHERE Title='Visual Basic .NET Database " & _
"Programming'", _
m_Connection)
' Open the connection, get the data,
' and close the connection.
m_Connection.Open()
txtURL.Text = db_command.ExecuteScalar()
m_Connection.Close()
End Sub
|
|
|
When the user modifies the value in the text box and clicks Set, the program builds an OleDbCommand object to execute an SQL UPDATE statement. It opens the connection, calls the command's ExecuteNonQuery method, and closes the connection. ExecuteNonQuery returns the number of records modified which, in this case, should be one.
|
|
' Update the URL for the book Visual Basic .NET
' Database Programming.
Private Sub btnSet_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles btnSet.Click
' Make a command to set the data.
Dim db_command As New OleDbCommand( _
"UPDATE Books SET URL='" & _
txtURL.Text.Trim & "' " & _
"WHERE Title='Visual Basic .NET Database " & _
"Programming'", _
m_Connection)
' Open the connection, update the data,
' and close the connection.
m_Connection.Open()
If db_command.ExecuteNonQuery() <> 1 Then
MsgBox("Update failed")
Else
MsgBox("Ok")
End If
m_Connection.Close()
End Sub
|
| |
|
| |
|
|
