Update SQL database using VB.NET

Question

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

get rid of the ticks around '@no' actually get rid of all of them — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Sep 29, 2016 at 22:11
@Slai why do I need to mention the database Name while it uses a Connection to it.... — Danys Chalifour
– Danys Chalifour, Commented Sep 29, 2016 at 23:10
is pro_no really a text field? make sure there is a record in the db with such a value — Ňɏssa Pøngjǣrdenlarp
– Ňɏssa Pøngjǣrdenlarp, Commented Sep 29, 2016 at 23:38

Pete · Accepted Answer · 2018-06-19 13:30:21Z

7

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

edited Jun 19, 2018 at 13:30

Pete

4697 silver badges21 bronze badges

answered Sep 29, 2016 at 23:43

Pavan Chandaka

13k5 gold badges35 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Danys Chalifour Over a year ago

okay so I should avoid ' in update statement but In others I should mention them? Why ?

Pavan Chandaka Over a year ago

It is not only update statement, it is about place holder. For place holders you no need to give "'".

Trevor Over a year ago

@Pavan Chandaka you do know you can add the value to the end of the param right? Currenlty above the readability lack, its confusing. Also wrap command and connections in using statements so they are properly disposed when you are done with them.

Pavan Chandaka Over a year ago

Ya Zaggler, But I tried to stick to his code mostly.

Trevor Over a year ago

It doesn't bother to show the correct way does it? Otherwise it's bad practice that keeps hanging around...

|

Collectives™ on Stack Overflow

Update SQL database using VB.NET

1 Answer 1

8 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Related