how to update a data user using vb.net2008 and sql server

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 4629 Views - Last Post: 29 April 2011 - 06:12 PM Rate Topic: -----

#1 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

how to update a data user using vb.net2008 and sql server

Posted 27 April 2011 - 10:39 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: how to update a data user using vb.net2008 and sql server

#2 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: how to update a data user using vb.net2008 and sql server

Posted 27 April 2011 - 11:02 PM

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..
Was This Post Helpful? 1
  • +
  • -

#3 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 01:39 AM

oh yah i already change that, but still getting this error "Incorrect syntax near the keyword 'where'." help me >.<
Was This Post Helpful? 0
  • +
  • -

#4 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 02:22 AM

Please remove comma at the end of password..
"password = @password, where"

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

Was This Post Helpful? 1
  • +
  • -

#5 CharlieMay  Icon User is online

  • This space intentionally left blank
  • member icon

Reputation: 1623
  • View blog
  • Posts: 5,199
  • Joined: 25-September 09

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 03:52 AM

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

Was This Post Helpful? 1
  • +
  • -

#6 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 05:16 PM

View PostCharlieMay, 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. ^_^

View PostElda, 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. ^_^
Was This Post Helpful? 0
  • +
  • -

#7 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 05:51 PM

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".
Was This Post Helpful? 0
  • +
  • -

#8 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 06:42 PM

still getting the same error :(
Was This Post Helpful? 0
  • +
  • -

#9 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 07:23 PM

View Postjcesz12, 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!
Was This Post Helpful? 0
  • +
  • -

#10 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 07:34 PM

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 !
Was This Post Helpful? 0
  • +
  • -

#11 Elda  Icon User is offline

  • D.I.C Regular

Reputation: 31
  • View blog
  • Posts: 314
  • Joined: 30-December 10

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 07:59 PM

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")

Was This Post Helpful? 0
  • +
  • -

#12 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 08:11 PM

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.

View PostElda, 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")

Was This Post Helpful? 0
  • +
  • -

#13 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 08:22 PM

View PostSurfingShark, 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..
Was This Post Helpful? 0
  • +
  • -

#14 SurfingShark  Icon User is offline

  • D.I.C Head

Reputation: 38
  • View blog
  • Posts: 188
  • Joined: 18-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 08:50 PM

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()


Was This Post Helpful? 0
  • +
  • -

#15 jcesz12  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 14
  • Joined: 27-April 11

Re: how to update a data user using vb.net2008 and sql server

Posted 28 April 2011 - 10:36 PM

View PostSurfingShark, 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.
Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2