-214 type errors (VB6)

[Night]

Ok, I keep getting an error number like

-2147467259

and everytime I do something to fix it I get another error with that same number with a different reason. I assume these are trappable errors. However, when I try to trap the error I get a runtime error in my line of code where it says

err.Raise err.Source, err.Number...etc...

Just curious what you all know about it. Thakns.

[charlotte]

You're probably triggering ADO errors. You'll need to trap them separately because ADO errors turn up in bunches as members of the connection's errors collection. You have to use a For Each to loop through them. Here's how I typically error trap with ADO:

1. In the declarations, I usually dim these objects:<pre> Dim errsCnn As ADODB.Errors
Dim errCurr As ADODB.Error

Set errsCnn = cnn.Errors</pre>

You don't absolutely need the errsCnn variable, but it makes the code easier to read.

2. A typical error handler in my code looks like this:<pre> If errsCnn.Count > 0 Then
For Each errCurr In errsCnn
MsgBox errCurr.Number & "--" & errCurr.Description
Next errCurr
errsCnn.Clear
Else
Msgbox err.Number & "--" & err.Description
End If
Resume Proc_exit</pre>

You can't raise the collection, so if you want to use Raise you'll have to do something like this:
<pre> errsCnn(0).Raise ...</pre>

Keep in mind also that the ADO errors aren't cleared by a Resume because they belong to the connection rather than the routine.

[Night]

Actually I alread have a trap like that. Here is my code

Private m_recLogin As ADODB.Recordset
Private mcnn As ADODB.Connection

Private Const UPDATE_CANCELLED As Long = -2147217842
Private Const ERRORS_OCCURRED As Long = -2147217887

Private Sub cmdOK_Click()

'Validate Passwords
'Makes sure the password in both boxes are the same
If txtPassword2.Text = txtPassword1.Text Then
MoveToRecord (adDoAddNew)
Else
MsgBox "Invalid Password.", vbExclamation + vbOKOnly
txtPassword1.SetFocus
End If

End Sub

Private Sub Form_Load()

Set m_recLogin = datLogin.Recordset
Set mcnn = m_recLogin.ActiveConnection

End Sub

Private Sub MoveToRecord(intDirection As String)

On Error GoTo MoveToRecord_Err

Select Case intDirection

'Adds User name and Password to Database
Case adDoAddNew
m_recLogin.AddNew "SOID", txtAlias

End Select

MoveToRecord_Exit:
frmTotals.Show
Exit Sub

MoveToRecord_Err:
If mcnn.Errors.Count > 0 Then
For Each errCurrent In mcnn.Errors
MsgBox errCurrent.Number & "--" & _
errCurrent.Description
Next errCurrent
mcnn.Errors.Clear
Else
Select Case Err.Number
Case UPDATE_CANCELLED, ERRORS_OCCURRED
'Do Nothing
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
End If
Resume MoveToRecord_Exit

End Sub

I assume what I did with the errCurrent is what you are talking about.

[Night]

Well I actually got rid of all my errors. Now the only problem I have is that when I enter the New User and Password it writes to the database perfectly. Problem is that it never moves down to the next field when I run it again. It just writes over what was entered the last time. Thats not good. Here is my code as of now. No errors are present.

Private m_recLogin As ADODB.Recordset
Private mcnn As ADODB.Connection

Private Const UPDATE_CANCELLED As Long = -2147217842
Private Const ERRORS_OCCURRED As Long = -2147217887

Private Sub cmdOK_Click()

'Validate Passwords
'Makes sure the password in both boxes are the same
If txtPassword2.Text = txtPassword1.Text Then
MoveToRecord (adRsnAddNew)
Else
MsgBox "Invalid Password.", vbExclamation + vbOKOnly
txtPassword1.SetFocus
End If

End Sub

Private Sub Form_Load()

txtAlias = ""
txtPassword1 = ""
txtPassword2 = ""
Set m_recLogin = datLogin.Recordset
Set mcnn = m_recLogin.ActiveConnection

End Sub

Private Sub MoveToRecord(intDirection As String)

Dim errCurrent As ADODB.Error

On Error GoTo MoveToRecord_Err

Select Case intDirection

'Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.Update "SOID", txtAlias
m_recLogin.Update "Password", txtPassword2


End Select

MoveToRecord_Exit:
frmTotals.Show
Exit Sub

