newbie learns ASP NET: null

Showing posts with label null. Show all posts

Thursday, March 29, 2012

newbie: inserting data into sql form writes only null values

Hi,

using VStudio 2005/sql server 2005

Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds a
new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:

I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is the
only field that actually writes a correct value

here's the code:using VStudio 2005/sql server 2005

here's the code:

<asp:TextBox ID="FirstName" runat="server"></asp:TextBox> 
Last name:
<asp:TextBox ID="LastName" runat="server"></asp:TextBox> 
Address:
<asp:TextBox ID="Address" runat="server"></asp:TextBox> 
City:
<asp:TextBox ID="City" runat="server"></asp:TextBox> 
Year created:
<asp:DropDownList ID="YearCreated" runat="server">
</asp:DropDownList>

<asp:Button ID="Save" runat="server" Text="Save" />
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString=
"<%$ ConnectionStrings:WHCConnectionString %>"
InsertCommand=
"INSERT INTO
[artsfestival] ([lastName], [firstName], [address], [city])
VALUES
(@.lastName, @.firstName, @.address, @.city)">
<InsertParameters>
<asp:FormParameter Name="lastName" Type="String"
FormField="LastName" />
<asp:FormParameter Name="firstName" Type="String"
FormField="FirstName" />
<asp:FormParameter Name="address" Type="String"
FormField="Address" />
<asp:FormParameter Name="city" Type="String"
FormField="City" />
</InsertParameters>
</asp:SqlDataSource>

"thersitz" <thersitz@.gmail.comwrote in message
news:OhRMtSXRHHA.2256@.TK2MSFTNGP02.phx.gbl...

Quote:

Originally Posted by

Hi,
>
using VStudio 2005/sql server 2005
>
Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds
a new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:
>
I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is
the only field that actually writes a correct value
>
here's the code:
>
>

Hi there,

Use

ControlParameter instead of FormParameter. The difference is that
FormParameter takes its value directly from Request.Form collection using the
name given by FormField. The problem with your vode is that, textbox does not
post its value in Request.Form[textBox.ID] but in
Request.Form[textBox.UniqueID] which reflects IDs of the parent controls.
Change you insertparameters declaration to:

--
Milosz

"thersitz" wrote:

Quote:

Originally Posted by

using VStudio 2005/sql server 2005
>
Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds a
new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:
>
I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is the
only field that actually writes a correct value
>
here's the code:
>
<asp:TextBox ID="FirstName" runat="server"></asp:TextBox> 
Last name:
<asp:TextBox ID="LastName" runat="server"></asp:TextBox> 
Address:
<asp:TextBox ID="Address" runat="server"></asp:TextBox> 
City:
<asp:TextBox ID="City" runat="server"></asp:TextBox> 
Year created:
<asp:DropDownList ID="YearCreated" runat="server">
</asp:DropDownList> 
>
<asp:Button ID="Save" runat="server" Text="Save" />
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString=
"<%$ ConnectionStrings:WHCConnectionString %>"
InsertCommand=
"INSERT INTO
[artsfestival] ([lastName], [firstName], [address], [city])
VALUES
(@.lastName, @.firstName, @.address, @.city)">
<InsertParameters>
<asp:FormParameter Name="lastName" Type="String"
FormField="LastName" />
<asp:FormParameter Name="firstName" Type="String"
FormField="FirstName" />
<asp:FormParameter Name="address" Type="String"
FormField="Address" />
<asp:FormParameter Name="city" Type="String"
FormField="City" />
</InsertParameters>
</asp:SqlDataSource>
>
>
>
>
"thersitz" <thersitz@.gmail.comwrote in message
news:OhRMtSXRHHA.2256@.TK2MSFTNGP02.phx.gbl...

Quote:

Originally Posted by

Hi,

using VStudio 2005/sql server 2005

Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds
a new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:

I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is
the only field that actually writes a correct value

here's the code:

>
>
>

Thanks Milosz, it worked.

I'm confused why the book had me use the FormParameter and FormFieldID --
but thanks for getting me past this point.

Take care.

