[jcesz12]

please help me i'm just a newbie in vb.net.i'm trying to update a data user using vb.net2008 and sql server management studio express for my backend. the error says "must declare the scalar variable "@username" here's the code . please help me. tnx so much

 Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click
 
 
        Dim connstring = New SqlClient.SqlConnection("Data Source=MISD-ASBAUTISTA\SQLEXPRESS;Initial Catalog=inventory system;Integrated Security=SSPI;")
        Dim adapt = New SqlClient.SqlDataAdapter("select Username, Password from tbl_user", connstring)
        Dim dset As DataSet = New DataSet
        Dim cmdAdd As New SqlClient.SqlCommand()
        connstring.Open()
        cmdAdd.Connection = connstring
 
 
        cmdAdd.CommandText = "update username SET username = @username, password = @password, where tbl_user = @user"
 
 
        cmdAdd.ExecuteNonQuery() 'error within this line
 
        adapt.Fill(dset, "tbl_user")
        With DataGridView1
            .DataMember = "tbl_user"
            .DataSource = dset.DefaultViewManager
            .Update()
        End With
        MsgBox("Account has been updated!", MsgBoxStyle.Information, "SUCCESS")

This post has been edited by jcesz12: 27 April 2011 - 10:41 PM

[Elda]

cmdAdd.CommandText = "update username SET username = @username, password = @password, where tbl_user = @user"

Update

"username"?

Is this the name of your table?

I guess it's

"tbl_user"

that you need to update..

[jcesz12]

oh yah i already change that, but still getting this error "Incorrect syntax near the keyword 'where'." help me

[Elda]

Please remove comma at the end of password..

"password = @password, where"

This post has been edited by Elda: 28 April 2011 - 02:23 AM

[CharlieMay]

Also, you're not specifying values for your parameters. Prior to the ExecuteNonQuery you would using something like:
cmdAdd.Parameters.AddWithValue("@username", variablethatholdstheusername)
...repeat for @password & @user

This post has been edited by CharlieMay: 28 April 2011 - 03:54 AM

[jcesz12]

CharlieMay, on 28 April 2011 - 03:52 AM, said:

Also, you're not specifying values for your parameters. Prior to the ExecuteNonQuery you would using something like:
cmdAdd.Parameters.AddWithValue("@username", variablethatholdstheusername)
...repeat for @password & @user

thank you i already add that one. but still getting this error " Incorrect syntax near '-the value when i edit username'"
im sorry like i said in my first post i'm just new in vb.net,i'm just about a week ago of studing vb.net and it driving me crazy please help me and thank you so much for your help i really appreciate it.

Elda, on 28 April 2011 - 02:22 AM, said:

Please remove comma at the end of password..

"password = @password, where"

thank you i already change that one. but still getting this error " Incorrect syntax near '-the value when i edit username'"
im sorry like i said in my first post i'm just new in vb.net,i'm just about a week ago of studying vb.net and it driving me crazy please help me and thank you so much for your help i really appreciate it.

[SurfingShark]

You need to add parameters for each SQL parameter you are using. So, you will have to do the SqlCommand.Parameters.AddWithValue for all of the "@" variables in your SQL query. So you need to do that with "@username", "@password", and "@user".

[jcesz12]

still getting the same error

[SurfingShark]

jcesz12, on 28 April 2011 - 06:42 PM, said:

still getting the same error

Please post where your code is at right now. There have been numerous changes, and to further accurately troubleshoot this I would like to see the revised code that is still throwing the error.

Thanks!

[jcesz12]

Private Sub btnupdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnupdate.Click

        Dim connstring = New SqlClient.SqlConnection("Data Source=MISD-ASBAUTISTA\SQLEXPRESS;Initial Catalog=inventory system;Integrated Security=SSPI;")
        Dim adapt = New SqlClient.SqlDataAdapter("select Username, Password from tbl_user", connstring)
        Dim dset As DataSet = New DataSet
        Dim cmdAdd As New SqlClient.SqlCommand()

        connstring.Open()
        cmdAdd.Connection = connstring

        cmdAdd.CommandText = "UPDATE tbl_user SET username = @user, password = @password where username =@username '" & txtusername.Text & "'"

        cmdAdd.Parameters.AddWithValue("@username", Me.txtusername.Text)
        cmdAdd.Parameters.AddWithValue("@password", Me.txtpassword.Text)
        cmdAdd.ExecuteNonQuery()

        adapt.Fill(dset, "tbl_user")
        With DataGridView1
            .DataMember = "tbl_user"
            .DataSource = dset.DefaultViewManager
            .Update()
            .Refresh()
        End With

        MsgBox("Account has been updated!", MsgBoxStyle.Information, "SUCCESS")

