SQL Server Stored Proc not updating

I am attempting to do an update on a SQL server 2008R2 from a web page, and although the stored proc executes without error, nothing at all is being updated. I fully admit to being fairly new at this, so any advice would be appreciated.
Here's the web code:

Code:

---

Private Sub updaterpta()
        Dim conn As New SqlConnection("Data Source=servername;Initial Catalog=esdforms;User ID=sa;Password=whatever")
        Dim cmd As New SqlCommand
        cmd.CommandText = "updaterpta"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Connection = conn
        conn.Open()
        cmd.Parameters.Clear()
        cmd.Parameters.AddWithValue("@rptnum", rptnum.Text)
        cmd.Parameters.AddWithValue("@date", dt.Text)
        cmd.Parameters.AddWithValue("@inspname", DropDownList1.SelectedValue)
        cmd.Parameters.AddWithValue("@inspphone", inspphone.Text)
        cmd.Parameters.AddWithValue("@inspmail", inspmail.Text)
        cmd.Parameters.AddWithValue("@insptype", insptype.SelectedValue)
        cmd.Parameters.AddWithValue("@tier", RBL1.SelectedValue)
        cmd.Parameters.AddWithValue("@grade", RBL7.SelectedValue)
        cmd.Parameters.AddWithValue("@enddate", enddt.Text)
        If enddt.Text = "" Then
            cmd.Parameters("@enddate").Value = DBNull.Value
        Else : cmd.Parameters("@enddate").Value = enddt.Text
        End If
        cmd.Parameters.AddWithValue("@starttime", sttime.Text)
        cmd.Parameters.AddWithValue("@endtime", endtime.Text)
        cmd.Parameters.AddWithValue("@lastinspdate", lastinsp.Text)
        If lastinsp.Text = "" Then
            cmd.Parameters("@lastinspdate").Value = DBNull.Value
        Else : cmd.Parameters("@lastinspdate").Value = lastinsp.Text
        End If
        cmd.Parameters.AddWithValue("@apptype", RadioButtonList2.SelectedValue)
        cmd.Parameters.AddWithValue("@pgp", DDL2.SelectedValue)
        cmd.Parameters.AddWithValue("@aap", DDL3.SelectedValue)
        cmd.Parameters.AddWithValue("@golf", RBL3.SelectedValue)
        cmd.Parameters.AddWithValue("@ca", DropDownList2.SelectedValue)
        cmd.Parameters.AddWithValue("@catype", DDL4.SelectedValue)
        cmd.Parameters.AddWithValue("@pu", DDL6.SelectedValue)
        cmd.Parameters.AddWithValue("@putype", DDL5.SelectedValue)
        cmd.Parameters.AddWithValue("@drift", RBL2.SelectedValue)
        cmd.Parameters.AddWithValue("@tanksamp", Tank.Text)
        cmd.Parameters.AddWithValue("@driftcard", drift.Text)
        cmd.Parameters.AddWithValue("@other", other.Text)
        cmd.Parameters.AddWithValue("@gwpl", txt1080.Text)
        cmd.Parameters.AddWithValue("@photo", photos.Text)
        cmd.Parameters.AddWithValue("@docs", docs.Text)
        cmd.Parameters.AddWithValue("@appprog", RBL4.SelectedValue)
        cmd.Parameters.AddWithValue("@equipinfo", RBL5.SelectedValue)
        cmd.Parameters.AddWithValue("@caname", TextBox27.Text)
        cmd.Parameters.AddWithValue("@employer", TextBox1.Text)
        cmd.Parameters.AddWithValue("@equiptype", RBL6.SelectedValue)
        cmd.Parameters.AddWithValue("@handname", TextBox26.Text)
        cmd.Parameters.AddWithValue("@noatsite", TextBox2.Text)
        cmd.Parameters.AddWithValue("@equiptag", DropDownList3.SelectedValue)
        cmd.Parameters.AddWithValue("@puname", TextBox25.Text)
        cmd.Parameters.AddWithValue("@interviewee", TextBox3.Text)
        cmd.Parameters.AddWithValue("@inttitle", TextBox4.Text)
        cmd.Parameters.AddWithValue("@coAddress", TextBox5.Text)
        cmd.Parameters.AddWithValue("@city", TextBox6.Text)
        cmd.Parameters.AddWithValue("@state", TextBox7.Text)
        cmd.Parameters.AddWithValue("@zip", TextBox8.Text)
        cmd.Parameters.AddWithValue("@phone", TextBox10.Text)
        cmd.Parameters.AddWithValue("@pgpname", pgpname.Text)
        cmd.Parameters.AddWithValue("@commodity", DropDownList4.SelectedValue)
        cmd.Parameters.AddWithValue("@pest", DropDownList5.SelectedValue)
        cmd.Parameters.AddWithValue("@acres", TextBox15.Text)
        cmd.Parameters.AddWithValue("@volume", TextBox9.Text)
        cmd.Parameters.AddWithValue("@windspeed", TextBox11.Text)
        cmd.Parameters.AddWithValue("@winddir", direction.SelectedValue)
        cmd.Parameters.AddWithValue("@applocation", TextBox16.Text)
        cmd.Parameters.AddWithValue("@appcity", TextBox17.Text)
        cmd.Parameters.AddWithValue("@appzip", TextBox18.Text)
        cmd.Parameters.AddWithValue("@county", cnty.Text)
        cmd.Parameters.AddWithValue("@adjacentn", TextBox20.Text)
        cmd.Parameters.AddWithValue("@adjacente", TextBox22.Text)
        cmd.Parameters.AddWithValue("@adjacentw", TextBox23.Text)
        cmd.Parameters.AddWithValue("@adjacents", TextBox21.Text)
        cmd.Parameters.AddWithValue("@pestreg", RadioButtonList1.SelectedValue)
        cmd.Parameters.AddWithValue("@aguse", YN2.SelectedValue)
        cmd.Parameters.AddWithValue("@peststored", YN16.SelectedValue)
        cmd.Parameters.AddWithValue("@commtreated", YN6.SelectedValue)
        cmd.Parameters.AddWithValue("@appmethod", YN8.SelectedValue)
        cmd.Parameters.AddWithValue("@chemdilut", YN10.SelectedValue)
        cmd.Parameters.AddWithValue("@apprate", YN12.SelectedValue)
        cmd.Parameters.AddWithValue("@cautionlabel", YN4.SelectedValue)
        cmd.Parameters.AddWithValue("@labelsreadable", YN18.SelectedValue)
        cmd.Parameters.AddWithValue("@avoiddrift", YN23.SelectedValue)
        cmd.Parameters.AddWithValue("@appcertif", YN25.SelectedValue)
        cmd.Parameters.AddWithValue("@ruphandler", YN27.SelectedValue)
        cmd.Parameters.AddWithValue("@clearapparea", YN29.SelectedValue)
        cmd.Parameters.AddWithValue("@contamination", YN34.SelectedValue)
        cmd.Parameters.AddWithValue("@rinsed", YN36.SelectedValue)
        cmd.Parameters.AddWithValue("@disposed", YN38.SelectedValue)
        cmd.Parameters.AddWithValue("@decontamination", YN40.SelectedValue)
        cmd.Parameters.AddWithValue("@deptnotice", YN43.SelectedValue)
        cmd.Parameters.AddWithValue("@labelcoll", YN45.SelectedValue)
        cmd.Parameters.AddWithValue("@coll1080", YN47.SelectedValue)
        cmd.Parameters.AddWithValue("@comments", Comments.Text)
        cmd.Parameters.AddWithValue("@correction", TextBox13.Text)
        cmd.Parameters.AddWithValue("@reinsp", nextdt.Text)
        cmd.Parameters.AddWithValue("@chemapplied", product.SelectedValue)
        cmd.Parameters.AddWithValue("@ai", AI.Text)
        cmd.Parameters.AddWithValue("@rup", RUP.Text)
        cmd.Parameters.AddWithValue("@epa", EPA.Text)
        cmd.Parameters.AddWithValue("@rei", REI.Text)
        cmd.Parameters.AddWithValue("@tlchem", tlchem.Text)
        cmd.Parameters.AddWithValue("@measure", measure.SelectedValue)
        cmd.Parameters.AddWithValue("@chemapplied1", product1.Text)
        cmd.Parameters.AddWithValue("@ai1", ai1.Text)
        cmd.Parameters.AddWithValue("@rup1", rup1.Text)
        cmd.Parameters.AddWithValue("@epa1", epa1.Text)
        cmd.Parameters.AddWithValue("@rei1", rei1.Text)
        cmd.Parameters.AddWithValue("@tlchem1", tlchem1.Text)
        cmd.Parameters.AddWithValue("@measure1", measure1.SelectedValue)
        cmd.Parameters.AddWithValue("@chemapplied2", product2.Text)
        cmd.Parameters.AddWithValue("@ai2", ai2.Text)
        cmd.Parameters.AddWithValue("@rup2", rup2.Text)
        cmd.Parameters.AddWithValue("@epa2", epa2.Text)
        cmd.Parameters.AddWithValue("@rei2", rei2.Text)
        cmd.Parameters.AddWithValue("@tlchem2", tlchem2.Text)
        cmd.Parameters.AddWithValue("@measure2", measure2.SelectedValue)
        cmd.Parameters.AddWithValue("@chemapplied3", product3.Text)
        cmd.Parameters.AddWithValue("@ai3", ai3.Text)
        cmd.Parameters.AddWithValue("@rup3", rup3.Text)
        cmd.Parameters.AddWithValue("@epa3", epa3.Text)
        cmd.Parameters.AddWithValue("@rei3", rei3.Text)
        cmd.Parameters.AddWithValue("@tlchem3", tlchem3.Text)
        cmd.Parameters.AddWithValue("@measure3", measure3.SelectedValue)
        cmd.Parameters.AddWithValue("@signed", TextBox30.Text)
        cmd.Parameters.AddWithValue("@signdate", TextBox31.Text)
        If TextBox31.Text = "" Then
            cmd.Parameters("@signdate").Value = DBNull.Value
        Else : cmd.Parameters("@signdate").Value = TextBox31.Text
        End If
        cmd.Parameters.AddWithValue("@inspdate", TextBox29.Text)
        If TextBox29.Text = "" Then
            cmd.Parameters("@inspdate").Value = DBNull.Value
        Else : cmd.Parameters("@inspdate").Value = TextBox29.Text
        End If
        cmd.Parameters.AddWithValue("@agree", RadioButtonList50.SelectedValue)
        cmd.Parameters.AddWithValue("@forme", eform.Text)
        cmd.Parameters.AddWithValue("@formb", bform.Text)
        cmd.Parameters.AddWithValue("@formt", tform.Text)
        cmd.Parameters.AddWithValue("@eupnum", txteup.Text)
        cmd.Parameters.AddWithValue("@eupappr", eupappr.Text)
        cmd.Parameters.AddWithValue("@testco", testco.Text)
        cmd.Parameters.AddWithValue("@otherco", otherco.Text)
        cmd.Parameters.AddWithValue("@eupaddr", eupaddr.Text)
        cmd.Parameters.AddWithValue("@testaddr", testaddr.Text)
        cmd.Parameters.AddWithValue("@rep1", rep1.Text)
        cmd.Parameters.AddWithValue("@eupcty", eupcty.Text)
        cmd.Parameters.AddWithValue("@eupstate", eupstate.Text)
        cmd.Parameters.AddWithValue("@eupzip", eupzip.Text)
        cmd.Parameters.AddWithValue("@testcty", testcty.Text)
        cmd.Parameters.AddWithValue("@teststate", teststate.Text)
        cmd.Parameters.AddWithValue("@testzip", testzip.Text)
        cmd.Parameters.AddWithValue("@otherco1", otherco1.Text)
        cmd.Parameters.AddWithValue("@corep", corep.Text)
        cmd.Parameters.AddWithValue("@apprep", apprep.Text)
        cmd.Parameters.AddWithValue("@rep2", rep2.Text)
        cmd.Parameters.AddWithValue("@coreptitle", coreptitle.Text)
        cmd.Parameters.AddWithValue("@corepphone", corepphone.Text)
        cmd.Parameters.AddWithValue("@appreptitle", appreptitle.Text)
        cmd.Parameters.AddWithValue("@apprepphone", apprepphone.Text)
        cmd.Parameters.AddWithValue("@otherco2", otherco2.Text)
        cmd.Parameters.AddWithValue("@apprepemail", apprepmail.Text)
        cmd.Parameters.AddWithValue("@testemail", testemail.Text)
        cmd.Parameters.AddWithValue("@rep3", rep3.Text)
        cmd.Parameters.AddWithValue("@rep3title", rep3title.Text)
        cmd.Parameters.AddWithValue("@chkeup", Chkeup.SelectedValue)
        cmd.ExecuteNonQuery()
   
        conn.Close()

          reload()
    End Sub

