5 Replies - 1056 Views - Last Post: 13 March 2011 - 04:30 PM Rate Topic: -----

#1 theblackmage  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 21-January 10

problem updating a database

Posted 08 March 2011 - 06:47 AM

Hiya, for my A2 computing project, I need to get my program to interface with a database. Now I'm having a problem in updating records on the database. Here's what I've got so far.
    Sub LockProgram()
        'this updates the program to be locked, to prevent peoples trying to force their way in.
        'it is simply some basic validation and an update statement on the database
        If loginAttemptsToday >= 5 Then
            dataSet.Clear()
            locked = True
            loginAttemptsToday = 0
            SQL = "SELECT * FROM Admin"
            DataAdapter = New OleDb.OleDbDataAdapter(SQL, con)
            commandBuilder = New OleDb.OleDbCommandBuilder(DataAdapter)
            con.Open()
            DataAdapter.Fill(dataSet, "Admin")
            dataSet.Tables("Admin").Rows(0).Item("Locked") = 0
            DataAdapter.Update(dataSet, "Admin")
            con.Close()
            determineLocked()
        End If
    End Sub


My program will read from a database perfectly, it will delete and add rows, but it simply will not update

thanks for any replies

Is This A Good Question/Topic? 0
  • +

Replies To: problem updating a database

#2 modi123_1  Icon User is offline

  • Suitor #2
  • member icon



Reputation: 9207
  • View blog
  • Posts: 34,589
  • Joined: 12-June 08

Re: problem updating a database

Posted 08 March 2011 - 08:43 AM

What does your update statement look like?
Was This Post Helpful? 1
  • +
  • -

#3 T3hC13h  Icon User is offline

  • D.I.C Regular

Reputation: 65
  • View blog
  • Posts: 337
  • Joined: 05-February 08

Re: problem updating a database

Posted 08 March 2011 - 09:54 AM

I believe you need to call .GetUpdateCommand() before you call update.
Was This Post Helpful? 1
  • +
  • -

#4 theblackmage  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 21-January 10

Re: problem updating a database

Posted 13 March 2011 - 05:02 AM

Hi, thanks, that worked perfectly :lol:

however, now I've run into another problem in the same program. I've got my locking/unlocking functions working, but now something thats an adaptation of previous code dosen't work. this is my code that's returning an error:

SQL = "SELECT * FROM UserMarks, Quiz, Homework WHERE UserClassess.Username = '" + username + "' AND Homework.ClassID = UserClassess.ClassID" + _
 " AND HomeworkQuizz.HomeworkID = Homework.HomeworkID AND Quiz.QuizID = HomeworkQuizz.QuizID AND UserMarks.Username = UserClassess.Username AND" + _
        " UserMarks.QuizID = Quiz.QuizID AND UserMarks.result < 1"
        dataSet.Clear()
        DataAdapter = New OleDb.OleDbDataAdapter(SQL, con)
        con.Open()
        DataAdapter.Fill(dataSet, "Homework")
        con.Close()


which was adapted strait from

        SQL = "SELECT * FROM Admin"
        dataSet.Clear()
        DataAdapter = New OleDb.OleDbDataAdapter(SQL, con)
        con.Open()
        DataAdapter.Fill(dataSet, "Admin")
        con.Close()


but the second one works, the first one dosen't. The error I'm given sates that an 'OLEDB exception was unhandeled' and 'No value given for one or more required parameters.', hanging from the fill line

thanks for any replies. I'm majorly sucking with databases :/
Was This Post Helpful? 0
  • +
  • -

#5 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 385
  • View blog
  • Posts: 1,057
  • Joined: 17-July 10

Re: problem updating a database

Posted 13 March 2011 - 05:36 AM

Do you use Access database? If so, Access sees every unknown column as a parameter. In your select you make a join between three tables, but you also reference a 4th table: HomeworkQuizz. Put that in from section of your statement. I would advice you to first create the query in the database query designer and then put in your code.
Was This Post Helpful? 1
  • +
  • -

#6 theblackmage  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 46
  • Joined: 21-January 10

Re: problem updating a database

Posted 13 March 2011 - 04:30 PM

thanks for that, didn;t know that as I learnt using mySQL, which dosen't have that restriction :oops:

I'd love to just use the access query builder, but the guy who stands at the head of class and moans alot has insisted we write all our queries in SQL code on the project
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1