Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Development Tools
 ASP.NET
 Insert Function Adds two records

Author  Topic 

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-03 : 13:54:55
I have a simple function that inserts values from text fields into table. This works but always inserts two identical records rather than the expected one. The primary key on the table is set to Id. Incr.1 and Id.seed 1.


Imports System.Data
Imports System.Data.SqlClient
Imports System.IO
Partial Class LivingEvacueesUp
Inherits System.Web.UI.Page

Protected Sub Page_Load(sender As Object, e As EventArgs) Handles Me.Load

End Sub

Public Function InsertData(ByVal cmd As SqlCommand) As Boolean
Dim strConnString As String = System.Configuration.ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
Dim con As New SqlConnection(strConnString)
cmd.CommandType = CommandType.Text
cmd.Connection = con
Try
con.Open()
cmd.ExecuteNonQuery()
Return True
Catch ex As Exception
Response.Write(ex.Message)
Return False
Finally
con.Close()
con.Dispose()
End Try
End Function

Protected Sub btnUpload_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnUpload.Click

' Read the text fields values

Dim surname As String = txtSurname.Text
Dim forename As String = txtForename.Text
Dim dob As Date = txtDob.Text
Dim address As String = txtAddress.Text
Dim evacto As String = txtEvacuatedTo.Text
Dim extrainfo As String = txtRemarks.Text



'insert into database
Dim strQuery As String = "INSERT INTO tblLivingEvacuees(Surname, Forename, DateOfBirth, Address, EvacuatedTo, ExtraInfo)" _
& "VALUES(@surname, @forename, @dob, @address, @evacto, @extrainfo)"



Dim cmd As New SqlCommand(strQuery)
cmd.Parameters.AddWithValue("@surname", SqlDbType.VarChar).Value = surname
cmd.Parameters.AddWithValue("@forename", SqlDbType.VarChar).Value = forename

cmd.Parameters.AddWithValue("@dob", SqlDbType.Date).Value = dob
cmd.Parameters.AddWithValue("@address", SqlDbType.VarChar).Value = address
cmd.Parameters.AddWithValue("@evacto", SqlDbType.VarChar).Value = evacto
cmd.Parameters.AddWithValue("@extrainfo", SqlDbType.VarChar).Value = extrainfo


If InsertData(cmd) Then

lblMessage.ForeColor = System.Drawing.Color.Green
lblMessage.Text = "Information Submitted Successfully"
Else
lblMessage.ForeColor = System.Drawing.Color.Red
lblMessage.Text = "Error!" _
& " Please try again."
End If

End Sub
End Class



Thanks

viripuff

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-03 : 14:31:56
I don't see any issue with your code. I would check if there's a trigger on the table and also run your code in debug mode. Step through the code and determine if you are calling it twice.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-03 : 14:52:10
Thanks I'll check the table again and run in debug that I have not tried.

viripuff
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-04 : 14:11:57
Here is the code for the table. I can't see anything wrong with this either but I'm new to SQL.

USE [List1777]
GO

/****** Object: Table [dbo].[tblLivingEvacuees] Script Date: 2/4/2015 12:07:59 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[tblLivingEvacuees](
[LivingEvacueesId] [int] IDENTITY(1,1) NOT NULL,
[Surname] [varchar](50) NOT NULL,
[Forename] [varchar](50) NOT NULL,
[DateOfBirth] [date] NOT NULL,
[Address] [varchar](50) NOT NULL,
[EvacuatedTo] [varchar](50) NOT NULL,
[ExtraInfo] [varchar](max) NULL,
CONSTRAINT [tblLivingEvacuees_PrimaryKey] PRIMARY KEY CLUSTERED
(
[LivingEvacueesId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO



viripuff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 14:12:45
If there isn't a trigger on the table that's also doing an insert, then the problem is your application code. You need to step through the code to figure out where the second insert is occurring.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-04 : 15:08:29
I'm not sure whether there's a trigger on the table. My presumption is that the sql for the table is correct. The table is part of a db created on GoDaddy, could this have anything to do with this error?

viripuff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 15:11:53
The code you posted didn't include a trigger, but you can easily check in Management Studio by navigating to the table, expanding it and then expanding Triggers. If nothing appears, then there isn't a trigger causing the issue.

The problem is in your application code though, I would bet a million bucks.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-04 : 15:16:51
Ok thanks. Now I need to learn how to step through the code. I never done this before.

viripuff
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-04 : 16:40:38
By stepping through the code as suggested I now know that after executing InsertData(cmd)it goes back to
Dim strQuery As String = "INSERT INTO tblLivingEvacuees(Surname, Forename, DateOfBirth, Address, EvacuatedTo, ExtraInfo)" _
& "VALUES(@surname, @forename, @dob, @address, @evacto, @extrainfo)"

and then goes on to execute InsertData(cmd) again. Thus 2 records are inserted. But I can't see why its going back instead of executing the rest of the code?

viripuff
Go to Top of Page

ViRiPuFF
Starting Member

7 Posts

Posted - 2015-02-04 : 17:42:14
I edited the code as below and it solved the problem thanks

If IsPostBack Then

InsertData(cmd)
cmd.Dispose()
cmd.Cancel()
Response.Redirect("LivingEvacueesUp.aspx")
Exit Sub

End If

viripuff
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 18:06:02


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
   

- Advertisement -