Returning string value from a stored procedure in sql server 2005 to vc# ide code at runtime

5 pts.
Tags:
SQL Server 2005
SQL Server 2005 Express
SQL Server stored procedures
Hi,

This is Pushan Chakraborty from Kolkata. I have complied 2 stored procedures to retrieve the names of 2 user defined tables that I have created. The stored procedures are set:


ANSI_NULLS ON set



QUOTED_IDENTIFIER ON

go

  

-- =============================================

-- Author:

-- Create date: <23rd nov>

-- Description:

-- =============================================

ALTER

PROCEDURE [dbo].[select_table1] @tablename char(50) OUTPUT AS

BEGIN



-- SET NOCOUNT ON added to prevent extra result sets from

 

-- interfering with SELECT statements.

 

SET NOCOUNT ON;

 

SELECT @tablename=name FROM sys.tables where object_id ='1275151588'

 

return (@tablename)

 

END





and

set

ANSI_NULLS ON set



QUOTED_IDENTIFIER ON

go

 

 

-- =============================================

-- Author:

-- Create date: <23rd nov 2009>

-- Description:

-- =============================================

ALTER

PROCEDURE [dbo].[select_table2] @tablename char(50)OUTPUT AS

BEGIN



-- SET NOCOUNT ON added to prevent extra result sets from

 

-- interfering with SELECT statements.

 

SET NOCOUNT ON;

 

SELECT @tablename=name FROM sys.tables where object_id ='1259151531'

 

return(@tablename)

 

END

But the above procedures are not returning any string value but are returning int value by default. the calling code in vc#.net is:


conn.Open();

 

SqlCommand command = new SqlCommand("select_table1", conn);

command.CommandType = CommandType.StoredProcedure;  



string tab1 = command.ExecuteNonQuery(); comboBox3.Items.Add(tab1);

 



SqlCommand command1 = new SqlCommand("select_table2", conn); command1.CommandType =



CommandType.StoredProcedure;  



string tab2 = command1.ExecuteNonQuery(); comboBox3.Items.Add(tab2);

 



MessageBox.Show("comboBox populated"); conn.Close();

It is giving me an error message cannot implicitly convert type int to string:--Please suggest a solution I want to get the table names by calling the stored procedure which is currently not happening

Thanks

Pushan


Software/Hardware used:
visual studio 2005 , sql server 2005
ASKED: November 26, 2009  3:27 PM
UPDATED: September 17, 2013  3:42 PM

Answer Wiki

Thanks. We'll let you know when a new response is added.

By definition, procedures don’t return values. If you want to return the result of your query, you would need to use an output parameter.

On the other hand, ExecuteNonQuery returns the number of affected rows (for inserts, updates and deletes) or -1, and that’s why you are getting the casting error. In this case, it might be more appropriate to use ExecuteScalar instead.

———-

Stored procedure return values must be numeric. You’ll need to pass the object name back as an output parameter or return it as part of a recordset.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following