Using Edit in VB

[nagatron]

Hello to all masters. . .I would like to ask if it is possible to use edit function in database without primary key. Ex.

I have a button edit, a two textfields that holds the name and age of a person.

If I put 2 same names in the database with same age:
Name Age
-------------------------------
Mark Jackson 20
-------------------------------
Mark Jackson 20
------------------------------

then I want to edit the second one with "Michel Jackson", why is it the first one also affected. Every changes I made on the second person affects the first one.
Name Age
-------------------------------
Michel Jackson 20
-------------------------------
Michel Jackson 20
------------------------------

How can I resolve this? I want it to make it like this:
Name Age
-------------------------------
Mark Jackson 20
-------------------------------
Michel Jackson 20
------------------------------
after the changes is being done.

Thank you.
Best regards
neil

[aktharshaik]

The answer to why is it that the first one is also affected is, you get what u asked for.

u might have given in the query like
UPDATE table SET NAME = 'Michel Jackson' WHERE NAME = 'Mark Jackson'

here not only for 2 records, if u have 10 records in the same fashion, all the ten will be affected because the query given does not specify any scope and hence all the records in the table as checked for the condition of NAME = 'Mark Jackson' and change them to 'Michel Jackson'

If without primary key u cannot specify any filter. The most u can do, i think is, Open all the records with condition into a Recordset.
Move to the 2nd Record, if it exists, and Update the fields.

And why is it that u dont want to add a primary key?

Regards
Shaik Akthar

[nagatron]

So how am i suppose to do that? Here is a sample code fragment of my program:

Private Sub cmdEdit_Click()
chec:

Set rst = New ADODB.Recordset

With rst

.ActiveConnection = Con
.CursorLocation = adUseClient
.CursorType = adOpenDynamic
.LockType = adLockOptimistic

.Open "datainfo"

End With

With rst

.Fields!Date = StrConv(fldDateGetter, vbProperCase)
.Fields!firstname = StrConv(fldFname, vbProperCase)
.Fields!middlename = StrConv(fldMname, vbProperCase)
.Fields!lastname = StrConv(fldLname, vbProperCase)
.Fields!age = StrConv(fldAge, vbProperCase)
.Fields!sex = StrConv(fldSex, vbProperCase)
.Fields!telno = StrConv(fldTelNo, vbProperCase)
.Fields!address = StrConv(fldAddress, vbProperCase)
.Fields!occupation = StrConv(fldOccupation, vbProperCase)
.Fields!diagnosis = StrConv(fldDiagnosis, vbProperCase)
.Fields!treatment = StrConv(fldTreatment, vbProperCase)
.Fields!remarks = StrConv(fldRemarks, vbProperCase)
.Fields!timenow = StrConv(fldTimeGetter, vbProperCase)
.Fields!datetester = StrConv(fldDateTester, vbProperCase)
.Update
End With

Call dload2
End Sub

The dload() there is where the connection between database and MSFlexGrid. Based on the sample code above, can you tell me where is the part that needs to be change. thank you for the help.

Regards
Neil

[aktharshaik]

Private Sub cmdEdit_Click()
chec:

 Set rst = New ADODB.Recordset

 With rst
  .ActiveConnection = Con
  .CursorLocation = adUseClient
  .CursorType = adOpenDynamic
  .LockType = adLockOptimistic
  .Open "datainfo"
 End With

'Before applying the values u can move to the next record first.
'But this statement can be applicable when the two records are very next
'to each other only or if they are opened with a condition like " WHERE NAME = 'xyz' "

 rst.MoveNext
 With rst
  .Fields!Date = StrConv(fldDateGetter, vbProperCase)
  .Fields!firstname = StrConv(fldFname, vbProperCase)
  .Fields!middlename = StrConv(fldMname, vbProperCase)
  .Fields!lastname = StrConv(fldLname, vbProperCase)
  .Fields!age = StrConv(fldAge, vbProperCase)
  .Fields!sex = StrConv(fldSex, vbProperCase)
  .Fields!telno = StrConv(fldTelNo, vbProperCase)
  .Fields!address = StrConv(fldAddress, vbProperCase)
  .Fields!occupation = StrConv(fldOccupation, vbProperCase)
  .Fields!diagnosis = StrConv(fldDiagnosis, vbProperCase)
  .Fields!treatment = StrConv(fldTreatment, vbProperCase)
  .Fields!remarks = StrConv(fldRemarks, vbProperCase)
  .Fields!timenow = StrConv(fldTimeGetter, vbProperCase)
  .Fields!datetester = StrConv(fldDateTester, vbProperCase)
  .Update
 End With

 Call dload2
End Sub

I do not think that this is the possible solution. A Primary Key needs yet to be defined if u want to identify a row uniquely in your table.

Regards
Shaik Akthar

[aktharshaik]

If u dont have a primary key in ur table u can use a dummy column to retreive the data.

By this u can have another column which will be the SlNo column. now u can update the row based on the serial number.

1. Create a Module in the MS ACCESS Database only and put the following code and save it.

Option Explicit
Dim mlngCounter As Long

Function ResetCounter()
  mlngCounter = 0
End Function

Function NxtCnt(pvar As Variant) As Long
  mlngCounter = mlngCounter + 1
  NxtCnt = mlngCounter
End Function

In VB use the following query to retreive the data from the table.

" SELECT NAME, AGE, ResetCounter() AS DUMMY1, NxtCnt(NAME) AS SLNO
FROM DATAINFO "

u will get two columns along with the name and age columns, i.e. the DUMMY1 column which is empty and SLNO column as 1,2,3,4... in serial.

Don't know whether this may help u or not but this was the most i could think of.

Regards
Shaik Akthar

[nagatron]

Thank you for the reply. . .i am trying it now:)

[nagatron]

Thank you so much for the help, but it didn't work. . Still, other data that has same property to the data which is being edit are all affected.

In my own idea, there is something that needs to be test first before edit execution process is being done. But i don't know what to test and how to test.

[aparnesh]

If the 2 records are identical in all respects, then any change to one will affect the other. If there is some field (say Address or City) where they differ, you have to include that field in your edit statement to identify the record you want to edit. So if u have 2 Mark Jacksons of Age 20, but one lives in New York and one in Las vegas, use something like
UPDATE <TABLE_NAME> SET Name = "Michael Jackson" WHERE Name = "Mark Johnson" AND City = "New York". This will not affect the Mark Johnson of Las Vegas.