Question I can't edit, delete, or add

[kemo]

Hello All, I have a project that's been assigned to me which involves communication between the front end which is vb form and a back end which is SQL server. I am new to sql server. I wrote a sample form before using the live data to see if I would be able to achieve this or not. I was able to get the data from sql db and go through it with next and previous buttons but I can't edit, delete, or add any information or data to the database through the vb form. So any help on this would be greatly appreciated.
Code:

Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Dim da As OleDbDataAdapter
    Dim ds As New DataSet
    Dim con As New OleDb.OleDbConnection("Provider=sqloledb;Data Source=sql;Initial Catalog=db name;User Id=user id;Password=pw;")
    Dim cb As OleDbCommandBuilder

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

        da = New OleDbDataAdapter("Select * from student", con)
        da.Fill(ds, "student")
        con.Open()

        txtname.DataBindings.Add(New System.Windows.Forms.Binding("text", ds, "student.name"))
        txtroll.DataBindings.Add(New System.Windows.Forms.Binding("text", ds, "student.roll"))
        txtcourse.DataBindings.Add(New System.Windows.Forms.Binding("text", ds, "student.course"))
        lbl1.Text = ds.Tables("student").Rows.Count
    End Sub
    Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnadd.Click
        BindingContext(ds, "student").AddNew()
        MessageBox.Show("Successfully Inserted")
    End Sub
    Private Sub btndelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btndelete.Click
        BindingContext(ds, "student").RemoveAt(BindingContext(ds, "student").Position)
        da.Update(ds)
    End Sub
    Private Sub btnnext_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnnext.Click
        BindingContext(ds, "Student").Position = BindingContext(ds, "Student").Position + 1
    End Sub
    Private Sub btnprev_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnprev.Click
        BindingContext(ds, "student").Position = BindingContext(ds, "student").Position - 1
    End Sub
    Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
        Me.BindingContext(ds, "student").EndCurrentEdit()
        da.Update(ds)
    End Sub
    Private Sub btnfirst_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnfirst.Click
        BindingContext(ds, "student").Position = 0
    End Sub
    Private Sub btnlast_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnlast.Click
        BindingContext(ds, "student").Position = BindingContext(ds, "student").Count - 1
    End Sub
    Private Sub btnclear_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnclear.Click
        txtname.Clear()
        txtroll.Clear()
        txtcourse.Clear()
    End Sub
    Private Sub btnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExit.Click
        con.Close()
        Me.Close()
    End Sub
End Class

 

[jmcilhinney]

Forget using BindingContext. Populate your DataTable using a DataAdapter and then bind it to a BindingSource, then bind that to your controls. The BindingSource has all you need to manipulate the data, e.g. MoveFirst, MoveNext, RemoveCurrent, etc. You can even add a BindingNavigator and associate it with the BindingSource and do away with all your Buttons. All you would have to do is add a Save button handler to call EndEdit on the BindingSource and Update on the DataAdapter.

 

[kemo]

That sounds greate. Would you have an example or sample code to look at to give me an idea where to start. Thank for your help.

 

[kemo]

Any one else can find out the problem or have an example?
Thanks.

 

[jmcilhinney]

What have you done to help yourself? I can't believe that you have searched and, on the entire internet, you were not able to find a single example, so I can only assume that you haven't tried.

Would you have an example or sample code to look at to give me an idea where to start.

I've already given you an idea of where to start:

Populate your DataTable using a DataAdapter and then bind it to a BindingSource, then bind that to your controls. The BindingSource has all you need to manipulate the data, e.g. MoveFirst, MoveNext, RemoveCurrent, etc. You can even add a BindingNavigator and associate it with the BindingSource and do away with all your Buttons.

Instead of sitting on your hands waiting for others to write the code for you, use the information you have already been given to do what you can for yourself. If you have problems then by all means post back and show us what you've done and what you're having problems with. You've been pointed in the right direction, now you need to follow it.

 

[kemo]

Thank you jmcilhinney for your reply. I did search online and part of searching online is being here. But maybe I was not very clear. Your idea was greate, it just will require changing alot in an area that I'm not familiar with. Also, I'm few steps to get this working. My first problem was that I was not able to get the form to connect the sql at all, then it was not reading the record. And I think by using text boxes instead for datagridview will give me more options to do in the long run. Like, if I work on a project that got numbers in the text boxes, I can have a nother text box or a lable that would calculate that for the user. This might be possible using datagridview also, but I'm not aware of it and I don't know how to use it at all. And all the examples I found online were using datagrid, not text boxes. So you had the best idea, but I don't think it would meet my needs. And I totally appreciate you and your replies. At least you tried.
Thank you.

 

[jmcilhinney]

The type of control is pretty much irrelevant because, in code, you work with the BindingSource. Retrieving the data is exactly the same regardless. The ONLY thing that changes is binding to the controls. Using a DataGridView:

Me.DataGridView1.DataSource = Me.BindingSource1

Using TextBoxes:

Me.TextBox1.DataBindings.Add("Text", Me.BindingSource1, "Column1")
Me.TextBox2.DataBindings.Add("Text", Me.BindingSource1, "Column2")

 

[kemo]

Thank you for your reply. That was helpful, but now I'm getting this error message:
A network-related or instance-specific orror occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and the SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, orror: 26 - Error Locationg Server/Instance Specified)

any idea on how to fix that? I know what it means, it's just does not make sense becuase I have the sql server mapped on my pc and I can connect to it with no problem. It's just when I try to connect to it through vb, I get this message.
Thanks for your help.

 

[jmcilhinney]

If your SQL Server is indeed configured correctly then there must be an issue with your connection string. Also, if you're not already, I suggest that you use SqlClient rather than OleDb.