Database Updates Failing

Discussion in 'ASP General' started by Will, Nov 5, 2005.

  1. Will

    Will Guest

    I have a small problem which I am sure is easily resolved but my lack of ASP
    experience is holdng me back so I'm hoping someone can assist.
    I have a very simple procedure which involves a user updating a field value
    on an ASP.Net page, clicking a button which then kicks off an SQL stored
    procedure to update the underlying table. The problem is the update is being
    ignored, there are no error messages. If I run the procedure from query
    analyser it works.
    My code is:
    Dim myParam1 As New SqlParameter("@cancType", SqlDbType.VarChar)
    myParam1.Direction = ParameterDirection.Input
    myParam1.Value = txtCancType.Text

    Dim myParam3 As New SqlParameter("@cNum", SqlDbType.Int)
    myParam3.Direction = ParameterDirection.Input
    myParam3.Value = cint(session("myID")

    Dim daReg As New SqlDataAdapter
    daReg.SelectCommand = New SqlCommand
    daReg.SelectCommand.Connection = SqlConn2
    daReg.SelectCommand.CommandText = "updateDetails2"
    daReg.SelectCommand.CommandType = CommandType.StoredProcedure
    daReg.SelectCommand.Parameters.Add(myParam1)
    daReg.SelectCommand.Parameters.Add(myParam3)

    SqlConn2.Open()
    daReg.SelectCommand.ExecuteNonQuery()
    SqlConn2.Close()

    And the stored procedure is:
    CREATE PROCEDURE dbo.updateDetails2
    @cancType varchar(50),
    @cNum int

    As
    update PatientDetails
    set cancType = @cancType--,basics_FK = @basicsFK
    where basics_FK = @cNum
    GO

    In the PatientDetails table the basics_FK field is set up as "int".

    Any/all constructive suggestions appreciated.

    Thanks

    WW
    Will, Nov 5, 2005
    #1
    1. Advertising

  2. Will wrote:
    > I have a small problem which I am sure is easily resolved but my lack
    > of ASP experience is holdng me back so I'm hoping someone can assist.
    > I have a very simple procedure which involves a user updating a field
    > value on an ASP.Net page,


    There was no way for you to know it, but this is a classic asp newsgroup
    (which is much different from ASP.Net).
    While you may be lucky enough to find a dotnet-knowledgeable person here who
    can answer your question, you can eliminate the luck factor by posting your
    question to a group where those dotnet-knowledgeable people hang out. I
    suggest microsoft.public.dotnet.framework.aspnet.

    However, read on:

    > clicking a button which then kicks off an
    > SQL stored procedure to update the underlying table. The problem is
    > the update is being ignored, there are no error messages.


    A good tool to use is SQL Profiler (in the SQL Server program group in your
    Windows Start menu). A trace will show what is happening.

    Are you using Visual Studio? if so, have you attempted to debug this code?
    If not, you can turn tracing on in web.config. Check out the documentation
    at msdn.microsoft.com/library

    > If I run
    > the procedure from query analyser it works.
    > My code is:
    > Dim myParam1 As New SqlParameter("@cancType", SqlDbType.VarChar)
    > myParam1.Direction = ParameterDirection.Input
    > myParam1.Value = txtCancType.Text


    You never set the length parameter. Non-fixed-length datatypes require the
    length to be set. I'm surprised executing the command did not raise an
    error. Are you sure this entire section isn't enclosed in a
    Try...Catch...End Try block?

    >
    > Dim myParam3 As New SqlParameter("@cNum", SqlDbType.Int)
    > myParam3.Direction = ParameterDirection.Input
    > myParam3.Value = cint(session("myID")
    >
    > Dim daReg As New SqlDataAdapter


    OK, your query does not return records, so there is no need for a data
    adapter.

    > daReg.SelectCommand = New SqlCommand
    > daReg.SelectCommand.Connection = SqlConn2


    Why "SqlConn2"? This implies that this is the second connection object on
    this page. Are you dealing with two separate sql servers?

    > daReg.SelectCommand.CommandText = "updateDetails2"
    > daReg.SelectCommand.CommandType = CommandType.StoredProcedure
    > daReg.SelectCommand.Parameters.Add(myParam1)
    > daReg.SelectCommand.Parameters.Add(myParam3)
    >
    > SqlConn2.Open()
    > daReg.SelectCommand.ExecuteNonQuery()


    A simpler version follows (I usually open my connection first, that way I
    avoid running all those other lines of code if the connection fails):

    SqlConn2.Open()
    Dim cmd as New SqlCommand(SqlConn2)
    Wth cmd
    .CommandText = "updateDetails2"
    .CommandType = CommandType.StoredProcedure
    .Parameters.Add("@cancType", SqlDbType.VarChar, _
    50).Value=txtCancType.Text
    .Parameters.Add("@cNum", _
    SqlDbType.Int).Value=txtCancType.Text
    End With
    Try
    cmd.ExecuteNonQuery()
    Catch ex as exception
    'display ex.Message
    finally
    SqlConn2.close()
    SqlConn2.dispose()
    end try

    > SqlConn2.Close()
    >
    > And the stored procedure is:
    > CREATE PROCEDURE dbo.updateDetails2
    > @cancType varchar(50),
    > @cNum int
    >
    > As


    /%
    It is good practice to use the following command in all procedures to
    be run via ADO/ADO.Net - google it to see why
    %/

    SET NOCOUNT ON

    > update PatientDetails
    > set cancType = @cancType--,basics_FK = @basicsFK
    > where basics_FK = @cNum
    > GO


    If you still have problems, and neither tracing nor SQL Profiler help,
    gollow up in the aspnet group.

    Bob Barrows
    --
    Microsoft MVP - ASP/ASP.NET
    Please reply to the newsgroup. This email account is my spam trap so I
    don't check it very often. If you must reply off-line, then remove the
    "NO SPAM"
    Bob Barrows [MVP], Nov 5, 2005
    #2
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Jill Graham

    Concurrent database updates

    Jill Graham, Jul 1, 2004, in forum: ASP .Net
    Replies:
    5
    Views:
    3,023
    Trevor Benedict R
    Jul 3, 2004
  2. Gino
    Replies:
    0
    Views:
    277
  3. Rickey Whitworth

    Gridview Updates not writing to database after clicking update

    Rickey Whitworth, Nov 7, 2005, in forum: ASP .Net Web Controls
    Replies:
    6
    Views:
    153
    Steven Cheng[MSFT]
    Nov 11, 2005
  4. Jeff Clark
    Replies:
    15
    Views:
    197
    Jeff Cochran
    Nov 20, 2003
  5. Neil Zanella
    Replies:
    3
    Views:
    109
    Ken Cox [Microsoft MVP]
    Jan 23, 2005
Loading...

Share This Page