---

And here's the stored proc:

Code:

---

USE [ESDForms]
GO
/****** Object:  StoredProcedure [dbo].[updaterpta]    Script Date: 11/13/2017 10:37:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:                <Author,,Name>
-- Create date: <Create Date,,>
-- Description:        <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[updaterpta]
        (@rptnum as nvarchar(50),
@date as date,
@inspname as nvarchar(50),
@inspphone as nvarchar(12),
@inspmail as nvarchar(50),
@insptype as nvarchar(12),
@tier as nvarchar(5),
@grade as nvarchar(1),
@enddate as date,
@starttime as varchar(4),
@endtime as varchar(4),
@lastinspdate as date,
@pgp as nvarchar(5),
@aap as nvarchar(5),
@golf as nvarchar(1),
@ca as nvarchar(5),
@catype as nvarchar(3),
@pu as nvarchar(5),
@putype as nvarchar(3),
@drift as nvarchar(1),
@tanksamp as nvarchar(2),
@driftcard as nvarchar(2),
@other as nvarchar(2),
@gwpl as nvarchar(2),
@photo as nvarchar(2),
@docs as nvarchar(2),
@appprog as nvarchar(35),
@equipinfo as nvarchar(20),
@caname as nvarchar(50),
@employer as nvarchar(50),
@equiptype as nvarchar(18),
@handname as nvarchar(50),
@noatsite as numeric(18,0),
@equiptag as nvarchar(50),
@puname as nvarchar(50),
@interviewee as nvarchar(50),
@inttitle as nvarchar(50),
@coaddress as nvarchar(50),
@city as nvarchar(50),
@state as nvarchar(2),
@zip as nvarchar(12),
@phone as nvarchar(12),
@pgpname as nvarchar(50),
@commodity as nvarchar(50),
@pest as nvarchar(50),
@acres as numeric(18,2),
@volume as numeric(18,0),
@windspeed as numeric(18,0),
@winddir as nvarchar(3),
@applocation as nvarchar(50),
@appcity as nvarchar(50),
@county as nvarchar(50),
@appzip as nvarchar(12),
@adjacentn as nvarchar(50),
@adjacente as nvarchar(50),
@adjacentw as nvarchar(50),
@adjacents as nvarchar(50),
@pestreg as nvarchar(3),
@aguse as nvarchar(3),
@peststored as nvarchar(3),
@commtreated as nvarchar(3),
@appmethod as nvarchar(3),
@chemdilut as nvarchar(3),
@apprate as nvarchar(3),
@cautionlabel as nvarchar(3),
@labelsreadable as nvarchar(3),
@avoiddrift as nvarchar(3),
@appcertif as nvarchar(3),
@ruphandler as nvarchar(3),
@clearapparea as nvarchar(3),
@contamination as nvarchar(3),
@rinsed as nvarchar(3),
@disposed as nvarchar(3),
@decontamination as nvarchar(3),
@labelcoll as nvarchar(3),
@coll1080 as nvarchar(3),
@deptnotice as nvarchar(3),
@comments as ntext,
@correction as numeric(18,0),
@reinsp as date,
@chemapplied as nvarchar(50),
@ai as nvarchar(max),
@epa as nvarchar(50),
@rup as nvarchar(1),
@rei as nvarchar(10),
@tlchem as numeric(10,2),
@measure as nvarchar(3),
@chemapplied1 as nvarchar(50),
@ai1 as nvarchar(max),
@epa1 as nvarchar(50),
@rup1 as nvarchar(1),
@rei1 as nvarchar(10),
@tlchem1 as numeric(10,2),
@measure1 as nvarchar(3),
@chemapplied2 as nvarchar(50),
@ai2 as nvarchar(max),
@epa2 as nvarchar(50),
@rup2 as nvarchar(1),
@rei2 as nvarchar(10),
@tlchem2 as numeric(10,2),
@measure2 as nvarchar(3),
@chemapplied3 as nvarchar(50),
@ai3 as nvarchar(max),
@epa3 as nvarchar(50),
@rup3 as nvarchar(1),
@rei3 as nvarchar(10),
@tlchem3 as numeric(10,2),
@measure3 as nvarchar(3),
@signed as nvarchar(50),
@signdate as date,
@agree as nvarchar(35),
@forme as nvarchar(50),
@formb as nvarchar(50),
@formt as nvarchar(50),
@inspdate as date,
@eupnum as nvarchar(20),
@eupappr as nvarchar(50),
@testco as nvarchar(50),
@otherco as nvarchar(50),
@eupaddr as nvarchar(50),
@testaddr as nvarchar(50),
@rep1 as nvarchar(50),
@eupcty as nvarchar(50),
@eupstate as nvarchar(2),
@eupzip as nvarchar(10),
@testcty as nvarchar(50),
@teststate as nvarchar(2),
@testzip as nvarchar(10),
@otherco1 as nvarchar(50),
@corep as nvarchar(50),
@apprep as nvarchar(50),
@rep2 as nvarchar(50),
@coreptitle as nvarchar(20),
@corepphone as nvarchar(50),
@appreptitle as nvarchar(20),
@apprepphone as nvarchar(20),
@otherco2 as nvarchar(50),
@apprepemail as nvarchar(50),
@testemail as nvarchar(50),
@rep3 as nvarchar(50),
@rep3title as nvarchar(20),
@chkeup as nvarchar(1),
@apptype as nvarchar(10))

AS
BEGIN
        -- SET NOCOUNT ON added to prevent extra result sets from
        -- interfering with SELECT statements.


update forma set
[date]=@date,
inspname=@inspname,
inspphone=@inspphone,
inspmail=@inspmail,
insptype= @insptype,
tier=@tier,
grade=@grade,
enddate=@enddate,
starttime=@starttime,
endtime=@endtime,
lastinspdate=@lastinspdate,
pgp=@pgp,
aap=@aap,
golf=@golf,
ca=@ca,
catype=@catype,
pu=@pu,
putype=@putype,
drift=@drift,
tanksamp=@tanksamp,
driftcard=@driftcard,
other=@other,
gwpl=@gwpl,
photo=@photo,
docs=@docs,
appprog=@appprog,
equipinfo=@equipinfo,
caname=@caname,
employer=@employer,
equiptype=@equiptype,
handname=@handname,
noatsite=@noatsite,
equiptag=@equiptag,
puname=@puname,
interviewee=@interviewee,
inttitle=@inttitle,
coaddress=@coaddress,
city=@city,
[state]=@state,
zip=@zip,
phone=@phone,
pgpname=@pgpname,
commodity=@commodity,
pest=@pest,
acres=@acres,
volume=@volume,
windspeed=@windspeed,
winddir=@winddir,
applocation=@applocation,
appcity=@appcity,
appzip=@appzip,
county=@county,
adjacentN=@adjacentn,
adjacente=@adjacente,
adjacentw=@adjacentw,
adjacents=@adjacents,
pestreg=@pestreg,
aguse=@aguse,
peststored=@peststored,
commtreated=@commtreated,
AppMethod=@appmethod,
chemdilut=@chemdilut,
apprate=@apprate,
cautionlabel=@cautionlabel,
labelsreadable=@labelsreadable,
avoiddrift=@avoiddrift,
appcertif=@appcertif,
ruphandler=@ruphandler,
clearapparea=@clearapparea,
contamination=@contamination,
rinsed=@rinsed,
disposed=@disposed,
decontamination=@decontamination,
labelcoll=@labelcoll,
coll1080=@coll1080,
comments=@comments,
correction=@correction,
reinsp=@reinsp,
ChemApplied=@chemapplied,
ai=@ai,
EPA=@epa,
REI=@rei,
RUP=@rup,
TlChem=@tlchem,
measure=@measure,
ChemApplied1=@chemapplied1,
ai1=@ai1,
EPA1=@epa1,
REI1=@rei1,
RUP1=@rup1,
TlChem1=@tlchem1,
measure1=@measure1,
ChemApplied2=@chemapplied2,
ai2=@ai2,
EPA2=@epa2,
REI2=@rei2,
RUP2=@rup2,
TlChem2=@tlchem2,
measure2=@measure2,
ChemApplied3=@chemapplied3,
ai3=@ai3,
EPA3=@epa3,
REI3=@rei3,
RUP3=@rei3,
TlChem3=@tlchem3,
measure3=@measure3,
signed=@signed,
signdate=@signdate,
agree=@agree,
inspdate=@inspdate,
forme=@forme,
formt=@formt,
formb=@formb,
eupnum=@eupnum,
eupappr=@eupappr,
testco=@testco,
otherco=@otherco,
eupaddr=@eupaddr,
testaddr=@testaddr,
rep1=@rep1,
eupcty=@eupcty,
eupstate=@eupstate,
eupzip=@eupzip,
testcty=@testcty,
teststate=@teststate,
testzip=@testzip,
otherco1=@otherco1,
corep=@corep,
apprep=@apprep,
rep2=@rep2,
coreptitle=@coreptitle,
corepphone=@corepphone,
appreptitle=@appreptitle,
apprepphone=@apprepphone,
otherco2=@otherco2,
apprepemail=@apprepemail,
testemail=@testemail,
rep3=@rep3,
rep3title=@rep3title,
chkeup=@chkeup,
deptnotice=@deptnotice,
apptype=@apptype
where rptnum = @rptnum
END

---

Any help would be appreciated. I have run the SP within SQL, and it seems fine.

I can't see anything wrong...are you sure the value of @rptNum is on the database?

Quote:

---

Originally Posted by TysonLPrice

I can't see anything wrong...are you sure the value of @rptNum is on the database?

---

Yes, quite sure. The form is populated by virtue of the user choosing a rptnum to edit from a drop-down that lists the rptnum values already in the database;

I'm glad you found nothing wrong:o, but evidently, there is..

Just a shot in the dark if you are really stuck....right at the top of the SPROC divide by zero.

declare @x int
set @x = 1 / 0

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

See if the app brings back that hard error. It will at least let you know you are hitting the SPROC.

Quote:

---

Originally Posted by TysonLPrice

Just a shot in the dark if you are really stuck....right at the top of the SPROC divide by zero.

declare @x int
set @x = 1 / 0

Msg 8134, Level 16, State 1, Line 3
Divide by zero error encountered.

See if the app brings back that hard error. It will at least let you know you are hitting the SPROC.

---

I am sure I am hitting the proc. I can run a script on the SQL server that shows the last time the proc was executed, and it's correct.

That might give a kind of false positive... I recommend trying Tyson's suggestion, as you may get an unexpected result.


You have a very large amount of parameters, and several of them have a data type issue that could well be causing some kind of failure.

The @enddate parameter is a good example, as the data type in the SP is date , but the data type you are passing to it is varchar based - because you are using .Parameters.AddWithValue (which guesses the data type based on the value you pass), and are passing a String to it.

You need to somehow convert the value from a String to a Date, but that can be awkward/unsafe, and how you do it depends on what format the text is in (alternatively simply swap the textbox to a control designed to input Date values, such as a DateTimePicker, and the problem disappears).

The same issue applies to at least some of the other parameters, so I recommend you check them all.


Also note that in the sections of code like this:

Code:

---

        cmd.Parameters.AddWithValue("@enddate", enddt.Text)
        If enddt.Text = "" Then
            cmd.Parameters("@enddate").Value = DBNull.Value
        Else : cmd.Parameters("@enddate").Value = enddt.Text
        End If

---

...there is no need for the Else, because you are just re-assigning the same value that you already have.