Ad:

Visual Basic 4 / 5 / 6 Discussion Thread
Marked Solved
Views: 565

Nov 8th, 2009

Error checking for an existing record

Expand Post »

i am trying to make sure that one record is not entered twice into the same database but the code i am using is giving me an error

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
Set ri = deSub.conn.Execute("SELECT* FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")
If ri.EOF = True Then
 
ri.AddNew
ri.Fields("firstname") = txtfname.Text
ri.Fields("surname") = txtsurname.Text
ri.Fields("numberofcopies") = x * Y
ri.Fields("copiesout") = txtcopies.Text
ri.Fields("copiesremaining") = x * Y
ri.Update 
Else......
Set ri = deSub.conn.Execute("SELECT* FROM individualsubs WHERE firstname = '" & txtfname.Text & "' AND surname = '" & txtsurname.Text & "'")
If ri.EOF = True Then

ri.AddNew
ri.Fields("firstname") = txtfname.Text
ri.Fields("surname") = txtsurname.Text
ri.Fields("numberofcopies") = x * Y
ri.Fields("copiesout") = txtcopies.Text
ri.Fields("copiesremaining") = x * Y
ri.Update 
Else......

the error i am getting is

Quote ...

current recordset does not support updating. This might be a limitation of the provider or the selected locktype lock type

but i am using adlockoptimistic as shown below which suports updating...

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
With ri
.ActiveConnection = deSub.conn
.LockType = adLockOptimistic
.Source = "select * from individualsubs"
.Open
End With
With ri
.ActiveConnection = deSub.conn
.LockType = adLockOptimistic
.Source = "select * from individualsubs"
.Open
End With

Similar Threads

Error Checking for user input in Java
Custom error checking in C#
error checking in Java
User Form error checking in PHP
Help with 1.pointers and 2.error checking in C++

Israelsimba

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

41 posts
since Oct 2009

Permalink

Nov 8th, 2009

Re: Error checking for an existing record

since i found roblems with adding a new record after checking if it was existant i then tried a another method but its giving me an error. the code is

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
deSub.conn.Execute ("INSERT INTO individualsubs VALUES (clientnumber = " & txtcopies.Text & ",  surname = '" & txtsurname.Text & "', firstname = '" & txtfname.Text & "', nowdate = '" & DTPicker2.Value & "', numberofcopies = " & x * Y & ", copiesout = " & txtcopies.Text & ", copiesremaining = " & x * Y & ")")
deSub.conn.Execute ("INSERT INTO individualsubs VALUES (clientnumber = " & txtcopies.Text & ",  surname = '" & txtsurname.Text & "', firstname = '" & txtfname.Text & "', nowdate = '" & DTPicker2.Value & "', numberofcopies = " & x * Y & ", copiesout = " & txtcopies.Text & ", copiesremaining = " & x * Y & ")")

one or more reqired fields is missing in the requested ordinal
what could be wrong with my code here
thatnx

Israelsimba

Reputation Points: 10

Solved Threads: 0

Light Poster

Offline

41 posts
since Oct 2009

Permalink

Nov 8th, 2009

Re: Error checking for an existing record

Okay, in your first post you set the lock type with the second code snippet but I don't see it in the first code snippet, which means if you have not explicitly set it, it defaults back to its default settings, which might be your problem.

Now, in your second post, you have formatted the insert statement incorrectly. Presently it looks more like an update statement but that is neither here nor there or the answer to your problem. So, a properly formatted insert statement looks like this...

Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)
strSQL = "INSERT INTO tablename(Numberfieldname1, Stringfieldname2) VALUES(" & VariableContainingNumberValueForFieldName1 & ",'" & VariableContainingStringValueForFieldName2 & "')"
strSQL = "INSERT INTO tablename(Numberfieldname1, Stringfieldname2) VALUES(" & VariableContainingNumberValueForFieldName1 & ",'" & VariableContainingStringValueForFieldName2 & "')"

Good Luck

Edit: to see correctly toggle to plain text

Last edited by vb5prgrmr; Nov 8th, 2009 at 10:11 am.

vb5prgrmr

Reputation Points: 156

Solved Threads: 296

Posting Virtuoso

Offline

1,670 posts
since Mar 2009

This thread is solved

Either the thread starter or a moderator has marked this thread as solved. You can most likely trust the responses and answers given. There is most likely no reason for any further responses to be posted here. If you have a related question, please start a new thread in this forum instead.

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in Visual Basic 4 / 5 / 6 Forum Timeline: If then Statement in excel using data in 2-3 columns

Next Thread in Visual Basic 4 / 5 / 6 Forum Timeline: calling event form module

DaniWeb Message

Cancel Changes

User Name
Password