Must declare the scalar variable error::PleaseHelp

rzvme

public DataSet Get_Friends_Birthday(DateTime d) { User=0; DataSet ds = new DataSet(); SqlConnection con = new SqlConnection(@"Data Source=.\SQLEXPRESS;AttachDbFilename=" + '"' + @"C:\Users\LadyLara\Documents\Visual Studio 2008\Projects\Private Notebook WebService\Private Notebook WebService\App_Data\Private Notebook Database.mdf" + '"' + ";Integrated Security=True;User Instance=True"); string scom = "SELECT FriendID, FFirstName, FLastName, FBirthday, FE_mail1, FE_mail2, FE_mail3, FE_mail4, FPhone_Number1, FPhone_Number2, FPhone_Number3, FPhone_Number4, UserID FROM Friends WHERE (UserID = "+"@IDUS)"+" AND (FBirthday = "+"@Date)"; SqlCommand sqlcom = new SqlCommand(scom,con); sqlcom.Parameters.Add("IDUS", SqlDbType.Int).Value=User.ToString(); sqlcom.Parameters.Add("DATE", SqlDbType.SmallDateTime).Value=d.ToString(); SqlDataAdapter adapt = new SqlDataAdapter(scom, con); con.Open(); adapt.Fill(ds); return ds; } when it reaches adapt.fill(ds) i get the folowing error. "System.Data.SqlClient.SqlException: Must declare the scalar variable "@IDUS"." Please Help!

rzvme

pmarfleet

rzvme wrote:

sqlcom.Parameters.Add("IDUS", SqlDbType.Int).Value=User.ToString(); sqlcom.Parameters.Add("DATE", SqlDbType.SmallDateTime).Value=d.ToString();

Prefix your parameter names with @ symbols. BTW, you should consider not using inline SQL statements. Use stored procedures instead.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

rzvme

i thought about using stored procedures only i don't know exactly how to do this.(oracle fan) and by the way....even with the @ symbol in front of the parametername i still get the exact same error.

rzvme

pmarfleet

string scom = "SELECT FriendID, FFirstName, FLastName, FBirthday, FE_mail1, FE_mail2, FE_mail3, FE_mail4, FPhone_Number1, FPhone_Number2, FPhone_Number3, FPhone_Number4, UserID FROM Friends WHERE (UserID = "+"@IDUS)"+" AND (FBirthday = "+"@Date)"; You don't need to do any string concaternation here. You should still really consider using stored procedures instead - you wouldn't have these problems if you did.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

rzvme

ALTER PROCEDURE [dbo].[uspGetBirthdayFriends] @CRK INT @BDATE SMALLDATETIME AS begin select * from Friends where UserID=@CRK and FBirthday =@BDATE RETURN end same error incorrect sintax near '@BDATE' must declare scalar variable "@crk"

rzvme

pmarfleet

You have made a syntax error. Each parameter declaration should be separated by a comma.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

rzvme

Thank you. That worked. But now i get another error and i can't seem to determine the cause after i invoke adapt.Fill(ds); /*This is the code of the procedure*/ ALTER PROCEDURE dbo.uspGetBirthdayFriends @CRK INT, @BDATE SMALLDATETIME AS begin declare @ID int declare @BDATE smalldatetime select * from Friends where UserID=@ID and FBirthday =@BDATE end RETURN i get the following error "Procedure uspGetBirthdayFriends has no parameters and arguments were supplied." Thank you

rzvme

pmarfleet

You've declared @BDATE twice in your SP - once as a parameter and secondly as a variable. This code won't complile - remove your variable declaration.

Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush

Pete OHanlon

You should really pass @ID in. Change it to:

dbo.uspGetBirthdayFriends


@ID INT, 
@BDATE SMALLDATETIME

AS
begin
select * from Friends where UserID=@ID and FBirthday =@BDATE
end
RETURN

From what I saw of your earlier post, you also seem to have a problem with the way you are handling your parameter. When you add in the SqlParameter, you need to put the parameter in with an @ in front of the name, e.g. command.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int));

Deja View - the feeling that you've seen this post before.

My blog | My articles

rzvme

Thank you very much. Both of you. I finaly solved the problem.

rzvme

Paul Conrad

pmarfleet wrote:

You don't need to do any string concaternation here.

Yes, that is dangerous.

"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon