?
Solved

Updating form

Posted on 2004-04-26
5
Medium Priority
?
304 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 200 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
Suggested Courses

762 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