Can not update MSSQL database

krentzis

New member
Joined
Jun 5, 2010
Messages
3
Programming Experience
Beginner
Hello,

I am aware about the COPY ALWAYS or DO NOT COPY features for files and database files.

But my problem is the following:
This is the code:
VB.NET:
Imports System.Data.SqlClient
Imports System
Imports System.Data
Imports System.Windows.Forms

Public Class Toybla
    ' Dim ConnectionString As String
    Dim myconnection As SqlConnection
    Dim mytransaction As SqlTransaction
    Dim mycommand1 As SqlCommand
    Dim myparam As SqlParameter
    Dim da As SqlDataAdapter
    Dim ds As New DataSet()
    Dim args() As String = Environment.GetCommandLineArgs

    Dim datatable As DataTable
    Dim currRec, totalRec As Integer

    Private Sub calculate()

        Dim senazia As Integer = Math.Floor(text_ypsos.Text / 1)
        Dim senazia_area As Double = senazia * text_mhkos.Text * senazi_paxos
        Dim mona_toybla_area As Double = text_mhkos.Text * mono_toyblo_ypsos
        Dim dipla_toybla_area As Double = ((text_mhkos.Text * text_ypsos.Text) - senazia_area) - mona_toybla_area

        lbl_res_embadon1.Text = text_mhkos.Text * text_ypsos.Text
        lbl_res_embadon2.Text = lbl_res_embadon1.Text * 2

        If cmb_typos.Text = "Εσωτερικός" Then
            lbl_embadon2.Visible = False
            lbl_res_embadon2.Visible = False
            lbl_res_dipla_toybla.Text = (dipla_toybla_area / 1) * dipla_toybla_sto_tetragoniko
            lbl_res_mona_toybla.Text = text_mhkos.Text * mona_toybla_sto_metro
            lbl_res_senazi.Text = text_mhkos.Text * senazia
            'lbl_kostos_sidera_senazi.Text = lbl_res_senazi.Text * 4 * senazi_sidero
            lbl_res_sidera.Text = senazia * text_mhkos.Text * 4
            lbl_kostos_senazi.Text = lbl_res_senazi.Text * timh_senazioy
            lbl_kostos_xtisimo.Text = (text_mhkos.Text * text_ypsos.Text) * timh_xtisimatos + (lbl_res_senazi.Text * timh_senazioy)

        End If

        If cmb_typos.Text = "Εξωτερικός" Then
            lbl_embadon2.Visible = True
            lbl_res_embadon2.Visible = True
            lbl_res_dipla_toybla.Text = ((dipla_toybla_area / 1) * dipla_toybla_sto_tetragoniko) * 2
            lbl_res_mona_toybla.Text = text_mhkos.Text * mona_toybla_sto_metro * 2
            lbl_res_senazi.Text = text_mhkos.Text * senazia * 2
            lbl_res_sidera.Text = senazia * text_mhkos.Text * 4 * 2
            lbl_kostos_senazi.Text = (lbl_res_senazi.Text / 2) * timh_diploy_senazioy
            lbl_kostos_xtisimo.Text = (lbl_res_embadon2.Text * timh_xtisimatos) + ((lbl_res_senazi.Text / 2) * timh_diploy_senazioy)


        End If
        lbl_kostos_dipla_toybla.Text = Math.Floor(lbl_res_dipla_toybla.Text * diplo_toyblo_timh)
        lbl_kostos_mona_toybla.Text = Math.Floor(lbl_res_mona_toybla.Text * mono_toyblo_timh)
        lbl_kostos_sidera_senazi.Text = Math.Floor(lbl_res_senazi.Text * 4 * senazi_sidero)

        Dim sunolo As Integer = Val(lbl_kostos_xtisimo.Text) + Val(lbl_kostos_sidera_senazi.Text) + Val(lbl_kostos_mona_toybla.Text) + Val(lbl_kostos_dipla_toybla.Text)
        'Integer.TryParse(sunolo_string, sunolo)
        lbl_sunolo.Text = sunolo
    End Sub

    Public Sub New()

        ' This call is required by the designer.
        InitializeComponent()
        Dim mhkos As Integer = 1
        Dim ypsos As Integer = 1
        text_mhkos.Text = mhkos
        text_ypsos.Text = ypsos

        ' ConnectionString = "Server=(local)\SQLEXPRESS;Initial Catalog=newteks;Integrated Security=SSPI"
        myconnection = New SqlConnection(ConnectionString)
        myconnection.Open()

        da = New SqlDataAdapter("select * from walls", myconnection)
        da.Fill(ds, "wallsds")
        datatable = ds.Tables("wallsds")
        currRec = 0
        totalRec = datatable.Rows.Count
        MsgBox(totalRec)

        Dim dg1 As New DataGrid
        Dim dadg1 As New SqlDataAdapter
        Dim dsdg1 As New DataSet
        dadg1 = New SqlDataAdapter("select * from walls", myconnection)
        dadg1.Fill(dsdg1, "wallsdg")

        dg1.SetDataBinding(dsdg1, "wallsdg")
        dg1.Show()

        dg1.Location = New Point(297, 67)
        dg1.Size = New Size(161, 91)
        dg1.CaptionText = "Microsoft DataGrid Control"
        'AddHandler myDataGrid.MouseUp, AddressOf Grid_MouseUp


        Me.Controls.Add(dg1)

        myconnection.Close()
        Me.BindingContext(ds.Tables("wallsds")).Position = 1

        lbl_id.DataBindings.Add("Text", ds.Tables("wallsds"), "ID")
        text_project_no.DataBindings.Add("Text", ds.Tables("wallsds"), "project_no")
        text_toixos.DataBindings.Add("Text", ds.Tables("wallsds"), "wall_number")
        text_mhkos.DataBindings.Add("Text", ds.Tables("wallsds"), "width")
        text_ypsos.DataBindings.Add("Text", ds.Tables("wallsds"), "height")
        cmb_orofos.DataBindings.Add("Text", ds.Tables("wallsds"), "floor")
        lbl_res_dipla_toybla.DataBindings.Add("Text", ds.Tables("wallsds"), "num_of_double_bricks")
        lbl_res_mona_toybla.DataBindings.Add("Text", ds.Tables("wallsds"), "num_of_single_bricks")
        cmb_typos.DataBindings.Add("Text", ds.Tables("wallsds"), "type")
        lbl_res_senazi.DataBindings.Add("Text", ds.Tables("wallsds"), "senazi")
        lbl_res_sidera.DataBindings.Add("Text", ds.Tables("wallsds"), "sidera")
        If cmb_typos.Text = "Εξωτερικός" Then
            lbl_res_embadon2.DataBindings.Add("Text", ds.Tables("walls"), "area")
        End If
        If cmb_typos.Text = "Εσωτερικός" Then
            lbl_res_embadon1.DataBindings.Add("Text", ds.Tables("wallsds"), "area")
        End If
        lbl_kostos_dipla_toybla.DataBindings.Add("Text", ds.Tables("wallsds"), "kostos_dipla_toybla")
        lbl_kostos_mona_toybla.DataBindings.Add("Text", ds.Tables("wallsds"), "kostos_mona_toybla")
        lbl_kostos_sidera_senazi.DataBindings.Add("Text", ds.Tables("wallsds"), "kostos_sidera")
        lbl_kostos_xtisimo.DataBindings.Add("Text", ds.Tables("wallsds"), "kostos_ergatika")
        lbl_sunolo.DataBindings.Add("Text", ds.Tables("wallsds"), "synolo")
        text_notes.DataBindings.Add("Text", ds.Tables("wallsds"), "notes")

        ' Add any initialization after the InitializeComponent() call.

    End Sub

    Private Sub btn_epomeno_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_epomeno.Click
        Me.BindingContext(ds.Tables("wallsds")).Position += 1
    End Sub

    Private Sub btn_proigoumeno_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_proigoumeno.Click
        Me.BindingContext(ds.Tables("wallsds")).Position -= 1
    End Sub
    Private Sub btn_calculate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
        calculate()
    End Sub

    Private Sub btn_save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_save.Click

        If text_mhkos.Text > 0 And text_ypsos.Text > 0 Then
            calculate()
        End If

        mycommand1 = New SqlCommand("update wallsds set project_no= @project_no , " & _
                                "wall_number= @wall_number , " & _
                                "width = @width , " & _
                                "height = @height, " & _
                                "floor = @floor, " & _
                                "num_of_double_bricks = @num_of_double_bricks , " & _
                                "num_of_single_bricks = @num_of_single_bricks , " & _
                                "type = @type , " & _
                                "senazi = @senazi , " & _
                                "sidera = @sidera , " & _
                                "kostos_dipla_toybla = @kostos_dipla_toybla , " & _
                                "kostos_mona_toybla = @kostos_mona_toybla , " & _
                                "kostos_sidera = @kostos_sidera , " & _
                                "kostos_ergatika = @kostos_ergatika , " & _
                                "synolo = @synolo , " & _
                                "notes = @notes  " & _
                                " where ID=@ID ", myconnection)
   
        mycommand1.Parameters.Add("@ID", SqlDbType.Int).Value = lbl_id.Text
        mycommand1.Parameters.Add("@project_no", SqlDbType.Char).Value = text_project_no.Text
        mycommand1.Parameters.Add("@wall_number", SqlDbType.Char).Value = text_toixos.Text
        mycommand1.Parameters.Add("@width", SqlDbType.Decimal).Value = text_mhkos.Text
        mycommand1.Parameters.Add("@height", SqlDbType.Decimal).Value = text_ypsos.Text
        mycommand1.Parameters.Add("@floor", SqlDbType.NChar).Value = cmb_orofos.Text
        mycommand1.Parameters.Add("@num_of_double_bricks", SqlDbType.Decimal).Value = lbl_res_dipla_toybla.Text
        mycommand1.Parameters.Add("@num_of_single_bricks", SqlDbType.Decimal).Value = lbl_res_mona_toybla.Text
        mycommand1.Parameters.Add("@type", SqlDbType.NChar).Value = cmb_typos.Text
        mycommand1.Parameters.Add("@senazi", SqlDbType.Decimal).Value = lbl_res_senazi.Text
        mycommand1.Parameters.Add("@sidera", SqlDbType.Decimal).Value = lbl_res_sidera.Text
        mycommand1.Parameters.Add("@kostos_dipla_toybla", SqlDbType.Decimal).Value = lbl_kostos_dipla_toybla.Text
        mycommand1.Parameters.Add("@kostos_mona_toybla", SqlDbType.Decimal).Value = lbl_kostos_mona_toybla.Text
        mycommand1.Parameters.Add("@kostos_sidera", SqlDbType.Decimal).Value = lbl_kostos_sidera_senazi.Text
        mycommand1.Parameters.Add("@kostos_ergatika", SqlDbType.Decimal).Value = lbl_kostos_xtisimo.Text
        mycommand1.Parameters.Add("@synolo", SqlDbType.Decimal).Value = lbl_sunolo.Text
        '  mycommand1.Parameters.Add("@area", SqlDbType.Char).Value = text_toixos.Text
        mycommand1.Parameters.Add("@notes", SqlDbType.Text).Value = text_notes.Text

        myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand1.Transaction = mytransaction
        da.UpdateCommand = mycommand1
        Try
            da.Update(ds, "wallsds")
            mytransaction.Commit()
            MsgBox("Επιτυχής αποθήκευση δεδωμένων !")
        Catch ex As Exception
            mytransaction.Rollback()
            MsgBox("Αποτυχία αποθήκευσης δεδωμένων !")
        Finally
            myconnection.Close()
        End Try

   
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

    End Sub
