DaniWeb Forum Index > Software Development > Visual Basic 4 / 5 / 6

Error checking for an existing record

Please support our Visual Basic 4 / 5 / 6 advertiser: Programming Forums - DaniWeb Sister Site

Thread Solved

•

Join Date: Oct 2009

Posts: 25

Reputation: Israelsimba is an unknown quantity at this point

Solved Threads: 0

Israelsimba Israelsimba is offline

Offline

Light Poster

Error checking for an existing record

Nov 8th, 2009

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

 Help with Code Tags 
 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

•

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...

 Help with Code Tags 
 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

•

Join Date: Oct 2009

Posts: 25

Reputation: Israelsimba is an unknown quantity at this point

Solved Threads: 0

Israelsimba Israelsimba is offline

Offline

Light Poster

Nov 8th, 2009

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

 Help with Code Tags 
 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

•

Join Date: Mar 2009

Posts: 907

Reputation: vb5prgrmr will become famous soon enough

Solved Threads: 167

vb5prgrmr

Offline

Posting Shark

Nov 8th, 2009

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...

 Help with Code Tags 
 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 & "')"