An "Import" Feature - alas

Andrew.Berry · October 10th, 2007, 03:30 AM

Hi guys,

I've started with ASP a couple of months ago, and have posted a few times. I've managed to resolve most of them, but there is one little bit that I can't do.

I have an app that takes in a customerid querystring. This customerid int then loads up a gridview after a db lookup (to return their products).

My problem is, that if a customer has no transactions, then the gridview doesn't show up. This means I can't select a value to get my DetailsView to show, I cannot insert new entries. So a new customer will not be able to insert new entries.

My proposed solution is to have an import facility. This would work by having an import button and a DropDownList of all existing Customers in the Database. You select a Customer, Press Import, and then what I want it to do is for every entry relevant to the selected customer(from the dropdownlist), I want to write an entry into the databse, with the same values as the old entry, but changing the customercode to the customercode in the querystring.

Hmmm, i'll try explain with pseudocode....

Select Customer to copy
Import
For each entry for the old customer
INSERT into TABLE where CustCode=QueryString and all other results staying the same.

Hope that explains things enough for you.

Cheers

dparsons · October 10th, 2007, 07:14 AM

You can do this strictly in SQL which, i think, would be a bit faster then writing all the code you proposed above:

Code:

CREATE PROCEDURE usp_ImportCustomer
(
 @customerIDOld int,
 @customerIDNew int
)
as

INSERT INTO [Table](customerID, col1, col2, col3)
SELECT
    @customerIDNew,
    col1, 
    col2,
    col3
FROM [Table]
WHERE customerID = @customerIDOld

CustomerIDOld is obviously the id you would get from the DropDownList and customerIDNew would be the current users id.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

Andrew.Berry · October 10th, 2007, 07:45 AM

Ah, I see. I'll give that a whirl. Just one of things where you look too deep into a problem, when a simple solution is available!

dparsons · October 10th, 2007, 07:57 AM

No problem, hope it works out for you ^^

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
Technical Editor for:
Beginning Visual C# 2008
Professional Search Engine Optimization with ASP.NET
Professional IIS 7 and ASP.NET Integrated Programming
Wrox Blox: Introduction to Google Gears
Wrox Blox: Create Amazing Custom User Interfaces with WPF and .NET 3.0
================================================== =========

Andrew.Berry · October 15th, 2007, 08:52 AM

Got the Stored Proc done, but i'm having problems using it in my VB code(for the button_onClick command).

I have been looking on the microsoft support site, and it's asking me to populate a Dataset, but i'm not sure what i'm meant to do.

Here is the link for reference: http://support.microsoft.com/kb/306574

And also, here is my Stored Proc, for others if needed:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Andrew Berry>
-- Create date: <11/10/2007>
-- Description: <HIP Pricing - Import Customer records>
-- =============================================
CREATE PROCEDURE [dbo].[thar_HIP_importcusts](
    -- Add the parameters for the stored procedure here
    @custcode_old varchar(32),
    @custcode_new varchar(32)
    )
AS
BEGIN
INSERT INTO HIPPricing (ProductCode, CustomerCode, CoverType, Pages, Quantity, Price, NoPrintPrice)

        Select
            ProductCode,
            @custcode_new,
            CoverType,
            Pages,
            Quantity,
            Price,
            NoPrintPrice
        From HIPPricing
        Where CustomerCode = @custcode_old
END

planoie · October 15th, 2007, 09:08 AM

Andrew,

You need to use a data adapter to populate the dataset. Look here:

http://msdn2.microsoft.com/en-us/lib...taadapter.aspx

-Peter

Andrew.Berry · October 19th, 2007, 03:51 AM

Hi again.

I'm nearly there. I'm just getting some connection issues. When I hit the import button, when I try to open the connection to run the Stored Proc, I get the following:

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

I've tried everything I can think of to allow remote connections, but to no avail. Thoughts?

Andrew.Berry · October 19th, 2007, 03:53 AM

Sorry for the numerous edits. I have been trying myself and now get the following error:

ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: ExecuteNonQuery requires an open and available Connection. The connection's current state is closed.

Here is the vb code for button_click:

        Dim strConnection As String
        strConnection = System.Web.Configuration.WebConfigurationManager.C onnectionStrings("FSPConnectionString").Connection String

        Dim MyConnection As SqlConnection
        MyConnection = New SqlConnection(strConnection)

        Dim cmdTest As New SqlDataAdapter("dbo.thar_HIP_importcusts", strConnection)

        cmdTest.SelectCommand.CommandType = Data.CommandType.StoredProcedure

        cmdTest.SelectCommand.Parameters.Add(New SqlParameter("@custcode_old", Data.SqlDbType.VarChar, 10))
        cmdTest.SelectCommand.Parameters("@custcode_old"). Value = DDL_Import.SelectedValue

        cmdTest.SelectCommand.Parameters.Add(New SqlParameter("@custcode_new", Data.SqlDbType.VarChar, 10))
        cmdTest.SelectCommand.Parameters("@custcode_new"). Value = DDL_CurrentCustomer.SelectedValue

        MyConnection.Open()
        cmdTest.SelectCommand.ExecuteNonQuery()
        MyConnection.Close()

gbianchi · October 19th, 2007, 07:32 AM

hi there.. since you are accessing sql remoting, did you configure it to allow remote connections?? by default sql 2005 exp. has that option turned off....

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Andrew.Berry · October 19th, 2007, 08:13 AM

How do I check this? In DB properties, Restrict Access = MULTI_USER, and I connect fine with SQL Express Management studio, and my Datasources connect without problems.