1 Replies - 1791 Views - Last Post: 21 August 2008 - 09:35 PM Rate Topic: -----

#1 mosanta   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 21-August 08

Copiying datat from another table into another

Posted 21 August 2008 - 08:35 PM

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

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,AuthorizationCode) 
	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,AuthorizationCode) 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

Is This A Good Question/Topic? 0
  • +

Replies To: Copiying datat from another table into another

#2 PsychoCoder   User is offline

  • Google.Sucks.Init(true);
  • member icon

Reputation: 1659
  • View blog
  • Posts: 19,853
  • Joined: 26-July 07

Re: Copiying datat from another table into another

Posted 21 August 2008 - 09:35 PM

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:

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,AuthorizationCode) 
	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


Was This Post Helpful? 0
  • +
  • -

Page 1 of 1