[RESOLVED] ADONET Tutorial1 converted from OleDb to SQL

[EntityX]

I have an SQL database on my website that I want to communicate with from my Windows forms application so I was looking at the ADONet Tutorial 1 and trying to convert it to SQL code.

Here's my attempted conversion of the code so far.

Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Data.SqlDbType
Imports System.Data.Sql

Public Class Form2

    Dim ds As New DataSet()
    Dim intCurrentIndex As Integer = 0
    Dim da As New SqlDataAdapter()
    Dim conn As New SqlConnection()




    Private Sub FillFields()
        txtFirstName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("FirstName").ToString()
        txtLastName.Text = ds.Tables(0).Rows(intCurrentIndex).Item("LastName").ToString()
        txtLocation.Text = ds.Tables(0).Rows(intCurrentIndex).Item("Location").ToString()
    End Sub

    Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=K:\Stuff\ADONetTutorial1\ADONetTutorial1\sample.mdb;User Id=admin;Password=;"

        da.SelectCommand = New SqlCommand("SELECT EmployeeID, FirstName, LastName, Location FROM tbl_Master")
        da.SelectCommand.Connection = conn


        da.UpdateCommand = New SqlCommand("UPDATE tbl_Master SET FirstName = @FirstName, LastName = @LastName, Location = @Location WHERE EmployeeID = @EmployeeID")
        da.UpdateCommand.Connection = conn
        da.UpdateCommand.Parameters.Add("@FirstName", VarChar, 40, "FirstName")
        da.UpdateCommand.Parameters.Add("@LastName", VarChar, 40, "LastName")
        da.UpdateCommand.Parameters.Add("@Location", VarChar, 40, "Location")
        da.UpdateCommand.Parameters.Add("@EmployeeID", Int, 5, "EmployeeID") '.SourceVersion = DataRowVersion.Original



        da.InsertCommand = New SqlCommand("INSERT INTO tbl_Master(FirstName, LastName, Location) VALUES(@FirstName,@LastName,@Location)")
        da.InsertCommand.Connection = conn
        da.InsertCommand.Parameters.Add("@FirstName", VarChar, 40, "FirstName")
        da.InsertCommand.Parameters.Add("@LastName", VarChar, 40, "LastName")
        da.InsertCommand.Parameters.Add("@Location", VarChar, 40, "Location")


        da.DeleteCommand = New SqlCommand("DELETE FROM tbl_Master WHERE EmployeeID = @EmployeeID")
        da.DeleteCommand.Connection = conn
        da.DeleteCommand.Parameters.Add("@EmployeeID", Int, 5, "EmployeeID")

        da.Fill(ds)

        If ds.Tables(0).Rows.Count > 0 Then 'Check to see if the table is empty
            FillFields()
        End If


    End Sub


    Private Sub btnFirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFirst.Click
        'Since 0 is the first row
        intCurrentIndex = 0
        FillFields()

    End Sub

    Private Sub btnPrevious_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPrevious.Click

        If intCurrentIndex > 0 Then  'We move back only if we're not at the first row.
            intCurrentIndex = intCurrentIndex - 1  'Subtract one from the current index.
            FillFields()
        Else
            MessageBox.Show("You're already at the first record.")
        End If

    End Sub


    Private Sub btnNext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNext.Click
        If intCurrentIndex < ds.Tables(0).Rows.Count - 1 Then 'ds.Tables(0).Rows.Count - 1 is the index for the last row
            intCurrentIndex = intCurrentIndex + 1  'Add one to the current index.
            FillFields()
        Else
            MessageBox.Show("You're already at the last record.")
        End If
    End Sub

    Private Sub btnLast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLast.Click
        intCurrentIndex = ds.Tables(0).Rows.Count - 1 'ds.Tables(0).Rows.Count - 1 is the index for the last row
        FillFields()

    End Sub



    
    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click

        Dim dr As DataRow
        dr = ds.Tables(0).Rows(intCurrentIndex) 'This gets a reference to the row currently being edited
        dr.BeginEdit()
        dr("FirstName") = txtFirstName.Text
        dr("LastName") = txtLastName.Text
        dr("Location") = txtLocation.Text
        dr.EndEdit()

        da.Update(ds)  'Ask the dataadapter to call the UpdateCommand and update the database
        ds.AcceptChanges() 'Commits the change to the dataset.



    End Sub

    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
        Dim dr As DataRow

        dr = ds.Tables(0).NewRow()  'Gets a reference to a new row.
        dr("FirstName") = txtFirstName.Text
        dr("LastName") = txtLastName.Text
        dr("Location") = txtLocation.Text

        ds.Tables(0).Rows.Add(dr)
        da.Update(ds)
        ds.AcceptChanges()


    End Sub

    Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
        Dim dr As DataRow
        dr = ds.Tables(0).Rows(intCurrentIndex)
        dr.Delete() 'Delete the row

        da.Update(ds)
        ds.AcceptChanges()

    End Sub
End Class

If I try to run it I get an system argument exception for the line :

       conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=K:\Stuff\ADONetTutorial1\ADONetTutorial1\sample.mdb;User Id=admin;Password=;"

and it says : keyword not supported : 'provider'

Perhaps I have to use a different table than the one in the ADO NET tutorial and obviously that line has to be changed. Do OleDb and SQL use the same tables?

[HowTo]

sorry the connection string will be changed if you change the database.....

to get the correct connection string,check out this site:

www.connectionstrings.com

[techgnome]

the SQLCLient namespace is for SQL SERVER.... but you are connecting to an access database... so you should be using the OLEDBClient namespace. The reason it's choking on Provider is because you don't include it when using the SQLClient namespace. It is automaticaly added.

-tg

[gep13]

If you are having issues getting the connection string correct, have a look at connectionstrings.com.

It is a very handy reference to keep around.

Gary