[RESOLVED] ADONET Tutorial1 converted from OleDb to SQL
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.
Code:
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 :
Code:
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?
Re: ADONET Tutorial1 converted from OleDb to SQL
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
Re: ADONET Tutorial1 converted from OleDb to SQL
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
Re: ADONET Tutorial1 converted from OleDb to SQL
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
