How do I Write Values into my Database

[stephenm1]

Hi guys,

I'm having some trouble with linking in an Access 2003 database which i have created into Visual Basic 6.

I've created a game, so far i have it successfully writing the username to the database, but i cannot for the life of me figure out how to get it to also write the users scores to the database (win, lose and played).
These values are stored and updated by the program each time the user plays a game and I want to be able to write the value into the database under the current username.

I am new to VB so please dont assume i know anything quite advanced.

I have so far come up with the following code:

Dim cn As Connection

Public Sub Form_Load()
  
    Set cn = New Connection
    Dim Rs As New Recordset
    
    ChDir (App.Path)
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Hangman.mdb"
    
    Rs.Open "[user]", cn, adOpenKeyset
    Rs.MoveFirst

    strUsername = InputBox("Please enter a username.", "UserName", Rs.Fields("Username"))
    
    If strUsername = "" Then
        Rs.Close
        Set Rs = Nothing
        
        Unload Me
        Exit Sub
    End If
    
    Rs.Find "Username = '" & strUsername & "'"
    
    If Rs.EOF Or Rs.BOF Then
        cn.Execute ("INSERT INTO [user] (Username) VALUES ('" & strUsername & "')")
        Rs.Requery
        Rs.Find "Username = '" & strUsername & "'"
    End If
    
    Rs.Close
    Set Rs = Nothing

End Sub

This section seems to be functioning fine as the username is being writen to the database.

Private Sub cmdHint_Click()
     
    Set cn = New Connection
    Dim Rs As New Recordset
    
    ChDir (App.Path)
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Hangman.mdb"

    iHints = iHints + 1
    
    
    Rs.Filter = "Username ='" & strUsername & "'"
    cn.Execute ("INSERT INTO [user] (Hints) VALUES ('" & iHints & "')")

End Sub

This is so far what I've come up with, this should add 1 to the "Hints" field each time the user select the hint button.
Once I get the correct syntax for this section i will extend it to keep track of wins/loses/played aswell.


I have looked extensively elsewhere for snippets of code to guide me in the right direction but have found nothing of use, I hope you will be able to assist me :)

Many thanks,
Stephen

[Mariostg]

What variable type is Hints? If it is numeric, remove the single quotes from your sql statement:

cn.Execute ("INSERT INTO [user] (Hints) VALUES (" & iHints & ")")

Use quotes only for text fields.

[stephenm1]

Thanks for the reply :)

I've just ran through the program again. It is now successfully writing the hint values, which was numerical yes :), into the field.
However what is happening is that it is creating a new record with ID = 0.
The program plays as expected first time and then it will hang the second time round since there is no new records which can be made.

Any suggestions as to how i can get it to write the value into the record where the name is the same as the username who is currently playing. I thought the following line would have fixed this issue, it appears not :)

 Rs.Filter = "Username ='" & strUsername & "'"

[Mariostg]

I was going to say something about your line 12 in my previous post but I refrained. The filter does nothing really to alter the behaviour of your line 13. If you want to insert values for a given username, you have to use a WHERE clause in your sql statement.

dim sql as string
sql="INSERT INTO [user] (Hints)" & _
   " VALUES (" & iHints & ")" & _
   " WHERE username='" & strUsername & "'"
sn.Execute sql

Now I don't know about your ID=0. There is not enough details about your table and its design. But I suspect you have an ID field which has a default value of 0

[stephenm1]

Hey again,

I've added in your code to the sub now,

Private Sub cmdHint_Click()
    Set cn = New Connection
    Dim Rs As New Recordset
    Dim sql As String

    ChDir (App.Path)
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Hangman.mdb"

    iHints = iHints + 1

    sql = "INSERT INTO [user] (Hints)" & _
          " VALUES (" & iHints & ")" & _
          " WHERE username='" & strUsername & "'"

    cn.Execute sql

    Hint = MsgBox(strHint, vbOKOnly, "Hint")
End Sub

It is now hitting me with the following error: Missing semicolon (;) at end of SQL statement. Highlighting line 5 of your solution.
I've googled the error in the hope i could work around without bothering you again and I've found a page where one of the users suggests " you can't use VALUES in conjunction with WHERE" -

** SNIP **

Do you know if this is the case or not?

[Mariostg]

Of course, my bad. Not enough coffee this morning. You cannot specify a where clause when you insert because the data is not there yet...
What you are probably looking at is an UPDATE statement to update the hints for a given user.

[Mariostg]

And this is what I mean:

SQL = "UPDATE [user] " & _
	" SET hints=" & iHints & _
	" WHERE username = '" & strUsername & "'"

This of course assumes the user already exist in the table.

[NeoPa]

I'm going to move this to the VB forum as this isn't an Access question.

Before I do so though, let me just say that UPDATE is indeed the SQL command to use. This can add missing values as well as update existing ones (See How to Update and Append Records in One Update Query), so it's not even necessary to create a record first if it doesn't exist.

[stephenm1]

Databases can be very frustrating :/

The program runs without hanging each time now, seemingly as required, however the actual value within the database is not being updated at all. I include my code for the sub and a screen dump of the database view in the hope it can be of use to you.

Private Sub cmdHint_Click()
     
    Set cn = New Connection
    Dim Rs As New Recordset
    Dim sql As String
    
    ChDir (App.Path)
    cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\Hangman.mdb"

    iHints = iHints + 1
    
    sql = "UPDATE [user] " & _
          " SET hints=" & iHints & _
          " WHERE username = '" & strUsername & "'"
    
   Set Rs = cn.Execute(sql)
   Set Rs = Nothing
    
    Hint = MsgBox(strHint, vbOKOnly, "Hint")

End Sub

Many thanks once again :)

http://imageshack.us/photo/my-images/641/accessg.jpg/

[NeoPa]

iHints is never set properly (and is unrequired anyway).

Try for line #13 :

          " SET [hints]=[hints]+1" & _

[Mariostg]

is strUsername set correctly.
Try Debug.Print strUsername and check the debug window (Ctrl-G)

[stephenm1]

Thank you very much Mario!
it was indeed an issue with strUsername which i would have never even noticed if you didnt introduce me to debugging :D

I must have forgot to put strUsername as a Dim at the top of the form when i decided to move the function in from another form to allow for a smoother running.

The Database is now updating as required and i am now in the process on extending this for the win/lose/played values too.

Many thanks once again, that was beginning to get a real headache for me!

[Mariostg]

Excellent for you :)
Debugging is the first thing to learn, no matter the programming language. More time is spent reading code than actually writing it.

Another observation. It seems like you do not have Option Explicit enabled by default. You definitely want that if there is such a way to do this in whatever VB editor you use. In MS Access 2003 VBA editor, it is under Tools->Options->Editor->Require variable declaration.

[NeoPa]

I'm not sure if you'll ignore me again Stephen, but here are some links you should look at :

1. When Posting (VBA or SQL) Code.
2. How to Debug SQL String.

[stephenm1]

If your talking about your advice with the iHints(line 13) it was not ignored at all, it was a flaw i never even realised as with my method the new value would overwrite the current value if that user has played before - which i did not want. So indeed many thanks for spotting it, i simply failed to mention it in my last post as the code still wasn't working and i was more interested in finding the fault :)
mario simply led me in the right direction before i could give you credit :)

Thanks for the links, i will be giving them a thorough read.

Thanks again guys :D