Help with an SQL Update 1

[Terwin]

I have a form that is updating site information in a MS SQL database via an SQLCommand and a stored procedure. When I run the stored procedure from the server explorer, supplying the parameters myself, it works fine, so I know the sp is not the culprit. Any suggestions for how I might go about debugging this bit of code, which is executed in the OnClick of the Update button? The update does not seem to take place when I attempt to run the sp using the button, but I know at least that the code is being executed.

Thanks,
T

SqlConnection conn = get_sqlconnection();
SqlCommand Cmd = new SqlCommand ("[updSite]", conn);
Cmd.CommandType = CommandType.StoredProcedure;

Cmd.Parameters.Add(New SqlParameter("@id", SqlDbType.Int))
Cmd.Parameters(0).Value = Request.QueryString.Get("id")
Cmd.Parameters.Add(New SqlParameter("@name", SqlDbType.VarChar))
Cmd.Parameters(1).Value = txtName.Text
Cmd.Parameters.Add(New SqlParameter("@address1", SqlDbType.VarChar))
Cmd.Parameters(2).Value = txtAddr1.Text
Cmd.Parameters.Add(New SqlParameter("@address2", SqlDbType.VarChar))
Cmd.Parameters(3).Value = txtAddr2.Text
Cmd.Parameters.Add(New SqlParameter("@city", SqlDbType.VarChar))
Cmd.Parameters(4).Value = txtCity.Text
Cmd.Parameters.Add(New SqlParameter("@state", SqlDbType.VarChar))
Cmd.Parameters(5).Value = mnuState.SelectedItem.Value
Cmd.Parameters.Add(New SqlParameter("@zip", SqlDbType.VarChar))
Cmd.Parameters(6).Value = txtZip.Text

Cmd.ExecuteNonQuery()

 

[NoCoolHandle]

Ok, first things...

wrap your cmd.executenonquery in a try catch block

e.g.

Cmd.Parameters(6).Value = txtZip.Text
try
Cmd.ExecuteNonQuery()
catch ex as exception
 response.write "<h3>" & ex.message & "</h3>"
end try

1. Most likley a security issue
use a grant statement...

e.g. Grant execute on YourStoredProc to aspnet

assuming that aspnet is "how" your asp app logs into sqlserver...

2. If the above clues don't work run the sql trace/sql profiler utitity and "watch" your website login and execute your statement... this should show you what SQL server is seeing...

3. What does your Create Proc statement look like? (Just to the "as" statement..)
e.g.
[blue]
Create Proc someproc
@i int,
@x int output
as
[/blue]


Rob

 

[Terwin]

Rob,

Thanks for your reply - it was just what I needed to get my thinking on the right track. Using the query profiler, I determined that the parameters the sp was receiving were not the updated params, they were the original ones.

Yikes, I didn't realize the page was posting back before executing the onClick handler. The Page_Load method was overwriting the values in the form with another query. An If Not Page.IsPostBack solved the problem.

Thanks,
T