Database Updates Failing

[Will]

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

 

[Bob Barrows [MVP]]

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