locked
Database deletes records when I connect RRS feed

  • Question

  • User-556029652 posted

    I have a problem regarding database. I write to the database fine with this code:


    Public Sub AddData(ByVal Customer As String, ByVal Project As String, ByVal GroupName As String, ByVal NameOfElement As String, ByVal LockActivation As String, ByVal OutputExtension As String, ByVal UnlockingDuration As String, ByVal OpenMode As String, ByVal LocksAtPower As String, ByVal NetworkFunction As String, ByVal SharedMastercards As String, ByVal SharedMastercards2 As String, ByVal AdditionalCards As String, ByVal DoorOpenWarnings As String, ByVal DoorOpenTime As String, ByVal BurglaryAlarm As String, ByVal OpeningDelay As String, ByVal SilentAlarm As String, ByVal OpeningBlocking As String, ByVal SecurityOpeningMode As String, ByVal PincodeIdentification As String, ByVal KeyboardOrientation As String, ByVal FingerprintIdentification As String, ByVal EyewearDisplays As String, ByVal ButtonHandTerminal As String, ByVal ExternalAcousticBeeper As String, ByVal EventMemory As String, ByVal EmergencyOpeningUnit As String, ByVal NameOfElement1 As String, ByVal NameOfElement2 As String, ByVal NameOfElement3 As String, ByVal NameOfElement4 As String, ByVal NameOfElement5 As String, ByVal NameOfElement6 As String, ByVal NameOfElement7 As String, ByVal NameOfElement8 As String, ByVal MagneticLockOrSwitch1 As String, ByVal MagneticLockOrSwitch2 As String, ByVal MagneticLockOrSwitch3 As String, ByVal MagneticLockOrSwitch4 As String, ByVal MagneticLockOrSwitch5 As String, ByVal MagneticLockOrSwitch6 As String, ByVal MagneticLockOrSwitch7 As String, ByVal MagneticLockOrSwitch8 As String, ByVal OpeningDelay1 As String, ByVal OpeningDelay2 As String, ByVal OpeningDelay3 As String, ByVal OpeningDelay4 As String, ByVal OpeningDelay5 As String, ByVal OpeningDelay6 As String, ByVal OpeningDelay7 As String, ByVal OpeningDelay8 As String, ByVal UnlockingDuration1 As String, ByVal UnlockingDuration2 As String, ByVal UnlockingDuration3 As String, ByVal UnlockingDuration4 As String, ByVal UnlockingDuration5 As String, ByVal UnlockingDuration6 As String, ByVal UnlockingDuration7 As String, ByVal UnlockingDuration8 As String, ByVal DoorOpenContactInvert1 As String, ByVal DoorOpenContactInvert2 As String, ByVal DoorOpenContactInvert3 As String, ByVal DoorOpenContactInvert4 As String, ByVal DoorOpenContactInvert5 As String, ByVal DoorOpenContactInvert6 As String, ByVal DoorOpenContactInvert7 As String, ByVal DoorOpenContactInvert8 As String, ByVal DoorOpenControl1 As String, ByVal DoorOpenControl2 As String, ByVal DoorOpenControl3 As String, ByVal DoorOpenControl4 As String, ByVal DoorOpenControl5 As String, ByVal DoorOpenControl6 As String, ByVal DoorOpenControl7 As String, ByVal DoorOpenControl8 As String, ByVal DoorOpenTime1 As String, ByVal DoorOpenTime2 As String, ByVal DoorOpenTime3 As String, ByVal DoorOpenTime4 As String, ByVal DoorOpenTime5 As String, ByVal DoorOpenTime6 As String, ByVal DoorOpenTime7 As String, ByVal DoorOpenTime8 As String) 'As Data.DataTable
            Dim myCommand As New OleDb.OleDbCommand()
            Dim sqlInsert As String = "INSERT INTO tblDatabaseRecords (Customer, Project, GroupName, NameOfElement, LockActivation, OutputExtension, UnlockingDuration, OpenMode, LocksAtPower, NetworkFunction, SharedMastercards, SharedMastercards2, AdditionalCards, DoorOpenWarnings, DoorOpenTime, BurglaryAlarm, OpeningDelay, SilentAlarm, OpeningBlocking, SecurityOpeningMode, PincodeIdentification,KeyboardOrientation,FingerprintIdentification,EyewearDisplays,ButtonHandTerminal, ExternalAcousticBeeper, EventMemory, EmergencyOpeningUnit, NameOfElement1, NameOfElement2, NameOfElement3, NameOfElement4, NameOfElement5, NameOfElement6, NameOfElement7, NameOfElement8, MagneticLockOrSwitch1, MagneticLockOrSwitch2, MagneticLockOrSwitch3, MagneticLockOrSwitch4, MagneticLockOrSwitch5, MagneticLockOrSwitch6, MagneticLockOrSwitch7, MagneticLockOrSwitch8, OpeningDelay1, OpeningDelay2, OpeningDelay3, OpeningDelay4, OpeningDelay5, OpeningDelay6, OpeningDelay7, OpeningDelay8, UnlockingDuration1, UnlockingDuration2, UnlockingDuration3, UnlockingDuration4, UnlockingDuration5, UnlockingDuration6, UnlockingDuration7, UnlockingDuration8, DoorOpenContactInvert1, DoorOpenContactInvert2, DoorOpenContactInvert3, DoorOpenContactInvert4, DoorOpenContactInvert5, DoorOpenContactInvert6, DoorOpenContactInvert7, DoorOpenContactInvert8, DoorOpenControl1, DoorOpenControl2, DoorOpenControl3, DoorOpenControl4, DoorOpenControl5, DoorOpenControl6, DoorOpenControl7, DoorOpenControl8, DoorOpenTime1, DoorOpenTime2, DoorOpenTime3, DoorOpenTime4, DoorOpenTime5, DoorOpenTime6, DoorOpenTime7, DoorOpenTime8) VALUES ('" & Customer & "','" & Project & "','" & GroupName & "','" & NameOfElement & "','" & LockActivation & "','" & OutputExtension & "','" & UnlockingDuration & "','" & OpenMode & "','" & LocksAtPower & "','" & NetworkFunction & "','" & SharedMastercards & "','" & SharedMastercards2 & "','" & AdditionalCards & "','" & DoorOpenWarnings & "','" & DoorOpenTime & "','" & BurglaryAlarm & "','" & OpeningDelay & "','" & SilentAlarm & "','" & OpeningBlocking & "','" & SecurityOpeningMode & "','" & PincodeIdentification & "','" & KeyboardOrientation & "','" & FingerprintIdentification & "','" & EyewearDisplays & "','" & ButtonHandTerminal & "','" & ExternalAcousticBeeper & "','" & EventMemory & "','" & EmergencyOpeningUnit & "','" & NameOfElement1 & "','" & NameOfElement2 & "','" & NameOfElement3 & "','" & NameOfElement4 & "','" & NameOfElement5 & "','" & NameOfElement6 & "','" & NameOfElement7 & "','" & NameOfElement8 & "','" & MagneticLockOrSwitch1 & "','" & MagneticLockOrSwitch2 & "','" & MagneticLockOrSwitch3 & "','" & MagneticLockOrSwitch4 & "','" & MagneticLockOrSwitch5 & "','" & MagneticLockOrSwitch6 & "','" & MagneticLockOrSwitch7 & "','" & MagneticLockOrSwitch8 & "','" & OpeningDelay1 & "','" & OpeningDelay2 & "','" & OpeningDelay3 & "','" & OpeningDelay4 & "','" & OpeningDelay5 & "','" & OpeningDelay6 & "','" & OpeningDelay7 & "','" & OpeningDelay8 & "','" & UnlockingDuration1 & "','" & UnlockingDuration2 & "','" & UnlockingDuration3 & "','" & UnlockingDuration4 & "','" & UnlockingDuration5 & "','" & UnlockingDuration6 & "','" & UnlockingDuration7 & "','" & UnlockingDuration8 & "','" & DoorOpenContactInvert1 & "','" & DoorOpenContactInvert2 & "','" & DoorOpenContactInvert3 & "','" & DoorOpenContactInvert4 & "','" & DoorOpenContactInvert5 & "','" & DoorOpenContactInvert6 & "','" & DoorOpenContactInvert7 & "','" & DoorOpenContactInvert8 & "','" & DoorOpenControl1 & "','" & DoorOpenControl2 & "','" & DoorOpenControl3 & "','" & DoorOpenControl4 & "','" & DoorOpenControl5 & "','" & DoorOpenControl6 & "','" & DoorOpenControl7 & "','" & DoorOpenControl8 & "','" & DoorOpenTime1 & "','" & DoorOpenTime2 & "','" & DoorOpenTime3 & "','" & DoorOpenTime4 & "','" & DoorOpenTime5 & "','" & DoorOpenTime6 & "','" & DoorOpenTime7 & "','" & DoorOpenTime8 & "')"
    
            myCommand.Connection = myConnection
            myCommand.CommandText = sqlInsert
            myCommand.ExecuteReader()
            myConnection.InitializeLifetimeService()
        End Sub


    When I close the application and view the Database with access.. the records are added ok, but when I start the application the records are deleted. This is what I have in the Form_Load:

    'Database connection string (Access Command & Database Path)
            myConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=dbConfigurations.mdb;Persist Security Info=False"
            'Open Database 
            myConnection.Open()


    Does anyone have an idea what I'm doing wrong?


    Thanks






    Wednesday, October 6, 2010 9:14 AM

Answers

  • User-1199946673 posted

    the records are added ok
     

    Are you sure? Is myConnection Open?

    And when you want to execute an Action command (INSERT/UPDATE/DELETE), use ExecuteNonQuery, not ExecuteReader

     And since you don't use parameterized queries (You should, read: http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access), it can be that because one of the values containd a single quote, the INSERT will fail.

    but when I start the application the records are deleted

    Records will not simply disappear. you will need to execute a delete command.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, October 6, 2010 11:15 AM