Click here to Skip to main content
15,897,371 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
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#



SQL
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)


C#
 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);

            }
        }
    }
Posted
Updated 27-Aug-15 22:28pm
v4
Comments
Herman<T>.Instance 28-Aug-15 4:30am    
And what is the question my dear?

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:

C#
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}
});


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:
SQL
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:

SQL
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 ^_^

SQL
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



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
 
Share this answer
 
v3
Comments
Member 11841001 28-Aug-15 6:22am    
Sorry about that I am new at this ,but still no change!.
Andy Lanng 28-Aug-15 6:34am    
hmm, then the syntax is good, now. So you'll have to move on to debugging:

C# to Sql debugging is "fairly" simple. I'll add it to my solution...
Andy Lanng 28-Aug-15 6:55am    
updated
Try this it will work for you...
also in procedure give the data time properly.
and also see that "dateofbirth" in
Quote:
dbCmd.Parameters.AddWithValue("@DateOfBith", DateOfBirth);

is in date format.
SQL
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>
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900