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
Solved

Updating form

Posted on 2004-04-26
5
300 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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

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…
It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that unde…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

838 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