如何访问现有VB.net + MS ACCESS上的Sql数据库 [英] How does one access Sql Database on existing VB.net+MS ACCESS

问题描述

我有一个已经存在的程序，可以与MSAccess数据库(.mdb)很好地链接.问题是如何将后端更改为MS SQL?我已经在桌面上安装了MS SQL Express，并且用谷歌搜索并尝试了无数种方法，但无济于事.也许，问题出在我现有形式中使用的编码的不同.我希望在现有的编码中保留尽可能多的功能和编码.这是我用于链接MSACCESS作为数据源的编码:

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

在此先感谢所有可以帮助和指导我的人.

解决方案

对不起，我的错误
1. 1.将所有出现的OleDb.OleDbConnectin替换为SqlClient.SqlConnection

数字4可以正常使用.

1.将所有出现的OleDb.OleDbDataAdapter替换为SqlClient.SqlConnection
2.将连接字符串更改为Sql Connection字符串，例如"Provider = SQLOLEDB; Data Source =.\ SQLEXPRESS; Initial Catalog = DATABASE.mdf; Integrated Security = True;"
3.将所有出现的Oledb.CommandBuilder替换为SqlClient.CommandBuilder.
4.将所有出现的OleDb.OleDbDataAdapter替换为SqlClient.SqlDataAdapter

完成此操作后，我认为您的应用程序将成功连接到SQL Server数据库

首先将数据从Access迁移到SQL Server.

将您的连接字符串更改为: http://www.sqlstrings.com/SQL-Server-connection-strings. htm [ ^ ]

如果您正在使用Sql Server post 2010，则可能需要更改源，因为OleDB可能不起作用，在这种情况下，您将需要使用SqlConnection，SqlCommand等.

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.

解决方案

Sorry my mistake
1. 1. Replace all occurrences of OleDb.OleDbConnectin with SqlClient.SqlConnection

Number 4 is ok the way it is .

1. Replace all occurrences of OleDb.OleDbDataAdapter with SqlClient.SqlConnection
2. Change your connection strings to Sql Connection strings like "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf;Integrated Security=True;"
3. Replace all occurrences of Oledb.CommandBuilder with SqlClient.CommandBuilder.
4. Replace all occurences of OleDb.OleDbDataAdapter with SqlClient.SqlDataAdapter

With that done I think your application will succesfully connect to a SQL Server DB

Migrate your data from Access to SQL Server first.

Change your connection string to : http://www.sqlstrings.com/SQL-Server-connection-strings.htm[^]

You may need to change your source if you are using Sql Server post 2010 as OleDB might not work, in which case you will need to use SqlConnection, SqlCommand etc.

这篇关于如何访问现有VB.net + MS ACCESS上的Sql数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！