5 Replies - 3219 Views - Last Post: 03 September 2012 - 06:14 PM

#1 Rickysay  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 25-October 11

How toChange MS ACCESS to MS SQL on VB.NET?

Posted 15 August 2012 - 01:42 AM

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 MS SQL express installed in the desktop and have set up a local server and have tried countless ways from google 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 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.

Is This A Good Question/Topic? 0
  • +

Replies To: How toChange MS ACCESS to MS SQL on VB.NET?

#2 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 592
  • View blog
  • Posts: 1,321
  • Joined: 24-April 12

Re: How toChange MS ACCESS to MS SQL on VB.NET?

Posted 16 August 2012 - 11:12 AM

The first thing that stands out is that you are using Jet in your connection string. That's not going to work. Jet is for accessing Excel and Access, not for accessing SQL Server. You can get a connection string at ConnectionStrings.com.

http://www.connectio...sql-server-2008


Something like this might work pretty well:

Quote

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;


There may be a lot of little changes. But the first thing is to get connected.
Was This Post Helpful? 1
  • +
  • -

#3 Rickysay  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 25-October 11

Re: How toChange MS ACCESS to MS SQL on VB.NET?

Posted 22 August 2012 - 08:52 PM

View PostBBeck, on 17 August 2012 - 02:12 AM, said:

The first thing that stands out is that you are using Jet in your connection string. That's not going to work. Jet is for accessing Excel and Access, not for accessing SQL Server. You can get a connection string at ConnectionStrings.com.

http://www.connectio...sql-server-2008


Something like this might work pretty well:

Quote

Provider=SQLNCLI10;Server=myServerAddress;Database=myDataBase;Uid=myUsername; Pwd=myPassword;


There may be a lot of little changes. But the first thing is to get connected.


The link you provided is used on VB web applications not Windows applications.
Was This Post Helpful? 0
  • +
  • -

#4 BBeck  Icon User is offline

  • Here to help.
  • member icon


Reputation: 592
  • View blog
  • Posts: 1,321
  • Joined: 24-April 12

Re: How toChange MS ACCESS to MS SQL on VB.NET?

Posted 23 August 2012 - 11:50 AM

The link provided gives connection strings for every imaginable situation under the sun. A connection string is a connection string it has little to do with whether it's a web ap or a desktop app.

Here's a connection string that I'm using from C#. It's definately not a web app. It uses Integrated Security (NT Authentication) and presumably uses OLE DB rather than SQL Native Client, but otherwise it's pretty much the same thing.

ConnectionString= @"server = MyServer; integrated security = true; database = MyDatabase"

Oh. Also, I just noticed you said you're using SQL Express. SQL Express installs as a named instance, so the server name isn't going to be enough on it's own. You need something like this:

ConnectionString= @"server = MyServer\InstanceName; integrated security = true; database = MyDatabase"

where InstanceName is the name of the instance. In this case I think the Instance Name is SQLExpress (you can get it by looking at the services).

This post has been edited by BBeck: 23 August 2012 - 12:48 PM

Was This Post Helpful? 0
  • +
  • -

#5 Rickysay  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 25-October 11

Re: How toChange MS ACCESS to MS SQL on VB.NET?

Posted 28 August 2012 - 12:41 AM

when u mentioned a lot of little changes, does my add, edit, and delete works fine with the coding above?
Was This Post Helpful? 0
  • +
  • -

#6 Rickysay  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 25-October 11

Re: How toChange MS ACCESS to MS SQL on VB.NET?

Posted 03 September 2012 - 06:14 PM

http://pastebin.com/HM2TvVNw

Okay i tried to change most of the codings already like injecting SQLConnection instead of OleDB connections as well as changing the connection strings.

But i got an error:

Posted Image


Do you know what and how to fix this? I've tried adding numerous SqlConnection properties but to no avail.

Sorry for the image above. Wrong pic. here's the correct one:

Posted Image
:smile2:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1