ALTER PROCEDURE dbo.Adduser
(
@UserID int,
@UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
RETURN
这个是AdduserALTER PROCEDURE dbo.Adduser
(
@UserID int,
@UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
RETURN
这个是Adduser 的存储过程 userID为自动增长的
//创建数据库连接和命令的对象
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); //打开数据库连接
myConnection.Open();
SqlCommand myCommand = new SqlCommand("Adduser", myConnection); //指明Sql命令的操作类型是使用存储过程
myCommand.CommandType = CommandType.StoredProcedure;
//给存储过程添加参数
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);
SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 16);
parameterUserName.Value = TextBox1.Text;
myCommand.Parameters.Add(parameterUserName); SqlParameter parameterPassword = new SqlParameter("@Password", SqlDbType.NVarChar, 12);
parameterPassword.Value = TextBox2.Text;
myCommand.Parameters.Add(parameterPassword); SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 10);
parameterName.Value = TextBox3.Text;
myCommand.Parameters.Add(parameterName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
parameterEmail.Value = TextBox4.Text;
myCommand.Parameters.Add(parameterEmail); SqlParameter parameterIDCardNumber = new SqlParameter("@IDCardNumber", SqlDbType.NVarChar, 18);
parameterIDCardNumber.Value = TextBox5.Text;
myCommand.Parameters.Add(parameterIDCardNumber); SqlParameter parameterTelephoneNumber = new SqlParameter("@TelephoneNumber", SqlDbType.NVarChar, 12);
parameterTelephoneNumber.Value = TextBox6.Text;
myCommand.Parameters.Add(parameterTelephoneNumber); SqlParameter parameterAddress = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
parameterAddress.Value = TextBox7.Text;
myCommand.Parameters.Add(parameterAddress); SqlParameter parameterZoneCode = new SqlParameter("@ZoneCode", SqlDbType.NVarChar, 10);
parameterZoneCode.Value = TextBox8.Text;
myCommand.Parameters.Add(parameterZoneCode); SqlParameter parameterMobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 12);
parameterMobilephone.Value = TextBox9.Text;
myCommand.Parameters.Add(parameterMobilephone); //进行数据库操作
myCommand.ExecuteNonQuery();
//关闭数据库连接
myConnection.Close();
红色部分错误 应该怎么写 UserID是自增长的
(
@UserID int,
@UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
RETURN
这个是AdduserALTER PROCEDURE dbo.Adduser
(
@UserID int,
@UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
RETURN
这个是Adduser 的存储过程 userID为自动增长的
//创建数据库连接和命令的对象
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); //打开数据库连接
myConnection.Open();
SqlCommand myCommand = new SqlCommand("Adduser", myConnection); //指明Sql命令的操作类型是使用存储过程
myCommand.CommandType = CommandType.StoredProcedure;
//给存储过程添加参数
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);
SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 16);
parameterUserName.Value = TextBox1.Text;
myCommand.Parameters.Add(parameterUserName); SqlParameter parameterPassword = new SqlParameter("@Password", SqlDbType.NVarChar, 12);
parameterPassword.Value = TextBox2.Text;
myCommand.Parameters.Add(parameterPassword); SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 10);
parameterName.Value = TextBox3.Text;
myCommand.Parameters.Add(parameterName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
parameterEmail.Value = TextBox4.Text;
myCommand.Parameters.Add(parameterEmail); SqlParameter parameterIDCardNumber = new SqlParameter("@IDCardNumber", SqlDbType.NVarChar, 18);
parameterIDCardNumber.Value = TextBox5.Text;
myCommand.Parameters.Add(parameterIDCardNumber); SqlParameter parameterTelephoneNumber = new SqlParameter("@TelephoneNumber", SqlDbType.NVarChar, 12);
parameterTelephoneNumber.Value = TextBox6.Text;
myCommand.Parameters.Add(parameterTelephoneNumber); SqlParameter parameterAddress = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
parameterAddress.Value = TextBox7.Text;
myCommand.Parameters.Add(parameterAddress); SqlParameter parameterZoneCode = new SqlParameter("@ZoneCode", SqlDbType.NVarChar, 10);
parameterZoneCode.Value = TextBox8.Text;
myCommand.Parameters.Add(parameterZoneCode); SqlParameter parameterMobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 12);
parameterMobilephone.Value = TextBox9.Text;
myCommand.Parameters.Add(parameterMobilephone); //进行数据库操作
myCommand.ExecuteNonQuery();
//关闭数据库连接
myConnection.Close();
红色部分错误 应该怎么写 UserID是自增长的
Parameter.Direction = ParameterDirection.InputOutput
Parameter.Direction = ParameterDirection.ReturnValue
就能取得UserID的值
你不取也不会出错
只要声明了就可以用了
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID .Direction = ParameterDirection.InputOutput
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
我觉得应该要把UserID和@UserID都删掉才对!
@UserId int output 在ado中,设置Parameter.Direction = ParameterDirection.Output
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);
AdduserALTER PROCEDURE dbo.Adduser (
@UserID int, @UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserID,
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserID,
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
既然是自动增长的,你完全可以在代码及数据库里面把红色的内容去掉,这样就正常了。我经常使用的,不会出错
无法将 NULL 值插入列 'UserID',表 'Storeonline.dbo.User';该列不允许空值。INSERT 失败。
在往表里插入数据是,自增列字段是不用考虑的.
AdduserALTER PROCEDURE dbo.Adduser (
@UserName nvarchar(16),
@Password nvarchar(12),
@Name nvarchar(10),
@Email nvarchar(50),
@IDCardNumber nvarchar(18),
@TelephoneNumber nvarchar(12),
@Address nvarchar(50),
@ZoneCode nvarchar(10),
@Mobilephone nvarchar(12)
)
AS
INSERT INTO [User]
(
UserName,
Password,
Name,
Email,
IDCardNumber,
TelephoneNumber,
Address,
ZoneCode,
Mobilephone
)
VALUES (
@UserName,
@Password,
@Name,
@Email,
@IDCardNumber,
@TelephoneNumber,
@Address,
@ZoneCode,
@Mobilephone )
RETURN @@IDENTITY
//创建数据库连接和命令的对象
SqlConnection myConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]); //打开数据库连接
myConnection.Open();
SqlCommand myCommand = new SqlCommand("Adduser", myConnection); //指明Sql命令的操作类型是使用存储过程
myCommand.CommandType = CommandType.StoredProcedure; //给存储过程添加参数
SqlParameter parameterUserID = new SqlParameter("@UserID", SqlDbType.Int, 4);
parameterUserID.Direction = ParameterDirection.ReturnValue;
parameterUserID.Value = Int32.Parse(userID);
myCommand.Parameters.Add(parameterUserID);
SqlParameter parameterUserName = new SqlParameter("@UserName", SqlDbType.NVarChar, 16);
parameterUserName.Value = TextBox1.Text;
myCommand.Parameters.Add(parameterUserName); SqlParameter parameterPassword = new SqlParameter("@Password", SqlDbType.NVarChar, 12);
parameterPassword.Value = TextBox2.Text;
myCommand.Parameters.Add(parameterPassword); SqlParameter parameterName = new SqlParameter("@Name", SqlDbType.NVarChar, 10);
parameterName.Value = TextBox3.Text;
myCommand.Parameters.Add(parameterName); SqlParameter parameterEmail = new SqlParameter("@Email", SqlDbType.NVarChar, 50);
parameterEmail.Value = TextBox4.Text;
myCommand.Parameters.Add(parameterEmail); SqlParameter parameterIDCardNumber = new SqlParameter("@IDCardNumber", SqlDbType.NVarChar, 18);
parameterIDCardNumber.Value = TextBox5.Text;
myCommand.Parameters.Add(parameterIDCardNumber); SqlParameter parameterTelephoneNumber = new SqlParameter("@TelephoneNumber", SqlDbType.NVarChar, 12);
parameterTelephoneNumber.Value = TextBox6.Text;
myCommand.Parameters.Add(parameterTelephoneNumber); SqlParameter parameterAddress = new SqlParameter("@Address", SqlDbType.NVarChar, 50);
parameterAddress.Value = TextBox7.Text;
myCommand.Parameters.Add(parameterAddress); SqlParameter parameterZoneCode = new SqlParameter("@ZoneCode", SqlDbType.NVarChar, 10);
parameterZoneCode.Value = TextBox8.Text;
myCommand.Parameters.Add(parameterZoneCode); SqlParameter parameterMobilephone = new SqlParameter("@Mobilephone", SqlDbType.NVarChar, 12);
parameterMobilephone.Value = TextBox9.Text;
myCommand.Parameters.Add(parameterMobilephone); //进行数据库操作
myCommand.ExecuteNonQuery();
//关闭数据库连接
myConnection.Close();
看看还出错否?
提示是
当前上下文中不存在名称“UserID”