Member Avatar for blackviper04

OK I have an assignment on my Access course, I find this site really good and require some help if possible.

Basically I have a login screen which checks the Access Database I am using the code I have is this:

Username = txtUsername.Text
Password = txtPassword.Text

'Following code connects the program to the access database.
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\Users\BlackVIPER09\Documents\Visual Studio 2008\Projects\Primary School Maths Test\PrimaryMaths.mdb"

'This is the program opening the database, checking the users login and then closes the database.

connection.Open()
sql = "SELECT * FROM MathsTest WHERE LoginName = '" & Username & "' AND Password= '" & Password & "'"

adaptor = New OleDb.OleDbDataAdapter(sql, connection)
adaptor.Fill(ds, "TestInfo")
connection.Close()

OK next is the problem I am having, the above code is working fine. BUT when the user has completed a test and I want to apply data into the database under the person that has logged in how do I do this? As I understand I need to use UPDATE so have written the following code in the place I need to add the persons test results:

'Connection to the Database
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source =C:\Users\BlackVIPER09\Documents\Visual Studio 2008\Projects\Primary School Maths Test\PrimaryMaths.mdb"

'This is to insert a new user and password into the database

command.Connection = connection
command.CommandText = "UPDATE MathsTest" & "(Easy_Time, Easy_Date)" & "VALUES(@EasyTime, @EasyDate)"
command.Parameters.AddWithValue("@EasyTime", lblDate.Text)
command.Parameters.AddWithValue("@Password", lblTimer.Text)
connection.Open()
command.ExecuteNonQuery()
connection.Close()

I think the code is right but, tell me if I am wrong I am ment to set a position for the user when they login?? Please help I have tried to research this and not finding a thing.

Any code would be appreciated!!

Edited by kvprajapati because: Add [code] tags. Encase your code in: [code] and [/code] tags.
  • 2 Contributors
  • 1 Reply
  • 162 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by Smith5646
Member Avatar for Smith5646

From the code posted, I think you are trying to update the record you previously read, correct? If so, the syntax you need is something like:

UPDATE MathsTest
SET EasyTime = @EasyTime, EasyDate = @EasyDate
WHERE LoginName = '@Username' AND Password= '@Password'

and don't forget to also pass the Username and Password.

This also assumes there is only one row for the Username / Password. If there are multiple rows (as in multiple tests) then you will probably need to add additional criteria to the where clause to specify which row (test) to update.

There is also a way to specify on the select that it is for update but that is a little beyond my knowledge (and not sure if it works in Access). I always specify the where clause when I do updates just in case I read another record and forgot.

Edited by Smith5646 because: n/a
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.