c#和aspx中的邮政编码为VarChar，而不是int

本文关键字：int VarChar aspx 邮政编码 | 更新日期: 2023-09-27 18:03:17

我试图使邮政编码在SQL数据库类型的VarChar，并有它在数据库中提交字母和数字。当邮政编码为int SQL Database类型时，表单将提交到数据库中的zip列中，但是当邮政编码的值为VarChar 50时，该值不会提交到数据库中名为zipPostal的新列中。

以下是aspx中的代码:

    <asp:Label runat="server" ID="zipLabel" AssociatedControlID="zip">Zip/Postal 
     Code</asp:Label>     
    <asp:TextBox ID="zip" name="zip" runat="server"></asp:TextBox>

下面是c#中的代码片段:

    /// Form post data from registration form
    string username = Request.Form["UserName"];
    string first = Request.Form["first"];
    string last = Request.Form["last"];
    string zip = Request.Form["zip"];
    sqlCommand.Parameters.Add(new SqlParameter("@email", SqlDbType.VarChar, 50,  
    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, emails));
    sqlCommand.Parameters.Add(new SqlParameter("@username", SqlDbType.VarChar, 15,   
    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, username));
    sqlCommand.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar, 20,    
    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, first));
    sqlCommand.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar, 20,      
    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, last));

sqlCommand.Parameters.Add(new SqlParameter("@zip", SqlDbType.VarChar, 50, 
    ParameterDirection.Input, false, 0, 0, "", DataRowVersion.Proposed, zip));
命令类型: 
     int chk = sqlCommand.ExecuteNonQuery();
     public string CheckEmail(string Email)
     {
     string returnValue = string.Empty;
     SqlConnection conn = new SqlConnection(strConnection);
     SqlCommand sqlCommand = new SqlCommand
     ("CheckEmailAvailability", conn);
     try
     {
        sqlCommand.CommandType = 
     CommandType.StoredProcedure;
     sqlCommand.Parameters.AddWithValue("@Email", 
     Email.Trim());
        conn.Open();
        returnValue = sqlCommand.ExecuteScalar().ToString();
        conn.Close();
     }
     catch
     {
     }
     finally
     {
        conn.Close();
        sqlCommand.Dispose();
     }
     return returnValue;
     }

////

    public string CheckUsername(string Username)
    {
    string returnValue = string.Empty;
    SqlConnection conn = new SqlConnection(strConnection);
    SqlCommand sqlCommand = new SqlCommand
    ("CheckUsernameAvailability", conn);
    try
    {
        sqlCommand.CommandType = 
    CommandType.StoredProcedure;
        sqlCommand.Parameters.AddWithValue("@UserName", 
    Username.Trim());
        conn.Open();
        returnValue = sqlCommand.ExecuteScalar().ToString();
        conn.Close();
    }
    catch
    {
    }
    finally
    {
        conn.Close();
        sqlCommand.Dispose();
    }
    return returnValue;
    }
    }

