Member Avatar for cpopham

I am trying to learn VB .NET again. Have not used in a long time. I have bound my controls to my datatable and then I put values into the controls, I then have a button that I am trying to get to add/update the rows to the database. It acts like it works fine no errors or anything, but whn I look at the table data after the update, it is all null... ugg.. can someone please look at my code and tell me what I am doing wrong?

Dim conn As SqlCeConnection
    Dim da As SqlCeDataAdapter
    Dim dtAcct As New DataTable("Account")
    Dim dtAcctType As New DataTable("AccountType")


    Private Sub frmAccount_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim cmd As New System.Data.SqlServerCe.SqlCeCommand
        Dim cmd1 As String = "SELECT * FROM Account"
        Dim da2 As New SqlCeDataAdapter

        conn = New SqlCeConnection("Data Source=C:\Documents and Settings\Owner\Desktop\Finances\HomeComplete\Databases\HomeComplete.sdf")


        conn.Open()
        cmd.Connection = conn
        da = New SqlCeDataAdapter(cmd1, conn)
        Dim cmdBuild As SqlCeCommandBuilder = New SqlCeCommandBuilder(da)
        da.Fill(dtAcct)
        cmd.CommandText = "SELECT * FROM AccountType"
        da2 = New SqlCeDataAdapter(cmd)
        da2.Fill(dtAcctType)

        txtAcctName.DataBindings.Add("Text", dtAcct, "AcctName")
        txtPhone.DataBindings.Add("Text", dtAcct, "AcctPh")
        dtOpenDate.DataBindings.Add("Text", dtAcct, "AcctOpenDate")
        dtCloseDate.DataBindings.Add("Text", dtAcct, "AcctCloseDate")
        chkActive.DataBindings.Add("checked", dtAcct, "AcctActive")
        txtWeb.DataBindings.Add("Text", dtAcct, "AcctWebAddress")
        txtID.DataBindings.Add("Text", dtAcct, "AcctWebID")
        txtPW.DataBindings.Add("Text", dtAcct, "AccteWebPWord")
        txt1.DataBindings.Add("Text", dtAcct, "AcctBillAddr1")
        txt2.DataBindings.Add("Text", dtAcct, "AcctBillAddr2")
        txtCity.DataBindings.Add("Text", dtAcct, "AcctBillCity")
        txtSt.DataBindings.Add("Text", dtAcct, "AcctBillSt")
        txtZip.DataBindings.Add("Text", dtAcct, "AcctBillZip")
        cboType.DataSource = dtAcctType
        cboType.DisplayMember = "AcctType"
        cboType.ValueMember = "AcctTypeID"
        cboType.DataBindings.Add("SelectedValue", dtAcct, "AcctTypeID")


    End Sub


    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            conn.Open()
        Catch
        End Try

        Dim cmd1 As String = "SELECT * FROM Account"
        da = New SqlCeDataAdapter(cmd1, conn)
        Dim cmdBuild As SqlCeCommandBuilder = New SqlCeCommandBuilder(da)
        da.UpdateCommand = cmdBuild.GetUpdateCommand
        da.InsertCommand = cmdBuild.GetInsertCommand
        da.DeleteCommand = cmdBuild.GetDeleteCommand
        da.Update(dtAcct)


    End Sub
  • 2 Contributors
  • 1 Reply
  • 216 Views
  • 5 Months Discussion Span
  • Latest Post Latest Post by WaterboyVB

Dani AI

Generated

Short summary for and : the symptoms (rows created/updated but all columns NULL) usually come from three things — bound-control edits not being committed into the DataTable, the SqlCeCommandBuilder being unable to generate correct commands (no primary key / complex SELECT), or exceptions being swallowed so failures are hidden. The code posted shows an empty Catch and no explicit EndEdit/Validate before calling Update; those are the most likely culprits.

Quick checklist (apply in this order):

  1. Commit binding edits before Update. Example:
Me.Validate()
BindingContext(dtAcct).EndCurrentEdit()
  1. Confirm there actually are pending changes:
Dim changes As DataTable = dtAcct.GetChanges()
If changes Is Nothing Then Debug.WriteLine("No pending changes")
  1. Ensure the Account table has a proper primary key (CommandBuilder needs one). Check in code:
If dtAcct.PrimaryKey.Length = 0 Then Debug.WriteLine("No primary key — CommandBuilder will fail")
  1. Stop hiding exceptions; log them so failing SQL or permission issues are visible:
Try
  conn.Open()
Catch ex As Exception
  Debug.WriteLine(ex.ToString())
End Try

Extra diagnostics and cautions: output the generated SQL (CommandBuilder.GetUpdateCommand().CommandText) to verify parameters and column mapping. Avoid calling AcceptChanges() before Update — that clears RowState so nothing will be sent to the database. Verify bound column names exactly match dtAcct columns (typos like AccteWebPWord will produce nulls). Finally, confirm the exact .sdf file being opened (Visual Studio sometimes copies the DB to bin\Debug) and check its last-write time to be sure the file observed is the one being updated.

Applying these checks usually reveals the root cause.

Member Avatar for WaterboyVB

Hi All,

Has anyone figured this out? I currently have the same issue, when I attempt an INSERT INTO or UPDATE it updates a dataset and not the SQL CE Server itself.

Any help is appreciated.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.