Stored Procedure Not Storing Data : The Official Microsoft ASP.NET Forums

Reply

Stored Procedure Not Storing Data

Feb 22, 2013 11:17 PM|LINK

I have a stored procedure that is not working, I need some assistance on why.

The first part of the procedure is this:

ALTER PROCEDURE dbo.Admin_Customers_Create
	(
	-- Parameters Only For Navigation of This Procedure
	@UserName nvarchar(256),
	@Promo int, -- EITHER 0 or 1
	--CuData Table
	@CustomerTypeID int,
	@AccountTypeID int,
	@AccountName varchar(100),
	@CompanyWebsite nvarchar(256), -- MAY BE NULL
	@CompanyDescription nvarchar(2000),-- MAY BE NULL
	@AccountSupervisorID int,
	@CustomerSince datetime,
	@CreditRating decimal(2,1),
	@DueDate int,
	@RewardPointUseID int,

	-- CuDataSummary Table
	@MarketingMediaID int,

	-- CuLocations Table
	@LocationName varchar(50),
	@StreetAddress varchar(50),
	@Suite varchar(8),
	@City varchar(50),
	@County varchar(50),
	@StateID char(2),
	@ZipCode varchar(10),
	@Country varchar(50),
	@BuildingTypeID int,
	@Floors tinyint,
	@PhoneNumber char(12), -- MAY BE NULL
	@FaxNumber char(12), -- MAY BE NULL
	@CommunityName nvarchar(256), -- MAY BE NULL
	@GateCode varchar(10), -- MAY BE NULL
	@SqFt decimal(10 ,2), -- MAY BE NULL
	@WeService bit, -- MAY BE NULL
	@AlarmCode nvarchar(10), -- MAY BE NULL
	@LocationTypeID int,
	@DistrictID int,
	@DistrictName nvarchar(256),

	-- CuUser Table
	@FirstName varchar(50),
	@LastName varchar(50),
	@Credentials varchar(50),-- MAY BE NULL
	@Title varchar(50),-- MAY BE NULL
	@EmailWork nvarchar(256),-- MAY BE NULL
	@EmailHome nvarchar(256),-- MAY BE NULL
	@PhoneWork char(12),-- MAY BE NULL
	@PhoneHome char(12),-- MAY BE NULL
	@PhoneMobile char(12),-- MAY BE NULL
	@PhoneExt varchar(8)-- MAY BE NULL
	)
AS
BEGIN
	


-- ------------------------------------------------------------
-- STEP 1: Insert Basic Customer Info and Create New CustomerID
-- ------------------------------------------------------------
INSERT INTO CuData(
	CustomerTypeID,
	AccountTypeID,
	AccountStatusID,
	AccountName,
	CompanyDescription,
	CompanyWebsite,
	CreditRating,
	CustomerDiscount,
	DueDate,
	AccountSupervisorID,
	RewardPointUseID,
	InventoryTracking,
	OnlineUser,
	Reference,
	SetupComplete,
	CustomerSince,
	DateUpdated)
	VALUES(
	@CustomerTypeID,
	@AccountTypeID,
	'1',
	@AccountName,
	@CompanyDescription,
	@CompanyWebsite,
	@CreditRating,
	'0.0',
	@DueDate,
	@AccountSupervisorID,
	@RewardPointUseID,
	'False',
	'True',
	'True',
	'False',
	@CustomerSince,
	GETDATE())
	-- NEXT: GET New CustomerID
	DECLARE @CustomerID int
	SELECT @CustomerID = CustomerID FROM CuData WHERE AccountName = @AccountName

-- ------------------------------------------------------------
-- STEP 2: Insert Basic Location Info and Create New LocationID
-- ------------------------------------------------------------

	DECLARE @CombinedAddressSuite varchar(50)
	SET @CombinedAddressSuite = @StreetAddress
	IF (@Suite IS NOT NULL)
	BEGIN
		SET @CombinedAddressSuite = @StreetAddress + ' ' + @Suite
	END
-- Insert Customers Address

My question is: If no data is being stored starting from the first table, what possible places could the error be if

