Hi, i have taken over a project which helps our sales people create quotes for suppliers. they have asked for us to add a duplicate quote button my issue is as i am new to asp and have only used php before i dont have a clue how to do this. 

the main reason im finding this hard is that quotes are stored in 2 tables>

Quotes

1. QuoteRef    (unique id)
   
2. UserID
3. DateCreated
4. ShortDesc
5. Archived
6. Customer
7. CustRef
8. DelCost
9. DelSell
10. ArchiveStatus
11. Contact
12. CustomerQuoteRef

QuoteData

1. ItemRef (unique id)
2. QuoteRef
3. ManfPartNo
4. SuppID
5. Quantity
6. SellPrice
7. MiscItem
8. Position

the quote data holds the items that are in the quote and the quotes table holds the quote header so to speak.

in the quotes table i need the customerquoteref to take the next available number for that customer (see code below already have this bit)

obviously the quote ref in the Quotes table will be the next available number and i need this to be copied to the quoteref in the QuoteData table.

how would i go about doing this in ASP??see below what creates the quote dont know if this helps also this will be called from a button on another page not sure what this should contain

public int createQuote(int UserID, string[] ManfNos, string[] Suppliers, int[] Quantities, double[] SellPrices, int QuoteRef, string ShortDesc, string CustRef, bool Customer, string Contact)
        {
            int intQuoteRef = QuoteRef;
            FMSConnection.Open();
            //create quote entry if new quote or delete all quotedata if not
            if (QuoteRef == 0)
            {
                //find last customerquoteref for this pricecode
                int CustQuoteRef = 1;
                String sqlCQR = "SELECT TOP 1 CustomerQuoteRef FROM Quotes " +
                    "LEFT JOIN Users ON Quotes.UserID = Users.ID " +
                    "WHERE Users.PriceCode = (SELECT PriceCode FROM Users WHERE ID = " + UserID + ") ORDER BY Quotes.CustomerQuoteRef DESC";
                SqlCommand myCommandCQR = new SqlCommand(sqlCQR, FMSConnection);
                if (myCommandCQR.ExecuteScalar() != DBNull.Value && myCommandCQR.ExecuteScalar() != null)
                {
                    CustQuoteRef = Convert.ToInt32(myCommandCQR.ExecuteScalar()) + 1;
                }

                //add to Quotes
                String sqlInsert = "INSERT INTO Quotes (UserID, DateCreated, DelCost, DelSell, CustomerQuoteRef) " +
                        "VALUES (" + UserID + ", GETDATE(), 5.95, 10, " + CustQuoteRef + ")";
                SqlCommand myCommand1 = new SqlCommand(sqlInsert, FMSConnection);
                myCommand1.ExecuteNonQuery();

                //find new QuoteRef
                SqlCommand myCommand2 = new SqlCommand("SELECT TOP 1 QuoteRef FROM Quotes WHERE UserID = " + UserID + " ORDER BY DateCreated DESC", FMSConnection);
                intQuoteRef = Convert.ToInt32(myCommand2.ExecuteScalar());
            }
            else
            {
                //clear all quotedata
                SqlCommand myCommand3 = new SqlCommand("DELETE FROM QuoteData WHERE QuoteRef = " + QuoteRef + "AND MiscItem IS NULL", FMSConnection);
                myCommand3.ExecuteNonQuery();
            }

            //add shortdesc where none present (and custref, customer/prospect)
            string SQL1 = "";
            string SQL2 = "";
            string SQL3 = "";
            if (ShortDesc != "")
            {
                SQL1 = ", ShortDesc = @ShortDesc";
            }
            if (CustRef != "")
            {
                SQL2 = ", CustRef = @CustRef";
            }
            if (Contact != "")
            {
                SQL3 = ", Contact = @Contact";
            }

            String sqlUpdate = "UPDATE Quotes SET Customer = @Customer" + SQL1 + SQL2 + SQL3 + " WHERE QuoteRef = @QuoteRef";
            SqlCommand myCommand7 = new SqlCommand(sqlUpdate, FMSConnection);
            myCommand7.Parameters.Add(new SqlParameter("@ShortDesc", SqlDbType.VarChar, 256));
            myCommand7.Parameters.Add(new SqlParameter("@CustRef", SqlDbType.VarChar, 50));
            myCommand7.Parameters.Add(new SqlParameter("@Customer", SqlDbType.Bit, 1));
            myCommand7.Parameters.Add(new SqlParameter("@QuoteRef", SqlDbType.Int, 4));
            myCommand7.Parameters.Add(new SqlParameter("@Contact", SqlDbType.VarChar, 50));
            Hashtable vals = new Hashtable();
            vals.Add("ShortDesc", ShortDesc);
            vals.Add("CustRef", CustRef);
            vals.Add("Customer", Customer);
            vals.Add("QuoteRef", intQuoteRef);
            vals.Add("Contact", Contact);
            foreach (string key in vals.Keys)
            {
                if (vals[key] == null) { myCommand7.Parameters["@" + key].Value = DBNull.Value; }
                else { myCommand7.Parameters["@" + key].Value = vals[key]; }
            }
            myCommand7.ExecuteNonQuery();


            int SuppID = 0;
            //create new quotedata for quoteref            
            for (int i = 0; i < ManfNos.Length; i++)
            {
                //fetch suppID from suppliers
                SqlCommand myCommand5 = new SqlCommand("SELECT supp_id FROM suppliers WHERE supp_name = '" + Suppliers[i] + "'", FMSConnection);
                SuppID = Convert.ToInt32(myCommand5.ExecuteScalar());

                //insert data into quotedata
                String sqlInsert = "INSERT INTO QuoteData (QuoteRef, ManfPartNo, SuppID, Quantity, SellPrice) " +
                    "VALUES (" + intQuoteRef + ", '" + ManfNos[i] + "', " + SuppID + ", " + Quantities[i] + ", " + SellPrices[i] + ")";
                SqlCommand myCommand6 = new SqlCommand(sqlInsert, FMSConnection);
                myCommand6.ExecuteNonQuery();
            }
            FMSConnection.Close();
            return intQuoteRef;

        }

