ASP.NET

Syntax error in store procedure

28-Dec-12 21:50

Hi,

there is an article here:
Detect Caps Lock with JavaScript[^]

Gook luck!

Comm100 - Leading Live Chat Software Provider

modified 28-May-14 1:45am.

Application Layering

Omersayeed26-Dec-12 2:31

Omersayeed

26-Dec-12 2:31

Hello Everybody, I want to develop an asp.net application.

I want to layer it in such a way that the data layer, data access layer and Business Logic Layer are designed in such a way that these layers could be used for any application UI such as silver light, windows apps, WPF ,android apps and ipad apps.

To achieve this I was planning to develop the BL layer using restful services and WCF.

BL layer will be the one interacting with the UI. I have seen lots of debates on this. I feel what I am planning is correct but the application will be huge around 3000 or more web pages and around 1000 table, I want to feel confident I am not going on a wrong track.

Any suggestion and advice will be appreciated.

Regards

anubhaw.gupta25-Dec-12 22:06

anubhaw.gupta

25-Dec-12 22:06

here is my piece of code

alter procedure mdi_anu
(
@entry_date varchar(20),
@updated_date varchar(20),
@status varchar(20),
@cno varchar(20),
@eno varchar(20))

as

declare @SQL varchar(max)
set @SQL='SELECT * FROM anu123 where 1=1 '

if (@status='ALL')
begin
set @status='ALL'
end

if (@status='A')
begin
set @status='A'
end

if (@status='R')
begin
set @status='R'
end

if (@status='D')
begin
set @status='D'
end

if (@status='P')
begin
set @status='P'
end

if (@entry_date<>'' and @updated_date<>'')
begin

set @entry_date =CONVERT (datetime, @entry_date, 103)
set @updated_date= CONVERT(datetime, @updated_date, 103)
set @SQL=@SQL+'and entry_date>=''' +@entry_date+'''and updated_date<='''+@updated_date+''''
end

if (@cno<>'')
begin
set @cno=@cno
set @SQL=@SQL+'and cno=''' +@cno
end

if (@eno<>'')
begin
set @eno=@eno
set @SQL=@SQL+'and eno=''' +@eno
end

exec (@SQL)
End

Can anyone tell me,where am wrong? stuck from 2 hr. Saying incorrect syntax near the last end Hmmm | :|

Sravanthid2825-Dec-12 22:21

Sravanthid28

25-Dec-12 22:21

U have to write BEGIN Keyword after As Statement

anubhaw.gupta25-Dec-12 22:40

anubhaw.gupta

25-Dec-12 22:40

Hey i got it. but that's not compulsory i guess. Hmmm | :|

anubhaw.gupta25-Dec-12 22:45

anubhaw.gupta

25-Dec-12 22:45

alter procedure mdi_anu  
(  
@entry_date varchar(20),  
@updated_date varchar(20),  
@status varchar(20),  
@cno varchar(20),  
@eno varchar(20)
)  
  
as 
begin
declare @SQL varchar(max)  
set @SQL='SELECT * FROM anu123 where 1=1 '  
  
	--if (@status='ALL')  
	--	begin  
	--	set @status='ALL'  
	--	set @SQL=@SQL + 'and status=''' +@status
	--end  
  
	if (@status='A')  
		begin  
		set @status='A'  
		--set @SQL=@SQL+' and status='''+@status
	end  
  
	if (@status='R')  
		begin  
		set @status='R'  
		set @SQL=@SQL+' and status=''' +@status
	end  
  
	if (@status='D')  
		begin  
		set @status='D'  
		set @SQL=@SQL+' and status=''' +@status
	end  
  
	if (@status='P')  
		begin  
		set @status='P'  
		set @SQL=@SQL+' and status=''' +@status
	end 
	
	
	 
	if (@entry_date<>'' and @updated_date<>'')  
	begin  
   
	set @entry_date =CONVERT (datetime, @entry_date, 103)        
	set @updated_date= CONVERT(datetime, @updated_date, 103)  
	set @SQL=@SQL+'and entry_date>=''' +@entry_date+'''and updated_date<='''+@updated_date+''''  
	end
  
	if (@cno<>'')  
	begin  
	set @cno=@cno  
	set @SQL=@SQL+'and cno=''' +@cno+
	end  

  
	if (@eno<>'')  
	begin  
	set @eno=@eno  
	set @SQL=@SQL+'and eno=''' +@eno+
	end 

exec (@SQL)

end

the error which m getting is that,suppose m choosing 'D' as my input in the status i get
"Unclosed quotation mark after the character string 'D'.
Incorrect syntax near 'D'."
Whats the problem?

sathish s26-Dec-12 1:39

sathish s

26-Dec-12 1:39

May be you should append a single quotes after +@status in all your if conditions, if it a varchar field?

Re: id or guid.. which one?

Zafar Sultan26-Dec-12 3:31

