Question From Access to sql

[elianeasmar]

Hello. I need a hand in my code. i wrote the following code. and i am trying to work with sql.
what should i change?
I really need some help with this :/

Public Class BindingContext
    Dim _cn As New OleDb.OleDbConnection
    Dim _DataAdapter As New OleDb.OleDbDataAdapter()
    Dim _CommandBuilder As OleDb.OleDbCommandBuilder
    Dim _DataSet As New System.Data.DataSet()
Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim _provider As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
        Dim _fullPath As String = "C:\Working Storage\Developments\LAB\Database Connection\Database Connection\Database\"
        Dim _dataSource As String = "Data Source=" & _fullPath & "ADOnet.MDB"
        _cn = New OleDb.OleDbConnection(_provider & _dataSource)
        _DataAdapter.SelectCommand = New OleDb.OleDbCommand("SELECT * FROM tblCustomer", _cn)
        _DataSet = New DataSet()
        Try
            _DataAdapter.Fill(_DataSet)
        Catch eror As Exception
            MsgBox(eror.Message)
        End Try
        cbDept.DataSource = _DataSet.Tables(0)
        cbDept.DisplayMember = "Name"
        cbDept.ValueMember = "ID"
        Me.txtName.Text = "Sjflkjasdlk=fj"
        txtName.DataBindings.Add("text", _DataSet.Tables("tblCustomer"), "Name")
        txtEmail.DataBindings.Add("text", _DataSet.Tables(0), "Email")
        txtAddress.DataBindings.Add("text", _DataSet.Tables(0), "Address")
        cbDept.DataBindings.Add("SelectedValue", _DataSet.Tables(0), "DeptID")
    End Sub
    Private Sub cmdRequery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRequery.Click
        DBRequery()
        Me.BindingContext(_DataSet.Tables(0)).CancelCurrentEdit()
        Me.BindingContext(_DataSet.Tables(0)).Position = 0
        RefreshData(True)
    End Sub
    Private Sub DBRequery()
        cmdSave.Enabled = False
        _DataSet.Clear()
        Try
            _DataAdapter.Fill(_DataSet)
        Catch Eror As Exception
            cmdAdd.Enabled = False
            cmdUpdate.Enabled = False
            cmdDelete.Enabled = False
            MsgBox(Eror.Message, MsgBoxStyle.Exclamation, "Error Opening Database")
            Close()
            Exit Sub
        End Try
        txtCount.Text = Format(_DataSet.Tables(0).Rows.Count, "#,##0")
        If _DataSet.Tables(0).Rows.Count > 0 Then
            txtCurrent.Text = "1"
            EnableNavigation()
        End If
        cmdAdd.Enabled = True
    End Sub


    Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Try
            _DataAdapter.Update(_DataSet)
        Catch eror As Exception
            MsgBox("This was an error updating database." & ControlChars.CrLf & _
                eror.Message)
        End Try
    End Sub
    Private Sub cmdGo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdGo.Click
        If IsNumeric(txtGoto.Text) Then
            If CInt(txtGoto.Text) = CDec(txtGoto.Text) Then
                If 1 <= CInt(txtGoto.Text) And CInt(txtGoto.Text) <= CInt(txtCount.Text) Then
                    BindingContext(_DataSet.Tables(0)).Position = CInt(txtGoto.Text) - 1
                    RefreshData(True)
                Else
                    GotoError()
                    Exit Sub
                End If
            Else
                GotoError()
                Exit Sub
            End If
        Else
            GotoError()
            Exit Sub
        End If
    End Sub
 Private Sub GotoError()
        MsgBox("This must be an integer between 1 and " & txtCount.Text & " inclusive", MsgBoxStyle.Exclamation)
    End Sub
 Private Sub cmdAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdAdd.Click
        Dim pDataRow As DataRow
        pDataRow = _DataSet.Tables(0).NewRow()
        pDataRow!name = txtName.Text
        pDataRow!email = txtEmail.Text
        pDataRow!Address = txtAddress.Text
        _DataSet.Tables(0).Rows.Add(pDataRow)
        txtCount.Text = CStr(CInt(txtCount.Text) + 1)
        cmdSave.Enabled = True  ' let user update the underlying database
        txtCurrent.Text = txtCount.Text ' make the current row, the last (recently added) row
        EnableNavigation()
    End Sub
    Private Sub cmdUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdUpdate.Click
        Dim pDataRow As DataRow
        pDataRow = _DataSet.Tables(0).Rows(CInt(txtCurrent.Text) - 1)
        BindingContext(_DataSet.Tables(0)).EndCurrentEdit()
        pDataRow!name = txtName.Text
        pDataRow!email = txtEmail.Text
        pDataRow!address = txtAddress.Text
        cmdSave.Enabled = True  ' let user update the underlying database
    End Sub
 Private Sub cmdDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdDelete.Click
        BindingContext(_DataSet.Tables(0)).RemoveAt(BindingContext(_DataSet.Tables(0)).Position)
        txtCount.Text = CStr(CInt(txtCount.Text) - 1)
        RefreshData(True)
        cmdSave.Enabled = True
    End Sub
 Private Sub cmdClear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdClear.Click
        txtName.Clear()
        txtEmail.Clear()
        txtAddress.Clear()
        cbDept.SelectedIndex = -1
        cbDept.SelectedIndex = -1
    End Sub
    Private Sub cmdRefresh_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdRefresh.Click
        BindingContext(_DataSet.Tables(0)).CancelCurrentEdit()
    End Sub
    Private Sub RefreshData(Optional ByVal vEnableNavigation As Boolean = False)
        txtCurrent.Text = CStr(Me.BindingContext(_DataSet.Tables(0)).Position + 1)
        If vEnableNavigation Then EnableNavigation()
    End Sub
End Class

Thank you sooo much for your time

 

[IanRyder]

Hi,

You say:-

what should i change?
I really need some help with this :/

But you forgot to tell us what is wrong or what is not working. If you would like to expand your comments to include what is wrong then I am sure that someone here will help you to solve your problem.

Cheers,

Ian

 

[jmcilhinney]

When you say "sql", do you actually mean SQL Server? If so then this is a perfect example of why you should always use the proper names for things, i.e. to avoid confusion. "sql" is not a database. It is a programming language for writing database queries. Microsoft's RDBMS is named SQL Server so if that's what you mean then that's what you should say. You're not the only one who takes the lazy option but this is one instance where the actual meaning is not clear otherwise.

Assuming that that is the case then pretty much all you need to change is to use types from the SqlClient namespace instead of the OleDb namespace, e.g. SqlConnection rather then OleDbConnection, and change the connection string. Visit www.connectionstrings.com for all your connection string needs.

I would also suggest that you get rid of all that stuff using BindingContext and use a BindingSource instead. Bind your DataTable to a BindingSource and then use that everywhere. You have methods like MoveNext, RemoveAt, RemoveCurrent and CancelEdit as well as properties like Position and Current.

 

[elianeasmar]

Hi Ian. The code i posted works fin. But i was asking if i can get some help to start converting it to sql server.
hey jmcilhinney. I used the datasource. It's easy to use.but i wanted to write everything by code. I am going to start converting to sql server.
And thank you jmcilhinney for the explanation about sql and sql server.
Thank you Ian fror your response