dcsimg
Results 1 to 6 of 6

Thread: SQL Server Stored Proc not updating

  1. #1

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    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.

  2. #2
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,389

    Re: SQL Server Stored Proc not updating

    I can't see anything wrong...are you sure the value of @rptNum is on the database?
    Please remember next time...elections matter!

  3. #3

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: SQL Server Stored Proc not updating

    Quote Originally Posted by TysonLPrice View Post
    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, but evidently, there is..

  4. #4
    Wall Poster TysonLPrice's Avatar
    Join Date
    Sep 2002
    Location
    Columbus, Ohio
    Posts
    3,389

    Re: SQL Server Stored Proc not updating

    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.
    Please remember next time...elections matter!

  5. #5

    Thread Starter
    Member
    Join Date
    Sep 2014
    Posts
    35

    Re: SQL Server Stored Proc not updating

    Quote Originally Posted by TysonLPrice View Post
    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.

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,301

    Re: SQL Server Stored Proc not updating

    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.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width