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

Visual Basic 4 / 5 / 6 RSS

Error checking for an existing record

Thread Solved

•

Join Date: Oct 2009

Posts: 23

Reputation: Israelsimba is an unknown quantity at this point

Solved Threads: 0

Israelsimba Israelsimba is offline

Offline

Newbie Poster

Error checking for an existing record

20 Days Ago

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: 23

Reputation: Israelsimba is an unknown quantity at this point

Solved Threads: 0

Israelsimba Israelsimba is offline

Offline

Newbie Poster

20 Days Ago

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: 811

Reputation: vb5prgrmr will become famous soon enough

Solved Threads: 147

vb5prgrmr

Offline

Practically a Posting Shark

20 Days Ago

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 & "')"