如何在另一个表中存储一个表ID [英] How Can I Store One Table Id In Another Table

问题描述

con.Open();
SqlCommand cmd = new SqlCommand("insert into Exe_ContactDetails values(@Name,@Mobile,@Address,@UserId)", con);
cmd.CommandType = CommandType.Text;
string str = "Select ID from Team_Registration where UserName= '" + Session["New"] + "'";
//int N = Convert.ToInt32(str);
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@UserId", str);
cmd.ExecuteNonQuery();
con.Close();

（转换nvarchar值时转换失败'选择ID来自Team_Registration，其中UserName ='madhu''到数据类型int。）得到此错误。

请告诉我如何删除此错误。



如何将Team_registration ID存储在另一个表列中，如UserId

when I executed this code (Conversion failed when converting the nvarchar value 'Select ID from Team_Registration where UserName= 'madhu'' to data type int.)getting this error.
Please suggest to me how can I erase this error.

How can I store the Team_registration ID in another table column as UserId

推荐答案

You have forgotten to execute cmd to find ID from.Except that everything is ok in your code.happy coding

con.Open();
SqlCommand cmd = new SqlCommand("insert into Exe_ContactDetails values(@Name,@Mobile,@Address,@UserId)", con);
cmd.CommandType = CommandType.Text;
string str = "Select ID from Team_Registration where UserName= '" + Session["New"] + "'";
//-----------------You missed
SqlCommand cmd1 = new SqlCommand(str, con);
int id=(Int32)cmd1.ExecuteScalar();
//---------------------
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@UserId", id);
cmd.ExecuteNonQuery();
con.Close();

转换nvarchar时转换失败...

你有什么预期，您尝试将SQL查询字符串转换为整数，值...



您有两种可能的解决方案：

1. 坏...

运行两个查询。一个用于检索ID，第二个用于将其插入到另一个表中

"Conversion failed when converting the nvarchar"...
What have you expected, you try to convert a SQL query string to an integer, value...

You have two possible solution:
1. The bad...
Run two queries. one for retrieve the ID and the second to insert it in to an other table

con.Open();

SqlCommand cmd = new SqlCommand("Select ID from Team_Registration where UserName= '@UserName'";
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@UserName", Session["New"]);
int N = Convert.ToInt32(cmd.ExecuteScalar());

SqlCommand cmd = new SqlCommand("insert into Exe_ContactDetails values(@Name,@Mobile,@Address,@UserId)", con);
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@UserId", N);
cmd.ExecuteNonQuery();

con.Close();

2.好......

创建一个存储过程，接收所有相关参数并单独执行所有必要的查询...

2. The good...
Create a stored procedure, that receives all the relevant parameters and do all the necessary queries alone...

CREATE PROCEDURE MY_SP @NAME AS NVARCHAR(MAX), @MOBILE AS NVARCHAR(MAX), @ADDRESS AS NVARCHAR(MAX), @USERNAME AS NVARCHAR(MAX)
AS
  DECLARE @USERID AS INT

  SELECT @USERID = ID FROM TEAM_REGISTRATION WHERE USERNAME = @USERNAME

  INSERT INTO EXE_CONTACTDERAILS VALUES(@NAME, @MOBILE, @ADDRESS, @USERID)

con.Open();

SqlCommand cmd = new SqlCommand("MY_SP", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Name", txtName.Text.ToString());
cmd.Parameters.AddWithValue("@Mobile", txtMobile.Text.ToString());
cmd.Parameters.AddWithValue("@Address", txtAddress.Text.ToString());
cmd.Parameters.AddWithValue("@UserName", Session["New"]);
cmd.ExecuteNonQuery();

con.Close();

这篇关于如何在另一个表中存储一个表ID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！