"Milosz Skalecki [MCAD]" <mily242@.REMOVEITwp.plwrote in message
news:1206D448-EDF2-419B-9A48-D05F7B51FAAF@.microsoft.com...

Quote:

Originally Posted by

Hi there,
>
Use
>
ControlParameter instead of FormParameter. The difference is that
FormParameter takes its value directly from Request.Form collection using
the
name given by FormField. The problem with your vode is that, textbox does
not
post its value in Request.Form[textBox.ID] but in
Request.Form[textBox.UniqueID] which reflects IDs of the parent controls.
Change you insertparameters declaration to:
>
<InsertParameters>
<asp:ControlParameter Name="lastName" Type="String" ControlID="LastName"
PropertyName="Text"/>
<asp:ControlParameter Name="firstName" ControlID="FirstName" Type="String"
PropertyName="Text"/>
<asp:ControlParameter Name="address" Type="String" ControlID="Address"
PropertyName="Text"/>
<asp:ControlParameter Name="city" Type="String" ControlID="City"
PropertyName="Text"/>
</InsertParameters>
>
--
Milosz
>
>
"thersitz" wrote:
>

Quote:

Originally Posted by

> using VStudio 2005/sql server 2005
>>
>Have a simple web form that inserts the results in a table. It seems to
>write to the table, but not the values from the asp forms fields. It adds
>a
>new record and increments the id field by one -- but in all the other
>fields, it merely writes Null to the fields. He are some other points:
>>
>I am not inserting data into every field -- for test purposes I am only
>using 4 fields.
>The id field is NOT one of the fields on the asp form -- although it is
>the
>only field that actually writes a correct value
>>
>here's the code:
>>
> <asp:TextBox ID="FirstName" runat="server"></asp:TextBox> 
> Last name:
> <asp:TextBox ID="LastName" runat="server"></asp:TextBox> 
> Address:
> <asp:TextBox ID="Address" runat="server"></asp:TextBox> 
> City:
> <asp:TextBox ID="City" runat="server"></asp:TextBox> 
> Year created:
> <asp:DropDownList ID="YearCreated" runat="server">
> </asp:DropDownList> 
>>
> <asp:Button ID="Save" runat="server" Text="Save" />
> </div>
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString=
> "<%$ ConnectionStrings:WHCConnectionString %>"
> InsertCommand=
> "INSERT INTO
> [artsfestival] ([lastName], [firstName], [address], [city])
> VALUES
> (@.lastName, @.firstName, @.address, @.city)">
> <InsertParameters>
> <asp:FormParameter Name="lastName" Type="String"
> FormField="LastName" />
> <asp:FormParameter Name="firstName" Type="String"
> FormField="FirstName" />
> <asp:FormParameter Name="address" Type="String"
> FormField="Address" />
> <asp:FormParameter Name="city" Type="String"
> FormField="City" />
> </InsertParameters>
> </asp:SqlDataSource>
>>
>>
>>
>>
>"thersitz" <thersitz@.gmail.comwrote in message
>news:OhRMtSXRHHA.2256@.TK2MSFTNGP02.phx.gbl...

Quote:

Originally Posted by

Hi,
>
using VStudio 2005/sql server 2005
>
Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It
adds
a new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:
>
I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is
the only field that actually writes a correct value
>
here's the code:
>
>

>>
>>
>>

newbie: inserting data into sql form writes only null values

