Danys Chalifour
Danys Chalifour

Reputation: 320

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

Upvotes: 2

Views: 32208

Answers (1)

Pavan Chandaka
Pavan Chandaka

Reputation: 12831

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

And also you have mismatch inputs. Below should be your update statement.

"UPDATE T_Programme Set pro_nom=@nom , pro_nbr_unites=@unit , pro_nbr_heures=@heures WHERE pro_no =@no"

I tried below code. And it works fine.

    Private Sub Form1_Load(sender As Object, e As System.EventArgs) Handles Me.Load

    Dim rowsAffected As Integer

    Using con As New SqlConnection("server=.;database=Test;integrated security=true")
        Using cmd As 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).Value = "1234"
            cmd.Parameters.Add("@nom", SqlDbType.VarChar).Value = "qwerty"
            cmd.Parameters.Add("@unit", SqlDbType.Float).Value = 12.0
            cmd.Parameters.Add("@heures", SqlDbType.Int).Value = 2

            con.Open()
            rowsAffected = cmd.ExecuteNonQuery()

        End Using
    End Using

End Sub

Upvotes: 7

Related Questions