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)
	
	RETURN

The 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

Recommended Answers

All 36 Replies

You are still adding the companyid parameter.
Take that out and you should be good to go.

myCommand.Parameters.Add(new SqlParameter("@idcompany",i_companyID));//, SqlDbType.Int, 4, "ID_Company"));

The Green highlighted pieces show the code that I had that successfully added the data to the tables but I had to manually enter the ID number ... the code then selected the ID_Company based on that parameter and added to the table new table (all this worked with the Executing the stored procedure and viewing the output in the output window BUT would not recognise that the sproc existed in my code)

I hope this makes sense!

The red pieces of code show the changes that I made to the sproc. I declared the @idcompany inside the sproc with the hope that I could pass in an integer variable declared in my code which stores the ID for ID_Company after the user selects the Company Name from a drop down list.

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)
	
	RETURN

The C# syntax is as follows::

myCommand.Parameters.Add(new SqlParameter("@idcompany",i_companyID));

             // The above line SHOULD take in the unique ID value and pass into the sproc
            
        }

Elmo

What is the create vehicle stored proc?

I'm not understanding that if you already have the company_id, why you don't want to pass it in.

You are inserting the new vehicle, and have a company id already.

I would suggest creating two stored procedures. Maintain this one that inserts a vehicle only and another stored procedure that calls to insert the vehicle, gets the id, and makes the call to insert customer vehicle.

I have two different versions of the AddNewVehicle sproc (purely because the program threw a hissy fit by saying it couldnt see the CreateVehicle sproc)

the content of both are the same as defined above.

I have 3 tables (Customer, Vehicle and Customer_Vehicle)

The idea is that somebody could phone in and request information about some product a company sells...the company could record their details on the system...then if that customer decides they would like to buy a product, then the company would be able to search the Existing customers (dropDown list) and then proceed to type in relevant information ... then click on an "ADD" button

this button would then trigger a method that adds the text enetered into textboxex on the interface along with the value stored in the integer variable (referring to the name the user chose from the dropdown list)

So, the integer variable and all the textbox.text values are all passed into their respective parameters of the sproc .... then there should be a successful entry (but theres not)

I'm not understanding that if you already have the company_id, why you don't want to pass it in.

You are inserting the new vehicle, and have a company id already.

I would suggest creating two stored procedures. Maintain this one that inserts a vehicle only and another stored procedure that calls to insert the vehicle, gets the id, and makes the call to insert customer vehicle.

Thats a valid point :)

but either way I will still be faced with the problem of inserting the ID value for the Customer name selected from the DropDown list.

How can I write a stored procedure that accepts a variable from the code?

Im confusing myself now with this lol

lol what?

I thought you already have the stored procedure in the database, and you are accepting the values through the code.

myCommand = new SqlCommand("CreateVehicle", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

You mean the above code is not hitting the database?

lol what?

I thought you already have the stored procedure in the database, and you are accepting the values through the code.

myCommand = new SqlCommand("CreateVehicle", myConnection);
            myCommand.CommandType = CommandType.StoredProcedure;

You mean the above code is not hitting the database?

lol

yes - but ... :)

when I test the stored procedure itself...it DOES add to the database...

but when I run the code its throwing a fit!!

The code needs to call the stored procedure we have...I believe lol.

What happens when you do this?

myCommand = new SqlCommand("AddNewVehicle", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

I would suggest doing this, calling p_InsertVehicleWithCustomer from the code.

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
	@idvehicle	int OUTPUT
	)
	
AS
	
	-- 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
	
	
	RETURN



PROCEDURE dbo.p_InsertVehicleWithCustomer
	(
	@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),
	@idcompany int                  -- 12
	)

	DECLARE @idvehicle int
	EXEC AddNewVehicle(@reg, @manufacturer, @model, @genericName, @fleetNo, @serialNo, @engineNo, @chassisNo, @vinNo, @year, @colour, @idvehicle OUTPUT)

	INSERT INTO Customer_Vehicle(ID_Company, ID_Vehicle)
	VALUES(@idcompany, @idvehicle)

The code needs to call the stored procedure we have...I believe lol.

What happens when you do this?

myCommand = new SqlCommand("AddNewVehicle", myConnection);
myCommand.CommandType = CommandType.StoredProcedure;

stepping through the code in debug mode at that point, it picks up the CommandText as "AddNewVehicle" and stepping on through the rest of the code it see's the values for each of the parameters ...

..but when it reaches myCommand.ExecuteNonQuery(); it bugs out saying:

SqlException was unhandled
Could not find stored procedure 'AddNewVehicle'

NOTE :: I have managed to get the ID value to pass in as a parameter to the sproc ... I had to cast it to a string. (thats me over the ID hurdle) Just need to understand now why it cant FIND the stored procedure !!! :@

