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) OUTPUTAS
Declare
@SQL VarChar(100)SELECT
@SQL = 'SELECT ' + @Table + '_' + @Field + ' FROM ' + @TableSELECT
@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..
SamPosted Oct 29, 2008, 3:04 PM
Think I got...used CLR integration and built a stored procedure into a new class contained within my program. Probably not the best way to get fields, but I am a network guy...this is all greek to me.
using
System;using
System.Data;using
System.Data.SqlClient;using
System.Data.SqlTypes;using
Microsoft.SqlServer.Server;namespace
game{
public static class StoredProcedures{
[
SqlProcedure] public static string GetCreatureField(
string field, int id){
string connString = "Data Source=sd\\sqlexpress;Initial Catalog=GameDatabase;Integrated Security=SSPI;timeout=5"; SqlConnection cnn = new SqlConnection(connString);cnn.Open();
SqlCommand cmd = new SqlCommand();cmd.Connection = cnn;
cmd.CommandText =
"select Creature_" + field + " from Creature where Creature_ID = @p1"; SqlParameter p1 = new SqlParameter("@p1", id);cmd.Parameters.Add(p1);
object obj = cmd.ExecuteScalar();cnn.Close();
return obj.ToString();}}}