Hi,
using VStudio 2005/sql server 2005
Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds a
new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:
I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is the
only field that actually writes a correct value
here's the code:using VStudio 2005/sql server 2005
Have a simple web form that inserts the results in a table. It seems to
write to the table, but not the values from the asp forms fields. It adds a
new record and increments the id field by one -- but in all the other
fields, it merely writes Null to the fields. He are some other points:
I am not inserting data into every field -- for test purposes I am only
using 4 fields.
The id field is NOT one of the fields on the asp form -- although it is the
only field that actually writes a correct value
here's the code:
<asp:TextBox ID="FirstName" runat="server"></asp:TextBox> 
Last name:
<asp:TextBox ID="LastName" runat="server"></asp:TextBox> 
Address:
<asp:TextBox ID="Address" runat="server"></asp:TextBox> 
City:
<asp:TextBox ID="City" runat="server"></asp:TextBox> 
Year created:
<asp:DropDownList ID="YearCreated" runat="server">
</asp:DropDownList> 
<asp:Button ID="Save" runat="server" Text="Save" />
</div>
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString=
"<%$ ConnectionStrings:WHCConnectionString %>"
InsertCommand=
"INSERT INTO
[artsfestival] ([lastName], [firstName], [address], [city])
VALUES
(@.lastName, @.firstName, @.address, @.city)">
<InsertParameters>
<asp:FormParameter Name="lastName" Type="String"
FormField="LastName" />
<asp:FormParameter Name="firstName" Type="String"
FormField="FirstName" />
<asp:FormParameter Name="address" Type="String"
FormField="Address" />
<asp:FormParameter Name="city" Type="String"
FormField="City" />
</InsertParameters>
</asp:SqlDataSource>
"thersitz" <thersitz@.gmail.com> wrote in message
news:OhRMtSXRHHA.2256@.TK2MSFTNGP02.phx.gbl...
> Hi,
> using VStudio 2005/sql server 2005
> Have a simple web form that inserts the results in a table. It seems to
> write to the table, but not the values from the asp forms fields. It adds
> a new record and increments the id field by one -- but in all the other
> fields, it merely writes Null to the fields. He are some other points:
> I am not inserting data into every field -- for test purposes I am only
> using 4 fields.
> The id field is NOT one of the fields on the asp form -- although it is
> the only field that actually writes a correct value
> here's the code:
>
Hi there,
Use
ControlParameter instead of FormParameter. The difference is that
FormParameter takes its value directly from Request.Form collection using th
e
name given by FormField. The problem with your vode is that, textbox does no
t
post its value in Request.Form[textBox.ID] but in
Request.Form[textBox.UniqueID] which reflects IDs of the parent controls.
Change you insertparameters declaration to:
<InsertParameters>
<asp:ControlParameter Name="lastName" Type="String" ControlID="LastName"
PropertyName="Text"/>
<asp:ControlParameter Name="firstName" ControlID="FirstName" Type="String"
PropertyName="Text"/>
<asp:ControlParameter Name="address" Type="String" ControlID="Address"
PropertyName="Text"/>
<asp:ControlParameter Name="city" Type="String" ControlID="City"
PropertyName="Text"/>
</InsertParameters>
Milosz
"thersitz" wrote:

> using VStudio 2005/sql server 2005
> Have a simple web form that inserts the results in a table. It seems to
> write to the table, but not the values from the asp forms fields. It adds
a
> new record and increments the id field by one -- but in all the other
> fields, it merely writes Null to the fields. He are some other points:
> I am not inserting data into every field -- for test purposes I am only
> using 4 fields.
> The id field is NOT one of the fields on the asp form -- although it is th
e
> only field that actually writes a correct value
> here's the code:
> <asp:TextBox ID="FirstName" runat="server"></asp:TextBox> 
> Last name:
> <asp:TextBox ID="LastName" runat="server"></asp:TextBox> 
> Address:
> <asp:TextBox ID="Address" runat="server"></asp:TextBox> 
> City:
> <asp:TextBox ID="City" runat="server"></asp:TextBox> 
> Year created:
> <asp:DropDownList ID="YearCreated" runat="server">
> </asp:DropDownList> 
> <asp:Button ID="Save" runat="server" Text="Save" />
> </div>
> <asp:SqlDataSource ID="SqlDataSource1" runat="server"
> ConnectionString=
> "<%$ ConnectionStrings:WHCConnectionString %>"
> InsertCommand=
> "INSERT INTO
> [artsfestival] ([lastName], [firstName], [address], [city])
> VALUES
> (@.lastName, @.firstName, @.address, @.city)">
> <InsertParameters>
> <asp:FormParameter Name="lastName" Type="String"
> FormField="LastName" />
> <asp:FormParameter Name="firstName" Type="String"
> FormField="FirstName" />
> <asp:FormParameter Name="address" Type="String"
> FormField="Address" />
> <asp:FormParameter Name="city" Type="String"
> FormField="City" />
> </InsertParameters>
> </asp:SqlDataSource>
>
>
> "thersitz" <thersitz@.gmail.com> wrote in message
> news:OhRMtSXRHHA.2256@.TK2MSFTNGP02.phx.gbl...
>
>
Thanks Milosz, it worked.
I'm

