How does one access Sql Database on existing VB.net+MS ACCESS

Question

0.00/5 (No votes)

See more:

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.

Posted 14-Aug-12 22:11pm

Rickysay

Add a Solution

3 solutions

Solution 1

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.

Posted 14-Aug-12 23:15pm

Mehdi Gholam

Comments

Rickysay 16-Aug-12 2:10am

I have tried the link that u gave me and have changed the connection string but to no avail. All seems to fail.(error coming out) I kept getting this msg "Cannot open database "DATABASE.mdf" requested by the login. The login failed."

Mehdi Gholam 16-Aug-12 2:21am

You have to use the sql server database name in which you migrated your data from access to, not "database.mdf".

Rickysay 16-Aug-12 2:27am

I deleted my MS ACCESS database and create a MS SQL database as my data source under .\SQLEXPRESS named DATABASE.mdf. Here is an example of the connection string i used:

sqlconn.ConnectionString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf;Integrated Security=SSPI;"

Mehdi Gholam 16-Aug-12 2:31am

What name did you give your sql database in the studio? Use that instead of database.mdf

Rickysay 16-Aug-12 2:34am

http://upit.cc/i/18a0c019.jpg <-- The name of my Database.

So what should be the name be in the connection string?

Mehdi Gholam 16-Aug-12 2:38am

sqlconn.ConnectionString = "Provider=SQLOLEDB;Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE;Integrated Security=SSPI;"

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

codejet · Accepted Answer · 2012-08-16T09:29:00

Solution 3

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

Number 4 is ok the way it is .

Posted 16-Aug-12 9:29am

codejet

Comments

Rickysay 16-Aug-12 23:37pm

I don't have OleDb.OleDbConnectin in my coding. Which are u really referring to?

Rickysay 17-Aug-12 2:53am

Anyone?

codejet 23-Aug-12 4:25am

Sorry ignore that. Is it fine now ?

Rickysay 27-Aug-12 23:29pm

No it doesnt work, all the changes make for more even worse errors. Are there any solution?

Rickysay 28-Aug-12 23:18pm

Do i have to change my button codings to use with methods such as executequery or the current one is fine?

codejet 29-Aug-12 8:17am

ExecuteNonQuery is ok. What errors are you getting now?

Rickysay 31-Aug-12 4:22am

Is it possible not to use ExecuteNonQuery? I've tried it and i have no luck with that.

Here's my coding after changing what you pointed me out to change.

http://pastebin.com/c1nJr368[^]

I have three errors, all stating "Type 'SqlClient.CommandBuilder' is not defined"
Here's a pic:
http://upit.cc/i/af1ea223.png

codejet 31-Aug-12 4:41am

change
con.ConnectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=DATABASE.mdf

Rickysay 31-Aug-12 20:55pm

Same error pops up.

Wait, hold up. Do i have to "Add New Data Source" through Data window?

Rickysay 31-Aug-12 4:46am

Oops. Changed it , but this error pops up when i f5

http://upit.cc/i/4316f49c.png[^]

Rickysay 2-Sep-12 22:09pm

I fixed the above error by inserting this line in connection string; Trusted_Connection=Yes;
But another error came up:

An error occurred creating the form. See Exception.InnerException for details. The error is: Cannot open database "DATABASE.mdf" requested by the login. The login failed.
Login failed for user 'USER-PC\USER'.

Rickysay 3-Sep-12 21:02pm

is there no one else that can help me? hello?

Rickysay 4-Sep-12 2:08am

Thanks. I just solved it myself, turns out there were a lot of duplicated records(from previous tries) on my table in MSSQL.

Thanks again codejet~!

codejet · Accepted Answer · 2012-08-15T21:14:00

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