[maiOHmai]

i have a datagridview named dgvSearch. it contains some data from the access database such as the ID, and a concat of LName and FName. my problem is that, when i'm using the UPDATE query my database is not updated though my msgbox pop-up a message that it updates successfully.

here is my update code: (Button Click event)

 Dim con As New OleDbConnection
        Dim sql As String
        'Dim i As Integer
        con.ConnectionString = "provider = Microsoft.ACE.OLEDB.12.0; data source = ..\sample.accdb"

       
        sql = "update list set LName = ' " & txtLName.Text & " ' [size="3"][size="5"][b]where ID = (' " & txtID.Text & " '[/b][/size][/size])  ; "
        Dim cmd As OleDbCommand
        Try
            con.Open()
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteScalar()
            MessageBox.Show("Update Successfully")
            con.Close()
            cmd.Dispose()
        Catch ex As OleDbException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Oledb Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

i think that my problem starts on the where condition, because when i removed it the update query works fine, it's just that it updates ALL LName's in my database.

i forgot. i also try this code:

 Dim con As New OleDbConnection
        Dim sql As String
        [b][size="3"]Dim i As Integer[/size][/b]
        con.ConnectionString = "provider = Microsoft.ACE.OLEDB.12.0; data source = ..\sample.accdb"

        'con = New OleDbConnection(con.ConnectionString)
        sql = "update list set LName = ' " & txtLName.Text & " ' where ID = (' " & dgvSearch.Rows(0).Cells("ID").Value & " ')  ; "
        Dim cmd As OleDbCommand
        Try
            con.Open()
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteScalar()
            MessageBox.Show("Update Successfully")
            con.Close()
            cmd.Dispose()
        Catch ex As OleDbException
            MsgBox(ex.Message, MsgBoxStyle.Critical, "Oledb Error")
        Catch ex As Exception
            MsgBox(ex.Message, MsgBoxStyle.Critical, "General Error")
        End Try
    End Sub

i tried this code to call the first column, which is the ID, in my dgvSearch. yet it still doesn't work

This post has been edited by smohd: 18 July 2012 - 04:55 AM
Reason for edit:: Code tags added. Please use [code] tags when posting codes

[Ionut]

you have to use ExecuteNonQuery, not ExecuteScalar

[maiOHmai]

still doesn't work..

[sela007]

Have you noticed the spaces in the string?

sql = "update list set LName = '_" & txtLName.Text & "_' where ID = ('_" & txtID.Text & "_')"

remove spaces and try again:

sql = "update list set LName = '" & txtLName.Text & "' where ID = ('" & txtID.Text & "')"

This post has been edited by sela007: 15 July 2012 - 06:06 AM

[maiOHmai]

tnx.. it works well
but i have another problem, it also concerned the update query.
i have this code:

Imports System.Data.OleDb
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If txtNew.TextLength < 5 Then
            MsgBox("The New Password Should be of Atleast 5 Characters")
            txtNew.Text = ""
            txtRe.Text = ""
        ElseIf txtOld.Text = txtNew.Text Then
            MsgBox("The New Password is Same As Old Password")
            txtNew.Text = ""
            txtNew.Focus()
        ElseIf (txtNew.Text = txtRe.Text) Then
            Try
                Dim con As New OleDbConnection("provider = Microsoft.ACE.OLEDB.12.0; data source = ..\sample.accdb")
                Dim ds1 As New DataSet

                Dim da1 As New OleDbDataAdapter("select * from login where Username='" & txtUserName.Text & "'and Password='" & txtOld.Text & "'", con)
                If da1.Fill(ds1) Then
                    Dim ra As Integer
                    Dim cb As OleDbCommand
                    con.Open()
                    cb = New OleDbCommand("Update login set Password= ('" & txtNew.Text & "') where Username='" & txtUserName.Text & "'", con)
                    ra = cb.ExecuteNonQuery()
                    MessageBox.Show("Password Changed Successfully,Now Login into System")
                    con.Close()
                    'frmLogin.Show()
                    'Me.Hide()
                Else
                    MsgBox("Invalid Password or Username")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

End Class

im using this code to update a password. but an error message appears which says that, "Syntax error in update statement ". what is wrong in my update query? i wonder, because it's the same query im using on my other updates and it works.

please help.. tnx

This post has been edited by smohd: 19 July 2012 - 01:37 AM
Reason for edit:: Please use [code] tags when posting codes