why the book had me use the FormParameter and FormFieldID --
but thanks for getting me past this point.
Take care.
"Milosz Skalecki [MCAD]" <mily242@.REMOVEITwp.pl> wrote in message
news:1206D448-EDF2-419B-9A48-D05F7B51FAAF@.microsoft.com...
> Hi there,
> Use
> ControlParameter instead of FormParameter. The difference is that
> FormParameter takes its value directly from Request.Form collection using
> the
> name given by FormField. The problem with your vode is that, textbox does
> not
> post its value in Request.Form[textBox.ID] but in
> Request.Form[textBox.UniqueID] which reflects IDs of the parent controls.
> Change you insertparameters declaration to:
> <InsertParameters>
> <asp:ControlParameter Name="lastName" Type="String" ControlID="LastName"
> PropertyName="Text"/>
> <asp:ControlParameter Name="firstName" ControlID="FirstName" Type="String"
> PropertyName="Text"/>
> <asp:ControlParameter Name="address" Type="String" ControlID="Address"
> PropertyName="Text"/>
> <asp:ControlParameter Name="city" Type="String" ControlID="City"
> PropertyName="Text"/>
> </InsertParameters>
> --
> Milosz
>
> "thersitz" wrote:
>

Monday, March 26, 2012

newbie: my code crashe, NULL value

