[theblackmage]

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

[modi123_1]

What does your update statement look like?

[T3hC13h]

I believe you need to call .GetUpdateCommand() before you call update.

[theblackmage]

Hi, thanks, that worked perfectly

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

[Ionut]

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.

[theblackmage]

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

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