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.VarCh ar)
myParam1.Direct ion = ParameterDirect ion.Input
myParam1.Value = txtCancType.Tex t

Dim myParam3 As New SqlParameter("@ cNum", SqlDbType.Int)
myParam3.Direct ion = ParameterDirect ion.Input
myParam3.Value = cint(session("m yID")

Dim daReg As New SqlDataAdapter
daReg.SelectCom mand = New SqlCommand
daReg.SelectCom mand.Connection = SqlConn2
daReg.SelectCom mand.CommandTex t = "updateDetails2 "
daReg.SelectCom mand.CommandTyp e = CommandType.Sto redProcedure
daReg.SelectCom mand.Parameters .Add(myParam1)
daReg.SelectCom mand.Parameters .Add(myParam3)

SqlConn2.Open()
daReg.SelectCom mand.ExecuteNon Query()
SqlConn2.Close( )

And the stored procedure is:
CREATE PROCEDURE dbo.updateDetai ls2
@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]]

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.publi c.dotnet.framew ork.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.VarCh ar)
myParam1.Direct ion = ParameterDirect ion.Input
myParam1.Value = txtCancType.Tex t
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...E nd Try block?

Dim myParam3 As New SqlParameter("@ cNum", SqlDbType.Int)
myParam3.Direct ion = ParameterDirect ion.Input
myParam3.Value = cint(session("m yID")

Dim daReg As New SqlDataAdapter
OK, your query does not return records, so there is no need for a data
adapter.
daReg.SelectCom mand = New SqlCommand
daReg.SelectCom mand.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.SelectCom mand.CommandTex t = "updateDetails2 "
daReg.SelectCom mand.CommandTyp e = CommandType.Sto redProcedure
daReg.SelectCom mand.Parameters .Add(myParam1)
daReg.SelectCom mand.Parameters .Add(myParam3)

SqlConn2.Open()
daReg.SelectCom mand.ExecuteNon Query()
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(SqlC onn2)
Wth cmd
.CommandText = "updateDetails2 "
.CommandType = CommandType.Sto redProcedure
.Parameters.Add ("@cancType" , SqlDbType.VarCh ar, _
50).Value=txtCa ncType.Text
.Parameters.Add ("@cNum", _
SqlDbType.Int). Value=txtCancTy pe.Text
End With
Try
cmd.ExecuteNonQ uery()
Catch ex as exception
'display ex.Message
finally
SqlConn2.close( )
SqlConn2.dispos e()
end try
SqlConn2.Close( )

And the stored procedure is:
CREATE PROCEDURE dbo.updateDetai ls2
@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"