Updating Problems

[lotus18]

Hi everyone

I have a little problem in updating a record. I know how add a new record and check if the inputted new data is existing or not as what they have discussed in http://www.thescripts.com/forum/thread733529.html.

Now, my problem is... How can I check if updating data is existing or not? Please help!!!

Here's my sample code in adding a new entry:

[CODE=vb]Public dbConnection As New ADODB.Connectio n
Dim dbPath As String
dbPath = App.Path & "\Database\Cour ses.mdb"
[/CODE]
-------------------------------------------------------------------------------------------------------------------
[CODE=vb]
Public Sub SetConnection()
Set dbConnection = New ADODB.Connectio n
dbConnection.Op en "Provider=Micro soft.Jet.OLEDB. 4.0;Data Source=" & dbPath & _
";Persist Security Info=False"
End Sub
[/CODE]
-------------------------------------------------------------------------------------------------------------------
[CODE=vb]
Public Function CheckConnection (adRecordset As ADODB.Recordset )
If adRecordset.Sta te = 1 Then adRecordset.Clo se
End Function
[/CODE]
[CODE=vb]Public Function Duplicate(ByRef adConnection As ADODB.Connectio n, ByRef adRecordset As _
ADODB.Recordset , StrSQL As String, ByVal Title As String) As Boolean
'Default value
Duplicate = False

'Opens and checks the connection
SetConnection
CheckConnection adRecordset

adRecordset.Ope n StrSQL & Title & "'", _
dbConnection, 1, 1
'Checks if there is duplicate title
If adRecordset.Rec ordCount > 0 Then
Duplicate = True
Else
Duplicate = False
End If
End Function
[/CODE]
[CODE=vb]Dim str As String
str = "Select Course.Title From Courses"
If Duplicate(con, rs, str, txtCourse.Text) = False Then
rs.Open "Insert Into ([Course]) Values ('" & txtCourse.Text & "')", dbConnection, 3, 3
Else
Msgbox "Duplicatio n is not allowed.", 48
End If[/CODE]

[Killer42]

I think you need to check the string which is being built in strSQL in the Duplicate routine.

[lotus18]

Sorry for that line... I've forgot to check before submitting it :)

[Killer42]

Sorry for that line... I've forgot to check before submitting it :)

Um... not sure exactly what you mean. But anyway, I was a bit unclear in my post. What I meant was, I'd recommend you interrupt execution and check the value returned by this expression: StrSQL & Title & "'" at line 10 in the Duplicate routine.

[QVeen72]

Hi,

When using ADO, dont check for RecordCount>0, Check for Not EOF:
or Check for RecordCount <> 0.
As Sometimes Records are returned but RecordCount will be -1

[code=vb]
If Not adRecordset.EOF Then
Duplicate = True
Else
Duplicate = False
End If
[/code]

Regards
Veena

[lotus18]

Hello Killer42

Sorry for that line, it should be

[CODE=vb]adRecordset.Ope n StrSQL, _[/CODE]

[lotus18]

Hi,

When using ADO, dont check for RecordCount>0, Check for Not EOF:
or Check for RecordCount <> 0.
As Sometimes Records are returned but RecordCount will be -1

[code=vb]
If Not adRecordset.EOF Then
Duplicate = True
Else
Duplicate = False
End If
[/code]

Regards
Veena

Hi Veena

Does these codes can fix my problem for updating a record without duplication?
But anyway, thanks for your reply :)

[QVeen72]

Hi Veena

Does these codes can fix my problem for updating a record without duplication?
But anyway, thanks for your reply :)

Hi,

It should help you.. Give it a try..

Regards
Veena

[Killer42]

Sorry for that line, it should be[CODE=vb]adRecordset.Ope n StrSQL, _[/CODE]

I don't get it. Doesn't that mean you are checking for a duplicate by issuing Select Course.Title From Courses ? In other words, checking whether any records exist in the table?

[lotus18]

I don't get it. Doesn't that mean you are checking for a duplicate by issuing Select Course.Title From Courses ? In other words, checking whether any records exist in the table?

Exactly. I'm checking for an existing record. If the new data to be added is already existing then a message box will prompts you that duplication is not allowed.

[Killer42]

Exactly. I'm checking for an existing record. If the new data to be added is already existing then a message box will prompts you that duplication is not allowed.

Correct me if I'm wrong, but I would have thought "an existing record" actually meant an existing record with the same (relevant) details. Not absolutely any record at all. Or is this an unusual situation?

[QVeen72]

Hi,

What Killer is saying is right..
SQL String what you pass should be:

[code=vb]
strSQL= "Select Course.Title From Courses Where Course='" _
& txtCourse.Text & "'"
[/code]

Dont use "Str" as variable name, as it is Keyword in VB..

Regards
Veena

[lotus18]

Hi,

What Killer is saying is right..
SQL String what you pass should be:

[code=vb]
strSQL= "Select Course.Title From Courses Where Course='" _
& txtCourse.Text & "'"
[/code]

Dont use "Str" as variable name, as it is Keyword in VB..

Regards
Veena

Oh I see... OK. I'll change the variable. Thanks a lot : )