Zafar Sultan

26-Dec-12 3:31

Include a space before and after every string. For example instead of writing 'and eno=''' write ' and eno='' '

Hope it helps. If it does not instead of exec(@SQL) in your SP write print(@SQL) and you will have the complete SQL Query. That will clear the picture to a good deal where the error is.

id or guid.. which one?

Jassim Rahma25-Dec-12 7:22

Jassim Rahma

25-Dec-12 7:22

Hi,

I have a members website and every memeber will have his images saved ina folder on host.

I wanted to ask what's best way to do it:

/member_id/images

/member_guid/images

Technology News @ www.JassimRahma.com

Sandeep Mewara25-Dec-12 19:14

Sandeep Mewara

25-Dec-12 19:14

Jassim Rahma wrote:
/member_id/images

I would go with Member_Id as that sounds unique and will have a significant value.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

Re: id or guid.. which one?

CommDev25-Dec-12 19:25

why should I care about ExecuteScalar

25-Dec-12 19:25

Hi,

if the id will be changed, I suggest to choose the first one, or they are the same.

Comm100 - Leading Live Chat Software Provider

modified 28-May-14 1:46am.

Jassim Rahma25-Dec-12 5:57

Jassim Rahma

25-Dec-12 5:57

Hi,

I have been using ExecuteNonReader for a quite long time now and it's doing great for me. For example

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteNonReader();

then why should I use it like this:

SqlCommand cmd = new SqlCommand("select count(uname) from users_tbl where uname=@uname and password=@password", con);
cmd.Parameters.AddWithValue("uname", username);
cmd.Parameters.AddWithValue("password", password);
con.Open();
cmd.ExecuteScalar();

Technology News @ www.JassimRahma.com

Sandeep Mewara25-Dec-12 19:30

Sandeep Mewara

25-Dec-12 19:30

Jassim Rahma wrote:
ExecuteNonReader

I don't find a knowledge base article for the same in MSDN. Based on what I know and can infer, using it to get just a 'single' value will be an overhead. It should be used when database query is going to provide a set of records.

My guess, term in discussion is ExecuteNonQuery and should not be ExecuteNonReader.

Jassim Rahma wrote:
ExecuteScalar

Executes the query, and returns the first column of the first row in the result set returned by the query.
Refer: Refer: SqlCommand.ExecuteScalar Method [^]

Now, based on the details and documentation, for getting a count, ExecuteScalar fits in the best that will get me the result directly as needed.

Sandeep Mewara
Microsoft ASP.NET MVP 2012

[My Latest Article(s)]:
How to extend a WPF Textbox to Custom Picker
Server side Delimiters in ASP.NET

CommDev25-Dec-12 19:33

25-Dec-12 19:33

Hi,

ExecuteNonReader: used actions like insert, update, delete

ExecuteScalar: will return an object

for details you can learn from some books about these two.

Thanks,
CommDev

modified 6-Dec-16 1:27am.

CommDev25-Dec-12 19:40

25-Dec-12 19:40

Hi,

ExecuteReader

Do not use: when database query is going to provide for sure exactly 1 record. It may be getting record by its id (which is PK in the database) - GetOrderById and such. In this case use ExecuteNonQuery with output parameters.

Use: when database query is going to provide a set of records. It may be search or report.

ExecuteNonQuery

Use: when we are talking about a single database record - in Update, Insert, Delete and Get by Id. In all these cases we can use input/output/input-output parameters. Please note that from the application architecture point of view it is also good practices when your Insert and Update stored procedure returns changed record exactly like Get By Id method does.

ExecuteScalar

Do not use: when database query returns a single value and this value can be defined as parameter in T-SQL. ExecuteNonQuery with output parameter(s) is always preferred in this case since it is more flexible, tomorrow there will be 2 values therefore having ExecuteNonQuery we do not need to change method signatures.

Use: when database query returns a single value and this value cannot be defined as output parameter, because of T-SQL type limitation for variables. For example type image cannot be output parameter in MSSQL.

The most common example for ExecuteScalar is fetching a single image stored in the database and converting it to array of bytes. If you google it - most examples will demonstrate using of ExecuteReader to accomplish image handler, however ExecuteScalar will be more scalable and faster.

Conclusion

Always use ExecuteNonQuery except: when you have a set of records - use ExecuteReader and when you have a single output value that cannot be defined as a parameter - use ExecuteScalar. Hope this helped to clarify something. Enjoy Smile | :)

Comm100 - Leading Live Chat Software Provider

Jassim Rahma28-Dec-12 9:40

Jassim Rahma

28-Dec-12 9:40

Thanks CommDev.

But if I am going to return GetOrderById.. what's the difference if I am going to use ExecuteNonQuery with Output parameter or ExecuteReader with return SqldataReader?

Does it make a difference specially performance wise?

Technology News @ www.JassimRahma.com