I created a web service method that calls a stored procedure, which accepts an XML input parameter, in SQL Server 2005.
我在SQL Server 2005中创建了一个调用存储过程的Web服务方法,该存储过程接受XML输入参数。
I've created one in SQL Server 2008 and got it working but not in SQL Server 2005. The GridView shows the correct number of rows and columns but all are blank.
我在SQL Server 2008中创建了一个并且在SQL Server 2005中没有工作.GridView显示正确的行数和列数,但都是空白的。
EDIT: I removed the try-catch when filling the result set to the dataset ds_result and received this error instead:
编辑:我在将结果集填充到数据集ds_result时删除了try-catch,并收到此错误:
Server was unable to process request. ---> Conversion failed when converting datetime from character string.
C# for generating XML:
用于生成XML的C#:
DataSet CollectedReceivingData = new DataSet("CollectedReceivingData");
DataTable SR_Header = CollectedReceivingData.Tables.Add("SR_Header");
DataTable SR_Details = CollectedReceivingData.Tables.Add("SR_Details");
SR_Header.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("job_no", Type.GetType("System.Int32"));
SR_Header.Columns.Add("item_id", Type.GetType("System.Int32"));
SR_Header.Columns.Add("expiry_date", typeof(DateTime));
SR_Header.Columns.Add("remarks", typeof(string));
SR_Header.Columns.Add("rcv_qty", Type.GetType("System.Int32"));
SR_Header.Rows.Add("327051", "0381021", "21848", "02/03/2016", "This is a remarks test.", "50");
SR_Header.Rows.Add("327052", "0381021", "21849", "02/05/2016", "This is a remarks test for item 2", "25");
SR_Details.Columns.Add("receiving_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("pallet_id", typeof(string));
SR_Details.Columns.Add("qty_in_pallet", Type.GetType("System.Int32"));
SR_Details.Columns.Add("expiry_date", typeof(DateTime));
SR_Details.Columns.Add("remarks", typeof(string));
SR_Details.Columns.Add("warehouse_id", Type.GetType("System.Int32"));
SR_Details.Columns.Add("location_id", Type.GetType("System.Int32"));
SR_Details.Rows.Add("327051", "327051-001", "24", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-002", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-003", "5", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327051", "327051-004", "15", "02/03/2016", "This is a remarks test.", "2", "36");
SR_Details.Rows.Add("327052", "327052-001", "5", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-002", "15", "02/03/2016", "This is a remarks test.", "3", "2");
SR_Details.Rows.Add("327052", "327052-003", "5", "02/03/2016", "This is a remarks test.", "3", "2");
DataSet ds = new DataSet();
WebService1 wsobject = new WebService1();
ds = wsobject.UpdateCollectedReceivingData(CollectedReceivingData);
gvReceiving.DataSource = ds.Tables[0];
gvReceiving.DataBind();
gvReceiving2.DataSource = ds.Tables[1];
gvReceiving2.DataBind();
Web method:
[WebMethod]
public DataSet UpdateCollectedReceivingData(DataSet ds)
{
DataSet ds_result = new DataSet();
XmlDocument xmlDoc = new XmlDocument();
string connectionString = ConfigurationManager.ConnectionStrings["ConnStringWS"].ConnectionString;
SqlConnection objConn = new SqlConnection(connectionString);
DataSet dsResult = new DataSet();
objConn.Open();
using (SqlCommand cmd = new SqlCommand("[dbo].[SP_Receive]"))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = objConn;
cmd.Parameters.Add("@x", SqlDbType.Xml).Value = ds.GetXml();
SqlDataAdapter da = new SqlDataAdapter(cmd);
//try
//{
da.Fill(ds_result);
//}
//catch
//{
//}
}
objConn.Close();
return ds_result;
}
Stored procedure:
CREATE PROCEDURE [dbo].[SP_Receive]
(@x XML)
AS
BEGIN
DECLARE @t1 TABLE ([receiving_id] [int] NULL,
[job_no] [int] NULL,
[item_id] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[rcv_qty] [int] NULL
)
DECLARE @t2 TABLE ([receiving_id] [int] NULL,
[pallet_id] [nvarchar](20) NULL,
[qty_in_pallet] [int] NULL,
[expiry_date] [datetime] NULL,
[remarks] [nvarchar](255) NULL,
[warehouse_id] [int] NULL,
[location_id] [int] NULL
)
INSERT INTO @t1 ([receiving_id], [job_no], [item_id], [expiry_date],
[remarks], [rcv_qty])
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@job_no','int') as [job_no],
xmlVals.rowvals.value('@item_id','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@rcv_qty','int') as [rcv_qty]
FROM
@x.nodes('//CollectedReceivingData/SR_Header') as xmlVals(rowvals)
INSERT INTO @t2 ([receiving_id], [pallet_id], [qty_in_pallet],
[expiry_date], [remarks], [warehouse_id],
[location_id])
SELECT
xmlVals.rowvals.value('@receiving_id','int') as [receiving_id],
xmlVals.rowvals.value('@pallet_id','nvarchar(20)') as [job_no],
xmlVals.rowvals.value('@qty_in_pallet','int') as [item_id],
xmlVals.rowvals.value('@expiry_date','datetime') as [expiry_date],
xmlVals.rowvals.value('@remarks','nvarchar(255)') as [remarks],
xmlVals.rowvals.value('@warehouse_id','int') as [warehouse_id],
xmlVals.rowvals.value('@location_id','int') as [location_id]
FROM
@x.nodes('//CollectedReceivingData/SR_Details') as xmlVals(rowvals)
SELECT *
INTO #tmp1
FROM
(SELECT
t1.receiving_id as receiving_id,
error_message = CASE
WHEN t2.receiving_id IS NOT NULL THEN 'Item has already been received.'
WHEN total_rcv_qty > t1.rcv_qty THEN 'Total received quantity exceeded the quantity to be received.'
ELSE NULL
END
FROM
(SELECT
SD.receiving_id, SH.job_no, SH.item_id,
SH.rcv_qty, SUM(qty_in_pallet) as total_rcv_qty
FROM @t2 SD
INNER JOIN @t1 SH ON SH.receiving_id = SD.receiving_id
GROUP BY SD.receiving_id, SH.job_no, SH.item_id, SH.rcv_qty, SD.receiving_id) t1
LEFT JOIN
StockIN_Header t2 ON t1.receiving_id = t2.receiving_id
LEFT JOIN
StorBest.dbo.item_master IM ON t1.item_id = IM.itemid) AS tmp1
SELECT *
INTO #tmp2
FROM
(SELECT
SD.receiving_id, SD.pallet_id,
error_message = CASE
WHEN WLV.warehouseid IS NULL THEN 'Invalid location_id.'
WHEN W.warehouseid IS NULL THEN 'Invalid warehouse_id.'
WHEN SD2.pallet_id IS NOT NULL THEN 'Pallet has already been received'
ELSE NULL
END
FROM @t2 SD
LEFT JOIN StorBest.dbo.Warehouse_Loc_VIEW WLV ON SD.location_id = WLV.LocationID
LEFT JOIN StorBest.dbo.Warehouse W ON SD.warehouse_id = W.warehouseid
LEFT JOIN StockIN_Details SD2 ON SD.pallet_id = SD2.pallet_id
) AS tmp2
SELECT * FROM @t1
SELECT * FROM @t2
-- IF (SELECT COUNT(*) FROM (SELECT error_message FROM #tmp1 UNION ALL SELECT error_message FROM #tmp2) AS t WHERE error_message IS NOT NULL) = 0
-- BEGIN
-- INSERT INTO StockIN_Header SELECT * FROM @t1
-- INSERT INTO StockIN_Details SELECT * FROM @t2
-- END
END
1 个解决方案
#1
0
* wont let me use a comment because of rep, but what happens when you execute just the stored procedure? Do you get anything?
*不会因为rep而让我使用注释,但是当你只执行存储过程时会发生什么?你有什么收获吗?
#1
0
* wont let me use a comment because of rep, but what happens when you execute just the stored procedure? Do you get anything?
*不会因为rep而让我使用注释,但是当你只执行存储过程时会发生什么?你有什么收获吗?