Hey
asp.net 2.0
My code below crashes at the "return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;"
line. In the debugging window I see that when this exception occur, the
"return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;" has a NULL value...
public override int SendMessage(MessageDetails message)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dotnet.itags.org.receiver", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.sender", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.title", SqlDbType.NVarChar).Value =
message.Title;
cmd.Parameters.Add("@dotnet.itags.org.body", SqlDbType.NVarChar).Value =
message.Body;
cmd.Parameters.Add("@dotnet.itags.org.return", SqlDbType.Int).Direction =
ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;
}
}
This is the stored procdure called in the method:
ALTER PROCEDURE dbo.AH_network_SendMessages
@dotnet.itags.org.sender nvarchar(256),
@dotnet.itags.org.receiver nvarchar(256),
@dotnet.itags.org.title nvarchar(100),
@dotnet.itags.org.body nvarchar(2000),
@dotnet.itags.org.return int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AH_Messages (sender, receiver, title, body)
VALUES (@dotnet.itags.org.sender, @dotnet.itags.org.receiver, @dotnet.itags.org.title, @dotnet.itags.org.body);
set @dotnet.itags.org.return = 1;
select 1;
END
Please, what am I doing wrong here?
JeffIf altering the signature of your SP, you may consider the following option
that should work.
SqlParameter returnValue = sqlCommand.Parameters.Add("@.YourSPReturnValue",
SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
return (Int32)returnValue.Value;
Declaring an explicit return value is in my opinion preferrable, as it is
more clear about your intent and somewhat cleans up the SP declaration.
Tor Bdshaug
tor.badshaug(AT)bekk.no
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
What is the ExecuteNonQuery method you try to call after all?
Normally, you will do a cmd.ExecuteNonQuery, rather than
ExecuteNonQuery(cmd).
Could it be that your ExecuteNonQuery(SqlCommand cmd) method in somewhat
fail to do a cmd.ExecuteNonQuery.
The fact that the output parameter does not have a value may suggest that
may be the case.
Tor Bdshaug
tor.badshaug(AT)bekk.no
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
I am not sure why you are selecting 1 after setting the @.return. There is no
need unless you are doing something with it. Since you are ExecuteNonQuery,
this is a wasted cycle.
Next, why are you not testing "ret" in your code. If it is -1, the insert
failed for some reason. By testing that you could determine what your issue
is.
Also, why have you not wrapped the open and ExecuteNonQuery() in a try. Here
is a good pattern:
try
{
cn.Open();
int ret = ExecuteNonQuery(cmd);
}
finally
{
cn.Dispose();
}
Not sure what you are returning.
Next suggestion. Do not return until you test the parameter. If it is is
null, the cast to int will blow up (nice technical term ;-> ).
Be careful with calling thisgs return in a stored proc, as SQL Server
already returns a value (even if you do not declare it) called
@.RETURN_VALUE.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com/
****************************************
*********
Think Outside the Box!
****************************************
*********
"Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
> Hey
> asp.net 2.0
> My code below crashes at the "return
> (int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
> that when this exception occur, the "return
> (int)cmd.Parameters["@.return"].Value;" has a NULL value...
> public override int SendMessage(MessageDetails message)
> {
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
> message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
> message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
> message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
> ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
> }
> This is the stored procdure called in the method:
> ALTER PROCEDURE dbo.AH_network_SendMessages
> @.sender nvarchar(256),
> @.receiver nvarchar(256),
> @.title nvarchar(100),
> @.body nvarchar(2000),
> @.return int OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
> set @.return = 1;
> select 1;
> END
> Please, what am I doing wrong here?
> Jeff
>
Thanks, the error was in the ExecuteNonQuery method, which is just a wrapper
around SqlCommand.ExecuteNonQuery... It's solved now, thanks to your tip
about checking ExecuteNonQuery
"Tor Bdshaug" <tor.badshaug@.nospam.com> wrote in message
news:%23TqCCAw6GHA.4996@.TK2MSFTNGP04.phx.gbl...
> What is the ExecuteNonQuery method you try to call after all?
> Normally, you will do a cmd.ExecuteNonQuery, rather than
> ExecuteNonQuery(cmd).
> Could it be that your ExecuteNonQuery(SqlCommand cmd) method in somewhat
> fail to do a cmd.ExecuteNonQuery.
> The fact that the output parameter does not have a value may suggest that
> may be the case.
> Tor Bdshaug
> tor.badshaug(AT)bekk.no
> "Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
> news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
>
Hey
Thanks, yes selecting 1 after setting the @.return to 1 is bad programming. I
know it, I will later rewrite this stored procedure and then I will fix
this.. the procedure is also missing a commit/rollback... I guess auto
commit is enabled but I prefer using explicit transaction...
"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@.comcast.netNoSpamM> wrote in
message news:ON3X9Jw6GHA.2288@.TK2MSFTNGP05.phx.gbl...
>I am not sure why you are selecting 1 after setting the @.return. There is
>no need unless you are doing something with it. Since you are
>ExecuteNonQuery, this is a wasted cycle.
> Next, why are you not testing "ret" in your code. If it is -1, the insert
> failed for some reason. By testing that you could determine what your
> issue is.
> Also, why have you not wrapped the open and ExecuteNonQuery() in a try.
> Here is a good pattern:
> try
> {
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> }
> finally
> {
> cn.Dispose();
> }
> Not sure what you are returning.
> Next suggestion. Do not return until you test the parameter. If it is is
> null, the cast to int will blow up (nice technical term ;-> ).
> Be careful with calling thisgs return in a stored proc, as SQL Server
> already returns a value (even if you do not declare it) called
> @.RETURN_VALUE.
> --
> Gregory A. Beamer
> MVP; MCP: +I, SE, SD, DBA
> http://gregorybeamer.spaces.live.com/
> ****************************************
*********
> Think Outside the Box!
> ****************************************
*********
> "Jeff" <it_consultant1@.hotmail.com.NOSPAM> wrote in message
> news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...
>

newbie: my code crashe, NULL value

Hey

asp.net 2.0

My code below crashes at the "return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;"
line. In the debugging window I see that when this exception occur, the
"return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;" has a NULL value...

public override int SendMessage(MessageDetails message)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@dotnet.itags.org.receiver", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.sender", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@dotnet.itags.org.title", SqlDbType.NVarChar).Value =
message.Title;
cmd.Parameters.Add("@dotnet.itags.org.body", SqlDbType.NVarChar).Value =
message.Body;
cmd.Parameters.Add("@dotnet.itags.org.return", SqlDbType.Int).Direction =
ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@dotnet.itags.org.return"].Value;
}
}

