stored procedure performing insert returns object

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure performing insert returns object

Author

Topic

stevenandler
Starting Member

42 Posts

Posted - 2012-08-21 : 14:04:40

The following is my code which performs an insert in an SQL table. when I use the code with the T-SQL statement, the insert occurs and I get back the scope identity. If I comment out the T-SQL statement and call a stored procedure which performs the exact same task as the T-SQL statement, the ExcuteScalar failes with the infamous
object reference not set to an instance of an object
Here is my C# code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace insert
{
class Program
{
public static string company;
public static string phone;
public static Int32 newProdID;

static void Main(string[] args)
{

Console.WriteLine(" Enter the Company Name ");
company = Console.ReadLine();

Console.WriteLine(" Enter the phone number ");
phone = Console.ReadLine();

string connString = "Data Source=RAY-PC;User ID=sa;Password=*****";

string sql =
"INSERT INTO dbo.Shippers (CompanyName,Phone) VALUES (@CompanyName,@Phone);"
+ "SELECT CAST(scope_identity() AS int)";

// string sql = "execute dbo.InsertShipper";

using (SqlConnection conn = new SqlConnection(connString))
{
SqlCommand cmd = new SqlCommand(sql, conn);
SqlParameter myParm1 = cmd.Parameters.Add("@CompanyName", SqlDbType.NVarChar);
myParm1.Value = company;

SqlParameter myParm2 = cmd.Parameters.Add("@Phone", SqlDbType.NVarChar);
myParm2.Value = phone;

try
{
conn.Open();
newProdID = (Int32)cmd.ExecuteScalar();
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
Console.WriteLine(" The newProdID is " + newProdID);
string HOLD;
HOLD = Console.ReadLine();
}
}

}

Here is my Stored Procedure

USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[InsertShipper] Script Date: 08/21/2012 13:29:01 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[InsertShipper]
@CompanyName nvarchar(40) = NULL,
@Phone nvarchar(24) = NULL,
@ShipperID int = NULL OUTPUT
AS
SET NOCOUNT ON;
IF @CompanyName IS NULL
SET @ShipperID = 0;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY();
Select @ShipperID as ShipperID;
END

I have been struggling with this issue for over a week so if someone could recomend a good book which explains in detail how to program SQL with in C#, I would appreciate it.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 14:15:25

for getting output parameter value from procedure you need to declare the output parameter in c# code just like you did for other parameters and also set direction as output. then add a variable in c# to get receiving value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stevenandler
Starting Member

42 Posts

Posted - 2012-08-21 : 14:38:15

Ok I add the following to my code and I still get same exception.

SqlParameter myParm3 = cmd.Parameters.Add("@ShipperID", SqlDbType.Int,10);
myParm3.Direction = ParameterDirection.Output;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 15:01:41

how are you calling sp?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stevenandler
Starting Member

42 Posts

Posted - 2012-08-21 : 15:10:30

string sql = "execute dbo.InsertShipper";

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-21 : 15:13:05

see this

http://www.c-sharpcorner.com/UploadFile/rohatash/get-out-parameter-from-a-stored-procedure-in-Asp-Net/

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

stevenandler
Starting Member

42 Posts

Posted - 2012-08-21 : 16:44:15

Great news it works! The sample code was very helpful. I still need to learn a lot more about this stuff because everything I read so far indicates that using ExecuteNonQuery()prevents anything from being returned from a call to a stored procedure yet it works here.

For anyone interested, here is my new C# code and stored procedure that returnes the SCOPE_IDENTITY of the new record added.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;

namespace InsertConsoel
{
class Program
{

public static string company;
public static string phone;

static void Main(string[] args)
{

Console.WriteLine(" Enter the company name ");
company = Console.ReadLine();

Console.WriteLine(" Enter the phone number ");
phone = Console.ReadLine();

SqlConnection con = new SqlConnection("Data Source=RAY-PC; uid=sa; pwd=stevena!;");

con.Open();

SqlCommand cmd = new SqlCommand("dbo.InsertShipper", con);

cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("@CompanyName", company);

cmd.Parameters.AddWithValue("@Phone", phone);

cmd.Parameters.Add("@ShipperID", SqlDbType.Char, 500);

cmd.Parameters["@ShipperID"].Direction = ParameterDirection.Output;

cmd.ExecuteNonQuery();

Console.WriteLine(" The response is " + (string)cmd.Parameters["@ShipperID"].Value);
string hold;
hold = Console.ReadLine();

con.Close();
}

}
}

Stored Procedure
USE [Northwind]
GO
/****** Object: StoredProcedure [dbo].[InsertShipper] Script Date: 08/21/2012 13:05:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[InsertShipper]
@CompanyName nvarchar(40) = NULL,
@Phone nvarchar(24) = NULL,
@ShipperID int = NULL OUTPUT
AS
SET NOCOUNT ON;
IF @CompanyName IS NULL
SET @ShipperID = 0;
ELSE
BEGIN
INSERT INTO dbo.Shippers(CompanyName, Phone)
VALUES (@CompanyName, @Phone);
SET @ShipperID = SCOPE_IDENTITY();
END

Thank you very much for your assistance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources