Database update error

[darkfreedom]

i want 2 update an access datavase frm my v.net program. d code is below:

[COLOR=#00007f]Public[/COLOR] [COLOR=#00007f]Sub[/COLOR] UserLog_Update([COLOR=#00007f]ByVal[/COLOR] Username [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR], [COLOR=#00007f]ByVal[/COLOR] StatusText [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR])
        [COLOR=#00007f]Try[/COLOR]
            conn.[COLOR=#00007f]Open[/COLOR]()
            [COLOR=#00007f]Dim[/COLOR] sql [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]String[/COLOR] = "SELECT * FROM USER_LOG"
            [COLOR=#00007f]Dim[/COLOR] dbcmd [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]New[/COLOR] OleDbCommand(sql, conn)
            [COLOR=#00007f]Dim[/COLOR] da [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]New[/COLOR] OleDbDataAdapter(dbcmd)
            [COLOR=#00007f]Dim[/COLOR] cb [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]New[/COLOR] OleDbCommandBuilder(da)
            [COLOR=#00007f]Dim[/COLOR] DS [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]New[/COLOR] DataSet("USER_LOG")
[COLOR=#007f00]            'Dim DT As New DataTable
[/COLOR]            da.Fill(DS, "USER_LOG")
            [COLOR=#00007f]Dim[/COLOR] newLogRow [COLOR=#00007f]As[/COLOR] DataRow = DS.Tables("USER_LOG").NewRow
            [COLOR=#00007f]Dim[/COLOR] countr [COLOR=#00007f]As[/COLOR] [COLOR=#00007f]Integer[/COLOR] = DS.Tables("USER_LOG").Rows.Count
            newLogRow.[COLOR=#00007f]Item[/COLOR]("Index") = countr + 1
            newLogRow.[COLOR=#00007f]Item[/COLOR]("UserId") = Username
            newLogRow.[COLOR=#00007f]Item[/COLOR]("UserStatus") = StatusText
            newLogRow.[COLOR=#00007f]Item[/COLOR]("TimeStamp") = System.DateTime.[COLOR=#00007f]Now[/COLOR]
            DS.Tables("USER_LOG").Rows.[COLOR=#00007f]Add[/COLOR](newLogRow)
            DS.AcceptChanges()
[COLOR=#007f00]            'Saving to database
[/COLOR]            da.UpdateCommand = cb.GetInsertCommand
            da.Update(DS, "USER_LOG")
            conn.[COLOR=#00007f]Close[/COLOR]()
        [COLOR=#00007f]Catch[/COLOR] ex [COLOR=#00007f]As[/COLOR] Exception
            MessageBox.Show(ex.ToString, "Error Encountered")
        [COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Try[/COLOR]
[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR]

Everything works fine but nothing is updated in the database for real!!!!
pls help

 

[vis781]

Remove this line...

DS.AcceptChanges()

Secondly, you don't need to explicitly open and close the connection as the dataadapter will do this for you. Also if you do ever have to open the connection explicitly the only do it when you need to and not before and close it straight away.

 

[darkfreedom]

I did the following changes and im getting an error.

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] UserLog_Update([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Username [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] StatusText [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] conn.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#008000]'conn.Open()[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT Index, UserId, UserStatus, [TimeStamp] FROM USER_LOG"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dbcmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(sql, conn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(dbcmd)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommandBuilder(da)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DS [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet("USER_LOG")[/SIZE]
[SIZE=2]da.Fill(DS, "USER_LOG")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] newLogRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = DS.Tables("USER_LOG").NewRow[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] countr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = DS.Tables("USER_LOG").Rows.Count[/SIZE]
[SIZE=2]newLogRow.Item("Index") = countr + 1[/SIZE]
[SIZE=2]newLogRow.Item("UserId") = Username[/SIZE]
[SIZE=2]newLogRow.Item("UserStatus") = StatusText[/SIZE]
[SIZE=2]newLogRow.Item("TimeStamp") = System.DateTime.Now[/SIZE]
[SIZE=2][COLOR=#008000][/COLOR][/SIZE]
[SIZE=2]DS.Tables("USER_LOG").Rows.Add(newLogRow)[/SIZE]
[SIZE=2][COLOR=#008000]'Saving to database[/COLOR][/SIZE]
[SIZE=2]da.Update(DS, "USER_LOG")[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.ToString, "Error Encountered")[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

I'm getting exception at da.Update(DS, "USER_LOG") saying "Syntax error in INSERT INTO statement. Where am i wrong now!!

 

[vis781]

Since the INSERT INTO statement has been autogenerated by the command builder object it is difficult to say. So I need to have a look at it. But before we do that I have a sneaking suspicion that the problem is a 'Keyword' issue. By that i mean that i think you have named one of your columns a word that is reserved, that word I think is 'Index' So just change the column name to IndexColumn or something like that.

 

[darkfreedom]

Ok, i'll do dat and update!!

 

[darkfreedom]

[SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] UserLog_Update([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] Username [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] StatusText [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] conn.State = ConnectionState.Open [/SIZE][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "SELECT * FROM USER_LOG"[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dbcmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommand(sql, conn)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] da [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbDataAdapter(dbcmd)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cb [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] OleDbCommandBuilder(da)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DS [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] DataSet("USER_LOG")[/SIZE]
[SIZE=2]da.Fill(DS, "USER_LOG")[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] newLogRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = DS.Tables("USER_LOG").NewRow[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] countr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][SIZE=2] = DS.Tables("USER_LOG").Rows.Count[/SIZE]
[SIZE=2]newLogRow.Item("IndexCol") = countr + 1[/SIZE]
[SIZE=2]newLogRow.Item("UserId") = Username[/SIZE]
[SIZE=2]newLogRow.Item("UserStatus") = StatusText[/SIZE]
[SIZE=2]newLogRow.Item("DateTimeInfo") = System.DateTime.Now[/SIZE]
[SIZE=2]DS.Tables("USER_LOG").Rows.Add(newLogRow)[/SIZE]
[SIZE=2][COLOR=#008000]'Saving to database[/COLOR][/SIZE]
[SIZE=2]da.Update(DS, "USER_LOG")[/SIZE]
[SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE]
[SIZE=2]MessageBox.Show(ex.ToString, "Error Encountered")[/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]

Changed the field name! Works now, Thanks!!!

 

[cjard]

By the way.. downloading the entire contents of the log table just so you can add a row isnt a very sensible idea really.. I dont generally load my entire hard disk into memory just to add a new file