Update SQL don't work

Hi everybody,

I want to update a field in a SQL table but i am unable to do this.

Environment :
Visual Studio & Visual Basic 2005
SQL Server 2000

Here is part of my code :

Imports System
Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
Inherits System.Windows.Forms.Form

Public conn As SqlConnection
Public CmdS As SqlCommand
Public da As SqlDataAdapter
Public ds As New DataSet()
Public strSql As String
Public strConn As String = "Initial Catalog=FAVRE;Data Source=Serveur-corc;User ID=sa;Password=;"
Public i As Short
Public Dossier As String
Public Client As String
Public Sequence As String
Public Poids As String
Public Empl As String
Public Charg As Boolean
Public Chargem As String

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

Dossier = "CHT" & TextBox11.Text
Client = TextBox12.Text
Sequence = "SEQ-" & TextBox13.Text

strSql = "SELECT R.ESRC_FILE, R.RC_NUM, R.PS_CODE, R.FABWEIGHT, C.SIT_NAME, CU.INV_NAME, R.EMPLACEMENT, R.CHARGE FROM REF_PS AS R "

strSql = strSql & "JOIN CONTRACT AS C ON C.ESRC_FILE = R.ESRC_FILE AND C.RC_NUM = R.RC_NUM "

strSql = strSql & "JOIN CUSTOMER AS CU ON CU.CUST_CODE = C.CUST_CODE "

strSql = strSql & "WHERE R.ESRC_FILE = '" & Dossier & "' AND R.RC_NUM = '" & Client & "' AND R.PS_CODE = '" & Sequence & "' "

Connexion(strConn, strSql)

If ds.Tables("REF_PS").Rows.Count = 0 Then
MsgBox("***** LISTE NON TROUVEE *****")
Exit Sub
End If

With ds.Tables("REF_PS")
    Poids = .Rows(0).Item("FABWEIGHT")
    If Not (IsDBNull(.Rows(0).Item("EMPLACEMENT"))) Then
        Empl = .Rows(0).Item("EMPLACEMENT")
    End If
    Charg = False
    If Not (IsDBNull(.Rows(0).Item("CHARGE"))) Then
        If .Rows(0).Item("CHARGE") = "1" Then
            Charg = True
        End If
    End If
End With

TextBox21.Text = Dossier
TextBox22.Text = Client
TextBox23.Text = Sequence
TextBox24.Text = Poids
TextBox27.Text = Empl

If Charg Then
CheckBox2.Checked = True
End If
TextBox27.Focus()

End Sub

Private Sub Connexion(ByVal strConn As String, ByVal strSql As String)

conn = New SqlConnection(strConn)
conn.Open()
CmdS = New SqlCommand(strSql)
da = New SqlDataAdapter(CmdS)
CmdS.Connection() = conn
da.Fill(ds, "REF_PS")

End Sub

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click

Empl = TextBox27.Text

If CheckBox2.Checked = True Then
Chargem = "1"
Else
Chargem = "0"
End If

FIRST TRY :

With ds.Tables("REF_PS")
.Rows(0).Item("EMPLACEMENT") = Empl
.Rows(0).Item("CHARGE") = Chargem
End With

da.Update(ds.Tables("REF_PS"))

I GOT AN ERROT MESSAGE :
InvalidOperationException was unhandled
Update requires a valid UpdateCommand when passes DataRow collection with modified rows

SECOND TRY :

strSql = "UPDATE REF_PS SET EMPLACEMENT='" & Empl & "', CHARGE='" & Chargem & "' WHERE ESRC_FILE = '" & Dossier & "' AND RC_NUM = '" & Client & "' AND PS_CODE = '" & Sequence & "'"

conn = New SqlConnection(strConn)
conn.Open()
CmdS = New SqlCommand(strSql, conn)

CmdS = New SqlCommand()
CmdS.Connection = conn
CmdS.CommandText = strSql
CmdS.CommandType = CommandType.Text

Here is no error message but the table isn't updated.

Short explanation :
The user chooses a record in a form, several Textboxes are filled in the
same form (confirmation of choice) and then the user sets the values for
"EMPLACEMENT" and "CHARGE".

Thanks for your help.

Greetings. dp

Answer this question