Solved

Updating form

Posted on 2004-04-26
5
296 Views
Last Modified: 2010-04-24
I have a form with some data I read from a SQL Database. If I modify the form, when I close the form, SQL isn't updated. My Code is:

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

            Dim strsql As String = "SELECT * from names where Id='" & Me.TextH1.Text & "'"
            Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, cn)
            Dim dt = New DataTable
            da.Fill(dt)
                Me.LblIdH.Text = Trim$("" & dt.rows(0).item("Id"))
                Me.TextH2.Text = Trim$("" & dt.rows(0).item("Nom"))
                Me.TextH3.Text = Trim$("" & dt.rows(0).item("PrimerCognom"))
                Me.TextH4.Text = Trim$("" & dt.rows(0).item("SegonCognom"))
                Me.TextH5.Text = Trim$("" & dt.rows(0).item("CarrerNum"))
                Me.TextH6.Text = Trim$("" & dt.rows(0).item("Localitat"))
                Me.TextH7.Text = Trim$("" & dt.rows(0).item("Codipostal"))
                Me.TextH8.Text = Trim$("" & dt.rows(0).item("Telefon1"))
                Me.TextH9.Text = Trim$("" & dt.rows(0).item("Telefon2"))
                Me.TextH10.Text = Trim$("" & dt.rows(0).item("email"))
                Me.DTPicker1.Value = dt.rows(0).item("datanaix")
                Me.CmbBoxH1.Text = Trim$("" & dt.rows(0).item("provincia"))
                Me.CmbEscola.Text = Trim$("" & dt.rows(0).item("cluboescola"))
end sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim strsql As String = "SELECT * from names where Id=" & Me.LblIdH.Text
        Dim da As SqlDataAdapter = New SqlDataAdapter(strsql, cn)
        Dim dt = New DataTable
        da.Fill(dt)
            dt.rows(0).item("Nom") = Trim$("" & Me.TextH2.Text)
            dt.rows(0).item("PrimerCognom") = Trim$("" & Me.TextH3.Text)
            dt.rows(0).item("SegonCognom") = Trim$("" & Me.TextH4.Text)
            dt.rows(0).item("nomcomplet") = Trim$("" & Me.TextH2.Text) & " " & Trim$("" & Me.TextH3.Text) & " " & Trim$("" & Me.TextH4.Text)
            dt.rows(0).item("CarrerNum") = Trim$("" & Me.TextH5.Text)
            dt.rows(0).item("Localitat") = Trim$("" & Me.TextH6.Text)
            dt.rows(0).item("Codipostal") = Trim$("" & Me.TextH7.Text)
            dt.rows(0).item("Telefon1") = Trim$("" & Me.TextH8.Text)
            dt.rows(0).item("Telefon2") = Trim$("" & Me.TextH9.Text)
            dt.rows(0).item("email") = Trim$("" & Me.TextH10.Text)
            dt.rows(0).item("datanaix") = Me.DTPicker1.Value
            dt.rows(0).item("provincia") = Me.CmbBoxH1.Text
            dt.rows(0).item("cluboescola") = Me.CmbEscola.Text
            da.Update(dt)
end sub

The debugger stops at : da.Update(dt) line, with the following error:

Non controlled exception type 'System.InvalidOperationException' in microsoft.visualbasic.dll

Additional Information: Update requires that UpdateCommand is valid when DataRow has modified rows.

How I can update information in my Database ?

Thanks.

SAM
0
Comment
Question by:Uralula
  • 2
  • 2
5 Comments
 
LVL 5

Expert Comment

by:tgannetts
ID: 10918200
You need to use a CommandBuilder or a parameterized command to provide the SQL Command Text for the Update.

Add the following lines to the Button_Click sub:

Dim cb as SQLCommandBuilder

...

cb = new SQLCommandBuilder(da)
da.Update(dt)

Tom.
0
 

Author Comment

by:Uralula
ID: 10918260
Thanks for your help, but if I do this I get this message:

Additional Information: Dynamic SQL generation for UpdateCommand is not compatible with SelectCommand, that does not return any key column information.

SAM
0
 
LVL 5

Accepted Solution

by:
tgannetts earned 50 total points
ID: 10918398
In the Names table on the SQL Server, have you set a key field?
0
 
LVL 14

Expert Comment

by:ptakja
ID: 10918516
To expand on Tom's comments, I think you need one more line of code:

Put this line above the call to da.Update

da.UpdateCommand = cb.GetUpdateCommand

   
0
 

Author Comment

by:Uralula
ID: 10918529
Thanks Tom.

I don't remember to create the key field in my SQL Table.

Sam
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction As chip makers focus on adding processor cores over increasing clock speed, developers need to utilize the features of modern CPUs.  One of the ways we can do this is by implementing parallel algorithms in our software.   One recent…
Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
This video discusses moving either the default database or any database to a new volume.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now