Avatar of Richard Sprague
Richard Sprague
 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.
DatabasesMicrosoft SQL ServerVisual Basic.NETVBASQL

Avatar of undefined
Last Comment
Richard Sprague

8/22/2022 - Mon
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-
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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-
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
it_saige

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

-saige-
Your help has saved me hundreds of hours of internet surfing.
fblack61
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.....
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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-
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Richard Sprague

ASKER
Same thing happened again. Never used TeamViewer
SOLUTION
it_saige

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
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

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.