"UPDATE cg_security_user_right  SET user_id, right_id ,enable_flag  WHERE LastName= " & tuser.Text & " right_id = " & tright.Text & " enable_flag = " & CheckBox1.Enabled & ""

How can i improve this code thanks in advance

How can i improve this code thanks in advance

Your sql statement is not right

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

Edited 4 Years Ago by Jx_Man: n/a

Comments
nice work

hello !
check this

"UPDATE cg_security_user_right  SET user_id=@user_id, right_id=@right_id ,enable_flag=@enable_flag  WHERE LastName= " & tuser.Text & " right_id = " & tright.Text & " enable_flag = " & CheckBox1.Enabled & ""
'@name they are the variables and you can also replace them with you textboxes or using command parameters to give values.

Reagrds

hey i just got this error

"Must declare the scalar variable "@user_id"."

yes farhan , these are variables , you can replace with them with your controls like this

username = @username 
'--------------
username = txtusername.text

i can better help you if you provide some more code :)

Dim scon As New SqlConnection
        Dim cmd As New SqlCommand

        scon.ConnectionString = ""
        scon.Open() 'open the connection to the database

        cmd.Connection = scon
        cmd.CommandText = " INSERT INTO [cg_security_right] (right_name)VALUES('" & txtRight.Text & "')"

        cmd.ExecuteNonQuery()

        Try
        Catch ex As Exception
            MessageBox.Show("Error while inserting user.")

        End Try
        Me.Cg_security_userTableAdapter.Fill(Me.DataSet.cg_security_user)

Thanks mate really appreciate help

Edited 4 Years Ago by Farhan_B: mistake in sending code

sorry i just realised i given the wrong code

Dim scon As New SqlConnection
        Dim cmd As New SqlCommand

        scon.ConnectionString = ""
        scon.Open() 'open the connection to the database

        cmd.Connection = scon


        cmd.CommandText = "UPDATE cg_security_user_right  SET user_id=@user_id, right_id=@right_id ,enable_flag=@enable_flag  WHERE user_id= " & tuser.Text & " right_id = " & tright.Text & " enable_flag = " & CheckBox1.Enabled & ""
        cmd.ExecuteNonQuery()

        cmd.Dispose() 'Performs application-defined tasks associated with freeing, releasing, or resetting unmanaged resources
        MsgBox("Update Sucessfull")

        Try
        Catch ex As Exception
            MessageBox.Show("Error while updating  record.")

please use this code :)

Try
            Dim con As New SqlConnection("conn-string")
            Dim cmd As New SqlCommand
            con.Open()
            cmd.Connection = con
cmd.commandtext = 
"UPDATE cg_security_user_right  SET user_id=@user_id, right_id=@right_id ,enable_flag=@enable_flag  WHERE user_id= " & tuser.Text & " right_id = " & tright.Text & " enable_flag = " & CheckBox1.Enabled & ""
            cmd.Parameters.AddWithValue("@user_id", txtuserid.text) 'user val(txtuserid.text) if userid is integer
            cmd.Parameters.AddWithValue("@right_id", txtrightid.text) 'user val(txtrightid.text) if userid is integer
            cmd.Parameters.AddWithValue("@enable_flag", checkbox1.checked) 'i assume that you have a chk box 
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try

Best Regards

brother i have a small error with this code

it says " Incorrect syntax near 'right_id'."

yes , sorry i just not read your code carefully , use this code

Try
            Dim con As New SqlConnection("conn-string")
            Dim cmd As New SqlCommand
            con.Open()
            cmd.Connection = con
cmd.commandtext = 
"UPDATE cg_security_user_right  SET user_id=@user_id, right_id=@right_id ,enable_flag=@enable_flag  WHERE user_id= " & tuser.Text & "and right_id = " & tright.Text & " and enable_flag = " & CheckBox1.Enabled & ""
            cmd.Parameters.AddWithValue("@user_id", txtuserid.text) 'user val(txtuserid.text) if userid is integer
            cmd.Parameters.AddWithValue("@right_id", txtrightid.text) 'user val(txtrightid.text) if userid is integer
            cmd.Parameters.AddWithValue("@enable_flag", checkbox1.checked) 'i assume that you have a chk box 
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try

That has solved the syntax error pra but now it saying

"Invalid column name 'True'."

so sorry about this

Use parametreized query:

Dim query As String = "UPDATE cg_security_user_right  SET user_id, right_id ,enable_flag  WHERE LastName = @param1 AND right_id = @param2 AND enable_flag = @param3"
Dim conn As New SqlConnection("connString")
Dim cmd As SqlComamnd = New SqlCommand(query, conn)
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = tuser.Text
cmd.Parameters.Add("@param2", SqlDbType.Int).Value = Integer.Parse(tright.Text)
'check if its not an integer, if its string, do as in line before
cmd.Parameters.Add("@param3", SqlDbType.[Boolean]).Value = CheckBox1.Checked
'?? you want to use TRUE/FALSE - checked/unchecked?
cmd.ExecuteNonQuery()

farhan please use this code , as i assume that enable_flag is bit in your db .

Try
            Dim con As New SqlConnection("conn-string")
            Dim cmd As New SqlCommand
            con.Open()
            cmd.Connection = con
cmd.commandtext = 
"UPDATE cg_security_user_right  SET user_id=@user_id, right_id=@right_id ,enable_flag=@enable_flag  WHERE user_id= " & tuser.Text & "and right_id = " & tright.Text & " and enable_flag = " & CheckBox1.checked & ""
            cmd.Parameters.AddWithValue("@user_id", txtuserid.text) 'user val(txtuserid.text) if userid is integer
            cmd.Parameters.AddWithValue("@right_id", txtrightid.text) 'user val(txtrightid.text) if userid is integer
            cmd.Parameters.AddWithValue("@enable_flag", checkbox1.checked) 'i assume that you have a chk box 
            cmd.ExecuteNonQuery()
            con.Close()
        Catch ex As Exception
            MsgBox(Err.Description)
        End Try

hope this will works fine

the enable _flag is like a yes and no for the users waqas bhai

i still gave the problem
the error says

"Invalid column name 'True'."

Then do:

Dim query As String = "UPDATE cg_security_user_right  SET user_id, right_id ,enable_flag  WHERE LastName = @param1 AND right_id = @param2 AND enable_flag = @param3"
Dim conn As New SqlConnection("connString")
Dim cmd As SqlComamnd = New SqlCommand(query, conn)
cmd.Parameters.Add("@param1", SqlDbType.VarChar, 50).Value = tuser.Text
cmd.Parameters.Add("@param2", SqlDbType.Int).Value = Integer.Parse(tright.Text)
'check if its not an integer, if its string, do as in line before
cmd.Parameters.Add("@param3", SqlDbType.Bit).Value = If(CheckBox1.Checked, 1, 0)
cmd.ExecuteNonQuery()

well , please check your database column names and columns names in your code , and match them , i hope prob will solved , please check them carefully .

Regards

the true error has gone now it become false

"Invalid column name 'False'"

Who voted -1 for me and why?
damn thx, really appreciate it.

About your error: Check the TYPE of your column before we continue. Is it a bit type, is it a varchar, or what?
No need to vote -1, if the error is yours!!!

me want to learn developing a site but my logic is too poor in this field how can me improve this,,,,,,,,,

can anyone spot the syntax error
iv been tryng to find it and i am not having any luck thanks

it says Incorrect syntax near ' & tuser.Text & '."
"

cmd.CommandText = "INSERT INTO cg_security_user_right(user_id, right_id, enable_flag)VALUES "" & tuser.Text & "" & right_id.Text & "" & CheckBox2.Enabled & """

i am having an error which says
"The error i get is : "An expression of non-boolean type specified in a context where a condition is expected, near '_id'" "
my code is

cmd.Connection = con
        cmd.CommandText =
"UPDATE cg_security_user_right  SET user_id=@user_id, user_name=@user_name, right_name=@right_name,enable_flag=@enable_flag  WHERE  user _id " & Val(Cg_security_user_rightDataGridView.Item(0, Cg_security_user_rightDataGridView.CurrentRow.Index).Value.ToString) & " user _name " & Val(Cg_security_user_rightDataGridView.Item(1, Cg_security_user_rightDataGridView.CurrentRow.Index).Value.ToString) & "  right_name = " & Val(Cg_security_user_rightDataGridView.Item(2, Cg_security_user_rightDataGridView.CurrentRow.Index).Value.ToString) & " and enable_flag = '" & Cg_security_user_rightDataGridView.Item(3, Cg_security_user_rightDataGridView.CurrentRow.Index).Value.ToString & "'"

        cmd.Parameters.AddWithValue("@user_id", TextBox3.Text)
        cmd.Parameters.AddWithValue("@user_name", tuser.Text) 'user val(txtuserid.text) if userid is integer
        cmd.Parameters.AddWithValue("@right_name", tright.Text) 'user val(txtrightid.text) if userid is integer
        cmd.Parameters.AddWithValue("@enable_flag", CheckBox1.Checked)

why is it happening

This article has been dead for over six months. Start a new discussion instead.