End Class


My connection string is:
VB.NET:
 Public ConnectionString As String = "Server=(local)\SQLEXPRESS;Initial Catalog=newteks;Integrated Security=SSPI"

All the files in Solution explorer have Copy to output directory to DO NOT COPY

At Server explorer I have a MSSQL database kostas-pc\sqlexpress.newteks.dbo

When I run the application and click save there dataset does not updtae the database using this:
VB.NET:
 myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand1.Transaction = mytransaction
        da.UpdateCommand = mycommand1
        Try
            da.Update(ds, "wallsds")
            mytransaction.Commit()
            MsgBox("Επιτυχής αποθήκευση δεδωμένων !")
        Catch ex As Exception
            mytransaction.Rollback()
            MsgBox("Αποτυχία αποθήκευσης δεδωμένων !")
        Finally
            myconnection.Close()
        End Try

and this code doesn't fail either. It displays that the database has been updated.

I dont see any database file in Solution Explorer in order to change it to DO NOT COPY.

Also if I change the connection string to
Public ConnectionString As String = "Server=(local)\SQLEXPRESS;AttachDbFilename=C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\newteks.mdf; Integrated Security=SSPI;User Instance=FALSE"


where the MDF file is located I am getting an error...as the MSSQL server does not allow even to copy the file to an other location.


