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, Sep 29 '16 at 22:11
@Slai why do I need to mention the database Name while it uses a Connection to it.... — Danys Chalifour, Sep 29 '16 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, Sep 29 '16 at 23:38

score 6 · Accepted Answer · edited Jun 19 '18 at 13:30

6

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 '18 at 13:30

Pete

469
7
18

answered Sep 29 '16 at 23:43

Pavan Chandaka

11,671
5
26
34

okay so I should avoid ' in update statement but In others I should mention them? Why ? – Danys Chalifour Sep 29 '16 at 23:50
It is not only update statement, it is about place holder. For place holders you no need to give "'". – Pavan Chandaka Sep 29 '16 at 23:55
1

@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. – Trevor Sep 30 '16 at 00:01
Ya Zaggler, But I tried to stick to his code mostly. – Pavan Chandaka Sep 30 '16 at 00:03
It doesn't bother to show the correct way does it? Otherwise it's bad practice that keeps hanging around... – Trevor Sep 30 '16 at 00:06
I agree with you. it requires many improvements, even exception handling... but I have very less time to do all those things... So I just provided solution to his issue. – Pavan Chandaka Sep 30 '16 at 00:08
@Zaggler thank you sir, this statement is a Must know! – Danys Chalifour Sep 30 '16 at 03:39
@Çöđěxěŕ I am still not getting what is wrong with this code snippet. Please tell me little bit because I do the update operations in the same way and it seems to be correct. Help! – Shreekant Jan 17 '20 at 04:33

Update SQL database using VB.NET

1 Answers1