stored procedure...help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

stored procedure...help

Author

Topic

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 12:01:20

Hello

Here is my stored procedure and i want this stored procedure to execute when @TOCHSPrefixText as varchar(50), is null ,means if user does not enter prefixtext in the textbox.

what shall I do in order to handle blank string.

ALTER PROCEDURE [dbo].[sp_InsertTOCElement]

(@TOCHSCounterStyleID as int,
@TOCHSLevel as int,

@TOCHSPrefixText as varchar(50),
@TOCHeaderSchemaID as INT

)

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
Insert into TOCHSElement(TOCHSLevel,TOCHSPrefixText,TOCHSCounterStyleID,TOCHeaderSchemaID)VALUES( @TOCHSLevel,@TOCHSPrefixText,@TOCHSCounterStyleID,@TOCHeaderSchemaID)
END

please help

Thank You.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 12:05:08

Better to check that at client side, yor front-end application so that no call to database is made at all.

ALTER PROCEDURE dbo.sp_InsertTOCElement
(
	@TOCHSCounterStyleID as int,
	@TOCHSLevel as int,
	@TOCHSPrefixText as varchar(50),
	@TOCHeaderSchemaID as INT
)
AS

SET NOCOUNT ON

if @TOCHSPrefixText is null
	Insert	TOCHSElement
		(
			TOCHSLevel,
			TOCHSPrefixText,
			TOCHSCounterStyleID,
			TOCHeaderSchemaID
		)
	VALUES	(
			@TOCHSLevel,
			@TOCHSPrefixText,
			@TOCHSCounterStyleID,
			@TOCHeaderSchemaID
		)

E 12°55'05.63"
N 56°04'39.26"

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 12:08:56

thanks for reply,but I didnt understand this, actually I have 6 textboxes as prefix and I am calling my stored procedure 6 times in my c# code.
If user enters data in one of the 6 textbox,will this SP still work.

please do reply

thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-17 : 12:13:02

Only call the database for textboxes having valid data?

E 12°55'05.63"
N 56°04'39.26"

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 12:34:54

but I want SP works even if @TOCHSPrefixText as varchar(50), is null. I want to allow null values to be inserted.how to do that.

please help

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 12:51:42

quote:
Originally posted by bluestar

but I want SP works even if @TOCHSPrefixText as varchar(50), is null. I want to allow null values to be inserted.how to do that.

please help

Thank You

do you mean this?

ALTER PROCEDURE dbo.sp_InsertTOCElement
(
	@TOCHSCounterStyleID as int,
	@TOCHSLevel as int,
	@TOCHSPrefixText as varchar(50)=NULL,
	@TOCHeaderSchemaID as INT
)
AS

SET NOCOUNT ON


	Insert	TOCHSElement
		(
			TOCHSLevel,
			TOCHSPrefixText,
			TOCHSCounterStyleID,
			TOCHeaderSchemaID
		)
	VALUES	(
			@TOCHSLevel,
			@TOCHSPrefixText,
			@TOCHSCounterStyleID,
			@TOCHeaderSchemaID
		)

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 12:56:50

no.okay let me explain,if I am passing null value i.e if I am keeping my textbox blank then I am getting this error..

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

so - I need to be able to handle blank header strings.

how to solve this problem....please help

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:01:22

quote:
Originally posted by bluestar

no.okay let me explain,if I am passing null value i.e if I am keeping my textbox blank then I am getting this error..

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

so - I need to be able to handle blank header strings.

how to solve this problem....please help

Thank You

is TOCHSPrefixText a NOT NULL field in your table?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 13:06:12

well I have not mention it not null,

this is what I have done in my SP during declaration

ALTER PROCEDURE dbo.sp_InsertTOCElement
(
@TOCHSCounterStyleID as int,
@TOCHSLevel as int,
@TOCHSPrefixText as varchar(50),
@TOCHeaderSchemaID as INT
)
AS

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-09-17 : 13:09:31

Is that error even a SQL error? It doesn't look like it to me. Perhaps the problem is not with SQL but your front-end or middle teir code?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 13:12:56

while executing the SP if I keep @TOCHSPrefixText as varchar(50), blank then it says ....Procedure or Function 'sp_InsertTOCElement' expects parameter '@TOCHSPrefixText', which was not supplied.

how to handle this.I want my SP to run even if '@TOCHSPrefixText', is not supplied.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:17:28

quote:
Originally posted by bluestar

well I have not mention it not null,

this is what I have done in my SP during declaration

ALTER PROCEDURE dbo.sp_InsertTOCElement
(
@TOCHSCounterStyleID as int,
@TOCHSLevel as int,
@TOCHSPrefixText as varchar(50),
@TOCHeaderSchemaID as INT
)
AS

i'm also about column definition in table. just run this query and see


SELECT IS_NULLABLE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ='TOCHSElement' AND COLUMN_NAME='TOCHSPrefixText'

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:20:26

quote:
Originally posted by bluestar

while executing the SP if I keep @TOCHSPrefixText as varchar(50), blank then it says ....Procedure or Function 'sp_InsertTOCElement' expects parameter '@TOCHSPrefixText', which was not supplied.

how to handle this.I want my SP to run even if '@TOCHSPrefixText', is not supplied.

the solution to this is what i posted earlier

ALTER PROCEDURE dbo.sp_InsertTOCElement
(
	@TOCHSCounterStyleID as int,
	@TOCHSLevel as int,
	@TOCHeaderSchemaID as INT,
        @TOCHSPrefixText as varchar(50)=NULL,
)
AS

SET NOCOUNT ON


	Insert	TOCHSElement
		(
			TOCHSLevel,
			TOCHSPrefixText,
			TOCHSCounterStyleID,
			TOCHeaderSchemaID
		)
	VALUES	(
			@TOCHSLevel,
			@TOCHSPrefixText,
			@TOCHSCounterStyleID,
			@TOCHeaderSchemaID
		)

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 13:26:48

yes thanks my Stored procedure is working by equating it to NULL.
But in my ASP.Net I am still getting the error....
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Any Idea,why is this happening.

Any help will be appreciated.

Thank You

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-09-17 : 13:39:14

quote:
Originally posted by bluestar

yes thanks my Stored procedure is working by equating it to NULL.
But in my ASP.Net I am still getting the error....
Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct format.

Any Idea,why is this happening.

Any help will be appreciated.

Thank You

have you declared prefixtext as an optional parameter in asp?

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 14:55:21

no ,how to declare a textbox as optional parameter in ASP??
Actually I am very new to .net.

thank You

bluestar
Posting Yak Master

133 Posts

Posted - 2008-09-17 : 15:28:36

sorry i miss spelled, how to declared prefixtext as an optional parameter in asp.

please help

thank you

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources