Improve my code thanks

[Farhan_B]

"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

[Jx_Man]

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

[M.Waqas Aslam]

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

[Farhan_B]

hey i just got this error

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

[M.Waqas Aslam]

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

[Farhan_B]

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

[M.Waqas Aslam]

you always welcome :),bro

[M.Waqas Aslam]

thanks for the reputation :P , i was sad because some one vote me down twice :P thanks

[Farhan_B]

code u send me the modified version of code please

[Farhan_B]

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

[M.Waqas Aslam]

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

[Farhan_B]

brother i have a small error with this code

it says " Incorrect syntax near 'right_id'."

[M.Waqas Aslam]

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

[Farhan_B]

That has solved the syntax error pra but now it saying

"Invalid column name 'True'."

so sorry about this

[Mitja Bonca]

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

[M.Waqas Aslam]

can you please tell me about this field enable_flag what is this , what is the datatype of this field ?

[M.Waqas Aslam]

@Mitja Bonca sir, with due respect you are giving wrong code , please check again ,

[M.Waqas Aslam]

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

[Farhan_B]

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

[Mitja Bonca]

and its y type of boolean? But in your database is type of what? bit? if so, then you need 1 or 0.

[Farhan_B]

i still gave the problem
the error says

"Invalid column name 'True'."

[Farhan_B]

yes in my bd the results show as 1 and 0

[Mitja Bonca]

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

[M.Waqas Aslam]

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

[Farhan_B]

the true error has gone now it become false

"Invalid column name 'False'"

[Mitja Bonca]

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!!!

[sadiaali]

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

[M.Waqas Aslam]

please start new thread sadia , in it discussion forum ,http://www.daniweb.com/community-center/it-professionals-lounge/5 here is a link of it , post your question there ,hope so you will find some helpful tips.

Regards

[Farhan_B]

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

[Farhan_B]

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