Hey People :)
A little quesion for you all ...
I have a stored procedure which retrieves an ID from a Customer table and the ID from the newly Inserted row of the Vehicle table and adds them to a new table.
On my form I have a dropDown where the user can select a Name. I then force my code to get the ID for that name using a stored procedure and have the result stored in an integer variable. So what I was aiming for in the following code was to be able to pass in the integer value in the C# code to the procedure along with the rest of the data and have it save to both tables respectively.
Initially I had the Commented Out code as part of the procedure and on execution of the procedure (and knowing and manually typing the unique ID value for the Customer) it added the data to both tables. However when I ran it from my code it told me it could not find the Procedure or Function named "AddNewVehicle" when it was blatently staring it in the face!!!
As it stands now with the code belowDoes anyone know or hazard a guess as to why I am getting the error "Procedure or Function CreateVehicle has too many arguments specified"
ALTER PROCEDURE dbo.AddNewVehicle
-- Declare variables for inserts into 2 tables
(
@reg nvarchar(50), -- 1
@manufacturer nvarchar(50), -- 2
@model nvarchar (50), -- 3
@genericName nvarchar(50), -- 4
@fleetNo nvarchar(50), -- 5
@serialNo nvarchar(50), -- 6
@engineNo nvarchar(50), -- 7
@chassisNo nvarchar(50), -- 8
@vinNo nvarchar(50), -- 9
@year nvarchar(4), -- 10
@colour nvarchar(50) -- 11
--@idcompany int -- 12 (although declared again below)
)
AS
DECLARE @idvehicle int
DECLARE @idcompany int -- newly added
-- Insert all data into the Vehicle table
INSERT INTO Vehicle(Registration, Manufacturer, Model, GenericName, FleetNo, SerialNo, EngineNo, ChassisNo, VIN_No, YearOfManufacture, Colour)
VALUES (@reg, @manufacturer, @model, @genericName, @fleetNo, @serialNo, @engineNo, @chassisNo, @vinNo, @year, @colour)
-- Retrieve the automatically generated ID value from the Vehicle table
SET @idvehicle = @@IDENTITY
/*SELECT ID_Company
FROM Customer
WHERE ID_Company = @idcompany
*/
-- Insert new value into the Customer_Vehicle table
INSERT INTO Customer_Vehicle(ID_Company, ID_Vehicle)
VALUES(@idcompany, @idvehicle)
RETURNThe C# syntax is as follows::
public void setVehicleDetails()
{
getCompanyID();
//Declare myCommand properties
//myCommand = new SqlCommand("AddNewVehicle", myConnection);
myCommand = new SqlCommand("CreateVehicle", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;
//Open connection to the database
myConnection.Open();
//Initialise new instances of SqlParameter that declare the data type, size and column name
//for the data that is being passed in.
myCommand.Parameters.Add(new SqlParameter("@reg", SqlDbType.NVarChar, 50, "Registration"));
myCommand.Parameters.Add(new SqlParameter("@manufacturer", SqlDbType.NVarChar, 50, "Manufacturer"));
myCommand.Parameters.Add(new SqlParameter("@model", SqlDbType.NVarChar, 50, "Model"));
myCommand.Parameters.Add(new SqlParameter("@genericName", SqlDbType.NVarChar, 50, "GenericName"));
myCommand.Parameters.Add(new SqlParameter("@fleetNo", SqlDbType.NVarChar, 50, "FleetNo"));
myCommand.Parameters.Add(new SqlParameter("@serialNo", SqlDbType.NVarChar, 50, "SerialNo"));
myCommand.Parameters.Add(new SqlParameter("@engineNo", SqlDbType.NVarChar, 50, "EngineNo"));
myCommand.Parameters.Add(new SqlParameter("@chassisNo", SqlDbType.NVarChar, 50, "ChassisNo"));
myCommand.Parameters.Add(new SqlParameter("@vinNo", SqlDbType.NVarChar, 50, "VIN_No"));
myCommand.Parameters.Add(new SqlParameter("@year", SqlDbType.NVarChar, 4, "YearOfManufacture"));
myCommand.Parameters.Add(new SqlParameter("@colour", SqlDbType.NVarChar, 50, "Colour"));
myCommand.Parameters.Add(new SqlParameter("@idcompany",i_companyID));//, SqlDbType.Int, 4, "ID_Company"));
//Adds data to the Vehicle and Customer_Vehicle table based on the parameters passed in.
myCommand.Parameters["@reg"].Value = txt_Ex_Registration.Text;
myCommand.Parameters["@manufacturer"].Value = txt_Ex_Manufacturer.Text;
myCommand.Parameters["@model"].Value = txt_Ex_Model.Text;
myCommand.Parameters["@genericName"].Value = txt_Ex_GenericName.Text;
myCommand.Parameters["@fleetNo"].Value = txt_Ex_FleetNo.Text;
myCommand.Parameters["@serialNo"].Value = txt_Ex_SerialNo.Text;
myCommand.Parameters["@engineNo"].Value = txt_Ex_EngineNo.Text;
myCommand.Parameters["@chassisNo"].Value = txt_Ex_ChassisNo.Text;
myCommand.Parameters["@vinNo"].Value = txt_Ex_VinNo.Text;
myCommand.Parameters["@year"].Value = txt_Ex_Year.Text;
myCommand.Parameters["@colour"].Value = txt_Ex_Colour.Text;
//myCommand.Parameters["@idcompany"].Value = i_companyID;
//Close and dispose of open properties
myCommand.ExecuteNonQuery();
myCommand.Dispose();
myConnection.Close();
}I know this is the long winded version, and someone nicely showed me a neater way of doing this. But once I get the basics of my program working I will spend time playing about with more elegant coding.
On counting the parameters above, there are 12 (there are 11 and 1 declared in the sproc)
Can anyone throw some suggestions my way please :)
Elmo