Loosing integrity of my data...

[todd666]

My program is terminate. I am using it and I found a big problem. I'm using a bd and datagrid on which I made some search, add, delete, ... operation. I have only one table on my BD and it contains about 300 records.

I recently notice that my data is not ok.

I give you an example.

Here is the rows:
111 111 111
222 222 222
333 333 333
444 444 444

And, suddenly, here is my row:
111 111 111
222 222 222
111 333 333 <--
444 444 444

I have about 3 record on 300 that is like this. I don't understand. I check my code and nothing seems to tell to duplicate a cell into the wrong row.

Is anyone have some idea to help me in the resolution of this problem. Thanks in advance.

 

[todd666]

Is there anyone who can give me some idea to follow or to check?

 

[bjd4jc]

How can anyone help if we can not see the code?

 

[todd666]

Sorry, here is some code, if you need more, just tell me:
By the way, can anyone tell me the best way to post code?

Here is the code of my search function :

Private Sub Recherche_Click()

Select Case Search
Case 0
SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' ")
With Adodc1
.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\Unix\DataBase;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
.RecordSource = " SELECT * FROM [AIX] WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' AND [Recherche] LIKE '%R1%' "
.Refresh
End With
KeepTypes1 = SearchTypes
KeepCommandes1 = SearchCommandes
KeepDescription1 = SearchDescription
Search = 1

Case 1
SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%' ")
SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' AND [Recherche] LIKE '%R1%'")
With Adodc1
.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
.RecordSource = " SELECT * FROM [AIX] WHERE [Recherche] LIKE '%R2%' "
.Refresh
End With
KeepTypes2 = SearchTypes
KeepCommandes2 = SearchCommandes
KeepDescription2 = SearchDescription
Search = 2
Case 2
MAJRecherche
With Adodc1
.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
.RecordSource = " SELECT * FROM [AIX] WHERE [Recherche] LIKE '%R2%' AND [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%'"
.Refresh
End With
Search = 2
End Select


'New Search with new criteria
If Text1.Text = "" And Text2.Text = "" And Text3.Text = "" Then
SQL ("UPDATE [AIX] SET [Recherche]='' WHERE [Recherche] LIKE '%R1%' OR [Recherche] LIKE '%R2%'")
With Adodc1
.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
.RecordSource = " SELECT * FROM [AIX] WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' "
.Refresh
End With
End If

End Sub

Here is the module SQL:

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Function SQL(Request As String)
With conn
.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
.Open
End With
With rs
conn.Execute "" & Request & "", NUMOFRECORDS
conn.Close
End With
Set rs = Nothing
Set conn = Nothing
End Function


AND Here is the module MAJRecherche:
Public Sub MAJRecherche()
Select Case Search
Case 1 'Cor
SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%'")
Case 2
SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%' ")
SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & KeepTypes2 & "%' AND [Commandes] LIKE '%" & KeepCommandes2 & "%' AND [Description] LIKE '%" & KeepDescription2 & "%' AND [Recherche] LIKE '%R1%'")
End Select
End Sub

 

[bjd4jc]

I like to use the code blocks

put your code in between a

 and an

and make sure you have Process TGML checked before you submit

 

[todd666]

Here is the code of my search function :

Private Sub Recherche_Click()

Select Case Search
  Case 0  
  SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' ")
    With Adodc1
      .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\Unix\DataBase;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
      .RecordSource = " SELECT * FROM [AIX] WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' AND [Recherche] LIKE '%R1%' "
      .Refresh
    End With
KeepTypes1 = SearchTypes
KeepCommandes1 = SearchCommandes
KeepDescription1 = SearchDescription
Search = 1

Case 1             
    SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%' ")
    SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' AND [Recherche] LIKE '%R1%'")
    With Adodc1
      .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
      .RecordSource = " SELECT * FROM [AIX] WHERE [Recherche] LIKE '%R2%' "
      .Refresh
End With
    KeepTypes2 = SearchTypes
    KeepCommandes2 = SearchCommandes
    KeepDescription2 = SearchDescription
    Search = 2
  Case 2          
    MAJRecherche
    With Adodc1
      .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
      .RecordSource = " SELECT * FROM [AIX] WHERE [Recherche] LIKE '%R2%' AND [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%'"
      .Refresh
    End With
    Search = 2
End Select
  
   
  'New Search with new criteria
  If Text1.Text = "" And Text2.Text = "" And Text3.Text = "" Then
    SQL ("UPDATE [AIX] SET [Recherche]='' WHERE [Recherche] LIKE '%R1%' OR [Recherche] LIKE '%R2%'")
    With Adodc1
      .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
      .RecordSource = " SELECT * FROM [AIX] WHERE [Types] LIKE '%" & SearchTypes & "%' AND [Commandes] LIKE '%" & SearchCommandes & "%' AND [Description] LIKE '%" & SearchDescription & "%' "
      .Refresh
    End With
End If
  
End Sub

Here is the module SQL:

Dim conn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Function SQL(Request As String)
  With conn
    .ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=""DBQ=C:\Unix\DataBase\AIX.mdb;DefaultDir=C:\UNIX\DATABASE;Driver={Driver do Microsoft Access (*.mdb)};DriverId=25;FIL=MS Access;FILEDSN=C:\Unix\DataBase\ODBC.dsn;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UID=admin;UserCommitSync=Yes;"""
    .Open
  End With
  With rs
    conn.Execute "" & Request & "", NUMOFRECORDS
    conn.Close
  End With
  Set rs = Nothing
  Set conn = Nothing
End Function


AND Here is the module MAJRecherche:
Public Sub MAJRecherche()
  Select Case Search
    Case 1  'Cor
      SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%'")
    Case 2  
      SQL ("UPDATE [AIX] SET Recherche='R1' WHERE [Types] LIKE '%" & KeepTypes1 & "%' AND [Commandes] LIKE '%" & KeepCommandes1 & "%' AND [Description] LIKE '%" & KeepDescription1 & "%' ")
      SQL ("UPDATE [AIX] SET Recherche='R2' WHERE [Types] LIKE '%" & KeepTypes2 & "%' AND [Commandes] LIKE '%" & KeepCommandes2 & "%' AND [Description] LIKE '%" & KeepDescription2 & "%' AND [Recherche] LIKE '%R1%'")
  End Select
End Sub