This is the stored procdure called in the method:
ALTER PROCEDURE dbo.AH_network_SendMessages
@dotnet.itags.org.sender nvarchar(256),
@dotnet.itags.org.receiver nvarchar(256),
@dotnet.itags.org.title nvarchar(100),
@dotnet.itags.org.body nvarchar(2000),
@dotnet.itags.org.return int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AH_Messages (sender, receiver, title, body)
VALUES (@dotnet.itags.org.sender, @dotnet.itags.org.receiver, @dotnet.itags.org.title, @dotnet.itags.org.body);

set @dotnet.itags.org.return = 1;
select 1;
END

Please, what am I doing wrong here?

JeffIf altering the signature of your SP, you may consider the following option
that should work.

SqlParameter returnValue = sqlCommand.Parameters.Add("@.YourSPReturnValue",
SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
return (Int32)returnValue.Value;

Declaring an explicit return value is in my opinion preferrable, as it is
more clear about your intent and somewhat cleans up the SP declaration.

Tor Bdshaug
tor.badshaug(AT)bekk.no

"Jeff" <it_consultant1@.hotmail.com.NOSPAMwrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

Hey
>
asp.net 2.0
>
My code below crashes at the "return
(int)cmd.Parameters["@.return"].Value;" line. In the debugging window I see
that when this exception occur, the "return
(int)cmd.Parameters["@.return"].Value;" has a NULL value...
>
public override int SendMessage(MessageDetails message)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
message.Sender;
cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
message.Title;
cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
message.Body;
cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@.return"].Value;
}
}
>
This is the stored procdure called in the method:
ALTER PROCEDURE dbo.AH_network_SendMessages
@.sender nvarchar(256),
@.receiver nvarchar(256),
@.title nvarchar(100),
@.body nvarchar(2000),
@.return int OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO AH_Messages (sender, receiver, title, body)
VALUES (@.sender, @.receiver, @.title, @.body);
>
set @.return = 1;
select 1;
END
>
Please, what am I doing wrong here?
>
Jeff
>

Tor Bdshaug
tor.badshaug(AT)bekk.no

"Jeff" <it_consultant1@.hotmail.com.NOSPAMwrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

Thanks, the error was in the ExecuteNonQuery method, which is just a wrapper
around SqlCommand.ExecuteNonQuery... It's solved now, thanks to your tip
about checking ExecuteNonQuery

"Tor Bdshaug" <tor.badshaug@.nospam.comwrote in message
news:%23TqCCAw6GHA.4996@.TK2MSFTNGP04.phx.gbl...

Quote:

Originally Posted by

What is the ExecuteNonQuery method you try to call after all?
Normally, you will do a cmd.ExecuteNonQuery, rather than
ExecuteNonQuery(cmd).
Could it be that your ExecuteNonQuery(SqlCommand cmd) method in somewhat
fail to do a cmd.ExecuteNonQuery.
The fact that the output parameter does not have a value may suggest that
may be the case.
>
Tor Bdshaug
tor.badshaug(AT)bekk.no
>
"Jeff" <it_consultant1@.hotmail.com.NOSPAMwrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

