Solved

Datatable not creating new record

Posted on 2013-11-03
24
367 Views
Last Modified: 2013-11-03
What am I doing wrong?  Following is my code.  When I add a record and 'save' it, it's not creating a new record it's overwriting the last row in the database.

Loading Datatable:
            cmd = New SqlCommand("procCreateCSTable", connection)
            cmd.CommandType = CommandType.StoredProcedure

            adapter.SelectCommand = cmd
            adapter.Fill(dtCallServ)

Open in new window


Add button click:
 dtCallServ.NewRow()

Open in new window


Save button click:
 connection = New SqlConnection(ConnectString)
            Dim sqlDa As New SqlDataAdapter()

            adapter.SelectCommand = New SqlCommand()
            adapter.SelectCommand.Connection = connection
            adapter.SelectCommand.CommandText = "procCreateCSTable"
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure

            adapter.InsertCommand = New SqlCommand()
            adapter.InsertCommand.Connection = connection
            adapter.InsertCommand.CommandText = "procCreateCSTable"
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure

            adapter.UpdateCommand = New SqlCommand()
            adapter.UpdateCommand.Connection = connection
            adapter.UpdateCommand.CommandText = "procCreateCSTable"
            adapter.UpdateCommand.CommandType = CommandType.StoredProcedure
            Me.Validate()
            BindingContext(dtCallServ).EndCurrentEdit()

            For i As Integer = 0 To dtCallServ.Rows.Count - 1
                If dtCallServ.Rows(i).RowState = DataRowState.Added Then
                    Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
                    adapter.InsertCommand = cmdBuilder.GetInsertCommand
                    adapter.Update(dtCallServ)
                    connection.Open()
                    adapter.InsertCommand.ExecuteNonQuery()
                    connection.Close()
                ElseIf dtCallServ.Rows(i).RowState = DataRowState.Modified Then
                    Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
                    adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
                    adapter.Update(dtCallServ)
                    connection.Open()
                    adapter.UpdateCommand.ExecuteNonQuery()
                    connection.Close()
                End If
            Next

            dtCallServ.AcceptChanges()

Open in new window


Stored Procedure
USE [spis_beSQL]
GO
/****** Object:  StoredProcedure [dbo].[procCreateCSTable]    Script Date: 11/3/2013 10:21:34 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ProNet Systems
-- Create date: 11/1/2013
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[procCreateCSTable] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM  (SELECT ROW_NUMBER()  OVER (ORDER BY IncnoID DESC) AS Row, *,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) AS CS WHERE Row =1
END

Open in new window

0
Comment
Question by:K-9
  • 15
  • 9
24 Comments
 

Author Comment

by:K-9
ID: 39619888
When I 'add' a record and hit the save button, the .RowState = DataRowState.Modified is being reported instead of the .RowState = DataRowState.Added

Any idea why?
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619895
How do you "add" the record?

Also, you can change following code segment

            For i As Integer = 0 To dtCallServ.Rows.Count - 1
                If dtCallServ.Rows(i).RowState = DataRowState.Added Then
                    Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
                    adapter.InsertCommand = cmdBuilder.GetInsertCommand
                    adapter.Update(dtCallServ)
                    connection.Open()
                    adapter.InsertCommand.ExecuteNonQuery()
                    connection.Close()
                ElseIf dtCallServ.Rows(i).RowState = DataRowState.Modified Then
                    Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
                    adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
                    adapter.Update(dtCallServ)
                    connection.Open()
                    adapter.UpdateCommand.ExecuteNonQuery()
                    connection.Close()
                End If
            Next

Open in new window


to

                    Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
                    adapter.InsertCommand = cmdBuilder.GetInsertCommand
                    adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
                    adapter.Update(dtCallServ)

Open in new window


to get the same result
0
 

Author Comment

by:K-9
ID: 39619906
Thanks for the reply, not sure what you mean by how do I 'add' the record.  I call dtCallServ.NewRow() and then clear out all the textboxes and comboboxes on the form.  Am I missing something?

During the initial load of the datatable, I am only calling the first row (for performance reasons).
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619914
dtCallServ.NewRow() would return an empty row object which you then have to add to the rows collection. It is usually used programmatically like below

Dim dt As DataRow = dTable.NewRow
dt(0) = 1
dt(1) = "Value1"
dTable.Rows.Add(dt)


How are you controls bound to the datatable?
0
 

Author Comment

by:K-9
ID: 39619916
Oh sorry.. Here is how it's bound:

cmd = New SqlCommand("procCreateCSTable", connection)
            cmd.CommandType = CommandType.StoredProcedure

            adapter.SelectCommand = cmd
            adapter.Fill(dtCallServ)

 CSBindingSource1.DataSource = dtCallServ

            BindingNavigator1.BindingSource = CSBindingSource1

            txtIncidentNumberReq.DataBindings.Add(New Binding("Text", CSBindingSource1, "Incno", True))
            cmbComplaintantID.DataBindings.Add(New Binding("Text", CSBindingSource1, "CID", True))
            cmbOfficerInvolved.DataBindings.Add(New Binding("Text", CSBindingSource1, "Officer", True))
            cmbAssistedBy.DataBindings.Add(New Binding("Text", CSBindingSource1, "AssistedBy", True))
            cmbIncidentNature.DataBindings.Add(New Binding("Text", CSBindingSource1, "FoundComp", True))
            txtIncidentAddress.DataBindings.Add(New Binding("Text", CSBindingSource1, "Address1", True))
            txtIncidentCity.DataBindings.Add(New Binding("Text", CSBindingSource1, "City", True))
            txtIncidentZip.DataBindings.Add(New Binding("Text", CSBindingSource1, "Zip", True))
            txtIncidentSummary.DataBindings.Add(New Binding("Text", CSBindingSource1, "Details1", True))
cmbReceivedMethod.DataBindings.Add(New Binding("Text", CSBindingSource1, "Received", True))
            cmbCompletedMethod.DataBindings.Add(New Binding("Text", CSBindingSource1, "Completed", True))
            cmbMuncipality.DataBindings.Add(New Binding("Text", CSBindingSource1, "Muncipality", True))

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619926
Then only thing you need to do to add a new record is

CSBindingSource1.AddNew


This will add the row and automatically clear the controls as well.
0
 

Author Comment

by:K-9
ID: 39619933
I tried it before and just tried it again.  I put it under the add button click.  When I goto save. the dtcallserv.Rows(0).RowState is unchanged and the record does not get saved.  That's what has me so confused.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619934
And what is the state of Rows(1)?
0
 

Author Comment

by:K-9
ID: 39619940
Weird.. that is showing Added.  However the record is not saving to the database.
0
 

Author Comment

by:K-9
ID: 39619941
Is it because of my stored procedure only pulling in the first row?

SELECT * FROM  (SELECT ROW_NUMBER()  OVER (ORDER BY IncnoID DESC) AS Row, *,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) AS CS WHERE Row =1
0
 

Author Comment

by:K-9
ID: 39619947
Nope.. I created a new Stored Procedure
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

Open in new window


and changed the commandtext to point to the new stored procedure however I still get the same result, not saving the new record to the database, just updates the existing record.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619955
What is the command text being generated for InsertCommand?
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:K-9
ID: 39619961
"INSERT INTO [CALLSERV] ([Incno], [DATECOMP], [FOUNDCOMP], [FoundLocat], [CID], [OFFICER], [NARRATIVE], [OTHERS], [STATUS], [CLOSEDATE], [DISP], [UPDATE], [COMPLETED], [RDATE1], [DateClear], [Updated], [Entered], [Day], [OtherTime], [Dispatched], [Details1], [TimeComp], [Arrived], [TimeClear], [ASSISTEDBY], [RECEIVED], [IncnoYear], [IncnoSeqNumber], [Muncipality], [Photo], [cDispatchedDate], [ADDRESS1], [CITY], [STATE], [Zip], [DOB], [AGE], [HomePhone], [FULLNAME], [WorkPhone], [IncnoNumber], [Address2], [DomesticViolence], [MutualAid], [IncnoID]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8, @p9, @p10, @p11, @p12, @p13, @p14, @p15, @p16, @p17, @p18, @p19, @p20, @p21, @p22, @p23, @p24, @p25, @p26, @p27, @p28, @p29, @p30, @p31, @p32, @p33, @p34, @p35, @p36, @p37, @p38, @p39, @p40, @p41, @p42, @p43, @p44, @p45)"
0
 

Author Comment

by:K-9
ID: 39619964
The dtCallServ.rows.count is 2
0
 

Author Comment

by:K-9
ID: 39619967
Wait.. I put in a try catch and with the adapter.update(dtCallserv) comes up with the error (attached screenshot)
However that textbox is filled in when I added the record:
error.jpg
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619977
Is the correct value being shown when you do following before call to update?

msgbox dtCallServ.Rows(1).Item("IncNo")
0
 

Author Comment

by:K-9
ID: 39619988
It's showing blank even though I have that textbox filled in.
0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39620082
Try adding a bindingsource.endedit call
0
 

Author Comment

by:K-9
ID: 39620157
Where would i put that in the save record button?
0
 
LVL 83

Accepted Solution

by:
CodeCruiser earned 500 total points
ID: 39620161
Yes before the update call.
0
 

Author Comment

by:K-9
ID: 39620387
Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
            adapter.InsertCommand = cmdBuilder.GetInsertCommand
            adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
            CSBindingSource1.EndEdit()
            adapter.Update(dtCallServ)

Open in new window


I still get the same error.
0
 

Author Comment

by:K-9
ID: 39620395
This is strange, after putting that code in, I did the following.. 2013-0804 was the previous incno number and 2013-0805 was the new record.

? dtCallServ.Rows(0).Item("Incno")
"2013-0804" {String}
    String: "2013-0804"
? dtCallServ.Rows(1).Item("Incno")
{}
    System.DBNull: {}
? dtCallServ.Rows(2).Item("Incno")
"2013-0806" {String}
    String: "2013-0805"
0
 

Author Comment

by:K-9
ID: 39620434
Finally got it!  Had to remove the dtCallServ.NewRow I put in the add button click.  Here is my complete working code. Thank you so much CodeCruiser, if you are ever in Pennsylvania, let me know I owe you a few beers :)

Form Load:
cmd = New SqlCommand("procCreateCSTable", connection)
            cmd.CommandType = CommandType.StoredProcedure

            adapter.SelectCommand = cmd
            adapter.Fill(dtCallServ)
CSBindingSource1.DataSource = dtCallServ

            BindingNavigator1.BindingSource = CSBindingSource1

Open in new window


Add button click:

CSBindingSource1.AddNew()

Open in new window


Save button click:

connection = New SqlConnection(ConnectString)

            adapter.SelectCommand = New SqlCommand()
            adapter.SelectCommand.Connection = connection
            adapter.SelectCommand.CommandText = "procCreateCSTable"
            adapter.SelectCommand.CommandType = CommandType.StoredProcedure

            adapter.InsertCommand = New SqlCommand()
            adapter.InsertCommand.Connection = connection
            adapter.InsertCommand.CommandText = "procCreateCSTable"
            adapter.InsertCommand.CommandType = CommandType.StoredProcedure

            adapter.UpdateCommand = New SqlCommand()
            adapter.UpdateCommand.Connection = connection
            adapter.UpdateCommand.CommandText = "procCreateCSTable"
            adapter.UpdateCommand.CommandType = CommandType.StoredProcedure
            
          Dim cmdBuilder As New SqlClient.SqlCommandBuilder(adapter)
            adapter.InsertCommand = cmdBuilder.GetInsertCommand
            adapter.UpdateCommand = cmdBuilder.GetUpdateCommand
            CSBindingSource1.EndEdit()
            BindingContext(dtCallServ).EndCurrentEdit()

            adapter.Update(dtCallServ)

            dtCallServ.AcceptChanges()

Open in new window


Stored Procedure:

USE [spis_beSQL]
GO
/****** Object:  StoredProcedure [dbo].[procCreateCSTable]    Script Date: 11/3/2013 5:17:49 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		ProNet Systems
-- Create date: 11/1/2013
-- Description:	
-- =============================================
ALTER PROCEDURE [dbo].[procCreateCSTable] 
	-- Add the parameters for the stored procedure here
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT * FROM  (SELECT ROW_NUMBER()  OVER (ORDER BY IncnoID DESC) AS Row, *,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) AS CS WHERE Row =1
END

Open in new window

0
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39620494
Glad its working :-)
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now