[sela007]

Try without brackets

"UPDATE login SET Password= '" & txtNew.Text & "' WHERE Username='" & txtUserName.Text & "'"

From this I don't see any other possible irregularities.
If this don't work, then try with parameters.
My suggestion is to always use parameters.
If your txtNew or txtUserName contains apostrophe ' (McDonald's) you will get error!

[maiOHmai]

i don't know where to include the parameters. is the code right?

Imports System.Data.OleDb
Public Class Form1

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        If txtNew.TextLength < 5 Then
            MsgBox("The New Password Should be of Atleast 5 Characters")
            txtNew.Text = ""
            txtRe.Text = ""
        ElseIf txtOld.Text = txtNew.Text Then
            MsgBox("The New Password is Same As Old Password")
            txtNew.Text = ""
            txtNew.Focus()
        ElseIf (txtNew.Text = txtRe.Text) Then
            Try
                Dim con As New OleDbConnection("provider = Microsoft.ACE.OLEDB.12.0; data source = ..\sample.accdb")
                Dim ds1 As New DataSet

                Dim da1 As New OleDbDataAdapter("select * from login where FullName = '" & txtUserName.Text & "'and Password ='" & txtOld.Text & "'", con)
                If da1.Fill(ds1) Then
                    Dim ra As Integer
                    Dim cb As OleDbCommand
                    con.Open()
                    cb = New OleDbCommand("Update login set Password = '" & txtNew.Text & "' where FullName = '" & txtUserName.Text & "'", con)
                   
                    ra = cb.ExecuteNonQuery()
                    Dim param1 As New OleDb.OleDbParameter
                    param1.ParameterName = "Password"
                    param1.Value = txtNew.Text()
                    cb.Parameters.Add(param1)
                    MessageBox.Show("Password Changed Successfully,Now Login into System")
                    con.Close()
                    'frmLogin.Show()
                    'Me.Hide()
                Else
                    MsgBox("Invalid Password or Username")
                End If
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try
        End If
    End Sub

End Class

i've include the parameters and remove the parenthesis yet im still getting "Syntax error in update statement". i tried to change the Password column to Username column and it updated.! i think that the error might be on the Password. i've already checked the spelling on the db if they match, and they did..

This post has been edited by smohd: 19 July 2012 - 01:37 AM
Reason for edit:: Code tags added. Please use [code] tags when posting codes

[sela007]

You must add parameters before you call cb.ExecuteNonQuery.
You must set param1.DbType (in your case is string).
If you use parameters then your OleDbCommand.CommandText will look like this

"Update login set Password = @param1 where FullName = @param2".

Special tip: when you use parameters in ACCESS, you must add parameters in the same order as they appear in query.

This post has been edited by sela007: 18 July 2012 - 09:24 AM

[maiOHmai]

this is what i've done:

Dim da1 As New OleDbDataAdapter("select * from login where FullName = '" & txtUserName.Text & "'and Password ='" & txtOld.Text & "'", con)
                If da1.Fill(ds1) Then
                    Dim ra As Integer
                    Dim cb As OleDbCommand
                    con.Open()
                    cb = New OleDbCommand("Update login set Password = @param1 where FullName = @param2", con)
                   
                    Dim param1 As New OleDb.OleDbParameter
                    Dim param2 As New OleDb.OleDbParameter
                    param1.ParameterName = "Password"
                    param1.Value = txtNew.Text()
                    cb.Parameters.Add(param1)

                    param2.ParameterName = "FullName"
                    param2.Value = txtNew.Text()
                    cb.Parameters.Add(param2)

                    ra = cb.ExecuteNonQuery()
                    MessageBox.Show("Password Changed Successfully,Now Login into System")
                    con.Close()
                    'frmLogin.Show()
                    'Me.Hide()
                Else
                    MsgBox("Invalid Password or Username")
                End If

im still getting same error. by the way, what is the use of ParameterName?? and what do you mean by this, "You must set param1.DbType (in your case is string)."

This post has been edited by smohd: 19 July 2012 - 01:38 AM
Reason for edit:: code!!!!!!!!!!!!!!!!!!!!!!!

[maiOHmai]

tnx for your help.. i already solve it. I forget that Password is reserved and all i have to do is put [], like [Password]. anyways.. thank you so much..

[sela007]

Just to know, dbType is database value type, if username field type in your database is text then your parameter dbType will be text (string)