Ensure you have rights, and possible try prefixing with dbo.

And ensure you are specifying the correct database in the sqlconnection

I would suggest doing this, calling p_InsertVehicleWithCustomer from the code.

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
	@idvehicle	int OUTPUT
	)
	
AS
	
	-- 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
	
	
	RETURN



PROCEDURE dbo.p_InsertVehicleWithCustomer
	(
	@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),
	@idcompany int                  -- 12
	)

	DECLARE @idvehicle int
	EXEC AddNewVehicle(@reg, @manufacturer, @model, @genericName, @fleetNo, @serialNo, @engineNo, @chassisNo, @vinNo, @year, @colour, @idvehicle OUTPUT)

	INSERT INTO Customer_Vehicle(ID_Company, ID_Vehicle)
	VALUES(@idcompany, @idvehicle)

I think I see what you are trying to do .. but I have 3 tables

Customer (aka Company)
Vehicle
Customer_Vehicle

so the @idcompany is the primary key from the Customer table

Ensure you have rights, and possible try prefixing with dbo.

And ensure you are specifying the correct database in the sqlconnection

Ive already tried prefixingwith dbo with no joy :( lol

I have other sproc's that work ... like I have an AddCustomers sproc that just inserts the values into the table .... (Eazy peezy)

but ... ive hit a wall now lol....damn big one too

to me now .. my code should add the RIGHT values to the sproc ... except it cant FIND the sproc GRRRR

Did you add the user to the stored procedure?

No - its just meant to be a simple app, (an interface to a database) im making it for in-house purposes, so it just has the one user (hardcoded login for now anyway)

I mean, ensure the user you are connecting has permissions to execute this stored procedure.

Right click the stored procedure and check properties -> permissions, add the user if they are not in the list.

Can you post what you are using for your connection string and the scripted stored procedure that sql generated?

SqlConnection myConnection = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|DatabaseName.mdf;Integrated Security=True;User Instance=True");

public void setVehicleDetails2()
        {
            getCompanyID();
            //Holds the ID value for the Customer selected!
            str_CompanyID = (string)i_companyID.ToString();
            //Declare myCommand properties
            myCommand = new SqlCommand("AddNewVehicle", 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", 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 = str_CompanyID;

            //Close and dispose of open properties            
            myCommand.ExecuteNonQuery();

            //myCommand.Dispose();
            myConnection.Close();
        }

Looks good.

Possibly try removing User Instance=True.


Did you check the permissions on the stored procedure, and can you post the stored procedure sql code when you click on the stored procedure and click script to?


Right click the stored procedure and check properties -> permissions, add the user if they are not in the list.

Can you post what you are using for your connection string and the scripted stored procedure that sql generated?

My stored procedures dont have that option ! Im no longer at work, but I will post the sql code on monday :)

Thanks for your help in the mean time :)

Sure thing. Have a good weekend.

Hi

I have run my program again, and even created a new stored procedure containing the same code that "does what it says on the tin" while executed within the stored procedure itself. It adds to the database.

However, my code bugs out saying that it cant find the stored procedure!!

Why would it be doing that? Anyone have any ideas?

I have attached a wee screen shot of the line where it bugs out, the SqlException and proof that the stored procedure exists lol

please help :)

Case sensitive?

Case sensitive?

Ive double checked the case and the spelling...thats all correct!

Ive been reading about a tool called Sql Profiler, its not available on my machine, I have tried googling for it and came across Teratrax Performance Agent 7 day trial version...but Im not making much sense of it.

I seen on the MSDN forum somewhere that someone else was having the same problem as me, and they were told to run the Sql Profiler program to diagnose the problem by clicking Sql Server > Performance Tools > Profiler ... but I cant find that

I think it only came with MS-SQL 2000, I dont believe 2005 or 2008 have it as they dont have anything like the same front end.

OK thanks

Do you know of any tools available to VS2008 and Sql Server 2005 that may help me

Here's a link for the profiler
http://sqlprofiler.googlepages.com/

Are you sure the code is running as the same user as your login?

It seems like the user of the code, doesn't have permissions to the user permissions on that stored procedure still.


Are you sure the code is running as the same user as your login?

Actually no im not sure lol :) My login is simply textboxes and I then programatically check that what the user enters matches a set username and password.

I have it set up on the one machine ... and Im able to successful add a new customer record to the customer table. So I cant see why it should be any different to add a new vehicle record and then add the primary key from each table to a new (in-between) table.

Lol think there is login confusion.

By user, I mean the executing user of the windows process, as in administrator logon vs user logon that launches this process. There will also be a database windows user, since you are using integrated security. The launching user of this application, will need to have the priviledges inside of sql server.

The profiler should be able to show what user is calling the stored procedure.

Any information along that line?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.