Hello everyone!
I am posting this code for anyone who may be having issues with connecting to a database. Feel free to use this code as you wish.
This will be using the OLEDB library.
'Imports
Imports System.Data.OleDb
Public Class Form1
'Declarations
Dim con As OleDbConnection
Dim cmd As OleDbCommand
Dim sqls As String
Dim sqlcmd As String
Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
'In form_load event, but can be used on button click.
'For hard coded connection string
sqls = "Provider=SQLOLEDB;Datasource=YOURSERVERNAMEHERE;" & _
"Initial Catalog=YOURDATABASENAMEHERE;Integrated Security=SSPI"
'For dynamic connection string
sqls = "Provider=SQLOLEDB;Datasource=" & txtServerName.Text & ";Initial Catalog=" & _
txtDatabaseName.Text & ";Integrated Security=SSPI"
'Place all connection code in try/catch blocks.
Try
'Connect using the string we have just created
If con.State <> ConnectionState.Open Then
con.ConnectionString = sqls
con.Open()
End If
'Alternative way is:
If con.State <> ConnectionState.Open Then
con = New OleDbConnection(sqls)
con.Open()
End If
'Selecting everything from a database
sqlcmd = "SELECT * FROM tablename"
'Selecting a specific value when you have a reference
sqlcmd = "SELECT * FROM tablename WHERE columnname = referencevalue"
'Non hardcoded method
sqlcmd = "SELECT * FROM tablename WHERE columnname = '" & txtDataToSearchBy.Text & "'"
'Setting the command
cmd.Connection = con
cmd.CommandText = sqlcmd
'Alternative way is:
cmd = New OleDbCommand(sqlcmd, con)
'Querying the database
'Many differny ways.
'Returns number of rows
cmd.ExecuteNonQuery()
'Returns only the first column of first row.
cmd.ExecuteScalar()
'Builds a data reader with the current command.
cmd.ExecuteReader()
'Good practice is to close and dispose of a connection.
'This is more effecient than waiting for the 'garbage collector' to come around and dispose of it for you.
If con.State = ConnectionState.Open Then
con.Close()
con.Dispose()
End If
Catch ex As Exception
'For a descript message, use the ex.message method.
MsgBox(ex.Message)
End Try
End Sub
End ClassHope this helps anyone who may be stuck.
