I have a procedure where I am using OPENJSON to display the Json string as key value pairs. When I execute the procedure, it is returning the expected value bu in controller, the dataset is empty. It only shows the table headers - key, value, type. No data at all. When I use a different very similar procedure, it it seems to work fine. Not sure what is happening. Please help! Thanks in advance!
This is my procedure:
CREATE PROCEDURE [dbo].[PROC_NH_Journal_Tab1Data]
@EnterpriseId nvarchar,
@exactgroup nvarchar
AS
declare @json nvarchar(max)
set @json=(select Tab2Json from tbl_NH_T_JournalDataPost where
EnterpriseId=@EnterpriseId and GroupName = @exactgroup)
SELECT * FROM
OPENJSON ( @json )
Return 0
And here is my action:
public JsonResult GetAnswersFortab1(string exact)
{
string JSONresult = string.Empty;
string EnterpriseId = "User";
try
{
SqlConnection sqlcon = new SqlConnection(con);
SqlCommand cmd = new SqlCommand("[PROC_NH_Journal_Tab1Data]", sqlcon);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@EnterpriseId", EnterpriseId);
cmd.Parameters.AddWithValue("@exactgroup", exact);
sqlcon.Open();
SqlDataAdapter adapter = new SqlDataAdapter();
adapter.SelectCommand = cmd;
DataSet data;
data = new DataSet();
adapter.Fill(data, "Table2");
DataTable dt1 = data.Tables[0];
ViewBag.getJson = dt1;
}
The procedure when executed, gives a table with key, value pairs. But when I debug,in action, the 'data' is an empty table in 'DataSetVisualizer'. Empty table only with headers(key,value,type).No data is present. Here is my Print @json from the procedure
key value type
emoji2 2 1
check2 on 1
check5 on 1
check7 on 1
hiddentext weds 1