thank you !

[Elda]

Ok. I tried to arrange your codes. try it..

sqlquery = "Update tbl_user Set username=@username,password=@password where user = '" & txtuser.text & "'"
       

 constring = New SqlClient.SqlConnection("Data Source=MISD-ASBAUTISTA\SQLEXPRESS;Initial Catalog=inventory system;Integrated Security=SSPI;")
        cn = New SqlConnection(constring)
        cmdAdd = New SqlCommand(sqlquery, cn)



 cmdAdd.Parameters.Add(New SqlParameter("@user", SqlDbType.NVarChar, 50))
'so on...........

 

cmdAdd.Parameters("@user").Value = txtuser.Text
'so on............


 cn.Open()
        cmdAdd.ExecuteNonQuery()

        cn.Close()
        cmdAdd.Dispose()

         MsgBox("Account has been updated!", MsgBoxStyle.Information, "SUCCESS")

[SurfingShark]

Your commandtext is still off. It has to be:

cmdAdd.CommandText = "UPDATE tbl_user SET username = @user, password = @password where username =@username"

The above posting didn't use a SQL parameter, and whereas you could make that work (although the code still looks bugged), SQL Injection is a big problem, and it would be simple to destroy your data if malicious enough.

You are adding a parameter "@user" when it's not part of the command text.

Elda, on 28 April 2011 - 07:59 PM, said:

Ok. I tried to arrange your codes. try it..

sqlquery = "Update tbl_user Set username=@username,password=@password where user = '" & txtuser.text & "'"
       

 constring = New SqlClient.SqlConnection("Data Source=MISD-ASBAUTISTA\SQLEXPRESS;Initial Catalog=inventory system;Integrated Security=SSPI;")
        cn = New SqlConnection(constring)
        cmdAdd = New SqlCommand(sqlquery, cn)



 cmdAdd.Parameters.Add(New SqlParameter("@user", SqlDbType.NVarChar, 50))
'so on...........

 

cmdAdd.Parameters("@user").Value = txtuser.Text
'so on............


 cn.Open()
        cmdAdd.ExecuteNonQuery()

        cn.Close()
        cmdAdd.Dispose()

         MsgBox("Account has been updated!", MsgBoxStyle.Information, "SUCCESS")

[jcesz12]

SurfingShark, on 28 April 2011 - 08:11 PM, said:

Your commandtext is still off. It has to be:

cmdAdd.CommandText = "UPDATE tbl_user SET username = @user, password = @password where username =@username"

The above posting didn't use a SQL parameter, and whereas you could make that work (although the code still looks bugged), SQL Injection is a big problem, and it would be simple to destroy your data if malicious enough.

thank you ! no more error, but it is not updating..

[SurfingShark]

Use the below code as a guide. Obviously you'll need to modify the variables and certain parts of the code (i.e. connection string), but that is the proper way to execute an UPDATE query to the database. That code was not tested, and you will need to modify, but it is the right idea.

Dim connYourConnection As New SqlConnection("your connection string here")
Dim sqlInsertData As New SqlCommand
sqlInsertData.Connection = connYourConnection

sqlInsertData.CommandText = "UPDATE tbl_user SET username = @user, password = @password where username =@username"

sqlInsertData.Parameters.AddWithValue("@user", youruservariable)
sqlInsertData.Parameters.AddWithValue("@password", yourpasswordvariable)
sqlInsertData.Parameters.AddWithValue("@username", yourusernamevariable)

Try
    connYourConnection.Open()
    sqlInsertData.ExecuteNonQuery()
Catch ex As Exception
    connYourConnection.Dispose()
End Try

connYourConnection.Close()

[jcesz12]

SurfingShark, on 28 April 2011 - 08:50 PM, said:

Use the below code as a guide. Obviously you'll need to modify the variables and certain parts of the code (i.e. connection string), but that is the proper way to execute an UPDATE query to the database. That code was not tested, and you will need to modify, but it is the right idea.

Dim connYourConnection As New SqlConnection("your connection string here")
Dim sqlInsertData As New SqlCommand
sqlInsertData.Connection = connYourConnection

sqlInsertData.CommandText = "UPDATE tbl_user SET username = @user, password = @password where username =@username"

sqlInsertData.Parameters.AddWithValue("@user", youruservariable)
sqlInsertData.Parameters.AddWithValue("@password", yourpasswordvariable)
sqlInsertData.Parameters.AddWithValue("@username", yourusernamevariable)

Try
    connYourConnection.Open()
    sqlInsertData.ExecuteNonQuery()
Catch ex As Exception
    connYourConnection.Dispose()
End Try

connYourConnection.Close()

i'm sorry but it is not updating. thank you for your patience.