1. All parameters to be passed in are present and accounted for:

    private void CreateNewAccount()
    {
        _SqlCm = new SqlCommand();
        _SqlCn = new SqlConnection(Config.ConString);
        _SqlCm.CommandType = CommandType.StoredProcedure;
        _SqlCm.CommandText = "Admin_Customers_Create";
        _SqlCm.Parameters.AddWithValue("@UserName", InputCreateCustomer_Username.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@CustomerTypeID", InputCreateCustomer_CustomerType.SelectedValue);
        _SqlCm.Parameters.AddWithValue("@AccountTypeID", InputCreateCustomer_AccountType.SelectedValue);
        if (InputCreateCustomer_AccountType.SelectedValue == "1")
        {
            _SqlCm.Parameters.AddWithValue("@AccountName", InputCreateCustomer_CompanyName.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@DueDate", "15");
            _SqlCm.Parameters.AddWithValue("@CompanyDescription", InputCreateCustomer_CompanyDescription.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@CompanyWebsite", InputCreateCustomer_CompanyWebsite.Text.Trim());
        }
        if (InputCreateCustomer_AccountType.SelectedValue == "2")
        {
            _SqlCm.Parameters.AddWithValue("@AccountName", InputCreateCustomer_LastName.Text.Trim() + ", " + InputCreateCustomer_FirstName.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@DueDate", "0");
            _SqlCm.Parameters.AddWithValue("@CompanyDescription", DBNull.Value);
            _SqlCm.Parameters.AddWithValue("@CompanyWebsite", DBNull.Value);
        }
        _SqlCm.Parameters.AddWithValue("@MarketingMediaID", InputCreateCustomer_AcquisitionSource.SelectedValue);
        if (InputCreateCustomer_CustomerSinceShow.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@CustomerSince", InputCreateCustomer_CustomerSince.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@CreditRating", InputCreateCustomer_CreditRating.SelectedValue);

        }
        if (InputCreateCustomer_CustomerSinceHide.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@CustomerSince", DateTime.Now.ToString());
            _SqlCm.Parameters.AddWithValue("@CreditRating", "5.0");
        }
        _SqlCm.Parameters.AddWithValue("@AccountSupervisorID", InputCreateCustomer_AccountSupervisor.SelectedValue);

        // Insert Contact Information
        _SqlCm.Parameters.AddWithValue("@FirstName", InputCreateCustomer_FirstName.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@LastName", InputCreateCustomer_LastName.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@Title", InputCreateCustomer_Title.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@Credentials", InputCreateCustomer_Credentials.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@EmailWork", InputCreateCustomer_EmailWork.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@EmailHome", InputCreateCustomer_EmailHome.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@PhoneWork", InputCreateCustomer_PhoneNumber.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@PhoneExt", InputCreateCustomer_PhoneExt.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@PhoneHome", InputCreateCustomer_PhoneHome.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@PhoneMobile", InputCreateCustomer_PhoneMobile.Text.Trim());
        // Insert Social Networking
        //_SqlCm.Parameters.AddWithValue("@Facebook", "www.facebook.com/" + InputCreateCustomer_Facebook.Text.Trim());
        //_SqlCm.Parameters.AddWithValue("@Twitter", "https://twitter.com/#!/" + InputCreateCustomer_Twitter.Text.Trim());
        //_SqlCm.Parameters.AddWithValue("@LinkedIn", "http://www.linkedin.com/in/" + InputCreateCustomer_LinkedIn.Text.Trim());
        //_SqlCm.Parameters.AddWithValue("@BLOG", myUserId);
        //_SqlCm.Parameters.AddWithValue("@Tumblr", InputCreateCustomer_Tumblr.Text.Trim() + ".Tumblr.com/");
        // Insert Account
        _SqlCm.Parameters.AddWithValue("@Promo", "0");
        _SqlCm.Parameters.AddWithValue("@RewardPointUseID", InputCreateCustomer_RewardUse.SelectedValue);

        // Insert Location
        _SqlCm.Parameters.AddWithValue("@LocationName", InputCreateCustomer_LocationName.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@StreetAddress", InputCreateCustomer_StreetAddress.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@Suite", InputCreateCustomer_Suite.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@City", InputCreateCustomer_City.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@StateID", InputCreateCustomer_State.SelectedValue.ToString());
        _SqlCm.Parameters.AddWithValue("@ZipCode", InputCreateCustomer_ZipCode.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@County", InputCreateCustomer_County.SelectedItem.ToString());
        _SqlCm.Parameters.AddWithValue("@Country", InputCreateCustomer_Country.SelectedItem.ToString());
        _SqlCm.Parameters.AddWithValue("@PhoneNumber", InputCreateCustomer_PhoneNumber.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@FaxNumber", InputCreateCustomer_FaxNumber.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@BuildingTypeID", InputCreateCustomer_BuildingType.SelectedValue);
        _SqlCm.Parameters.AddWithValue("@LocationTypeID", InputCreateCustomer_LocationType.SelectedValue);
        _SqlCm.Parameters.AddWithValue("@Floors", InputCreateCustomer_Floors.SelectedValue);
        _SqlCm.Parameters.AddWithValue("@AlarmCode", InputCreateCustomer_AlarmCode.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@SqFt", InputCreateCustomer_SqFt.Text.Trim());
        _SqlCm.Parameters.AddWithValue("@WeService", InputCreateCustomer_WeService.Checked);
        if (SelectCreateCustomer_District_Hide.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@DistrictID", DBNull.Value);
            _SqlCm.Parameters.AddWithValue("@DistrictName", DBNull.Value);
        }
        if (SelectCreateCustomer_District_Show.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@DistrictID", InputCreateCustomer_DistrictID.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@DistrictName", InputCreateCustomer_DistrictName.Text.Trim());
        }
        if (SelectCreateCustomer_Community_Hide.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@CommunityName", DBNull.Value);
            _SqlCm.Parameters.AddWithValue("@GateCode", DBNull.Value);
        }
        if (SelectCreateCustomer_Community_Show.Checked == true)
        {
            _SqlCm.Parameters.AddWithValue("@CommunityName", InputCreateCustomer_CommunityName.Text.Trim());
            _SqlCm.Parameters.AddWithValue("@GateCode", InputCreateCustomer_GateCode.Text.Trim());
        }

and

2. All fields, columns, and restraints from the first table have been chaeck possibly by now 100 times?

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks

Edited by Bobby-Z on Feb 22, 2013 11:23 PM

Reply

Re: Stored Procedure Not Storing Data

Feb 23, 2013 12:30 AM|LINK

If that is your entire c# code, where are you calling ExecuteNonQuery on your command object?

If this fixed your issue then please 'Mark as Answer'

Reply

Re: Stored Procedure Not Storing Data

Feb 23, 2013 01:28 AM|LINK

right after the code posted before

try
        {
            _SqlCn.Open();
            _SqlCm.Connection = _SqlCn;
            _SqlCm.ExecuteNonQuery();
        }

Works fine with all of my other procedures, but for some reason this one will not work

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks

Edited by Bobby-Z on Feb 23, 2013 01:29 AM

Reply

Re: Stored Procedure Not Storing Data

Feb 23, 2013 09:48 PM|LINK

Anybody??? Any Suggestions - I have checked the code over 100 times and it should work, there are no errors, no omissions, so it must be something I am not seeing or don't know.

The only other thing would be to program C# to access the database 25 times,

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks

Reply

Re: Stored Procedure Not Storing Data

Feb 23, 2013 10:21 PM|LINK

It seems you are storing text in ID field which are set to int. So either use the 3 param overload where you pass in SqlDataType.? in c# or you can do trial by error, which is you cut down the params in both the sp and c# and try building them in blocks like 3-4 at a time. Its a simple process and will tell you where the problem may be.

If this fixed your issue then please 'Mark as Answer'

Reply

Bobby-Z

Contributor

2838 Points

1120 Posts

Re: Stored Procedure Not Storing Data

Feb 24, 2013 02:44 AM|LINK

I was finally able to get an error message

System.FormatException: Failed to convert parameter value from a String to a Decimal. ---> System.FormatException: Input string was not in a correct format.

I have checked are my input parameters ok, because I don't see any issues where I am trying to pass astring to a decimal, I only have 4 or 5 decimal fields

This might be the problem

SqlParameter _SqFt = _SqlCm.Parameters.AddWithValue("@SqFt", InputCreateCustomer_SqFt.Text.Trim());
        _SqFt.SqlDbType = SqlDbType.Decimal;

because I am not passing any value, so does that make it null or send an empty string which does not convert to decimal?

This would be an educated guess because before I added rollback transaction, the location information table was not being populated

"Success is the Sum of Small Efforts, Repeated Day in and Day Out - Without Ceasing!"

Currently Learning: ASP, SQL, CSS, JavaScript, AJAX, XML, XSLT, C# So please be patient with me! Thanks

Edited by Bobby-Z on Feb 24, 2013 02:46 AM
Edited by Bobby-Z on Feb 24, 2013 02:47 AM

Reply

ammd

Participant

1349 Points

257 Posts

Re: Stored Procedure Not Storing Data

Feb 24, 2013 04:30 AM|LINK

If you are passing null/empty values to fields in db that require integer/decimal then that would definately cause an issue.

If this fixed your issue then please 'Mark as Answer'

Stored Procedure Not Storing Data RSS

6 replies

Bobby-Z

Stored Procedure Not Storing Data

ammd

Re: Stored Procedure Not Storing Data

Bobby-Z

Re: Stored Procedure Not Storing Data

Bobby-Z

Re: Stored Procedure Not Storing Data

ammd

Re: Stored Procedure Not Storing Data

Bobby-Z

Re: Stored Procedure Not Storing Data

ammd

Re: Stored Procedure Not Storing Data

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft