SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 SP ID Request
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

dannyboy86
Starting Member

8 Posts

Posted - 09/26/2007 :  02:23:22  Show Profile  Reply with Quote
So i have the following stored procedure that inserts data into a table then returns the table ID...

ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic1]
@RegistrationDate DATETIME,
@MartimeRegNum INT,
@FishingVesselName VARCHAR(40),
@FishingVesselType INT,
@OperationalStatus VARCHAR(50),
@FishingVesselBasePort INT,
@FishingVesselRemarks VARCHAR(100),
@PreviousAuthorisation VARCHAR(50),
@FishingVesselLenght NUMERIC(18,2),
@FishingVesselWidth NUMERIC(18,2),
@FishingVesselHeight NUMERIC(18,2),
@ConstructionPlace VARCHAR(50),
@ConstructionCountry VARCHAR(25),
@ConstructionYear DATETIME,
@ConstructionShipyard VARCHAR(50),
@ConstructionHullMaterial VARCHAR(50),
@ConstructionRemarks VARCHAR(100)

AS
BEGIN
BEGIN TRY
--insert values into tb_vessellic_vsl_fsh
INSERT INTO tb_vessellic_vsl_fsh
(
vsl_RegistrationDate,
vsl_MartimeRegNumber,
vsl_FishingVesselName,
vsl_vst_VesselTypeID_fk,
vsl_OperationalStatus,
vsl_prt_BasePortID_fk,
vsl_VesselRemarks,
vsl_PreviousAuthorisation,
vsl_OverallLenght,
vsl_Width,
vsl_Height,
vsl_ConstructionPlace,
vsl_ConstructionCountry,
vsl_ConstructionYear,
vsl_ConstructionShipyard,
vsl_ConstructionHullMaterial,
vsl_ConstructionRemarks
)
VALUES
(
@RegistrationDate,
@MartimeRegNum,
@FishingVesselName,
@FishingVesselType,
@OperationalStatus,
@FishingVesselBasePort,
@FishingVesselRemarks,
@PreviousAuthorisation,
@FishingVesselLenght,
@FishingVesselWidth,
@FishingVesselHeight,
@ConstructionPlace,
@ConstructionCountry,
@ConstructionYear,
@ConstructionShipyard,
@ConstructionHullMaterial,
@ConstructionRemarks
)


DECLARE @ID AS INT
SELECT @ID = @@IDENTITY
PRINT @ID
RETURN @ID
END TRY
BEGIN CATCH
EXECUTE spA_GEN_LogError
END CATCH

END


now in the c# code i am calling the stored procedure through this:

public void InsertVslLic1(DateTime regDate, int martimeRegNo, string vesselName, int vesselCategory, string operativeStatus, int basePort, string vesselRemarks,
string previousAuthorisation, double lenght, double width, double height, string constructionPlace, string country, int constructionYear, string shipyard, string hullMaterial,
string structuralRemarks)
{
try
{
//Open Connection
DBConnection db = new DBConnection();
db.OpenConnection();

//Create SQL string
string _sqlString = ("EXECUTE spA_FSH_InsertVslLic1 @RegistrationDate = '" + regDate
+ "', @MartimeRegNum ='" + martimeRegNo
+ "', @FishingVesselName ='" + vesselName
+ "', @FishingVesselType ='" + vesselCategory
+ "', @OperationalStatus ='" + operativeStatus
+ "', @FishingVesselBasePort ='" + basePort
+ "', @FishingVesselRemarks ='" + vesselRemarks
+ "', @PreviousAuthorisation ='" + previousAuthorisation
+ "', @FishingVesselLenght ='" + lenght
+ "', @FishingVesselWidth ='" + width
+ "', @FishingVesselHeight ='" + height
+ "', @ConstructionPlace ='" + constructionPlace
+ "', @ConstructionCountry ='" + country
+ "', @ConstructionYear ='" + constructionYear
+ "', @ConstructionShipyard ='" + shipyard
+ "', @ConstructionHullMaterial ='" + hullMaterial
+ "', @ConstructionRemarks ='" + structuralRemarks + "'");
//Execute SQL String
db.RunSQLQuery(_sqlString);
}
catch (Exception ex)
{
throw ex;
}
}


RunSQLQuery being:

public int RunSQLQuery(string sqlStatement)
{
if (_sqlConnection.State != ConnectionState.Open)
_sqlConnection.Open();

_sqlCommand = new SqlCommand(sqlStatement,_sqlConnection);
return _sqlCommand.ExecuteNonQuery();
}


Now i am stuck on how i will get the ID from the sp (that is @ID) and return it to my C# code as i need it to update the same row further on :)all i am getting till now is the number of rows 'changed' i.e. 1! any thoughts? thanks

Edited by - dannyboy86 on 09/26/2007 02:58:33

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/26/2007 :  04:59:00  Show Profile  Reply with Quote
Couple of things:

You would be better off leaving the RETURN value of the Sproc as the Error state - 0=No error, anything else implies some sort of error.

You can return the IDENTITY instead using either a resultset:

SELECT @ID AS [MyID]

or as an OUTPUT Parameter to the SProc:

ALTER PROCEDURE [dbo].[spA_FSH_InsertVslLic1]
@RegistrationDate DATETIME,
...
@ConstructionRemarks VARCHAR(100),
@ID int = NULL OUTPUT
AS

and you don't then need the DECLARE @ID within your Sproc.

Also, change
SELECT @ID = @@IDENTITY
to
SELECT @ID = SCOPE_IDENTITY()

the use of @@IDENTITY was the "old way", and is subject to unwanted side effects in certain circumstances.

And whilst I'm being pedantic it would be good to get into the habit of naming the Table/View/Sproc/etc. "owner" as this offers a small performance improvement, and can also help some issues with deployment - well, either way, its a complete bugger if you have to change ALL your code later!

e.g.

INSERT INTO dbo.tb_vessellic_vsl_fsh

Kristen
Go to Top of Page

dannyboy86
Starting Member

8 Posts

Posted - 09/26/2007 :  05:06:47  Show Profile  Reply with Quote
thanks a bunch for your help, i'm on it right now :)thank you!
Go to Top of Page

dannyboy86
Starting Member

8 Posts

Posted - 09/26/2007 :  06:02:22  Show Profile  Reply with Quote
it is working perfectly fine thanks...sucks that now i am getting stuck in the c# coding and my RunSQLQuery is not returning the output correctly...its saying that the "_sqlCommand" does not have any parameters..wierd :(
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/26/2007 :  06:15:57  Show Profile  Reply with Quote
Some cache to flush & refresh maybe?
Go to Top of Page

dannyboy86
Starting Member

8 Posts

Posted - 09/26/2007 :  06:32:47  Show Profile  Reply with Quote
trying...still null :/
i guess the RunSQLQuery method is incorrect
Go to Top of Page

Kristen
Test

United Kingdom
22403 Posts

Posted - 09/26/2007 :  07:29:29  Show Profile  Reply with Quote
Sorry, can't help with the c# .NET stuff, not my field ...
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000