Databases

Welcome to Dream.In.Code
	Getting Help is Easy! Join 132,396 Programmers for FREE! Get instant access to thousands of experts, tutorials, code snippets, and more! There are 1,236 people online right now. Registration is fast and FREE... Join Now! Chat LIVE With a Expert

Copiying datat from another table into another

Copiying datat from another table into another, data copy

Options

mosanta

21 Aug, 2008 - 07:35 PM

Post #1

New D.I.C Head

Joined: 21 Aug, 2008
Posts: 1

Is this stored procedure to copy data from one table to the other i have the code here
its giving me realtion ship problems

sql


ALTER PROCEDURE dbo.CopyRecords
	
	(
	
	@UserName nvarchar(50),
	@OrderDate datetime,
	@OrderTime datetime,
	@AuthorizationCode text
	)
	
AS
	insert into OrderedItem  (StoreID,MenuID,ProductID,ItemName,Quantity,Price,Username,OrderDate,OrderTime,A
uthorizationCode) 
	Select  StoreID,MenuID,ProductID,ItemName,Quantity,Price,Username,@OrderDate,@OrderTime,
@AuthorizationCode from Item where Item.Username = @Username 
	RETURN

above i s the code for the stored procedure

below is the code on in the subprocedure


 Try
            
            mclass.connect()
            Dim sql As String = "Select * from Item where Username = '" & Session("Username") & "'"
            mclass.cmd.CommandText = sql
            mclass.cmd.CommandType = CommandType.Text
            mclass.dtr = mclass.cmd.ExecuteReader

            Dim sID, prodID, mID, Quant As Integer
            Dim money As Decimal
            Dim itemNam, Usernam As String

            If mclass.dtr.Read Then
                sID = mclass.dtr("StoreID")
                mID = mclass.dtr("MenuID")
                prodID = mclass.dtr("ProductID")
                itemNam = mclass.dtr("ItemName")
                Quant = mclass.dtr("Quantity")
                money = mclass.dtr("Price")
                Usernam = mclass.dtr("Username")
                mclass.dtr.Close()
            End If
            '@ItemName nchar(20),
            '	@Quantity int,
            '	@Price money,
            '	@UserName nvarchar(50),
            '	@OrderDate datetime,
            '	@OrderTime datetime,
            '	@AuthorizationCode text

            ' Dim query As String = "Insert into OrderedItem  (StoreID,MenuID,ProductID,ItemName,Quantity,Price,Username,OrderDate,OrderTime,A
uthorizationCode) Select StoreID,MenuID,ProductID,ItemName,Quantity," & total & ",'" & Session("Username") & "', '" & odate & "','" & Otime & "','" & autoCode & "' from Item where Item.Username = '" & Session("Username") & "'"
            mclass.cmd.Connection = mclass.con
            mclass.cmd.CommandText = "CopyRecords"
            mclass.cmd.CommandType = CommandType.StoredProcedure

            mclass.cmd.Parameters.AddWithValue("StoreID", SqlDbType.Int).Value = sID
            mclass.cmd.Parameters.AddWithValue("MenuID", SqlDbType.Int).Value = mID
            mclass.cmd.Parameters.AddWithValue("ProductID", SqlDbType.Int).Value = prodID
            mclass.cmd.Parameters.AddWithValue("ItemName", SqlDbType.NChar).Value = itemNam
            mclass.cmd.Parameters.AddWithValue("Quantity", SqlDbType.Int).Value = Quant
            mclass.cmd.Parameters.AddWithValue("", SqlDbType.Money).Value = money
            mclass.cmd.Parameters.AddWithValue("Username", SqlDbType.NVarChar).Value = Usernam
         
            mclass.cmd.ExecuteNonQuery()
            mclass.con.Close()
            MsgBox("Table updated!")
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

to any geek that knows

This Post Was Helpful! ^New!


Register for Free to Make These Ads Go Away!

PsychoCoder

21 Aug, 2008 - 08:35 PM

Post #2

using DIC.Core;

Joined: 26 Jul, 2007
Posts: 8,923

Windows

Thanked 118 times

Dream Kudos: 8475

Expert In: VB, VB.Net, C#, SQL, ASP, ASP.Net, Web Development, HTML, CSS, Win32 API, Javascript, mySQL, J#, Boo.Net

My Contributions

Well your stored procedure I dont see any issues with, well without seeing the code that created each tables. There may or maynot be some relationship issues that need to be worked through. I reformatted your stored procedure for making it easier to read:

sql


ALTER PROCEDURE dbo.CopyRecords
						@UserName VARCHAR(50), 
						@OrderDate DATETIME, 
						@OrderTime DATETIME, 
						@AuthorizationCode VARCHAR(255)
AS
	INSERT INTO 
		OrderedItem   (StoreID,MenuID,ProductID,ItemName,Quantity,Price,Username,OrderDate,OrderTime,A
uthorizationCode) 
	SELECT
		StoreID,
		MenuID,
		ProductID,
		ItemName,
		Quantity,
		Price,
		Username,
		@OrderDate,
		@OrderTime,
		@AuthorizationCode 
	FROM
		Item 
	WHERE
		Item.Username = @Username

As far as part number two of your post. You had your SqlParameters all wrong. You were trying to combine AddWithValue() with Add(String, SqlDbType). and that just wont work.

I made some changes to your code using AddWithValue() the way it's meant to be used to see if this will clear up some of your trouble. See if this solves anything for you


Try
    
    mclass.connect()
    Dim sql As String = "Select * from Item where Username = '" & Session("Username") & "'"
    mclass.cmd.CommandText = sql
    mclass.cmd.CommandType = CommandType.Text
    mclass.dtr = mclass.cmd.ExecuteReader

    Dim sID, prodID, mID, Quant As Integer
    Dim money As Decimal
    Dim itemNam, Usernam As String

    If mclass.dtr.Read Then
        sID = mclass.dtr("StoreID")
        mID = mclass.dtr("MenuID")
        prodID = mclass.dtr("ProductID")
        itemNam = mclass.dtr("ItemName")
        Quant = mclass.dtr("Quantity")
        money = mclass.dtr("Price")
        Usernam = mclass.dtr("Username")
        mclass.dtr.Close()
    End If

    
    mclass.cmd.Connection = mclass.con
    mclass.cmd.CommandText = "CopyRecords"
    mclass.cmd.CommandType = CommandType.StoredProcedure

    mclass.cmd.Parameters.AddWithValue("StoreID", sID)
    mclass.cmd.Parameters.AddWithValue("MenuID", mID)
    mclass.cmd.Parameters.AddWithValue("ProductID", prodID)
    mclass.cmd.Parameters.AddWithValue("ItemName", itemNam)
    mclass.cmd.Parameters.AddWithValue("Quantity", Quant)
    mclass.cmd.Parameters.AddWithValue("Username", Usernam)
 
    mclass.cmd.ExecuteNonQuery()
    mclass.con.Close()
    MsgBox("Table updated!")
Catch ex As Exception
    MsgBox(ex.ToString)
End Try