MoveToRecord_Err:
If mcnn.Errors.Count > 0 Then
For Each errCurrent In mcnn.Errors
MsgBox errCurrent.Number & "--" & _
errCurrent.Description
Next errCurrent
mcnn.Errors.Clear
Else
Select Case Err.Number
Case UPDATE_CANCELLED, ERRORS_OCCURRED
'Do Nothing
Case Else
Err.Raise Err.Number, Err.Source, Err.Description
End Select
End If
Resume MoveToRecord_Exit

End Sub

Thanks for any help

[charlotte]

Yep. Some ADO errors also trigger a DAO error like the ones you've been getting, but you need to get at the specifics from the ADO errors collection.

[charlotte]

<hr>Problem is that it never moves down to the next field when I run it again. It just writes over what was entered the last time<hr>

I don't understand. Are you saying that it doesn't add a new record the next time? I don't see any .AddNew in your MoveToRecord code, so I can't tell if you missed that or just didn't post the code to create the new record.

<hr>m_recLogin.Update "SOID", txtAlias
m_recLogin.Update "Password", txtPassword2<hr>

All this does is update the current record, so that's probably why you're running into a problem.

[Night]

Sure I understand that. However, when I use the AddNew function it keeps poping up an error

-2147467259 - Field Login.Password cannot be a zero-length string.

The code for that part looked the same as the Updates, except that it said AddNew. Update doesn't give me any kind of error, where as this one does. It will write fine with the "SOID" one and not give any errors but the moment I add the Password it starts giving me errors. I don't see why. I enter text in that text box, so why would it come back saying it is a zero-length string? Either way though even when I used it this way it just wrote over the existing information in that table. I add a MoveNext Function at the end and it still does the same thing.

[Night]

I'll try to explain this the best I can. This is what happends when I use this code in my routine.

Select Case intDirection

'Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.AddNew "SOID", txtAlias
m_recLogin.MoveNext

Now I run the program enter in the information the table shows only "SOID" added. Good thats what it's supposed to do. Now I go through it again and enter different information. this is where it gets hard to explain. It writes over the original entry putting in the information I entered. However, it also enters the password of the second entry. But it's weird. Maybe I can sort of draw it out.

Program has run once. Entered: hhhh (user) gggg(pass)

Resulting Table:

SOID Password
hhhh

Run second time. Entered: nnnn(user) cccc(pass)

Resulting Table

SOID Password
nnnn cccc
nnnn

If you can explain why it does that when I use the AddNew then I would be appreciative.

[charlotte]

It sounds like you've got a validation on a table field that is triggering the error when you add a new record. You can't work around that, you have to track it down and fix the problem. When you create a new record, the password *is* a zero-lenth string until you populate the field, so the sequence in which you're writing the fields may be the problem. If you have a field level validation, try taking it out and doing your validations in the form.

[charlotte]

You aren't adding a password the first time, which is probably what triggers the error message. You can pass param arrays into the AddNew method, which is what you need to do to add both fields and their values at once. That should cure the error message and allow you to use AddNew properly. Here's what Help says:

<hr>recordset.AddNew FieldList, Values

Parameters

FieldList Optional. A single name, or an array of names or ordinal positions of the fields in the new record.

Values Optional. A single value, or an array of values for the fields in the new record. <font color=red>If Fields is an array, Values must also be an array with the same number of members; otherwise, an error occurs.</font color=red> The order of field names must match the order of field values in each array.<hr>

The alternative is something like this:

m_recLogin.AddNew
m_recLogin!SOID = txtAlias
m_recLogin!Password = txtPassword2

Either method will work, and I usually use the latter because it's easier to read and debug.

[Night]

Charlotte I thank you for all of your help, and I believe I found and fixed my problem. What was happening was that I had set the DataField property on my bound controls so it was always reading it into the first field, and the just updating it as neccessary or something along those lines. So by binding them to the table only and keeping the Datafield blank I was able to use code to get it to work the way I wanted it to. Here is my final code for that part of the Routine.

Private Sub MoveToRecord(intDirection As String)

Dim errCurrent As ADODB.Error

On Error GoTo MoveToRecord_Err

Select Case intDirection


'Adds User name and Password to Database
Case adRsnAddNew
m_recLogin.AddNew "SOID", txtAlias
m_recLogin.Update "Password", txtPassword2
m_recLogin.MoveLast

End Select