Question about saving datatable to database

I have a vb.net windows application with a sql backend.  The form has a bindingsource and bindingnavigator.  On form load I create the datatable and fill it in from a table from my database.

When i edit a record from my form, I can save it and navigating through the records I can see it is still saved, however when I close my application and reopen it the old data is there.  This tells me that my issue is saving the datatable to the database.  How can I do this?  This is my code as an example:

    Public connection As SqlConnection = New SqlConnection(ConnectString)
    Public adapter As New SqlDataAdapter
    Public SPISDataSet As New DataSet
    Public dtMastName As New DataTable

     ReadConnectionStringFile()
     connection = New SqlConnection(ConnectString)
     connection.Open()

     Dim qryCreateTableMN As New SqlCommand("select * from MastName", connection)
     adapter.SelectCommand = qryCreateTableMN
     adapter.Fill(dtMastName)  'Fill the datatable from database
     connection.Close()

Private Sub btnSaveRecord_Click(sender As Object, e As EventArgs) Handles btnSaveRecord.Click

Dim CmdBuilderD As New SqlClient.SqlCommandBuilder(adapter)
adapter.UpdateCommand = CmdBuilderD.GetUpdateCommand
adapter.Update(dtMastName)
Me.Validate()
            Me.BindingSource1.EndEdit()

End Sub

Open in new window


From what I can gather is that I should not need a seperate 'INSERT' query because I am using a bindingsource and datatable, correct?
K-9Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Brian CroweDatabase AdministratorCommented:
It's been a while since I did database development from that side but form my recollection you still need to define the update and insert methods whether it be a SQL query or a stored procedure.  Essentially when you call Update it cycles through the rows and executes the appropriate command based on their rowstatus (changed, new, etc.).
0
CodeCruiserCommented:
You are editing a record so you should need insertcommand. You are already getting the update command so it should work (if you click on save). Does your table have a primary key defined?
0
K-9Author Commented:
I should or shouldn't need an insertcommand?  Its not a new record, just an existing one.  And yes I do have a primary key defined.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

CodeCruiserCommented:
typo. You should NOT need an insertcommand if you are modifying an existing record.

What happens if you remove the following lines?

Me.Validate()
            Me.BindingSource1.EndEdit()
0
K-9Author Commented:
I get no error message, but it still doesn't save it to the database.  It does save it to the datatable, since I can navigate away and then back and it will show the updated data.  However, like before once I close and reopen the application it goes back to the old data.
0
K-9Author Commented:
What is strange is that when I do a debug on the CmdBuilderD.Getupdate the query comes back as:

"UPDATE [Muncipality] SET [MuncipalityName] = @p1 WHERE (([ID] = @p2) AND ((@p3 = 1 AND [MuncipalityName] IS NULL) OR ([MuncipalityName] = @p4)))"

Thing is, Muncipality is not even in that datatable, I do have other datatables created when the application runs, but they are assigned to other datatables.
0
K-9Author Commented:
This is the code I have run when the application starts up.  

Public Sub CreateDataTables()

        Try
            ReadConnectionStringFile()
            connection = New SqlConnection(ConnectString)
            connection.Open()

            Dim qryCreateTableMN As New SqlCommand("select * from MastName", connection)
            adapter.SelectCommand = qryCreateTableMN
            'adapter.Fill(SPISDataSet, "dtMastName")
            adapter.Fill(dtMastName)


            Dim qryCreateTableCS As New SqlCommand("select *, CONVERT(VARCHAR, TimeComp, 108) AS newTimeComp,CONVERT(VARCHAR, Dispatched, 108) AS newDispatched,CONVERT(VARCHAR, Arrived, 108) AS newArrived,CONVERT(VARCHAR, TimeClear, 108) AS newTimeClear,CONVERT(VARCHAR, OtherTime, 108) AS newOtherTime from Callserv", connection)
            adapter.SelectCommand = qryCreateTableCS
            adapter.Fill(dtCallServ)

            Dim qryCreateTablecmbCSCID As New SqlCommand("select NameID, FullName, Convert(VarChar, DOB, 101) As DOB from MastName Order By NameID", connection)
            adapter.SelectCommand = qryCreateTablecmbCSCID
            adapter.Fill(dtcmbCallServCID)

            Dim qryCreateTablecmbMuncipality As New SqlCommand("select * from Muncipality Order By MuncipalityName", connection)
            adapter.SelectCommand = qryCreateTablecmbMuncipality
            adapter.Fill(dtcmbMuncipality)

            connection.Close()

        Catch ex As Exception
            MsgBox(ex.ToString)

        End Try

    End Sub

Open in new window


The form I am dealing with now is the dtMastName
0
K-9Author Commented:
So I added code: adapter.SelectCommand.CommandText ="select * from MastName"

