I have an already existing program which links well with MSAccess database(.mdb). Question is how do i change the back-end to MS SQL? I have already MS SQL express installed in the desktop and have googled and tried countless ways but to no avail. Perhaps, the problem lies in the difference of coding i used in my existing form. I wish to preserve as much function and coding as possible from my existing coding. Here are the coding i use for linking MSACCESS as data source:
Imports System.Data
Public Class frmStaff
Dim inc As Integer
Dim MAXROWS As Integer
Dim con As New OleDb.OleDbConnection
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim dt As New DataTable
Dim rowIndex As Integer = 0
Private Sub Form5_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = DATABASE.mdb"
con.Open()
sql = "SELECT * FROM Staff"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Staff")
con.Close()
MAXROWS = ds.Tables("Staff").Rows.Count
inc = -1
Dim connStr As String = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = DATABASE.mdb"
Dim sqlStr As String = "SELECT * FROM Staff"
Dim dataAdapater As New OleDb.OleDbDataAdapter(sqlStr, connStr)
dataAdapater.Fill(dt)
dataAdapater.Dispose()
UpdateTextBoxes()
End Sub
Private Sub Recordsatdatabase()
txtStaffID.Text = ds.Tables("Staff").Rows(inc).Item("StaffID")
txtStaffName.Text = ds.Tables("Staff").Rows(inc).Item("StaffName")
cbPosition.Text = ds.Tables("Staff").Rows(inc).Item("StaffPosition")
rbMale.Checked = ds.Tables("Staff").Rows(inc).Item("StaffGenderMale")
rbFemale.Checked = ds.Tables("Staff").Rows(inc).Item("StaffGenderFemale")
txtAddress1.Text = ds.Tables("Staff").Rows(inc).Item("StaffAddress1")
txtAddress2.Text = ds.Tables("Staff").Rows(inc).Item("StaffAddress2")
txtDOB.Text = ds.Tables("Staff").Rows(inc).Item("StaffDOB")
txtContactNo.Text = ds.Tables("Staff").Rows(inc).Item("StaffContactNo")
txtRegistrationDate.Text = ds.Tables("Staff").Rows(inc).Item("StaffRegistrationDate")
txtCountry.Text = ds.Tables("Staff").Rows(inc).Item("StaffCountry")
cbICColor.Text = ds.Tables("Staff").Rows(inc).Item("StaffICColor")
txtICNo.Text = ds.Tables("Staff").Rows(inc).Item("StaffICNo")
txtLabel.Text = CStr(dt.Rows(inc)("StaffPhoto"))
txtAnnualLeave.Text = ds.Tables("Staff").Rows(inc).Item("StaffAnnualLeave")
PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
End Sub
Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
If inc <> MAXROWS - 1 Then
inc = inc + 1
Recordsatdatabase()
Else
MsgBox("You are at the last record!")
End If
End Sub
Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click
If inc > 0 Then
inc = inc - 1
Recordsatdatabase()
Else
MsgBox("You are at the first record!")
End If
End Sub
Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
If inc <> 0 Then
inc = 0
Recordsatdatabase()
Else
MsgBox("You are at the first record already!")
End If
End Sub
Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
If inc <> MAXROWS - 1 Then
inc = MAXROWS - 1
Recordsatdatabase()
Else
MsgBox("You are at the end of the record!")
End If
End Sub
Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
btnConfirm.Enabled = True
btnNew.Enabled = False
btnUpdate.Enabled = False
btnDelete.Enabled = False
txtStaffID.Clear()
txtStaffName.Clear()
cbPosition.Text = "Select"
rbMale.Checked = False
rbFemale.Checked = False
txtAddress1.Clear()
txtAddress2.Clear()
txtContactNo.Clear()
txtCountry.Clear()
cbICColor.Text = "Select"
txtICNo.Text = "Clear"
PictureBox1.Image = Nothing
txtLabel.Text = "00.jpg"
End Sub
Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Staff").Rows(inc).Item("StaffID") = txtStaffID.Text
ds.Tables("Staff").Rows(inc).Item("StaffName") = txtStaffName.Text
ds.Tables("Staff").Rows(inc).Item("StaffPosition") = cbPosition.Text
ds.Tables("Staff").Rows(inc).Item("StaffGenderMale") = rbMale.Checked
ds.Tables("Staff").Rows(inc).Item("StaffGenderFemale") = rbFemale.Checked
ds.Tables("Staff").Rows(inc).Item("StaffAddress1") = txtAddress1.Text
ds.Tables("Staff").Rows(inc).Item("StaffAddress2") = txtAddress2.Text
ds.Tables("Staff").Rows(inc).Item("StaffDOB") = txtDOB.Text
ds.Tables("Staff").Rows(inc).Item("StaffContactNo") = txtContactNo.Text
ds.Tables("Staff").Rows(inc).Item("StaffRegistrationDate") = txtRegistrationDate.Text
ds.Tables("Staff").Rows(inc).Item("StaffPhoto") = txtLabel.Text
ds.Tables("Staff").Rows(inc).Item("StaffCountry") = txtCountry.Text
ds.Tables("Staff").Rows(inc).Item("StaffICColor") = cbICColor.Text
ds.Tables("Staff").Rows(inc).Item("StaffICNo") = txtICNo.Text
ds.Tables("Staff").Rows(inc).Item("StaffAnnualLeave") = txtAnnualLeave.Text
da.Update(ds, "Staff")
MsgBox("Data has been updated")
End Sub
Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
btnConfirm.Enabled = False
btnNew.Enabled = True
btnupdate.Enabled = True
btndelete.Enabled = True
inc = 0
Recordsatdatabase()
End Sub
#Region "Function for checking blank values in textbox"
Sub Check_Textbox()
Dim r As DialogResult
If txtStaffID.Text = "" _
Or txtStaffName.Text = "" _
Or cbPosition.Text = "Select" _
Or txtAddress1.Text = "" _
Or txtContactNo.Text = "" _
Or txtCountry.Text = "" _
Or cbICColor.Text = "Select" _
Or txtAnnualLeave.Text = "" _
Then
r = MessageBox.Show("The system has detected one or more forms are blank. Please try again.", "Warning", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
Call ProductSave()
End If
End Sub
#End Region
Private Sub ProductSave()
Dim cb As New OleDb.OleDbCommandBuilder(da)
Dim dsNewRow As DataRow
dsNewRow = ds.Tables("Staff").NewRow
dsNewRow.Item("StaffID") = txtStaffID.Text
dsNewRow.Item("StaffName") = txtStaffName.Text
dsNewRow.Item("StaffGenderMale") = rbMale.Checked
dsNewRow.Item("StaffGenderFemale") = rbFemale.Checked
dsNewRow.Item("StaffPosition") = cbPosition.Text
dsNewRow.Item("StaffAddress1") = txtAddress1.Text
dsNewRow.Item("StaffAddress2") = txtAddress2.Text
dsNewRow.Item("StaffDOB") = txtDOB.Text
dsNewRow.Item("StaffContactNo") = txtContactNo.Text
dsNewRow.Item("StaffRegistrationDate") = txtRegistrationDate.Text
dsNewRow.Item("StaffCountry") = txtCountry.Text
dsNewRow.Item("StaffICColor") = cbICColor.Text
dsNewRow.Item("StaffICNo") = txtICNo.Text
dsNewRow.Item("StaffPhoto") = txtLabel.Text
dsNewRow.Item("StaffAnnualLeave") = txtAnnualLeave.Text
PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
ds.Tables("Staff").Rows.Add(dsNewRow)
da.Update(ds, "Staff")
MsgBox("The new existing record has been saved in the database.")
btnConfirm.Enabled = False
btnNew.Enabled = True
btnUpdate.Enabled = True
btnDelete.Enabled = True
End Sub
Private Sub btnConfirm_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnConfirm.Click
Check_Textbox()
'If inc <> -1 Then
'End If
Call ModulefrmStaffRefresh.ResetStaff()
End Sub
Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
If MessageBox.Show("Would you like to remove this record?", "Delete", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = DialogResult.No Then
MsgBox("Operation Cancelled")
Exit Sub
Else
Dim cb As New OleDb.OleDbCommandBuilder(da)
ds.Tables("Staff").Rows(inc).Delete()
MAXROWS = MAXROWS - 1
inc = 0
Recordsatdatabase()
da.Update(ds, "Staff")
End If
End Sub
Private Sub btnQuit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnQuit.Click
Me.Close()
frmMainMenu.Show()
End Sub
Sub UpdateTextBoxes()
txtStaffID.Text = CStr(dt.Rows(rowIndex)("StaffID"))
txtStaffName.Text = CStr(dt.Rows(rowIndex)("StaffName"))
cbPosition.Text = CStr(dt.Rows(rowIndex)("StaffPosition"))
rbMale.Checked = CStr(dt.Rows(rowIndex)("StaffGenderMale"))
rbFemale.Checked = CStr(dt.Rows(rowIndex)("StaffGenderFemale"))
txtAddress1.Text = CStr(dt.Rows(rowIndex)("StaffAddress1"))
txtAddress2.Text = CStr(dt.Rows(rowIndex)("StaffAddress2"))
txtDOB.Text = CStr(dt.Rows(rowIndex)("StaffDOB"))
txtContactNo.Text = CStr(dt.Rows(rowIndex)("StaffContactNo"))
txtRegistrationDate.Text = CStr(dt.Rows(rowIndex)("StaffRegistrationDate"))
txtLabel.Text = CStr(dt.Rows(rowIndex)("StaffPhoto"))
txtCountry.Text = CStr(dt.Rows(rowIndex)("StaffCountry"))
cbICColor.Text = CStr(dt.Rows(rowIndex)("StaffICColor"))
txtICNo.Text = CStr(dt.Rows(rowIndex)("StaffICNo"))
PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
txtAnnualLeave.Text = CStr(dt.Rows(rowIndex)("StaffAnnualLeave"))
End Sub
Private Sub btnImage_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnImage.Click
Try
Dim fopen As New OpenFileDialog
fopen.FileName = ""
fopen.Filter = "Image Files (*.jpg)|*.jpg|(*.jpeg)|*.JPEG|(*.gif)|*.gif|(*.png)|*.png|All Files (*.*)|*.*"
fopen.ShowDialog()
PictureBox1.Image = System.Drawing.Bitmap.FromFile(fopen.FileName)
txtLabel.Text = fopen.FileName
Catch ex As Exception
End Try
End Sub
Private Sub rbMale_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbMale.CheckedChanged
If rbMale.Checked = True Then
rbFemale.Checked = False
Else
rbFemale.Checked = False
rbFemale.Checked = False
End If
End Sub
Private Sub rbFemale_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rbFemale.CheckedChanged
If rbFemale.Checked = True Then
rbMale.Checked = False
Else
End If
End Sub
Sub Grid(ByVal sqlStr As String)
Dim dt As New DataTable()
Dim connstr As String = "PROVIDER = Microsoft.Jet.OLEDB.4.0;Data Source = DATABASE.mdb"
Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connstr)
dataAdapter.Fill(dt)
dataAdapter.Dispose()
If dt.Rows.Count <> 0 Then
If txtStaffID.Text = CStr(dt.Rows(0)("StaffID")) Then
txtStaffID.Text = CStr(dt.Rows(0)("StaffID"))
txtStaffName.Text = CStr(dt.Rows(0)("StaffName"))
cbPosition.Text = CStr(dt.Rows(0)("StaffPosition"))
rbMale.Checked = CStr(dt.Rows(0)("StaffGenderMale"))
rbFemale.Checked = CStr(dt.Rows(0)("StaffGenderFemale"))
txtAddress1.Text = CStr(dt.Rows(0)("StaffAddress1"))
txtAddress2.Text = CStr(dt.Rows(0)("StaffAddress2"))
txtDOB.Text = CStr(dt.Rows(0)("StaffDOB"))
txtContactNo.Text = CStr(dt.Rows(0)("StaffContactNo"))
txtRegistrationDate.Text = CStr(dt.Rows(0)("StaffRegistrationDate"))
txtLabel.Text = CStr(dt.Rows(0)("StaffPhoto"))
txtCountry.Text = CStr(dt.Rows(0)("StaffCountry"))
cbICColor.Text = CStr(dt.Rows(0)("StaffICColor"))
txtICNo.Text = CStr(dt.Rows(0)("StaffICNo"))
txtLabel.Text = CStr(dt.Rows(0)("StaffPhoto"))
PictureBox1.Image = System.Drawing.Bitmap.FromFile(txtLabel.Text)
txtAnnualLeave.Text = CStr(dt.Rows(0)("StaffAnnualLeave"))
Else
txtStaffName.Clear()
txtAddress1.Clear()
txtAddress2.Clear()
txtContactNo.Clear()
txtCountry.Clear()
txtICNo.Clear()
txtAnnualLeave.Text = "0"
End If
End If
End Sub
Private Sub txtStaffID_TextChanged_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtStaffID.TextChanged
Grid("SELECT * FROM Staff WHERE StaffID LIKE '" & txtStaffID.Text & "%'")
End Sub
End Class
Big Thanks in advance for anyone who can help and guide me.