asked on

VS2017 Data lost after project restart

I have a project written in VB.Net. My updates to the SQL Server are working fine except when I restart my project all changes are lost. After a lot of searching I found that I have to change 'Copy to Output Directory' to 'Copy if Newer' or 'Do Not Copy' (depending which site I read) in the properties of the database in Solutions Explorer. I can change the Copy to Output in properties of the form but I don't have the database in my Solutions Explorer. Totally lost and confused. I am teaching myself from VBA to VB.Net.
Any help would be appreciated.

it_saige

All of which changes (code changes, data changes, etc)?

-saige-

Richard Sprague

ASKER

No, just the data. Code changes are fine. I think I understand what is going on, I just don't know how to fix it. I think something is set to avoid multi-user conflict on data change. My project is for one user only so I don't have to worry about that.

it_saige

How do you manipulate the data that is sent to the database? Can you provide a code example of what is used?

-saige-

Richard Sprague

ASKER

OK. Lots cut out for brevity.

Imports System.Data.SqlClient
Imports System.Globalization
Imports System.IO
Imports System.Windows.Forms.DataFormats
Imports Microsoft.SqlServer

Public Class FrmDataBackUp

    Private table As New DataTable
    Private adapter As SqlDataAdapter = New SqlDataAdapter()
    Private sqlString As String
    Private dtb As New DataTable
    Private dts As New DataSet
    Private changes As DataSet
    Private cmd As New SqlCommand
    Private cmdBuilder As SqlCommandBuilder
    Private AddFlag As Boolean = False
    Private adapterCx As New SqlDataAdapter
    Private adapt2 As New SqlDataAdapter
    Private row As Integer = 0
    Private NoOfRows As Integer = 0
    Private GridWidth As Integer = 0

    Private Sub FrmDataBackUp_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        ' Public variables declared elsewhere, values here for test purposes only
        ChildID = 17
        ParentID = 100
        DataID = 133

        ' Connection is made elsewhere
        adapterCx = CreateCustomerAdapter(connection)
        adapterCx.Fill(dts)

        DgvData.DataSource = dts.Tables(0)

        Call ConfigureGrid()

        BtnSave.Enabled = True
        LblSibling.Visible = Sibling
        Call CalcTotals(ChildID)
        LastId = GetLastID()

    End Sub

    Public Function CreateCustomerAdapter(ByVal connection As SqlConnection) As SqlDataAdapter

        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        ' Create the SelectCommand.
        sqlString = "SELECT [DataID], [ChildID], [ParentID]" &
                            ", [Date], [PaidIn], [Breakfast], [1500-1600], [1600-1630], [1630-1700], [1700-1730]" &
                            ", [ExtraMins], [Total], [TimeOut] FROM [Data] WHERE  " &
                            "[ChildID]=@ChildID ORDER BY [Date] DESC"
        Dim command As SqlCommand = New SqlCommand(sqlString, connection)

        ' Add the parameters for the SelectCommand.
        Call LoadParams(command, sqlString)
        adapter.SelectCommand = command

        ' Create the InsertCommand.
        ' Code commented out for brevity

        ' Create the UpdateCommand.
        sqlString = "UPDATE Data SET " &
                            "PaidIn=@PMPaidIn, Breakfast=@Breakfast, [1500-1600]=@PM1500, [1600-1630]=@PM1600, " &
                            "[1630-1700]=@PM1630, [1700-1730]=@PM1700, ExtraMins=@Extra, Total=@PMTotal, TimeOut=@Time" &
                            "WHERE DataID = @DataID"
        command = New SqlCommand(sqlString, connection)
        adapter.UpdateCommand.Parameters.Add("@DataID", SqlDbType.Int).Value = DataID
        adapter.UpdateCommand.Parameters.Add("@ParentID", SqlDbType.Int).Value = ParentID
        adapter.UpdateCommand.Parameters.Add("@ChildID", SqlDbType.Int).Value = ChildID
        adapter.UpdateCommand.Parameters.Add("@Date", SqlDbType.Date).Value = dteDate
        adapter.UpdateCommand.Parameters.Add("@Breakfast", SqlDbType.Decimal).Value = Breakfast
        adapter.UpdateCommand.Parameters.Add("@PM1500", SqlDbType.Decimal).Value = PM1500
        adapter.UpdateCommand.Parameters.Add("@PM1600", SqlDbType.Decimal).Value = PM1600
        adapter.UpdateCommand.Parameters.Add("@PM1630", SqlDbType.Decimal).Value = PM1630
        adapter.UpdateCommand.Parameters.Add("@PM1700", SqlDbType.Decimal).Value = PM1700
        adapter.UpdateCommand.Parameters.Add("@PMTotal", SqlDbType.Decimal).Value = PMTotal
        adapter.UpdateCommand.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn
        adapter.UpdateCommand.Parameters.Add("@Extra", SqlDbType.Decimal).Value = Extra
        adapter.UpdateCommand.Parameters.Add("@Discount", SqlDbType.Decimal).Value = PDiscount
        adapter.UpdateCommand.Parameters.Add("@Time", SqlDbType.DateTime).Value = tmeOut
        adapter.UpdateCommand.Parameters.Add("@PTimeOut", SqlDbType.Time).Value = PTimeOut
        adapter.UpdateCommand = command
        command.ExecuteNonQuery()

        ' Create the DeleteCommand.
        ' Code commented out for brevity

        Return adapter
    End Function
    
    Private Sub DgvData_Click(sender As System.Object, e As System.EventArgs) Handles DgvData.Click

    End Sub

    Private Sub BtnSave_Click(sender As Object, e As EventArgs) Handles BtnSave.Click

        Try
            adapt2.MissingSchemaAction = MissingSchemaAction.AddWithKey
            cmdBuilder = New SqlCommandBuilder(adapt2)
            changes = dts.GetChanges()
            If changes IsNot Nothing Then
                adapt2.Update(changes)
                Call ThumbsUp("Data saved")
            End If
        Catch ex As Exception
            Call ThumbsDown(ex.ToString)
        End Try

    End Sub

    Private Sub BtnExit_Click(sender As Object, e As EventArgs) Handles BtnExit.Click
        Form1.ToolStripStatusLabel1.Text = ""
        Close()
    End Sub

    Private Sub DgvData_CellValueChanged(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DgvData.CellValueChanged

        If e.RowIndex < 0 Then Exit Sub
        row = e.RowIndex

        With DgvData.Rows(row)
            If IsDBNull(.Cells(e.ColumnIndex).Value) And e.ColumnIndex > 4 And e.ColumnIndex < 12 Then _
                       .Cells(e.ColumnIndex).Value = "0.00"
            DataID = .Cells("DataID").Value
            ParentID = .Cells("ParentID").Value
            ChildID = .Cells("ChildID").Value
            dteDate = .Cells("Date").Value
            PMPaidIn = .Cells("PaidIn").Value
            Breakfast = .Cells("Breakfast").Value
            PM1500 = .Cells("1500-1600").Value
            PM1600 = .Cells("1600-1630").Value
            PM1630 = .Cells("1630-1700").Value
            PM1700 = .Cells("1700-1730").Value
            Extra = .Cells("ExtraMins").Value
            PTimeOut = .Cells("TimeOut").Value
            Dim AddedUp As Double = .Cells("Total").Value = .Cells("Breakfast").Value +
                    .Cells("1500-1600").Value +
                    .Cells("1600-1630").Value +
                    .Cells("1630-1700").Value +
                    .Cells("1700-1730").Value +
                    .Cells("ExtraMins").Value
            PMTotal = AddedUp

            DgvData.EndEdit()
            DgvData.Columns(0).Visible = False
            DgvData.Columns(1).Visible = False
            DgvData.Columns(2).Visible = False

        End With
        Call CalcCols()

    End Sub
    

End Class

Open in new window

Richard Sprague

ASKER

As an extra thought - the data in SSMS shows the changes, again until the project is restart

it_saige

SSMS shows the data being modified until the project is restarted. Does your database happen to be an included file in the project?

-saige-

Richard Sprague

ASKER

That, I think is my problem. In Solution Explorer there isn't a database listed. They are there is the Sever Explorer.

Richard Sprague

ASKER

Capture.PNG

it_saige

What does your connection string in the App.config have?

-saige-

Richard Sprague

ASKER

Catalog changes depending on me (programmer) or users. Either RascalsTest or Rascals

Public Sub ConOpen()
        conString = "Data Source=192.168.1.64,1433;Initial Catalog=" & Catalog & ";User ID=" & UserId & ";Password=" & Password
        Try
            connection = New SqlConnection With {.ConnectionString = conString}
            connection.Open()
        Catch ex As Exception
            MessageBox.Show("", ex.Message)
            connection.Close()
            End
        End Try

    End Sub

Open in new window

it_saige

Nothing you have shown is out of the ordinary. This might require a remote connection to diagnose. I'll be available at about 4PM CST if another solution cannot be found.

-saige-

Richard Sprague

ASKER

Mmm, might be a bit of a problem. I am in the Uk! Could do with a Time Lord.....

it_saige

I am online and available if you still need assistance with this issue.

-saige-

Richard Sprague

ASKER

Hi, sorry about the delay getting back to you but Microsoft decide it wanted to do updates.....!

I am ready if you are, what do you want me to do?

it_saige

Answered via private session. Duration 00:00:20

it_saige

Did the session not work for you?

-saige-

Richard Sprague

ASKER

No it flashed up in Chrome then said you had left the session

it_saige

Huh... Interesting... Let's try again, if it doesn't work, I can connect via TeamViewer.

-saige-

Richard Sprague

ASKER

Same thing happened again. Never used TeamViewer

SOLUTION

it_saige

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Richard Sprague

ASKER

Everything works fine if i do this:

        ' Create the UpdateCommand.
        sqlString = "UPDATE Data SET  PaidIn = @PMPaidIn WHERE DataID =@DiD"
        command = New SqlCommand(sqlString, connection)
        command.Parameters.Add("@DID", SqlDbType.Int).Value = DiD
        PMPaidIn = 5.55
        command.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn

        adapter.UpdateCommand = command
        command.ExecuteNonQuery()
        'command.ExecuteReader()
        connection.Close()

Open in new window

but not if I do this:

        sqlString = "UPDATE Data SET  PaidIn = @PMPaidIn WHERE DataID =@DiD"
        command = New SqlCommand(sqlString, connection)
        command.Parameters.Add("@DID", SqlDbType.Int).Value = DiD
        'PMPaidIn = 5.55
        command.Parameters.Add("@PMPaidIn", SqlDbType.Decimal).Value = PMPaidIn

        adapter.UpdateCommand = command
        command.ExecuteNonQuery()
        connection.Close()

        adapter.Fill(dts)
        adapter.MissingSchemaAction = MissingSchemaAction.AddWithKey

        DGVData.DataSource = dts.Tables(0)

        dts.Tables(0).Rows(5).Item("PaidIn") = dts.Tables(0).Rows(5).Item("PaidIn") + 5
        cmdBuilder = New SqlCommandBuilder(adapter)
        adapter.Update(dts)

Open in new window

ASKER CERTIFIED SOLUTION

Richard Sprague

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial