如何在Customer，Company和Cust_Comp这三个表中插入数据，这三个表是使用存储过程的前两个表的链接表 [英] How can I insert data in three tables which are Customer, Company and Cust_Comp which is the the linking table of the first two tables using stored procedure
问题描述
当我在Sql管理工作室执行时它似乎工作但是当我用C＃运行它时，只需要公司参数而不是客户！
这是我的代码存储过程和C＃
Its seems to work while I execute on Sql management studio but when i am running it with C# it only takes the Company Parameters not customer!
Here is my code Stored procedure and C#
USE [pastel]
GO
/****** Object: StoredProcedure [dbo].[AddCustomer] Script Date: 8/28/2015 9:59:06 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[AddCustomer]
@CompanyName nvarchar(50),
@RegistrationNo nvarchar(50),
@CompanyTelephoneNo nvarchar(50),
@BuildingName nvarchar(50),
@CompanyTown nvarchar(50),
@Town nvarchar(50),
@CopmanyCity nvarchar(50),
@City nvarchar(50),
@CompanyPostalCode nvarchar(50),
@Country nvarchar(50),
@TelephoneNo nvarchar(50),
@CompanyStreet nvarchar(50),
@YearsInBusiness nvarchar(50),
@Street nvarchar(50),
@CompanyLogo varbinary(max),
@EmailAddress nvarchar(50),
@CompanyWebsite nvarchar(50),
@BankName nvarchar(50),
@AccountNo nvarchar(50),
@BranchCode nvarchar(50),
@Status int,
@PostalCode nvarchar (50),
@FirstName nvarchar(50),
@LastName nvarchar(50),
@DateOfBirth date,
@CellNo nvarchar(50),
@Gender nvarchar(50),
@IDNo nvarchar(50),
@Nationality nvarchar(50),
@Race nvarchar(50)
As
declare @CompanyID int
declare @CustomerID int
INSERT INTO COMPANY
(CompanyName, RegistrationNo,CompanyTelephoneNo, BuildingName, CompanyStreet,CompanyTown,CompanyCity,CompanyPostalCode,Country,YearsInBusiness,CompanyLogo,EmailAddress,CompanyWebsite,BankName,AccountNo,BranchCode,[Status])
VALUES
(@CompanyName,@RegistrationNo,@CompanyTelephoneNo,@BuildingName, @CompanyStreet,@CompanyTown,@CopmanyCity,@PostalCode,@Country,@YearsInBusiness,@CompanyLogo,@EmailAddress,@CompanyWebsite,@BankName,@AccountNo,@BranchCode,@Status);
Set @CompanyID=SCOPE_IDENTITY()
INSERT INTO CUSTOMER
(FirstName,LastName,DateOfBirth,Gender,IDNo,Nationality,Race,Country,City,Town,Street,PostalCode,TelephoneNo,[CellNo],Email)
VALUES(@FirstName,@LastName,@DateOfBirth,@Gender,@IDNo,@Nationality,@Race,@Country,@City,@Town,@Street,@PostalCode,@TelephoneNo,@CellNo,@EmailAddress);
Set @CustomerID= SCOPE_IDENTITY()
INSERT INTO CUST_COMP
(CustomerID,CompanyID)
VALUES(@CustomerID,@CompanyID)
private void AddCustCompany(string FirstName, string LastName, string CellNo, string EmailAddress, string CompanyName, string BuildingName, string CompanyCity, string CompanyStreet, string CompanyTown,
string CompanyPostalCode, string CompanyTelephoneNo, string Country, string CompanyWebsite, string RegistrationNo, byte[] CompanyLogo, string City, string Street, string Town, string TelephoneNo,DateTime DateOfBirth)
{
using (SqlConnection dbConn = new SqlConnection(connString))
{
dbConn.Open();
SqlCommand dbCmd = new SqlCommand("AddCustomer", dbConn);
dbCmd.CommandType = CommandType.StoredProcedure;
dbCmd.Parameters.AddWithValue("@FirstName", SqlDbType.NVarChar).Value = FirstName;
dbCmd.Parameters.AddWithValue("@LastName", SqlDbType.NVarChar).Value = LastName;
dbCmd.Parameters.AddWithValue("@CellNo", SqlDbType.NVarChar).Value = CellNo;
dbCmd.Parameters.AddWithValue("@EmailAddress", SqlDbType.NVarChar).Value = EmailAddress;
dbCmd.Parameters.AddWithValue("@CompanyName", SqlDbType.NVarChar).Value = CompanyName;
dbCmd.Parameters.AddWithValue("@BuildingName", SqlDbType.NVarChar).Value = BuildingName;
dbCmd.Parameters.AddWithValue("@CompanyCity", SqlDbType.NVarChar).Value = CompanyCity;
dbCmd.Parameters.AddWithValue("@CompanyStreet", SqlDbType.NVarChar).Value = CompanyStreet;
dbCmd.Parameters.AddWithValue("@CompanyTown", SqlDbType.NVarChar).Value = CompanyTown;
dbCmd.Parameters.AddWithValue("@CompanyPostalCode", SqlDbType.NVarChar).Value = CompanyPostalCode;
dbCmd.Parameters.AddWithValue("@CompanyTelephoneNo", SqlDbType.NVarChar).Value = CompanyTelephoneNo;
dbCmd.Parameters.AddWithValue("@Country", SqlDbType.NVarChar).Value = Country;
dbCmd.Parameters.AddWithValue("@CompanyWebsite", SqlDbType.NVarChar).Value = CompanyWebsite;
dbCmd.Parameters.AddWithValue("@RegistrationNo", SqlDbType.NVarChar).Value = RegistrationNo;
dbCmd.Parameters.AddWithValue("@Town", SqlDbType.NVarChar).Value = Town;
dbCmd.Parameters.AddWithValue("@City", SqlDbType.NVarChar).Value = City;
dbCmd.Parameters.AddWithValue("@Streeet", SqlDbType.NVarChar).Value = Street;
dbCmd.Parameters.AddWithValue("@TelephoneNo", SqlDbType.NVarChar).Value = TelephoneNo;
dbCmd.Parameters.AddWithValue("@DateOfBirth", SqlDbType.Date).Value = DateOfBirth;
dbCmd.Parameters.AddWithValue("@CompanyLogo", SqlDbType.VarBinary).Value = CompanyLogo;
int i = dbCmd.ExecuteNonQuery();
dbConn.Close();
}
}
protected void btnCreateCustomer_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
FileUpload img = (FileUpload)FileUpload1;
Byte[] imgByte = null;
if (img.HasFile && img.PostedFile != null)
{
// Posted Image
HttpPostedFile File = FileUpload1.PostedFile;
imgByte = new Byte[File.ContentLength];
File.InputStream.Read(imgByte, 0, File.ContentLength);
}
string FirstName = txtFirstName.Text;
string LastName = txtLastName.Text;
string CellNo = txtCellNumber.Text;
string EmailAddress = txtEmail.Text;
string City = txtCity.Text;
string Street = txtStreet.Text;
string Town = txtTown.Text;
string TelephoneNo = txtTelephoneNo.Text;
DateTime DateOfBirth;
if (DateTime.TryParseExact(TxtDob.Text,"yyyy-MM-dd:0", null, DateTimeStyles.None, out DateOfBirth))
{
}
{
lblMessageDate.Text = ("date and year is incorrect");
}
// EncryptPassword encrypt = new EncryptPassword();
//GeneratePassword pass = new GeneratePassword();
string CompanyName = txtCompanyName.Text;
string BuildingName = txtBuildingName.Text;
string CompanyCity = txtCompCity.Text;
string CompanyStreet = txtStreetName.Text;
string CompanyTown = txtTown.Text;
string CompanyPostalCode = txtCompPostalCode.Text;
string CompanyTelephoneNo = txtPhoneNumber.Text;
string Country = DropDownList1.SelectedValue;
string CompanyWebsite = txtCompWebsite.Text;
string RegistrationNo = txtRegistrationNo.Text;
byte[] CompanyLogo = FileUpload1.FileBytes;
AddCustCompany( FirstName, LastName, CellNo, EmailAddress,CompanyName,BuildingName,CompanyCity, CompanyStreet, CompanyTown, CompanyPostalCode, CompanyTelephoneNo,Country,CompanyWebsite, RegistrationNo, CompanyLogo, City, Street, Town, TelephoneNo, DateOfBirth);
}
}
}
推荐答案
你确定它有效吗？ ？
dbCmd.Parameters.AddWithValue没有采用db类型的重载。您将值设置为SqlDbType.NVarChar然后更改它。
请尝试使用此语法。它更具可读性，恕我直言：
Are you sure it works at all?
dbCmd.Parameters.AddWithValue has no overloads that take a db type. You are setting the value as SqlDbType.NVarChar then changing it.
Try this syntax instead. It's a little more readable, IMHO:
dbCmd.Parameters.AddRange(new[]
{
new SqlParameter("@FirstName", SqlDbType.NVarChar) {Value = FirstName},
new SqlParameter("@LastName", SqlDbType.NVarChar) {Value = LastName},
new SqlParameter("@CellNo", SqlDbType.NVarChar) {Value = CellNo},
new SqlParameter("@EmailAddress", SqlDbType.NVarChar) {Value = EmailAddress},
new SqlParameter("@CompanyName", SqlDbType.NVarChar) {Value = CompanyName},
new SqlParameter("@BuildingName", SqlDbType.NVarChar) {Value = BuildingName},
new SqlParameter("@CompanyCity", SqlDbType.NVarChar) {Value = CompanyCity},
new SqlParameter("@CompanyStreet", SqlDbType.NVarChar) {Value = CompanyStreet},
new SqlParameter("@CompanyTown", SqlDbType.NVarChar) {Value = CompanyTown},
new SqlParameter("@CompanyPostalCode", SqlDbType.NVarChar) {Value = CompanyPostalCode},
new SqlParameter("@CompanyTelephoneNo", SqlDbType.NVarChar) {Value = CompanyTelephoneNo},
new SqlParameter("@Country", SqlDbType.NVarChar) {Value = Country},
new SqlParameter("@CompanyWebsite", SqlDbType.NVarChar) {Value = CompanyWebsite},
new SqlParameter("@RegistrationNo", SqlDbType.NVarChar) {Value = RegistrationNo},
new SqlParameter("@Town", SqlDbType.NVarChar) {Value = Town},
new SqlParameter("@City", SqlDbType.NVarChar) {Value = City},
new SqlParameter("@Streeet", SqlDbType.NVarChar) {Value = Street},
new SqlParameter("@TelephoneNo", SqlDbType.NVarChar) {Value = TelephoneNo},
new SqlParameter("@DateOfBirth", SqlDbType.Date) {Value = DateOfBirth},
new SqlParameter("@CompanyLogo", SqlDbType.VarBinary) {Value = CompanyLogo}
});
这样我们正确设置类型并在初始化ctor中构建对象。
我不知道这是否会解决问题他发出你的问题，但它会修复你向我们展示的代码^ _ ^
如果能解决问题，请告诉我。
安迪
更新：OP实施了变化，但仍然没有快乐（我以为可能没有）
现在我们继续调试。你C＃不会返回任何异常（或者你会告诉我们）所以bug必须在db中发生。这可能是我们没有看到的SP（存储过程）的问题，或者服务器上可能存在触发器或其他灾难性机制。我们可以向存储过程添加错误和错误检查：
首先，将整个事物包装在一个事务中。这样，如果有一件事失败，我们可以回滚所有内容：
This way we set the type correctly and build the object in the initialize ctor.
I don't know if that will fix the issue you have, but it will fix the code you have shown us ^_^
Let me know if that solves it.
Andy
UPDATE: OP has implemented the changes but still no joy (I thought there might not be)
Now we move on to debugging. You C# doesn't return any exception (or you would have told us) so the bugs must be happening within the db. This could be an issue with the SP (Stored Procedure) that we just aren't seeing, or there might be a trigger or some other disastrous mechanism on the server. We can add error and bug checking to the stored procedure:
First off, wrap the whole thing in a transaction. That way, if one thing fails we can roll back everything:
ALTER PROCEDURE [dbo].[AddCustomer]
@CompanyName nvarchar(50),
--blah blah
as
Begin Transaction
--Do stuff
Commit Transaction
所有这一切都是启动一个新事务并提交它。如果发生错误，我们需要一种回滚方式：
All this does is start a new transaction and commits it. We need a way to rollback if an error occurs:
ALTER PROCEDURE [dbo].[AddCustomer]
@CompanyName nvarchar(50),
--blah blah
as
BEGIN
Declare @ErrorNum INT
Begin Transaction
--Do stuff
SET @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
Rollback Transaction
return @@ErrorNum
END
--Do the next bit of stuff
SET @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
Rollback Transaction
return @@ErrorNum
END
Commit Transaction
return 0
END
现在我们可以判断是否有任何实际错误发生了，我们可以通过意外问题使数据完好无损。
让我们更深入
我们没有处理错误消息的流程，我们可以使用它来生成我们自己的^ _ ^
So now we can tell if any actual errors occur, and we can leave the data undamaged by unexpected issues.
"Lets go deeper"
No we have the process in place to handle error messages, we can use this to generate our own ^_^
ALTER PROCEDURE [dbo].[AddCustomer]
@CompanyName nvarchar(50),
--blah blah
as
BEGIN
Declare @ErrorNum INT --There are a set number of Error Numbers. Each have a message associated. There are many numbers that are left for use to use
--Check Parameters are all ok first
IF(badParams)
BEGIN
RAISERROR(50001, "Bad Parameter",10)
--We use RAISERROR (only 1 'E'"). Custom errornum starts at 50k. Message can be used as return too. Last digit is severity: 10=don't halt, 16=halt. We want don't halt because we want to make sure we close off the transaction
END
SET @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
Rollback Transaction
return @@ErrorNum
END
--Check record inserted
IF(FailedInsert)
BEGIN
RAISERROR(50002, "Failed Insert 1",10)
End
Begin Transaction
--Do stuff
SET @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
Rollback Transaction
return @@ErrorNum
END
--Do the next bit of stuff
--Check record inserted
IF(FailedInsert)
BEGIN
RAISERROR(50003, "Failed Insert 2",10)
End
SET @ErrorNum = @@ERROR
IF @ErrorNum <> 0
BEGIN
Rollback Transaction
return @@ErrorNum
END
Commit Transaction
return 0
END
你可以使用C＃代码中的返回值。 0 =一切都好，50001 =坏参数，50002& 50003 =数据没有插入。
到目前为止它什么也解决不了，但它会告诉我you where the error is :)
I know it seems long winded, but this is how composite SPs should be written
There is much online regarding Error Handling and RAISERROR online if you want to know more.
Let me know how it goes ^_^
Andy
You can the use the return value in your C# code. 0 = all ok, 50001 = bad Params, 50002 & 50003 = data didn't insert.
So far it solves nothing, but it will tell you where the error is :)
I know it seems long winded, but this is how composite SPs should be written
There is much online regarding Error Handling and RAISERROR online if you want to know more.
Let me know how it goes ^_^
Andy
Try this it will work for you...
also in procedure give the data time properly.
and also see that \"dateofbirth\" in
Try this it will work for you...
also in procedure give the data time properly.
and also see that "dateofbirth" in
dbCmd.Parameters.AddWithValue(\"@DateOfBith\" , DateOfBirth);
dbCmd.Parameters.AddWithValue("@DateOfBith", DateOfBirth);
is in date format.
is in date format.
dbCmd.Parameters.AddWithValue("@FirstName", FirstName);
dbCmd.Parameters.AddWithValue("@LastName", LastName);
dbCmd.Parameters.AddWithValue("@CellNo", CellNo);
dbCmd.Parameters.AddWithValue("@EmailAddress", EmailAddress);
dbCmd.Parameters.AddWithValue("@CompanyName", CompanyName);
dbCmd.Parameters.AddWithValue("@BuildingName", BuildingName);
dbCmd.Parameters.AddWithValue("@CompanyCity", CompanyCity);
dbCmd.Parameters.AddWithValue("@CompanyStreet", CompanyStreet);
dbCmd.Parameters.AddWithValue("@CompanyTown", CompanyTown);
dbCmd.Parameters.AddWithValue("@CompanyPostalCode", CompanyPostalCode);
dbCmd.Parameters.AddWithValue("@CompanyTelephoneNo", CompanyTelephoneNo);
dbCmd.Parameters.AddWithValue("@Country", Country);
dbCmd.Parameters.AddWithValue("@CompanyWebsite", CompanyWebsite);
dbCmd.Parameters.AddWithValue("@RegistrationNo", RegistrationNo);
dbCmd.Parameters.AddWithValue("@Town", Town);
dbCmd.Parameters.AddWithValue("@City",City);
dbCmd.Parameters.AddWithValue("@Streeet", Street);
dbCmd.Parameters.AddWithValue("@TelephoneNo",TelephoneNo);
dbCmd.Parameters.AddWithValue("@DateOfBith", DateOfBirth);
dbCmd.Parameters.AddWithValue("@CompanyLogo", CompanyLogo);
</pre>这篇关于如何在Customer，Company和Cust_Comp这三个表中插入数据，这三个表是使用存储过程的前两个表的链接表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