It's now selecting the right table, but the query is really messed up.  I don't understand why it is doing this, below is the result of the query it created.  It's a long one!  By the way it is still not updating the database.

"UPDATE [MastName] SET [DEPTID] = @p1, [Entered] = @p2, [NameID] = @p3, [NameIDType] = @p4, [LASTNAME] = @p5, [FIRSTNAME] = @p6, [MiddleInitial] = @p7, [SUFFIX] = @p8, [ADDRESS1] = @p9, [CITY] = @p10, [STATE] = @p11, [Zip] = @p12, [DOB] = @p13, [AGE] = @p14, [SEX] = @p15, [RACE] = @p16, [ETHNIC] = @p17, [HomePhone] = @p18, [SSN] = @p19, [OLNNO] = @p20, [OLNSTATE] = @p21, [FULLNAME] = @p22, [ADDCOPY] = @p23, [DECEASED] = @p24, [WorkPhone] = @p25, [Updated] = @p26, [EXPUNGE] = @p27, [MEMO] = @p28, [PROVADDS] = @p29, [NAME] = @p30, [Contact1] = @p31, [Contact2] = @p32, [Contact3] = @p33, [CPhone1] = @p34, [CPhone2] = @p35, [CPhone3] = @p36, [CellPhone] = @p37, [Address2] = @p38, [ShortID] = @p39, [Height] = @p40, [Weight] = @p41, [Hair] = @p42, [Eyes] = @p43, [Scars] = @p44, [MO] = @p45, [Photo] = @p46, [POB] = @p47, [Occupation] = @p48, [Employer] = @p49, [Permit] = @p50, [chkBackGroundColor] = @p51, [C2Phone1] = @p52, [C2Phone2] = @p53, [C2Phone3] = @p54, [AlarmCo] = @p55, [AlarmCoPhone] = @p56, [AlarmType] = @p57, [FaxNumber] = @p58, [BMuncipality] = @p59, [BActive] = @p60, [Religion] = @p61, [Signature] = @p62, [FBI] = @p63, [MNU] = @p64, [SID1] = @p65, [SID2] = @p66, [SID3] = @p67, [Resident] = @p68 WHERE (((@p69 = 1 AND [DEPTID] IS NULL) OR ([DEPTID] = @p70)) AND ((@p71 = 1 AND [Entered] IS NULL) OR ([Entered] = @p72)) AND ([NameID] = @p73) AND ((@p74 = 1 AND [NameIDType] IS NULL) OR ([NameIDType] = @p75)) AND ((@p76 = 1 AND [LASTNAME] IS NULL) OR ([LASTNAME] = @p77)) AND ((@p78 = 1 AND [FIRSTNAME] IS NULL) OR ([FIRSTNAME] = @p79)) AND ((@p80 = 1 AND [MiddleInitial] IS NULL) OR ([MiddleInitial] = @p81)) AND ((@p82 = 1 AND [SUFFIX] IS NULL) OR ([SUFFIX] = @p83)) AND ((@p84 = 1 AND [ADDRESS1] IS NULL) OR ([ADDRESS1] = @p85)) AND ((@p86 = 1 AND [CITY] IS NULL) OR ([CITY] = @p87)) AND ((@p88 = 1 AND [STATE] IS NULL) OR ([STATE] = @p89)) AND ((@p90 = 1 AND [Zip] IS NULL) OR ([Zip] = @p91)) AND ((@p92 = 1 AND [DOB] IS NULL) OR ([DOB] = @p93)) AND ((@p94 = 1 AND [AGE] IS NULL) OR ([AGE] = @p95)) AND ((@p96 = 1 AND [SEX] IS NULL) OR ([SEX] = @p97)) AND ((@p98 = 1 AND [RACE] IS NULL) OR ([RACE] = @p99)) AND ((@p100 = 1 AND [ETHNIC] IS NULL) OR ([ETHNIC] = @p101)) AND ((@p102 = 1 AND [HomePhone] IS NULL) OR ([HomePhone] = @p103)) AND ((@p104 = 1 AND [SSN] IS NULL) OR ([SSN] = @p105)) AND ((@p106 = 1 AND [OLNNO] IS NULL) OR ([OLNNO] = @p107)) AND ((@p108 = 1 AND [OLNSTATE] IS NULL) OR ([OLNSTATE] = @p109)) AND ((@p110 = 1 AND [FULLNAME] IS NULL) OR ([FULLNAME] = @p111)) AND ((@p112 = 1 AND [ADDCOPY] IS NULL) OR ([ADDCOPY] = @p113)) AND ((@p114 = 1 AND [DECEASED] IS NULL) OR ([DECEASED] = @p115)) AND ((@p116 = 1 AND [WorkPhone] IS NULL) OR ([WorkPhone] = @p117)) AND ((@p118 = 1 AND [Updated] IS NULL) OR ([Updated] = @p119)) AND ((@p120 = 1 AND [EXPUNGE] IS NULL) OR ([EXPUNGE] = @p121)) AND ((@p122 = 1 AND [MEMO] IS NULL) OR ([MEMO] = @p123)) AND ((@p124 = 1 AND [PROVADDS] IS NULL) OR ([PROVADDS] = @p125)) AND ((@p126 = 1 AND [NAME] IS NULL) OR ([NAME] = @p127)) AND ((@p128 = 1 AND [Contact1] IS NULL) OR ([Contact1] = @p129)) AND ((@p130 = 1 AND [Contact2] IS NULL) OR ([Contact2] = @p131)) AND ((@p132 = 1 AND [Contact3] IS NULL) OR ([Contact3] = @p133)) AND ((@p134 = 1 AND [CPhone1] IS NULL) OR ([CPhone1] = @p135)) AND ((@p136 = 1 AND [CPhone2] IS NULL) OR ([CPhone2] = @p137)) AND ((@p138 = 1 AND [CPhone3] IS NULL) OR ([CPhone3] = @p139)) AND ((@p140 = 1 AND [CellPhone] IS NULL) OR ([CellPhone] = @p141)) AND ((@p142 = 1 AND [Address2] IS NULL) OR ([Address2] = @p143)) AND ((@p144 = 1 AND [ShortID] IS NULL) OR ([ShortID] = @p145)) AND ((@p146 = 1 AND [Height] IS NULL) OR ([Height] = @p147)) AND ((@p148 = 1 AND [Weight] IS NULL) OR ([Weight] = @p149)) AND ((@p150 = 1 AND [Hair] IS NULL) OR ([Hair] = @p151)) AND ((@p152 = 1 AND [Eyes] IS NULL) OR ([Eyes] = @p153)) AND ((@p154 = 1 AND [Scars] IS NULL) OR ([Scars] = @p155)) AND ((@p156 = 1 AND [MO] IS NULL) OR ([MO] = @p157)) AND ([Photo] = @p158) AND ((@p159 = 1 AND [POB] IS NULL) OR ([POB] = @p160)) AND ((@p161 = 1 AND [Occupation] IS NULL) OR ([Occupation] = @p162)) AND ((@p163 = 1 AND [Employer] IS NULL) OR ([Employer] = @p164)) AND ([Permit] = @p165) AND ([chkBackGroundColor] = @p166) AND ([PrintID] = @p167) AND ((@p168 = 1 AND [C2Phone1] IS NULL) OR ([C2Phone1] = @p169)) AND ((@p170 = 1 AND [C2Phone2] IS NULL) OR ([C2Phone2] = @p171)) AND ((@p172 = 1 AND [C2Phone3] IS NULL) OR ([C2Phone3] = @p173)) AND ((@p174 = 1 AND [AlarmCo] IS NULL) OR ([AlarmCo] = @p175)) AND ((@p176 = 1 AND [AlarmCoPhone] IS NULL) OR ([AlarmCoPhone] = @p177)) AND ((@p178 = 1 AND [AlarmType] IS NULL) OR ([AlarmType] = @p179)) AND ((@p180 = 1 AND [FaxNumber] IS NULL) OR ([FaxNumber] = @p181)) AND ((@p182 = 1 AND [BMuncipality] IS NULL) OR ([BMuncipality] = @p183)) AND ([BActive] = @p184) AND ((@p185 = 1 AND [Religion] IS NULL) OR ([Religion] = @p186)) AND ((@p187 = 1 AND [FBI] IS NULL) OR ([FBI] = @p188)) AND ((@p189 = 1 AND [MNU] IS NULL) OR ([MNU] = @p190)) AND ((@p191 = 1 AND [SID1] IS NULL) OR ([SID1] = @p192)) AND ((@p193 = 1 AND [SID2] IS NULL) OR ([SID2] = @p194)) AND ((@p195 = 1 AND [SID3] IS NULL) OR ([SID3] = @p196)) AND ((@p197 = 1 AND [Resident] IS NULL) OR ([Resident] = @p198)))"

Open in new window

0
K-9Author Commented:
Doing a debug on the parameter values, everything comes back as 'Nothing'.
0
K-9Author Commented:
Finally got it working!  Though I don't quite understand it (found it by searching and searching through google).  Here is the code:

            adapter.SelectCommand.CommandText ="select * from MastName"
            Dim builder = New SqlCommandBuilder(adapter)
            adapter.InsertCommand = builder.GetInsertCommand
            adapter.DeleteCommand = builder.GetDeleteCommand
            adapter.UpdateCommand = builder.GetUpdateCommand

            BindingSource1.EndEdit()
            BindingContext(SPISDataSet.Tables("dtMastName")).EndCurrentEdit()
            adapter.Update(SPISDataSet, "dtMastName")
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
K-9Author Commented:
I was able to resolve it myself.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.