Code in your post is not well formatted. Use code button({}) to post code 

I am not sure if this is the proper syntax but this should accomplish what you need without to much .net code.  Use sql statement to get the job done

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE DuplicateQuote 
	@QuoteRef int
AS
BEGIN
	Declare @newQuoteID int
	
	INSERT into Quotes (QuoteRef, UserID, DateCreated, ShortDesc, Archived, Customer, CustRef, DelCost, DelSel, ArchiveStatus, Contact, CustomerQuoteRef)
	SELECT QuoteRef, UserID, DateCreated, ShortDesc, Archived, Customer, CustRef, DelCost, DelSel, ArchiveStatus, Contact, CustomerQuoteRef 
	FROM Quotes 
	WHERE QuoteRef = @QuoteRef
	
	SET @newQuoteID = SELECT @@IDENTITY
	
	INSERT INTO QuoteData (QuoteRef,ManfPartNo,SuppID,Quantity,SellPrice,MiscItem,Position)
	SELECT @newQuoteID, ManfPartNo, SuppID, Quantity, SellPrice, MiscItem, Position
	FROM QuoteData
	WHERE QuoteRef = @QuoteRef
END
GO

Hope this works for you. 

this is great but how do i put this into asp? im not good with asp im more a php coder?

i have created the button>    <asp:LinkButton ID="lnkDuplicate" runat="server" text="Duplicate&nbsp;Quote" OnCommand="Duplicate" CausesValidation="False" />

then at the top have the following:

        public void Duplicate(object sender, EventArgs e)
    {
 
    }

does anyone know how i do this? or any tutorials that will help me?

in php is so much simpler if a user clicks a button its easy to execute a command why is it so complicated in ASPx

Please could someone explain how i can do this, when i click the button i want it to duplicate the quote that the user is on.

marksie1988:

but i need to know what to put in here to execute the sql to duplicate the quote shown on the page :S

 Just call that method again.. Like this..

public void Duplicate(object sender, EventArgs e)    

{  

int i = createQuote(int UserID, string[] ManfNos, string[] Suppliers, int[] Quantities, double[] SellPrices, int QuoteRef, string ShortDesc, string CustRef, bool Customer, string Contact) ;

  

}