3 Replies - 1175 Views - Last Post: 18 August 2012 - 03:08 PM Rate Topic: -----

#1 lancomi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 17-August 12

trouble with inserting date in access database

Posted 18 August 2012 - 08:46 AM

Dim rs As New ADODB.Recordset
rs.Open "SELECT * FROM accounts WHERE username = '" & Text1 & "'", elie, adOpenStatic, adLockReadOnly
    If rs.RecordCount <> 0 Then
        If Text2 = rs!Password Then
            MsgBox "Login Succesful!", vbInformation, " "
            Text2.Text = ""
            With rsaccounts
            .Fields(0) = Text1.Text
            .Fields(1) = Text2.Text
            elie.Execute ("Insert Into Accounts(LastLogin) Values(#" & Date & "#)")
            .Update
            main.Show
            Unload Me
        Else
            MsgBox "Invalid Password, try again!", vbCritical, " "
            Text2.Text = ""
            Text2.SetFocus
        End If
    Else
        MsgBox "Invalid Login, try again!", vbCritical, " "
        Text1.Text = ""
        Text2.Text = ""
        Text1.SetFocus
    End If
    End If



I got runtime error '-2147467259 (80004005)': index or primary key cannot contain a null value

highlighted part:
elie.Execute ("Insert Into accounts(LastLogin) Values(#" & Date & "#)")


btw my primary key is username,
I'm trying to put the current date onto my lastlogin fields,
thanks for helping :)

Is This A Good Question/Topic? 0
  • +

Replies To: trouble with inserting date in access database

#2 Ionut  Icon User is offline

  • D.I.C Lover
  • member icon

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

Re: trouble with inserting date in access database

Posted 18 August 2012 - 08:54 AM

I think here
elie.Execute ("Insert Into Accounts(LastLogin) Values(#" & Date & "#)") 


you want an update statement.

elie.Execute ("update Accounts set LastLogin = #" & Date & "# where username = " & username) 


Although you should consider a parameterized query.

As the error said, a primary key cannot contain a null value. Your primary key is the username, if you try to insert a new record in your table, you have to specify the user as well.
Was This Post Helpful? 0
  • +
  • -

#3 guyfromri  Icon User is offline

  • D.I.C Addict

Reputation: 46
  • View blog
  • Posts: 829
  • Joined: 16-September 09

Re: trouble with inserting date in access database

Posted 18 August 2012 - 02:54 PM

If it's Access, why not take advantage of the built in DB functions in VB.


rs.AddNew
rs.Fields("LastLogin").Value=Now()
rs.update



That should do the trick :)
Was This Post Helpful? 0
  • +
  • -

#4 lancomi  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 6
  • Joined: 17-August 12

Re: trouble with inserting date in access database

Posted 18 August 2012 - 03:08 PM

thanks guys! its now working :)
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1