I remember I solved this problem in the past but after formatting my PC and reinstalled the database and project the problem came back.


can you please help.

thanks
 
Do this:
VB.NET:
        myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand1.Transaction = mytransaction
        da.UpdateCommand = mycommand1
        Try
[B]MessageBox.Show("dt haschanges: " & ds.Tables("wallsds").HasChanges)[/B]
            Dim numRowsSaved as Integer = da.Update(ds, "wallsds")
[B]MessageBox.Show("numRowsSaved: " & numRowsSaved)[/B]
            mytransaction.Commit()
            MsgBox("Επιτυχής αποθήκευση δεδωμένων !")
        Catch ex As Exception
            mytransaction.Rollback()
            MsgBox("Αποτυχία αποθήκευσης δεδωμένων !")
        Finally
            myconnection.Close()
        End Try

What do you see in your messageboxes?
 
thanks for the reply

I get at error at
MessageBox.Show("dt haschanges: " & ds.Tables("wallsds").HasChanges)
HasChanges is not a members of System.Data.DataTable

This MessageBox.Show("numRowsSaved: " & numRowsSaved) returns 0
 
Last edited:
in order to make everything easier I have made the following code which is sorter than the previous

VB.NET:
Imports System
Imports System.Xml
Imports System.Xml.Schema
Imports System.IO
Imports System.Data.SqlClient
Imports System.Collections
Imports System.Data

