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.
Any help would be appreciated.
DatabasesMicrosoft SQL ServerVisual Basic.NETVBASQL
Last Comment
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.
How do you manipulate the data that is sent to the database? Can you provide a code example of what is used?
-saige-
-saige-
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
ASKER
As an extra thought - the data in SSMS shows the changes, again until the project is restart
SSMS shows the data being modified until the project is restarted. Does your database happen to be an included file in the project?
-saige-
-saige-
ASKER
That, I think is my problem. In Solution Explorer there isn't a database listed. They are there is the Sever Explorer.
ASKER
What does your connection string in the App.config have?
-saige-
-saige-
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
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-
-saige-
ASKER
Mmm, might be a bit of a problem. I am in the Uk! Could do with a Time Lord.....
I am online and available if you still need assistance with this issue.
-saige-
-saige-
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?
I am ready if you are, what do you want me to do?
Answered via private session. Duration 00:00:20
Did the session not work for you?
-saige-
-saige-
ASKER
No it flashed up in Chrome then said you had left the session
Huh... Interesting... Let's try again, if it doesn't work, I can connect via TeamViewer.
-saige-
-saige-
ASKER
Same thing happened again. Never used TeamViewer
SOLUTION
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.
ASKER
Everything works fine if i do this:
but not 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()
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)
ASKER CERTIFIED SOLUTION
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.
-saige-