Avatar of spen_lang
spen_lang asked on

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
Visual Basic.NETMicrosoft SQL Server 2008

Avatar of undefined
Last Comment
spen_lang

8/22/2022 - Mon
unknown_routine

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"
Éric Moreau

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.
unknown_routine

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
ASKER
spen_lang

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

ASKER
spen_lang

Do i need to add an addition stored procedure for the update command?
ASKER CERTIFIED SOLUTION
Éric Moreau

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
unknown_routine

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).
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lludden

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.
ASKER
spen_lang

Please could someone provide an example of what needs to be changed in my code?
ASKER
spen_lang

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).
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy