Adding records

Hi, what I need to do now is add records to a database table from textboxes. Once I type a something on a text box and click an add button I want it to add whatever is typed on the textbox into the database and update it. I know this sounds really simple but its not working. I am trying to use addnew but it gives me an error. I am using an ADO connection and the recordset is called rstTable (just in case). Any help? thanks

can you post your code so we can have a look at it?

Here is the code to connect the recordset:

Dim rstPlayers As New Recordset
rstPlayers.ActiveConnection = Cnn
rstPlayers.CursorType = adOpenStatic
rstPlayers.LockType = adLockOptimistic
rstPlayers.Open "Players", , , , adCmdTable

This is my code for adding records (which gives me an error):

rstPlayers.AddNew
rstPlayers.Fields("Name") = txtName.Text
rstPlayers.Fields("Password") = txtPassword.Text
rstPlayers.Update
Else
MsgBox ("Passwords do not match, please try again"), vbInformation, "Passwords"

The connection works fine. But it woin't let me add records.

Thanks

what error does it give you, and on what line?

It gives me Run time error 91:

Object Variable or with block variable not set

On the rstPlayers.AddNew line.

that error is occurring because the rstPlayers recordset object is not yet set.

try this to open your recordset object eg:


Set rst = New ADODB.Recordset
rst.Open "TableName", cnn, adOpenStatic, adLockOptimistic, adCmdTable

Thanks, that worked. But now, how do I make the program detect if a user name repeats in the table? Say a user called "User1" is in the table. How do I make the program stop the user from creating another user name called "User1" since it is already in the database table. Thanks.

Several potential ways, here are two

1) Do a select count(*) query....if the aliased return is > 0 then this records already exists

2) Simply do a standard query with a where clause passing in the name. If not eof (or records returned with this condition) you know it already exists.

Could you give me a sample code for this? Thanks.

open a recordset based on the query eg:

SELECT * FROM [TableName] WHERE User = 'User1'

then if the recordset EOF property is false then you know that User1 is already in the table.

OK, I tried a lot of things but it still hasnt worked. Here is my code:

Dim strNewPlayer As String
'Creates a new player.
If txtConfirm.Text = txtPassword.Text Then
strNewPlayer = ""
strNewPlayer = "SELECT * FROM Players WHERE Name = txtName"
If rstPlayers.EOF = False Then
MsgBox ("Player name already exists, please enter another name"), vbInformation, "Player already exists"
ElseIf rstPlayers.EOF = True Then
rstPlayers.AddNew
rstPlayers.Fields("Name") = txtName.Text
rstPlayers.Fields("Password") = txtPassword.Text
rstPlayers.Update
End If
Else
MsgBox ("Passwords do not match, please try again"), vbInformation, "Passwords"
txtPassword = ""
txtConfirm = ""
txtPassword.SetFocus
End If

The problem i am getting is that it is still adding records even if they are duplicate and it gives me the msgbox about the existing records regardless if the record exists or not.

Any help will be appreciated

Thanks

Change your code like so:


Dim strNewPlayer As String
'Creates a new player.
If txtConfirm.Text = txtPassword.Text Then
strNewPlayer = ""
'***** change the following line - it is missing the double quote delimiters
strNewPlayer = "SELECT * FROM Players WHERE Name = " & chr(34) & txtName & chr(34)
'***** your condition needs to check if the recordset contains records
If NOT rstPlayers.EOF = False Then
MsgBox ("Player name already exists, please enter another name"), vbInformation, "Player already exists"
'you don't need to use rstPlayers.EOF = True just use:
ElseIf rstPlayers.EOF Then
rstPlayers.AddNew
rstPlayers.Fields("Name") = txtName.Text
rstPlayers.Fields("Password") = txtPassword.Text
rstPlayers.Update
End If
Else
MsgBox ("Passwords do not match, please try again"), vbInformation, "Passwords"
txtPassword = ""
txtConfirm = ""
txtPassword.SetFocus
End If

OK, it is still not working. This time it adds records regardless if they duplicate or not and it doesnt show me the msgbox at all now.

ahh of course!! you need to open a recordset object based on the SQL string in your code:

strNewPlayer = "SELECT * FROM Players WHERE Name = " & chr(34) & txtName & chr(34)

I dont see any difference between that line and the previous one. :confused:

what I mean is, you need to open your rstPlayers recordset like so:


strNewPlayer = "SELECT * FROM Players WHERE Name = " & chr(34) & txtName & chr(34)

Set rstPlayers = New ADODB.Recordset
rstPlayers.Open strNewPlayer, cnn

I tried that and its giving me an error coz the recordset is already open. :-\

well if it says the recordset is already open, what do you think you should do? close the recordset first.

Once again this is my connection code:

Private Sub Form_Load()
Set rstPlayers = New Recordset
rstPlayers.ActiveConnection = Cnn
rstPlayers.CursorType = adOpenDynamic
rstPlayers.LockType = adLockOptimistic
rstPlayers.Open "Players", , , , adCmdTable

The connection works fine so I have no problems with that. Now this is my code for adding a new record:

Dim strNewPlayer As String
'Creates a new player.
If txtConfirm.Text = txtPassword.Text Then
strNewPlayer = ""
strNewPlayer = "SELECT * FROM Players WHERE Name = " & Chr(34) & txtName & Chr(34)
If Not rstPlayers.EOF = False Then
MsgBox ("Player name already exists, please enter another name"), vbInformation, "Player already exists"
ElseIf rstPlayers.EOF Then
rstPlayers.AddNew
rstPlayers.Fields("Name") = txtName.Text
rstPlayers.Fields("Password") = txtPassword.Text
rstPlayers.Update
MsgBox ("Player has been added"), vbInformation, "Player added"
End If
Else
MsgBox ("Passwords do not match, please try again"), vbInformation, "Passwords"
txtPassword = ""
txtConfirm = ""
txtPassword.SetFocus
End If

This code does not do anything though. It just gives me the msgbox saying record already exists regardless of what i enter and it doesnt add anything to the database. However, when I exclude the query, it does add the record. All I want it to do is to stop me from adding duplicate records. Sorry I tried doing all you told me but it's not working.

Thanks

you need to open your recordset based on the query string:


Private Sub Form_Load()

Dim strNewPlayer As String

'Creates a new player.
If txtConfirm.Text = txtPassword.Text Then

strNewPlayer = "SELECT * FROM Players WHERE Name = " & chr(34) & txtName & chr(34)

Set rstPlayers = New ADODB.Recordset
rstPlayers.Open strNewPlayer, cnn

If Not rstPlayers.EOF = False Then
MsgBox ("Player name already exists, please enter another name"), vbInformation, "Player already exists"
ElseIf rstPlayers.EOF Then

rstPlayers.Close

rstPlayers.Open "Players", cnn

rstPlayers.AddNew
rstPlayers.Fields("Name") = txtName.Text
rstPlayers.Fields("Password") = txtPassword.Text
rstPlayers.Update
MsgBox ("Player has been added"), vbInformation, "Player added"
End If
Else
MsgBox ("Passwords do not match, please try again"), vbInformation, "Passwords"
txtPassword = ""
txtConfirm = ""
txtPassword.SetFocus
End If


you may have to experiment with the rstPlayers.Open parameters to get it to work

Hey, I finally got it to wrok. I just had to create two separate recordsets, one for checking if there were duplicate records and another one for adding those records. Thanks so much for your help. :)