我正在根據正確的電話號碼格式和“請勿撥打”電話號碼的資料庫表檢查用戶上傳的電話號碼串列，并回傳格式不正確或表中不存在的串列條目。電話號碼都定義為 varchar，但 .Fill 會自動將其轉換為 int 并引發溢位錯誤。有誰知道如何獲取 .Fill 以將電話號碼保留為 varchar？我已經嘗試預先宣告已填充的表列并將電話號碼輸出定義為存盤程序中的 bigint，但這些都沒有奏效。應用程式中的所有其他代碼都可以正常作業。提前致謝！
錯誤訊息，其中“x”表示電話號碼： System.Data.SqlClient.SqlException (0x80131904)：varchar 值“xxxxxxxxxx”的轉換溢位了 int 列。該陳述句已終止。
C＃：
System.Data.DataTable cleanedList = new System.Data.DataTable();
using (SqlConnection conn = new SqlConnection(connectionString))
using (SqlCommand cmd = new SqlCommand("dbo.uspCleanScrubberLeads", conn))
using (var da = new SqlDataAdapter(cmd))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@ProcedureDate", DateTime.Now);
cmd.Parameters.AddWithValue("@LeadListTable", dt);
da.Fill(cleanedList);//Error occurs here!!!
}
查詢陳述句：
ALTER PROCEDURE [dbo].[uspCleanScrubberLeads]
@ProcedureDate datetime2(0),
@LeadListTable AS LeadListTable READONLY
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #CleanedLeads (PhoneNumber varchar(20), Reason varchar(200))
INSERT INTO #CleanedLeads (PhoneNumber, Reason)
SELECT PhoneNumber, '' FROM @LeadListTable AS t1
UPDATE #CleanedLeads
SET Reason = 'Non-numeric phone number' WHERE ISNUMERIC(PhoneNumber) <> 1
UPDATE #CleanedLeads
SET Reason = 'Phone is greater than 10 digits' WHERE Reason = '' AND LEN(PhoneNumber) > 10
UPDATE #CleanedLeads
SET Reason = 'Phone is fewer than 10 digits' WHERE Reason = '' AND LEN(PhoneNumber) < 10
--Remove numbers in the DoNotCall table
DELETE FROM #CleanedLeads WHERE EXISTS (SELECT * FROM DoNotCall WHERE ISNUMERIC(DNCTelephone) = PhoneNumber)
AND Reason = ''
SELECT * FROM #CleanedLeads
END
uj5u.com熱心網友回復：
在你的宣告中：
DELETE FROM
#CleanedLeads
WHERE
EXISTS (
SELECT
*
FROM
DoNotCall
WHERE
ISNUMERIC(DNCTelephone) = PhoneNumber
AND Reason = ''
)
就在這里，你要比較ISNUMERIC(DNCTelephone)的VARCHAR(20)領域PhoneNumber。
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/311569.html
標籤：C＃ asp.net 。网 asp.net-mvc sql数据适配器