HELP! -> Stored procedure and ASP.NET

I've created a SP in MS SQL Server 2005 Express. In ASP.NET I want to use this SP, but somehow my data is not added...what am I doing wrong?
Also: how can I efficiently debug a SP?

Here's my SP:

setANSI_NULLSON

setQUOTED_IDENTIFIERON

go

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

ALTERPROCEDURE [dbo].[sp_InsertNewUser]

-- Add the parameters for the stored procedure here

(@.UserNamenchar(20),

@.pwdnchar(15),

@.Emailnchar(35),

@.IsMalebit,

@.BirthDatedatetime,

@.Countrysmallint,

@.PerfectSexnchar(1),

@.HasHTMLbit,

@.HasOffersbit,

@.HasNewsLetterbit

)

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SETNOCOUNTON;

INSERTINTO dbo.tblMember(UserName,pwd,Email,IsMale,BirthDate,Country,PerfectSex,HasHTML,HasOffers,HasNewsLetter)

VALUES(@.UserName,@.pwd,@.Email,@.IsMale,@.BirthDate,@.Country,@.PerfectSex,@.HasHTML,@.HasOffers,@.HasNewsLetter)

END

Here's my code:
Dim BirthDate As DateTime = CDate(ddlMonth.SelectedValue + "-" & ddlDay.SelectedValue & "-" & ddlYear.SelectedValue)
Dim ActivationCode As String = Session.SessionID
Randomize()
Dim sPwd As String = CStr(Int((1000000 * Rnd()) + 100000))
Dim MyCommand As New SqlCommand("sp_InsertNewUser", DBConnection)
MyCommand.CommandType = Data.CommandType.StoredProcedure
MyCommand.Parameters.Add(New SqlParameter("@.UserName", tbUserName.Text))
MyCommand.Parameters.Add(New SqlParameter("@.pwd", sPwd))
MyCommand.Parameters.Add(New SqlParameter("@.Email", tbEmail.Text))
MyCommand.Parameters.Add(New SqlParameter("@.IsMale", ddlSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.BirthDate", BirthDate))
MyCommand.Parameters.Add(New SqlParameter("@.Country", ddlCountry.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.PerfectSex", ddlPerfectSex.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasHTML", ddlEmailSupport.SelectedValue))
MyCommand.Parameters.Add(New SqlParameter("@.HasOffers", cbProdServices.Checked))
MyCommand.Parameters.Add(New SqlParameter("@.HasNewsLetter", True))

You've missed the pertinent part of your code. After you add the parameters, are you actually executing your MyCommand? And, where is this code running and are you sure its running?|||

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.
I've put everything in a try catch statement but no errors occur! I've checked the field definitions and they are good as well...

but I take it you dont see any syntax errors?
And do you know how I can debug a SP?

Thanks!

|||

Peter Smith wrote:

Im sure its running, as I stepped through the code.
I execute the command with ExecuteNonQuery.
the db connection opens and the statement appears to be executed.

I guess we'll have to trust you on this one.

Peter Smith wrote:

but I take it you dont see any syntax errors?

I don't see any obvious syntax errors. Have you tried running your stored procedure from Query Analyzer?

Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text

Peter Smith wrote:

And do you know how I can debug a SP?

Which IDE are you using? Visual Studio 2003? I know this includes a stored procedure debugging capability from within Server Explorer but I have not really used it myself (seeHOW TO: Debug Stored Procedures in Visual Studio .NET). I know there is a Transact-SQL debugger available from within Query Analyzer (seeStarting the Debugger in BOL) and I've only played around with that a bit. Unfortunately I debug stored procedures using old-fashioned manual methods: running the stored procedure in Query Analyzer, examining the results, tweaking the code if there are errors/unexpected results. I don't use a formal debugging tool.|||

I dont know how to use that quote thing ;)

"Also, I guess you are using ASP.NET 1.1? The syntax you are using for parameters has been deprecated for ASP.NET 2.0 (you would now use the AddWithValue method). Personally I would use the constructor that specifies the data type, e.g.:
MyCommand.Parameters.Add(New SqlParameter("@.UserName", SqlDbType.NChar,20)).Value = tbUserName.Text
or, more simply:
MyCommand.Parameters.Add("@.UserName", SqlDbType.NChar,20).Value = tbUserName.Text"

actually I use ASP.NET 2.0, I just didnt know I could also do it otherwise.
I will now use that simple constructor you provided, although I dont really see the purpose of specifying the datatype...is that for better debugging, or faster code or...I dont know...

I use Visual Web Developer 2005 Express and SQL Server 2005 Express...can I then still use the tools (query analyzer and server explorer) you mentioned?

Thanks!