ASP.net SQL update problem (VB)

[icklejez]

Hey guys,

I'm trying to update my database with values in text fields on my .aspx page.

I'm used to java so I'm struggling to figure out whats wrong with this.

Inherits System.Web.UI.Page
Dim conn As SqlConnection
Dim cmd As SqlCommand
Dim dst As DataSet
Dim dad As SqlDataAdapter
Dim strSql As String
Dim strConn As String = "server=xxx\xxx;database=xxx;uid=xxx;pwd=xxx"

Sub updateEmployee()
strSql = String.Format("UPDATE tblemployee SET login='"TxtLogin.Text"', password='"TxtPassword.Text"', statusid='"TxtStatusId.Text"', fullname='"TxtFullname.Text"', branchid='"TxtBranchId.Text"' WHERE employeeid={0}", radioEmp.SelectedItem.Value)
conn = New SqlConnection(strConn)
cmd = New SqlCommand(strSql, conn)
conn.Open()
cmd.ExecuteNonQuery()
End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
dst = New DataSet
strSql = ("select employeeid, fullname from tblEmployee")
conn = New SqlConnection(strConn)
dad = New SqlDataAdapter(strSql, conn)
dad.Fill(dst, "tblemployee")
radioEmp.DataSource = dst
radioEmp.DataTextField = "fullname"
radioEmp.DataValueField = "employeeid"
radioEmp.DataBind()
End Sub

Basically, on page_load, a list of employees is generated and you can select them with radio buttons. Whichever that is selected, i want updating. So ive got some text fields, and whatever is in there, i want updateStaff() to update it. But its underlining my update SQL statement saying this:

sql error

any ideas?

 

[KB]

I would strongly encourage you to look into some ORMs like NHibernate, SubSonic or even LINQ to SQL. They greatly reduce the amount of SQL statements you have to write.

Your sql statment isn't going to work anyway. When you issue that sql statement it is going to change the login name to the literal string "TxtLogin.Text" - it won't change the login name to the value in TxtLogin.text control. To accomplish what you are trying to do you would have to make the SQL statement:

"UPDATE tblemployee SET login=" & TxtLogin.Text & ", password=" & TxtPassword.Text & ", statusid= ...... WHERE employeeid={0}"

The above however is bad practice and open to SQL injection problems.

For your situation I would recommend using parameterized queries instead of String.Format.

strSql = "UPDATE tblemployee SET login=@login, password=@password, statusid=@statusid, fullname=@fullname, branchid=@branchid WHERE employeeid=@employeeid"

cmd = New SqlCommand(strSql, conn)

cmd.Parameters.Add("@login", SqlDbType.VarChar).Value = TxtLogin.Text
cmd.Parameters.Add("@password", SqlDbType.VarChar).Value = TxtPassword.Text
....
cmd.Parameters.Add("@employeeid", SqlDbType.Int32).Value = radioEmp.SelectedItem.Value

conn.Open()
cmd.ExecuteNonQuery()

 

[Pacemaker]

^