VB.NET Database Access

Hi,

I am writing a VB.Net application that connects to a SQL database. I am new to this and was wondering what was the best practice to select, update, insert data?

To select the data I am using a SQL stored procedure and a DataTable, but I was how should I update the bound textboxes to the database...

Thanks, Greg
spen_langAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
take a look here:
http://support.microsoft.com/kb/301248

The general procedure is same:

1: you define a connection and set its connecton string and open it

2: you define a command object and set its text .

3: Now you can run this command:

For example:
Dim cmdDB As New SqlCommand("Update MyTable Set Age=6 where name="James"
0
Éric MoreauSenior .Net ConsultantCommented:
If you are starting, seriously have a look at Entity FrameWork. It will generate the complete DAL for you. If you can get Julie Lerman's book (http://thedatafarm.com/main.aspx), it would really help you. Also have a look at the "articles" available from the same web site.
0
unknown_routineCommented:
My Comments continued:

You can also use a command object to run a stored procedure to update the DB .This method is up to 15% faster as SQL gets compiled on DB.

so to summarize:

1: If you are dealing with a small application, then using Command object with SQL as its command text is sufficient.

2: If you are part of  a company team of developers, dealing with huge application and someone else is responsible for writing stored procedures, then use command which run stored procedure to update the data.
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

spen_langAuthor Commented:
Hi,

I am using the following code...

The code gets the data and bounds it to the correct controls, but if I edit the controls value and click the update button the changes are not save back to the database...

    Private Sub frm_Users_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        Dim conn As New SqlConnection(")

        Dim cmd As New SqlCommand("proc_UserAccess_All", conn)

        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandTimeout = 30
        conn.Open()

        da = New SqlDataAdapter(cmd)

        Dim builder As SqlCommandBuilder = New SqlCommandBuilder(da)
        ds = New DataSet()
        da.Fill(ds, "tbl_Users")

        BindingSource1.DataSource = ds.Tables("tbl_Users")
        BindingNavigator1.BindingSource = BindingSource1

        cmd.Dispose()
        cmd = Nothing
        conn.Close()
        conn = Nothing

        txtWindowsUsername.DataBindings.Add("Text", BindingSource1, "UserID")
        txtName.DataBindings.Add("Text", BindingSource1, "Username")
        cbActive.DataBindings.Add("Checked", BindingSource1, "Active")

    End Sub

    Private Sub ToolStripButton1_Click(sender As System.Object, e As System.EventArgs) Handles ToolStripButton1.Click
        Me.Validate()
        Me.da.Update(Me.ds.Tables("tbl_Users"))
        Me.ds.AcceptChanges()

    End Sub

Open in new window

0
spen_langAuthor Commented:
Do i need to add an addition stored procedure for the update command?
0
Éric MoreauSenior .Net ConsultantCommented:
because your select is the result of a stored proc, you need to provide the UpdateCommand yourself. check http://www.c-sharpcorner.com/UploadFile/dclark/UseSPwithDP11282005035417AM/UseSPwithDP.aspx
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
unknown_routineCommented:
Do i need to add an addition stored procedure for the update command? 

Open in new window

Yes you do.

Binding data does not mean data gets updated where user presses enter. Every time data changes you need to update data yourself(Using a simple command object or a stored procedure).
0
lluddenCommented:
Take a good look at strongly typed datasets.   You can define them from within Visual Studio.  I typically will have VS create my CRUD stored procedures as part of defining the dataset.

Once you have a strongly typed dataset, it is quite easy to databind it to a form.  ADO.Net uses disconnected datasets, so binding will update the copy in memory, but you have to run the update command on the dataset to actually persist it out to the database.
0
spen_langAuthor Commented:
Please could someone provide an example of what needs to be changed in my code?
0
spen_langAuthor Commented:
Thank you all for your help I used the link emoreau supplied in the end and managed to get it working (although it may be overcomplicated... but I can trim it down later).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.