>Hey
>>
>asp.net 2.0
>>
>My code below crashes at the "return
>(int)cmd.Parameters["@.return"].Value;" line. In the debugging window I
>see that when this exception occur, the "return
>(int)cmd.Parameters["@.return"].Value;" has a NULL value...
>>
>public override int SendMessage(MessageDetails message)
>{
> using (SqlConnection cn = new SqlConnection(this.ConnectionString))
> {
> SqlCommand cmd = new SqlCommand("AH_network_SendMessages", cn);
> cmd.CommandType = CommandType.StoredProcedure;
> cmd.Parameters.Add("@.receiver", SqlDbType.NVarChar).Value =
>message.Sender;
> cmd.Parameters.Add("@.sender", SqlDbType.NVarChar).Value =
>message.Sender;
> cmd.Parameters.Add("@.title", SqlDbType.NVarChar).Value =
>message.Title;
> cmd.Parameters.Add("@.body", SqlDbType.NVarChar).Value =
>message.Body;
> cmd.Parameters.Add("@.return", SqlDbType.Int).Direction =
>ParameterDirection.Output;
> cn.Open();
> int ret = ExecuteNonQuery(cmd);
> return (int)cmd.Parameters["@.return"].Value;
> }
>}
>>
>This is the stored procdure called in the method:
>ALTER PROCEDURE dbo.AH_network_SendMessages
>@.sender nvarchar(256),
>@.receiver nvarchar(256),
>@.title nvarchar(100),
>@.body nvarchar(2000),
>@.return int OUTPUT
>AS
>BEGIN
>SET NOCOUNT ON;
>INSERT INTO AH_Messages (sender, receiver, title, body)
> VALUES (@.sender, @.receiver, @.title, @.body);
>>
>set @.return = 1;
>select 1;
>END
>>
>Please, what am I doing wrong here?
>>
>Jeff
>>

>
>

I am not sure why you are selecting 1 after setting the @.return. There is no
need unless you are doing something with it. Since you are ExecuteNonQuery,
this is a wasted cycle.

Next, why are you not testing "ret" in your code. If it is -1, the insert
failed for some reason. By testing that you could determine what your issue
is.

Also, why have you not wrapped the open and ExecuteNonQuery() in a try. Here
is a good pattern:

try
{
cn.Open();
int ret = ExecuteNonQuery(cmd);
}
finally
{
cn.Dispose();
}

Not sure what you are returning.

Next suggestion. Do not return until you test the parameter. If it is is
null, the cast to int will blow up (nice technical term ;->).

Be careful with calling thisgs return in a stored proc, as SQL Server
already returns a value (even if you do not declare it) called
@.RETURN_VALUE.

--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com/
*************************************************
Think Outside the Box!
*************************************************
"Jeff" <it_consultant1@.hotmail.com.NOSPAMwrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

Hey

Thanks, yes selecting 1 after setting the @.return to 1 is bad programming. I
know it, I will later rewrite this stored procedure and then I will fix
this.. the procedure is also missing a commit/rollback... I guess auto
commit is enabled but I prefer using explicit transaction...

"Cowboy (Gregory A. Beamer)" <NoSpamMgbworld@.comcast.netNoSpamMwrote in
message news:ON3X9Jw6GHA.2288@.TK2MSFTNGP05.phx.gbl...

Quote:

Originally Posted by

>I am not sure why you are selecting 1 after setting the @.return. There is
>no need unless you are doing something with it. Since you are
>ExecuteNonQuery, this is a wasted cycle.
>
Next, why are you not testing "ret" in your code. If it is -1, the insert
failed for some reason. By testing that you could determine what your
issue is.
>
Also, why have you not wrapped the open and ExecuteNonQuery() in a try.
Here is a good pattern:
>
try
{
cn.Open();
int ret = ExecuteNonQuery(cmd);
}
finally
{
cn.Dispose();
}
>
Not sure what you are returning.
>
Next suggestion. Do not return until you test the parameter. If it is is
null, the cast to int will blow up (nice technical term ;->).
>
Be careful with calling thisgs return in a stored proc, as SQL Server
already returns a value (even if you do not declare it) called
@.RETURN_VALUE.
>
--
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
http://gregorybeamer.spaces.live.com/
>
*************************************************
Think Outside the Box!
*************************************************
"Jeff" <it_consultant1@.hotmail.com.NOSPAMwrote in message
news:unl2osv6GHA.4580@.TK2MSFTNGP03.phx.gbl...

Quote:

Originally Posted by

>
>

Thursday, March 29, 2012

newbie: inserting data into sql form writes only null values

newbie: inserting data into sql form writes only null values

Monday, March 26, 2012

newbie: my code crashe, NULL value

newbie: my code crashe, NULL value

newbie learns ASP NET

Blog Archive

About Me