Public Class Form1
    Dim myconnection As SqlConnection
    Dim mytransaction As SqlTransaction
    Dim mycommand1 As SqlCommand
    Dim myparam As SqlParameter
    Dim da As SqlDataAdapter
    Dim ds As New DataSet()
    Dim args() As String = Environment.GetCommandLineArgs

    Dim datatable As DataTable
    Dim currRec, totalRec As Integer
    Public ConnectionString As String = "Server=(local)\SQLEXPRESS;Initial Catalog=test;Integrated Security=SSPI"
    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load


        myconnection = New SqlConnection(ConnectionString)
        myconnection.Open()

        da = New SqlDataAdapter("select * from test1", myconnection)
        da.Fill(ds, "testds")
        datatable = ds.Tables("testds")
        currRec = 0
        totalRec = datatable.Rows.Count
        MsgBox(totalRec)

        myconnection.Close()
        Me.BindingContext(ds.Tables("testds")).Position = 0

        text_id.DataBindings.Add("Text", ds.Tables("testds"), "ID")
        text_name.DataBindings.Add("Text", ds.Tables("testds"), "name")

    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        mycommand1 = New SqlCommand("update testds set name= @text_name   where ID=@ID ", myconnection)
        mycommand1.Parameters.Add("@ID", SqlDbType.Int).Value = text_id.Text
        mycommand1.Parameters.Add("@text", SqlDbType.NText).Value = text_name.Text

        myconnection.Open()
        mytransaction = myconnection.BeginTransaction()
        mycommand1.Transaction = mytransaction
        da.UpdateCommand = mycommand1

        Try
            MessageBox.Show("dt haschanges: " & ds.Tables("testds").HasChanges)

            Dim numRowsSaved As Integer = da.Update(ds, "testds")
            MessageBox.Show("numRowsSaved: " & numRowsSaved)
            mytransaction.Commit()
            MsgBox("Success !")
        Catch ex As Exception
            mytransaction.Rollback()
            MsgBox("Failure !")
        Finally
            myconnection.Close()
        End Try
    End Sub
End Class



and also I get a Success message.

My table has:
ID: int
name: ntext


thanks
 
Last edited:
Back
Top