Click here to Skip to main content
12,755,775 members (36,658 online)
Rate this:
 
Please Sign up or sign in to vote.
See more: C# SQL stored-procedure
Hello
I searched through this site and found some topic about my problem but still cannot make this work....
I am simply trying to retrieve data from SQL Database using a stored procedure.
I am passing one parameter that is a SqlDbType.Varchar(50) type, but get back an SQL error:

Procedure or function 'WyszukajPrzesylki' expects parameter '@Nazwisko', which was not supplied.

What am I doing wrong? Store procedure works if I test it in SSMS ...
It even works when I type

SqlCommand com = new SqlCommand("WyszukajPrzesylki @Nazwisko = Example", con);


You are my last hope guys... I've already lost 2 days with this problem and cannot get any further... don't have any problems with inserting and updating records using c# ... just this....

So here is the code of SP

ALTER PROCEDURE [dbo].[WyszukajPrzesylki] 
	-- Add the parameters for the stored procedure here
	@Nazwisko varchar(50)
    AS
    BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	-- SELECT * FROM Przesylki WHERE (NazwiskoAdresata = @NazwiskoAdresata)
	SELECT * FROM Przesylki WHERE (Przesylki.NazwiskoAdresata = @Nazwisko)
    END



and action of the button:


SqlConnection con = new SqlConnection(Properties.Settings.Default.Monitoring_PrzesylekString1);
            SqlCommand com = new SqlCommand("WyszukajPrzesylki", con);
            com.CommandType = CommandType.StoredProcedure;
            SqlParameter parametrNazwisko = new SqlParameter("@Nazwisko", SqlDbType.VarChar, 50);
            parametrNazwisko.Value = "Dziubak";
            com.Parameters.Add(parametrNazwisko);

            SqlDataAdapter ad = new SqlDataAdapter(com.CommandText, con);
            DataSet ds = new DataSet();
 
            ad.Fill(ds, "id");
            con.Close();
            DataTable datatableA = ds.Tables[0];
            dataGridView1.DataSource = ds;
            dataGridView1.DataMember = "id";
Posted 14-Dec-12 9:54am
Aelion240
Updated 18-Jul-16 23:47pm
Comments
Aelion 14-Dec-12 14:58pm
   
Why I cannot pass parameter using

parametrNazwisko.Value = "Dziubak";

? Any ideas?
__TR__ 14-Dec-12 15:02pm
   
Try
com.Parameters.Add(new SqlParameter("@Nazwisko", "Dziubak"));
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 3

I found problem solution!

I should pass whole command parameter like this

SqlDataAdapter ad = new SqlDataAdapter(com);


not just com.CommandText ... it was stupid.... but I lost so much time...
  Permalink  
Comments
OriginalGriff 14-Dec-12 15:23pm
   
Not stupid, just annoying. We all make mistakes!

You get my five for posting the solution.
Aelion 14-Dec-12 15:55pm
   
Thank you... thats my first "five" on this website ;-)
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 2

From this sample C# SqlParameter[^] it looks like you should use
SqlParameter parametrNazwisko = new SqlParameter("Nazwisko", SqlDbType.VarChar, 50);
in stead off
SqlParameter parametrNazwisko = new SqlParameter("@Nazwisko", SqlDbType.VarChar, 50);

So remove the '@' from the parameter name.
  Permalink  
v2
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 4

since you are using the stored procedure as the SQL statement you dont need to pass the parameter name with @ symbol just pass the name of the parameter SQL will do the rest.

SqlParameter parametrNazwisko = new SqlParameter("Nazwisko", "Dziubak"); 
  Permalink  
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 5

Add your solution here
Quote:
Add your solution here
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Mobile
Web01 | 2.8.170217.1 | Last Updated 19 Jul 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100