• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 381
  • Last Modified:

Datatable not creating new record

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
K-9
Asked:
K-9
  • 15
  • 9
1 Solution
 
K-9Author Commented:
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
 
CodeCruiserCommented:
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
 
K-9Author Commented:
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
CodeCruiserCommented:
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
 
K-9Author Commented:
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
 
CodeCruiserCommented:
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
 
K-9Author Commented:
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
 
CodeCruiserCommented:
And what is the state of Rows(1)?
0
 
K-9Author Commented:
Weird.. that is showing Added.  However the record is not saving to the database.
0
 
K-9Author Commented:
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
 
K-9Author Commented:
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
 
CodeCruiserCommented:
What is the command text being generated for InsertCommand?
0
 
K-9Author Commented:
"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
 
K-9Author Commented:
The dtCallServ.rows.count is 2
0
 
K-9Author Commented:
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
 
CodeCruiserCommented:
Is the correct value being shown when you do following before call to update?

msgbox dtCallServ.Rows(1).Item("IncNo")
0
 
K-9Author Commented:
It's showing blank even though I have that textbox filled in.
0
 
CodeCruiserCommented:
Try adding a bindingsource.endedit call
0
 
K-9Author Commented:
Where would i put that in the save record button?
0
 
CodeCruiserCommented:
Yes before the update call.
0
 
K-9Author Commented:
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
 
K-9Author Commented:
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
 
K-9Author Commented:
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
 
CodeCruiserCommented:
Glad its working :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 15
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now