Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Datatable not creating new record

Posted on 2013-11-03
24
Medium Priority
?
377 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: 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!

 
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
 

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 2000 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

Python: Series & Data Frames With Pandas

Learn the basics of Python’s pandas library of series & data frames and how we can use these tools for data manipulation.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
Real-time is more about the business, not the technology. In day-to-day life, to make real-time decisions like buying or investing, business needs the latest information(e.g. Gold Rate/Stock Rate). Unlike traditional days, you need not wait for a fe…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

715 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