//存储过程
   if (username != "")
   {
        string checkUN = CheckUsername(username);
        if (checkUN == "true")
        {
            Label2.Text = "Username already exists.";
            gotoauth = 0;
            return;
        }
    }
    //[ckc] end check username 
    if (password != confirmpwd)
    {
        Label2.Text = "The Password and Confirmation 
   Password must match.";
       gotoauth = 0;
        return;
    }

   //connection with db and call procedure.
    SqlConnection conn = new SqlConnection(strConnection);
    SqlCommand sqlCommand = new SqlCommand
    ("customers_insert", conn);
    sqlCommand.CommandType = CommandType.StoredProcedure;
    conn.Open();

    try
    {

    if (gotoauth == 0) 
     {}

    else
        {

var customerGateway = new 
    CustomerGateway(_loginApikey, _TransactionKey, 
    ServiceMode.Live);
    var NewCustomer = customerGateway.CreateCustomer
    (emails, "Live User Account");
    var datetime = DateTime.Now.ToString(); 
string proid = NewCustomer.ProfileID;
    Session["Customerproid"] = proid;

//SQL Procedure

    SET QUOTED_IDENTIFIER ON
    GO
    ALTER PROCEDURE [dbo].[customers_insert]
    @email varchar(50) ,
    @username varchar(15) ,
    @firstName varchar(20) ,
    @lastName varchar(20) ,
    @country varchar(50) ,
    @address varchar(100) ,
    @city varchar(100) ,
    @state varchar(50) ,
    @account bigint = null ,
    @profileID bigint = null ,
    @password varchar(20) ,
    @customerNumber int = null ,
    @question varchar(50)  ,
    @answer varchar(50) ,
    @dob varchar(50) ,
    @AccountNumber varchar(50) = null ,
    @PinNumber varchar(50) = null ,
    @PhoneNumber varchar(50) = null, 
    @ApprovalID int,
    @DateTime varchar (50) = null,
    @thoughts varchar (100) = null,
    @zip varchar (50) = null

    AS
    INSERT [dbo].[customers]
    (
    [email],
    [username],
    [firstName],
    [lastName],
    [country],
    [address],
    [city],
    [state],
    [account],
    [profileID],
    [password],
    [customerNumber],
    [question],
    [answer],
    [dob],
    [AccountNumber],
    [PinNumber],
    [PhoneNumber],
    [ApprovalID],
    [DateTime],
    [thoughts],
    [zip]

    )
    VALUES
    (
    @email,
    @username,
    @firstName,
    @lastName,
    @country,
    @address,
    @city,
    @state,
    @account,
    @profileID,
    @password,
    @customerNumber,
    @question,
    @answer,
    @dob,
    @AccountNumber,
    @PinNumber,
    @PhoneNumber,
    @ApprovalID,
    @DateTime,
    @thoughts,
    @zip
    )

在SQL数据库中，有一个列用于zip (int，非null)和一个列用于zipPostal (varChar(50)， null)。我希望邮政编码数据进入zipPostal列，以便它接受字母和数字。

c#和aspx中的邮政编码为VarChar，而不是int

很可能有一个查询——在后端数据库中以存储过程的形式，或者在c#代码中以SqlCommand的形式——引用zip列。您需要更改该查询/过程以引用适当的字段。如果你的代码说值应该插入zip，它永远不会插入zipPostal。

编辑:现在我们可以看到您的存储过程，它确认这是问题。下面是修复方法:

ALTER PROCEDURE [dbo].[customers_insert]
@email varchar(50) ,
@username varchar(15) ,
@firstName varchar(20) ,
@lastName varchar(20) ,
@country varchar(50) ,
@address varchar(100) ,
@city varchar(100) ,
@state varchar(50) ,
@account bigint = null ,
@profileID bigint = null ,
@password varchar(20) ,
@customerNumber int = null ,
@question varchar(50)  ,
@answer varchar(50) ,
@dob varchar(50) ,
@AccountNumber varchar(50) = null ,
@PinNumber varchar(50) = null ,
@PhoneNumber varchar(50) = null, 
@ApprovalID int,
@DateTime varchar (50) = null,
@thoughts varchar (100) = null,
@zip varchar (50) = null

AS
INSERT [dbo].[customers]
(
[email],
[username],
[firstName],
[lastName],
[country],
[address],
[city],
[state],
[account],
[profileID],
[password],
[customerNumber],
[question],
[answer],
[dob],
[AccountNumber],
[PinNumber],
[PhoneNumber],
[ApprovalID],
[DateTime],
[thoughts],
[zipPostal] /*Here's what I changed*/

)
VALUES
(
@email,
@username,
@firstName,
@lastName,
@country,
@address,
@city,
@state,
@account,
@profileID,
@password,
@customerNumber,
@question,
@answer,
@dob,
@AccountNumber,
@PinNumber,
@PhoneNumber,
@ApprovalID,
@DateTime,
@thoughts,
@zip
)

您也可以以更复杂的方式编辑此内容，根据其值将邮政编码放入Zip或zipPostal，但我认为您应该将所有内容放入zipPostal。您应该将所有数据放在一个地方，并且邮政编码数据应该始终以字符串而不是数字的形式存储，正如Galactic Cowboy在下面的评论中所写的那样。

您没有发布相关SQL代码

您已经发布了调用"CheckUserNameAvailability"存储过程和另一个调用名为"CheckEmailAvailability"存储过程的代码。这两个存储过程似乎都与试图设置邮政编码的代码无关。

我怀疑您正在调用另一个以@Zip作为参数的存储过程。必须编辑该存储过程以更新正确的列。

更新:

改变
INSERT [dbo].[customers]
(
[email],
[username],
[firstName],
[lastName],
[country],
[address],
[city],
[state],
[account],
[profileID],
[password],
[customerNumber],
[question],
[answer],
[dob],
[AccountNumber],
[PinNumber],
[PhoneNumber],
[ApprovalID],
[DateTime],
[thoughts],
[zip]

)
VALUES
(
@email,
@username,
@firstName,
@lastName,
@country,
@address,
@city,
@state,
@account,
@profileID,
@password,
@customerNumber,
@question,
@answer,
@dob,
@AccountNumber,
@PinNumber,
@PhoneNumber,
@ApprovalID,
@DateTime,
@thoughts,
@zip
)


INSERT [dbo].[customers]
(
[email],
[username],
[firstName],
[lastName],
[country],
[address],
[city],
[state],
[account],
[profileID],
[password],
[customerNumber],
[question],
[answer],
[dob],
[AccountNumber],
[PinNumber],
[PhoneNumber],
[ApprovalID],
[DateTime],
[thoughts],
[zipPostal]

)
VALUES
(
@email,
@username,
@firstName,
@lastName,
@country,
@address,
@city,
@state,
@account,
@profileID,
@password,
@customerNumber,
@question,
@answer,
@dob,
@AccountNumber,
@PinNumber,
@PhoneNumber,
@ApprovalID,
@DateTime,
@thoughts,
@zip
)
相关文章：