Sam

Sam

  • NA
  • 6
  • 0

stored procedure not returning a value

Oct 28 2008 3:22 PM

I made a stored procedure but it will not return a value to my method. I have spent way too long looking at this code so any help would be appreciated:

ALTER PROCEDURE dbo.GetRecord

@Table VarChar(50),

@Field VarChar(50),

@Record int,

@Returned VarChar(200) OUTPUT

AS

Declare @SQL VarChar(100)

SELECT @SQL = 'SELECT ' + @Table + '_' + @Field + ' FROM ' + @Table

SELECT @SQL = @SQL + ' WHERE  ' + @Table + '_ID = '

Exec (@SQL + @record)

 

The procedure does exactly what I want and it runs fine when I use the SQL Executor in VS08, but it doesn't seem to be returning a value. Here is my method.

 

public static string GetInfo(string sel_table, string sel_field, int sel_id)

{

string connectionstring = "Data Source=sd\\sqlexpress;Initial Catalog=GameDatabase;Integrated Security=SSPI;timeout=5";

SqlConnection sqlConnection1 = new SqlConnection(connectionstring);

SqlCommand cmd = new SqlCommand("GetRecord", sqlConnection1);

object returnValue;

cmd.CommandType = System.Data.CommandType.StoredProcedure;

cmd.CommandText = "GetRecord";

cmd.Connection = sqlConnection1;

sqlConnection1.Open();

cmd.Parameters.Add("@Table", System.Data.SqlDbType.VarChar, 50);

cmd.Parameters["@Table"].Value = sel_table;

cmd.Parameters.Add("@Field", System.Data.SqlDbType.VarChar, 50);

cmd.Parameters["@Field"].Value = sel_field;

cmd.Parameters.Add("@Record", System.Data.SqlDbType.Int);

cmd.Parameters["@Record"].Value = sel_id;

cmd.Parameters.Add("@Returned", System.Data.SqlDbType.VarChar,-1);

cmd.Parameters["@Returned"].Direction = System.Data.ParameterDirection.Output;

 

cmd.ExecuteReader();

returnValue = cmd.Parameters["@Returned"].Value;

return Convert.ToString(returnValue);

sqlConnection1.Close();

}

The output is being sent to a Form in my application. Just getting it to run took me a long time, but now no values return. I am also not clear on when to Open and Close the connection, maybe that's the problem? I tried moving that code around to no avail. Also, when I take my method arguments do this need to be explicitly converted to varchars? I am just lost..


Answers (1)