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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Development Tools
 ASP.NET
 Insert Function Adds two records
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

ViRiPuFF
Starting Member

Gibraltar
7 Posts

Posted - 02/03/2015 :  13:54:55  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/03/2015 :  14:31:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/03/2015 :  14:52:10  Show Profile  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/04/2015 :  14:11:57  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/04/2015 :  14:12:45  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/04/2015 :  15:08:29  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/04/2015 :  15:11:53  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/04/2015 :  15:16:51  Show Profile  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/04/2015 :  16:40:38  Show Profile  Reply with Quote
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

Gibraltar
7 Posts

Posted - 02/04/2015 :  17:42:14  Show Profile  Reply with Quote
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

USA
38200 Posts

Posted - 02/04/2015 :  18:06:02  Show Profile  Visit tkizer's Homepage  Reply with Quote


Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000