Danys Chalifour Danys Chalifour - 2 months ago 24
Vb.net Question

Update SQL database using VB.NET

The code throw no errors it just didn't Execute the satement

Private Sub Update_Program(item As Programme)
'Set Command
SchoolTypes.Connexion.Open()
item.Command = New SqlClient.SqlCommand("UPDATE T_Programme Set pro_nom='@nom' , pro_nbr_unites=@nom , pro_nbr_heures=@unit WHERE pro_no ='@no'", SchoolTypes.Connexion)

''Add Parameter
item.Command.Parameters.Add("@no", SqlDbType.VarChar, 6)
item.Command.Parameters.Add("@nom", SqlDbType.VarChar, 50)
item.Command.Parameters.Add("@unit", SqlDbType.Float)
item.Command.Parameters.Add("@heures", SqlDbType.Int)
''''Set Values
item.Command.Parameters("@no").Value = item.Pro_No
item.Command.Parameters("@nom").Value = item.Pro_Nom
item.Command.Parameters("@unit").Value = item.Pro_Nbr_Unit
item.Command.Parameters("@heures").Value = item.Pro_Nbr_Heure

Try
If item.Command.ExecuteNonQuery() > 0 Then
MsgBox("Modifier avec Succes!")
End If
SchoolTypes.Connexion.Close()
Catch ex As Exception
err.ShowDetails(System.Reflection.MethodBase.GetCurrentMethod(), ex)
End Try
End Sub


I have tested my Command and it works on sql but not on the program...

Here's a paste of my database
Database

Answer

Your command statement is wrong. yo should not give "''" marks for your parameters in update statement.

And also you have mismatch inputs. Below should be your update statement. cmd = New SqlCommand("UPDATE T_Programme Set pro_nom=@nom , pro_nbr_unites=@unit , pro_nbr_heures=@heures WHERE pro_no =@no", con)

I tried below code. And it works fine.

Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load
        Dim con As SqlConnection
        Dim cmd As SqlCommand
        Dim row As Integer
        'Dim str As String
        con = New SqlConnection("server=.;database=Test;integrated security=true")
        con.Open()
        cmd = New SqlCommand("UPDATE T_Programme Set  pro_nom=@nom , pro_nbr_unites=@unit , pro_nbr_heures=@heures WHERE pro_no =@no", con)
        cmd.Parameters.Add("@no", SqlDbType.VarChar, 6)
        cmd.Parameters.Add("@nom", SqlDbType.VarChar, 50)
        cmd.Parameters.Add("@unit", SqlDbType.Float)
        cmd.Parameters.Add("@heures", SqlDbType.Int)
        ''''Set Values
        cmd.Parameters("@no").Value = "1234"
        cmd.Parameters("@nom").Value = "qwerty"
        cmd.Parameters("@unit").Value = 12.0
        cmd.Parameters("@heures").Value = 2
        row = cmd.ExecuteNonQuery()

        con.Close()
    End Sub