Click here to Skip to main content
15,917,611 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have tried but it returns null.

What I have tried:

My stored Procedure is

USE [webservice]
GO
/****** Object:  StoredProcedure [dbo].[SP_VALIDATE_VIN]    Script Date: 6/30/2017 10:11:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER Proc [dbo].[SP_VALIDATE_VIN]
 @p_vin  varchar(1000),
 @Pmc int,
 @VIN                       varchar(1000) output,
@VEHICLE_MODEL             varchar(1000) output,
@MANUFACTURING_MONTH_YEAR  varchar(1000) output,
@INVOICE_NUMBER            varchar(1000) output,
@INVOICE_DATE              varchar(1000) output,
@INVOICE_TYPE              varchar(1000) output,
@INVOICE_DLR_FOR_CD        varchar(1000) output,
@CUST_NAME                 varchar(1000) output,
@CUST_ADDRESS1             varchar(1000) output,
@CUST_ADDRESS2             varchar(1000) output,
@CUST_ADDRESS3             varchar(1000) output,
@CUST_CITY                 varchar(1000) output,
@CUST_STATE                varchar(1000) output,
@CUST_PINCODE              varchar(1000) output,
@CUST_MOBILE               varchar(1000) output,
@CUST_EMAIL                varchar(1000) output,
@ERR_CD                    int output,
@ERR_MSG                   varchar(1000) output
 as begin
 select @VIN=VIN,@VEHICLE_MODEL=VEHICLE_MODEL,@MANUFACTURING_MONTH_YEAR=MANUFACTURING_MONTH_YEAR,@INVOICE_NUMBER=INVOICE_NUMBER,@INVOICE_DATE=INVOICE_DATE,@INVOICE_TYPE=INVOICE_TYPE,@INVOICE_DLR_FOR_CD=INVOICE_DLR_FOR_CD,@CUST_NAME=CUST_NAME,@CUST_ADDRESS1=CUST_ADDRESS1,@CUST_ADDRESS2=CUST_ADDRESS2,@CUST_ADDRESS3=CUST_ADDRESS3,
 @CUST_CITY=CUST_CITY,@CUST_STATE=@CUST_STATE,@CUST_PINCODE=@CUST_PINCODE,@CUST_MOBILE=@CUST_MOBILE,@CUST_EMAIL=CUST_EMAIL,@ERR_CD=ERR_CD,@ERR_MSG=ERR_MSG  from Validate_vin where P_VIN=@p_vin and PMC= @Pmc
 
 end


and my webservice code is

public string validate_vin(string _p_vin, int _PMC)
{
    List<Validate_vin> Vinvalidationlist = new List<Validate_vin>();
    try
    {

        // string sql = "select VIN,VEHICLE_MODEL,MANUFACTURING_MONTH_YEAR,INVOICE_NUMBER,INVOICE_DATE,INVOICE_TYPE,INVOICE_DLR_FOR_CD,CUST_NAME,CUST_ADDRESS1,CUST_ADDRESS2,CUST_ADDRESS3,CUST_CITY,CUST_STATE,CUST_PINCODE,CUST_MOBILE,CUST_EMAIL,ERR_CD,ERR_MSG from Validate_vin where P_VIN = '" + _p_vin + "' and PMC = '" + _PMC + "'";
        using (SqlConnection con = new SqlConnection(conn))
        {
            SqlCommand cmd = new SqlCommand();
            cmd.Connection = con;
            con.Open();
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "SP_VALIDATE_VIN";
            cmd.Parameters.AddWithValue("@p_vin", _p_vin);
            cmd.Parameters.AddWithValue("@Pmc", _PMC);
            cmd.Parameters.Add("@VIN", SqlDbType.VarChar,1000);
            cmd.Parameters["@VIN"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@VEHICLE_MODEL", SqlDbType.VarChar,1000);
            cmd.Parameters["@VEHICLE_MODEL"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@MANUFACTURING_MONTH_YEAR", SqlDbType.VarChar,1000);
            cmd.Parameters["@MANUFACTURING_MONTH_YEAR"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_NUMBER", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_NUMBER"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_DATE", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_DATE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_TYPE", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_TYPE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@INVOICE_DLR_FOR_CD", SqlDbType.VarChar,1000);
            cmd.Parameters["@INVOICE_DLR_FOR_CD"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_NAME", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_NAME"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS1", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS1"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS2", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS2"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_ADDRESS3", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_ADDRESS3"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_CITY", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_CITY"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_STATE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_STATE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_PINCODE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_PINCODE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_MOBILE", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_MOBILE"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@CUST_EMAIL", SqlDbType.VarChar,1000);
            cmd.Parameters["@CUST_EMAIL"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERR_CD", SqlDbType.Int);
            cmd.Parameters["@ERR_CD"].Direction = ParameterDirection.Output;
            cmd.Parameters.Add("@ERR_MSG", SqlDbType.VarChar,1000);
            cmd.Parameters["@ERR_MSG"].Direction = ParameterDirection.Output;
            SqlDataReader dr= cmd.ExecuteReader();
            //SqlDataAdapter da = new SqlDataAdapter("SP_VALIDATE_VIN", conn);
            //DataSet ds = new DataSet();
            //da.Fill(ds);
            while (dr.Read())
            {
                Validate_vin Vin_validation = new Validate_vin();
                Vin_validation.VIN = Convert.ToString(dr["VIN"].ToString());
                Vin_validation.VEHICLE_MODEL= Convert.ToString(dr["VEHICLE_MODEL"].ToString());
                Vin_validation.MANUFACTURING_MONTH_YEAR= Convert.ToString(dr["MANUFACTURING_MONTH_YEAR"].ToString());
                Vin_validation.INVOICE_NUMBER= Convert.ToString(dr["INVOICE_NUMBER"].ToString());
                Vin_validation.INVOICE_DATE= Convert.ToString(dr["INVOICE_DATE"].ToString());
                Vin_validation.INVOICE_TYPE= Convert.ToString(dr["INVOICE_TYPE"].ToString());
                Vin_validation.INVOICE_DLR_FOR_CD= Convert.ToString(dr["INVOICE_DLR_FOR_CD"].ToString());
                Vin_validation.CUST_NAME= Convert.ToString(dr["CUST_NAME"].ToString());
                Vin_validation.CUST_ADDRESS1= Convert.ToString(dr["CUST_ADDRESS1"].ToString());
                Vin_validation.CUST_ADDRESS2= Convert.ToString(dr["CUST_ADDRESS2"].ToString());
                Vin_validation.CUST_ADDRESS3= Convert.ToString(dr["CUST_ADDRESS3"].ToString());
                Vin_validation.CUST_CITY= Convert.ToString(dr["CUST_CITY"].ToString());
                Vin_validation.CUST_STATE= Convert.ToString(dr["CUST_STATE"].ToString());
                Vin_validation.CUST_PINCODE= Convert.ToString(dr["CUST_PINCODE"].ToString());
                Vin_validation.CUST_MOBILE= Convert.ToString(dr["CUST_MOBILE"].ToString());
                Vin_validation.CUST_EMAIL= Convert.ToString(dr["CUST_EMAIL"].ToString());
                Vin_validation.ERR_CD= Convert.ToString(dr["ERR_CD"].ToString());
                Vin_validation.ERR_MSG= Convert.ToString(dr["ERR_MSG"].ToString());
                Vinvalidationlist.Add(Vin_validation);
            }
            con.Close();
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
    finally
    {

    }
    return JsonConvert.SerializeObject(Vinvalidationlist, Newtonsoft.Json.Formatting.Indented);
}
Posted
Updated 30-Jun-17 10:47am
Comments
F-ES Sitecore 30-Jun-17 5:44am    
What's wrong with the code you posted? (Note - "It doesn't work" is not an answer that contains enough information for people to help you)
Member 12962919 30-Jun-17 5:50am    
after executing its return 0 count in dr.
F-ES Sitecore 30-Jun-17 6:09am    
Then SP_VALIDATE_VIN isn't returning data given the inputs you're supplying. We can't access your data and we don't know what your inputs are so we can't tell you why that is, you'll need to look at the database and the values you're giving for your parameters to try and work it out. Maybe use something like SQL Profiler as that will show you exactly what is being executed against the database.
Member 12962919 30-Jun-17 6:11am    
can you check the Procedure or code is completely true or anything wrong in it
F-ES Sitecore 30-Jun-17 6:19am    
Looking at it further it looks like you are selecting all data into your output parameters, but your code is accessing the data as if it was a result set. Your SP doesn't generate any results, it simply sets the value of output params, so with no results ExecuteReader returns nothing. You might need to use ExecuteNonQuery rather than ExecuteReader, then examine the output params (google "ado.net output parameters" to see how to properly use output params).


Alternatively ditch all of the output params and just do a normal "SELECT VIN, Vehicle_Model...." and that will generate a result set that you can use your existing code to process.

1 solution

you should return the data from SP instead of using out put paramters.
like below

-- =============================================
SQL
ALTER PROCEDURE [dbo].[my_test]	
	(
	@template_id varchar(50)
	
	
	)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT 
      [app_id]
    , [business_domain_id]
     , [business_line]
    ,  [language_cd]
    , [email_type]
    , [email_from]
     , [email_subject]
     [email_body]
  FROM [dbo].[EmailTemplate]
  where @template_id=template_id
END


in .NET

C#
public static string getData(string tempid)
        {
            List<MyTemplate> mylist = new List<MyTemplate>();
            SqlDataAdapter da = new SqlDataAdapter();
            System.Data.DataSet ds = new DataSet();
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnectionStr"].ToString()))
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = con;
                con.Open();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "dbo.[my_test]";
                cmd.Parameters.AddWithValue("@template_id", tempid);
                da.SelectCommand = cmd;
                da.Fill(ds);

            }
            if (ds != null && ds.Tables.Count > 0 && ds.Tables[0] != null)
            {
                foreach (DataRow Dr in ds.Tables[0].Rows)
                {
                    MyTemplate mTemp = new MyTemplate();
                    mTemp.appId = Dr["app_id"].ToString();
                    mTemp.bDomainId = Dr["business_domain_id"].ToString();
                    mTemp.Bline = Dr["business_line"].ToString();
                    mTemp.emlBody = Dr["email_body"].ToString();
                    mTemp.emlFrom = Dr["email_from"].ToString();
                  //  mTemp.emlSubj = Dr["email_subject"].ToString();
                    mTemp.emlTyp = Dr["email_type"].ToString();
                    mylist.Add(mTemp);

                }

            }


            return JsonConvert.SerializeObject(mylist, Newtonsoft.Json.Formatting.Indented);
        }
}









Response in JSON:

<pre lang="objc">
[
{
"appId": "0ba9a0e7-25d4-4174-967a-956b47d04d4c",
"bDomainId": "9dd3f102-273c-40a2-a122-69628712680a",
"Bline": "EUR",
"lnG": null,
"emlTyp": "userName",
"emlFrom": "support@xxx.com",
"emlSubj": null,
"emlBody": "[DEV] www.accessxxx.com"
}
]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900