CODEWITHSUNDEEP
.netvb.netvisual-studio-2012
LadyDi
LadyDi

Reputation: 1

VB.Net Update Command Not Working

I am new to VB.Net and am having a great deal of trouble with a Windows Form Application.

I have a button on my form that I am trying to set to update my local SQL Server database. Here is the code that I currently have behind my Save button. I've adjusted the code behind my Save button, and it now looks like this:

    Public Property InsertCommand As SqlCommand
    Public Property cn As SqlConnection     
    Private Sub b_Save_Click(sender As Object, e As EventArgs) Handles b_Save.Click
         Try

        Dim row As CV_Calls_DBDataSet.CV_Main_tblRow = DS_CV_Calls_DB.CV_Main_tbl.NewCV_Main_tblRow

        row.Call_Base_num = Me.CallNumber_mtx.Text
        row.Cash_Vendor_Code = Me.cbo_Vendor.Text
        row.Error_Code = Me.cbo_Error.Text
        row.Error_Location_Code = Me.cbo_Location.Text
        row.RowID = DS_CV_Calls_DB.CV_Main_tbl.Count + 1

        DS_CV_Calls_DB.CV_Main_tbl.AddCV_Main_tblRow(row)
        cn = New SqlConnection("Data Source=(LocalDB)\v11.0;Database=|DataDirectory|\CV_Calls_DB.mdf;Integrated Security=TRUE;")
        CreateCallAdapter(cn)
        'For Each row In DS_CV_Calls_DB.CV_Main_tbl.Rows
        '    Debug.WriteLine(row.RowState)
        '    Debug.WriteLine(row.Call_Base_num)
        '    Debug.WriteLine(row.Cash_Vendor_Code)
        '    Debug.WriteLine(row.Error_Location_Code)
        '    Debug.WriteLine(row.Error_Code)
        '    Debug.WriteLine(row.RowID)
        'Next


        TA_CV_Main.Update(DS_CV_Calls_DB.CV_Main_tbl)
        CreateCallAdapter(cn)
        MessageBox.Show(DS_CV_Calls_DB.HasChanges())
        MessageBox.Show("Saved!")
        Me.Close()
    Catch ex As Exception
        MessageBox.Show(ex.ToString)
    End Try
End Sub

    Public Function CreateCallAdapter(ByVal connection As SqlConnection) As SqlDataAdapter
    Dim adapter As SqlDataAdapter = New SqlDataAdapter
    Dim command As SqlCommand = New SqlCommand("Select * FROM CV_Main_tbl", connection)
    'Create InsertCommand
    command = New SqlCommand("INSERT INTO CV_Main_tbl (Call_Base_num, Cash_Vendor_Code, Error_Location_Code, Error_Code) VALUES (@Call_Base_num, @Cash_Vendor_Code, @Error_Location_Code, @Error_Code)", connection)
    'Add the parameters for the InsertCommand

    command.Parameters.Add("@Call_Base_num", SqlDbType.Int, Me.CallNumber_mtx.Text)
    command.Parameters.Add("@Cash_Vendor_Code", SqlDbType.SmallInt, Me.cbo_Vendor.Text)
    command.Parameters.Add("@Error_Location_Code", SqlDbType.SmallInt, Me.cbo_Location.Text)
    command.Parameters.Add("@Error_Code", SqlDbType.SmallInt, Me.cbo_Error.Text)
    command.Parameters.Add("@RowID", SqlDbType.Int, DS_CV_Calls_DB.CV_Main_tbl.Count + 1)
    adapter.InsertCommand = command
    Return adapter
End Function

When I run this code, I still get a message saying that the data was saved. However, when I check the actual table via the Server Explorer, nothing has been added. Do you have any suggestions as to what I am doing wrong?

Upvotes: 0

Views: 1227

Answers (2)

Daniel
Daniel

Reputation: 145

I ran into this same issue recently. If you are setting any input values when the page loads, those same values are being set again as you 'submit' the page. For example, this is a simplified version of my Page_Load event:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    displayValues()'this function sets all the input values on the page

End Sub

When I tried to submit the form and run an update statement, the update statement was running, but no values were being changed because the input values were being reset by displayValues() first.

Turns out this was happening on PostBack so to fix it, I added 'IF Not IsPostBack' so that the values only automatically load on the initial page load:

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    If Not IsPostBack Then
        displayValues()'this function sets all the input values on the page
    End If
End Sub

Hope this helps!

Upvotes: 0

Tim Schmelter
Tim Schmelter

Reputation: 460158

DataSet.HasChanges does not mean that the changes(changed,deleted or inserted rows) were saved to database. It just means that at least one row in a DataTable of this DataSet has a RowState <> Unchanged.

You need to use the DataAdapter' Update method with appropriate InsertCommand, DeleteCommand and UpdateCommands.

So i assume that you have not provided these Commands. Have you added rows to the table? Then you need an InsertCommand. Have you changed a field of a DataRow, then you need the UpdateCommand, have you called DataRow.Delete on one or multiple rows, then you need to provide the DeleteCommand.

Have a look at the example on MSDN.

In your code-sample you aren't adding the newly created DataRow to the DataTable. Therefore you need to use the auto-generated method with a name similar to:

DS_CV_Calls_DB.CV_Main_tbl.AddtblRow(row)

before you call DataAdapter.Update.

Upvotes: 2

Related Questions