Copy or Save As

[johmolan]

Hi,

I have a relation database, it looks some thing like this.

Customer1 --> Order1 --> Process1=value1
--> Process2=value2
--> Process3=value3

The qustion is, is it possible to copy theese values and into

Customer2 --> Order1 --> Process1=value1
--> Process2=value2
--> Process3=value3
If I want to make an order based on the values from customer 1?

is it possiple to copy all values regardign customer 1 and past it into customer 2 or make some kind of save as buttomn to make i work?

 

[InertiaM]

Hoping that I have understood your question correctly, the easiest way to do this would be using SQL queries. For example, to copy a customer would be:-

INSERT INTO
  tblCustomers
  (
    ID,
    CustomerName,
    CustomerAddress
  )
SELECT
  @NewCustomerID AS ID,
  tblCustomers.CustomerName,
  tblCustomers.CustomerAddress
FROM
  tblCustomers
WHERE
  tblCustomers.ID = @OldCustomerID

 

[johmolan]

I have tried to save using the code:

Public Sub LagreSom(ByVal connectionString As String)
connectionString = Form1.DS2
Dim commandText As String = "SELECT (KundeID, Kundenavn,Kontaktperson,Kunde_ABC)FROM dbo.Kunde Where KundeID = 1;"
Dim commandText2 As String = "INSERT INTO dbo.Kunde( KundeID, Kundenavn, Kontaktperson, Kunde_ABC) WHERE KundeID = 2;"
Using connection As New SqlConnection(connectionString)

Using command As New SqlCommand(commandText, connection)
connection.Open()
Using command2 As New SqlCommand(commandText2, connection)
End Using
End Using
End Using
End Sub

But somehow it will not work.
I guess I am missing something but I do not know what..

Anyone???

I call the method using LagreSom(Form1.DS2) which is my sql connection

 

[johmolan]

Maybe there is another way of doing this using the update method I really do not know.

 

[johmolan]

My last trial here was with the code:

Public Sub LagreSom(ByVal connectionString As String)

'Dim commandText As String = "SELECT * FROM tblKunde Where KundeID = 1;"
Dim commandText As String = "SELECT (KundeID, Kundenavn, Kontaktperson, Kunde_ABC) FROM tblKunde Where KundeID = 1;"
Dim commandText2 As String = "Update tblKunde SET (Kundenavn = @Kundenavn, Kontaktperson = @Kontaktperson, Kunde_ABC = @Kunde_ABC, KundeID = @KundeID) WHERE KundeID = 3;"
Using connection As New SqlConnection(connectionString)
Using command As New SqlCommand(commandText, connection)
Using command2 As New SqlCommand(commandText2, connection)
connection.Open()
End Using
End Using
End Using

But I still can't get it to save to the DB

 

[Element6]

Ok I think I know what your asking.

Lets Simplify the Terms because I think your looking at the problem from the wrong perspective.

Correct me where I am wrong.

You have 2 Customers, 1 Order, and 3 Process with Values.

1 of your customers has an Existing Order which has 3 Process (assuming items which have a "Monetary" value).

You want to Copy the Order from Customer 1, or Clone it, and Assign the same Order to Customer 2 so that Customer 1 and Customer 2 have identical orders.

Your method of handling the transaction is through SQL; not programmatic code.

Considering that the above is true.

The process is simple and relatively painless.

Process Approach I
Copy the Customer and everything associated.
Rename the Copy of the Customer to Customer 2

Process Approach II
Copy the Order from Customer I and everything associated to the order
Insert the Copied Order into Customer 2

My Solution
Don't copy the Order
Don't copy the Customer
Don't copy the Processes
make a Table Called CUSTOMER_ORDER_INDEX
make a Table Called ORDER_PROCESS_INDEX

Take the uniqueKey of Customer and Order
Insert into CUSTOMER_ORDER_INDEX

So much easier.

 

[johmolan]

I am not really sure that I understood how to do exactly what you ment here,

but if I change some of the values in the order to customer 2, won't that also affect the order to customer 1?? since as I understand the order is now the same?

My point is that I want to take order nr 1, make some changes and save it as order nr 2 without having to write all the values in the tables once more.

 

[InertiaM]

My last trial here was with the code:

But I still can't get it to save to the DB

I'm not surprised it isnt saving.

1. Where are you actually reading the information from the database using the select query?
2. Where are you paging through the records returned from the select query?
3. Where are you allocating any information to the parameters for the update query?
4. Where are you executing the update query?

 

[Element6]

Ok well, I'm not going to discuss the design flaw (bad habit of mine). Lets consider your approach.

If you want to move data in clones; I suggest that you make a replication or clone method for your interface (generic); if it's SQL the syntax is relatively easy.

SELECT * FROM

WHERE [MEETS SOME CRITERIA]

With that dataset the next step is to replicate and manipulate the data accordingly so just like in Programming world; use some sort of temporary reference to store a copy in SQL you do it with a "Temp Table"

When you have Finished and want to place the data into an actual table use your SQL Insert statement

INSERT INTO "table1" ("column1", "column2", ...)
SELECT "column3", "column4", ...
FROM "table2"

INSERT INTO Store_Information (store_name, Sales, Date)
SELECT store_name, Sales, Date
FROM Sales_Information
WHERE Year(Date) = 1998

or the old fashion

INSERT INTO "table_name" ("column1", "column2", ...)
VALUES ("value1", "value2", ...)

INSERT INTO Store_Information (store_name, Sales, Date)
VALUES ('Los Angeles', 900, 'Jan-10-1999')

Are you having trouble with the SQL syntax? Or how VB.NET actually interfaces SQL or the Database?

 

[johmolan]

The syntax is ok, but it is more like how the vb interfaces with the sql.

 

[Element6]

.NET uses a control or interface to communicate with SQL. Typically, developers use a ADO.NET Dataset, but there are many techniques to doing this.

For the traditional logic, a Programmer adds an ADO.NET Dataset and sets the properties of the Dataset to configure to a SQL database (normally Microsoft .NET dataset to Microsoft SQL Server but it is not limited it can talk with Oracle and other SQL Databases that follow SQL conventions)

The logic is as follows :

Establish a connection a local or remote SQL Database through an SQL connector of some kind.
Open the connection.
Pass a SQL statement to be executed
Handle SQL response appropriately.
When finished
Close Connection and Dispose.

Below is the code provided from an online tutorial. Notice the ordering SQLConnection, then SQLCommand (in dynamic adjustment). In this case there is no Dataset they are using the SQL Adapter - which is runtime without the interface.

The Connection String contains the ODBC logical connect requests.
The SQL string contains what your requesting in SQL the "Select * from" statement.
The Connection is the actual connection through OLAP
and the Adapter is what is filled with the results.

Imports System.Data.SqlClient
Public Class Form1
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim connetionString As String
        Dim connection As SqlConnection
        Dim command As SqlCommand
        Dim adapter As New SqlDataAdapter
        Dim ds As New DataSet
        Dim i As Integer
        Dim sql As String

		connetionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=UserName;Password=Password"
		sql = "Your SQL Statement Here"

        connection = New SqlConnection(connetionString)

        Try
            connection.Open()
            command = New SqlCommand(sql, connection)
            adapter.SelectCommand = command
            adapter.Fill(ds)
            adapter.Dispose()
            command.Dispose()
            connection.Close()

            For i = 0 To ds.Tables(0).Rows.Count - 1
                MsgBox(ds.Tables(0).Rows(i).Item(0) & "  --  " & ds.Tables(0).Rows(i).Item(1))
            Next

        Catch ex As Exception
            MsgBox("Can not open connection ! ")
        End Try
    End Sub
End Class

Hope this helps.

 

[johmolan]

Ok tnx, so when I know select the data from the database with the selectcommand and "place" the data into dataadapter. how kan I Insert this data into the existing table at the right plce? adapter.INSERT(DataSet.table) I guss is a way to do it but I do not know how to set the ID to a spesific ID. If you know what I mean. But again thanks a lott for the help so far.

 

[Element6]

Using SqlDataAdapter To Insert/Update/Delete Rows - ASP.NET Forums

Microsoft generally suggest in the forums you do a pass SQL statement.

Example code from the above link.

Dim dSet As DataSet
Dim dRow As DataRow
Dim dTable As DataTable
[B]Dim sqlCmd As SqlCommand[/B]
Dim sqlConn As SqlConnection
Dim [B]sqlAdapter [/B]As SqlDataAdapter
  
sqlConn = New SqlConnection("..........")
sqlDapter = New SqlDataAdapter
sqlCmd = New SqlCommand("SELECT * FROM MyTable", sqlConn)
.
.
.
[B]sqlCmd = New SqlCommand("INSERT INTO MyTable (Col1, Col2) VALUES (@Val1, @Val2)", sqlConn)[/B]

With sqlCmd
    .Parameters.Add("@Val1", SqlDbType.Int).Value = 1
    .Parameters.Add("@Val2", SqlDbType.VarChar, 50).Value = "peter"
End With

[B]sqlAdapter[/B].InsertCommand = sqlCmd
[B]sqlAdapter[/B].Update(dSet.Tables("MyTable"))

 

[johmolan]

I have tried theese methods for some time now, and read numerous solutions on the net , but somehow I can not get this to work.

I ended up with a code that looks like this:

Dim sqlDa As SqlDataAdapter
Dim cnSQL = New SqlClient.SqlConnection
cnSQL.ConnectionString = Form1.DS2
Dim cmSQL As SqlClient.SqlCommand

Dim strSQL As String
sqlDa = New SqlDataAdapter

'Testing the connection
Try
cnSQL.Open()
MsgBox("Connection is Open! ")
cnSQL.Close()
Catch ex As Exception
MsgBox("Can not open connection! ")
End Try


Try
'Opening connection
cnSQL.Open()

'Reading thefirst row in the table
strSQL = "SELECT * FROM Kunde WHERE (KundeID = 1))"
cmSQL = New SqlClient.SqlCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
sqlDa.SelectCommand = cmSQL

'Inserting the read row into the table with a new KundeID
strSQL = "Insert Kunde Values ((SELECT (MAX(KundeID) + 1) AS KundeID FROM Kunde), @Kundenavn, @Kontaktperson , @Kunde_ABC )"
cmSQL.ExecuteNonQuery()
sqlDa.InsertCommand = cmSQL

'Reading the new updated table
strSQL = "SELECT * FROM Kunde)"
cmSQL = New SqlClient.SqlCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()
sqlDa.SelectCommand = cmSQL


' Close and Clean up objects
cnSQL.Close()



Catch Exp As SqlClient.SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")

Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try

'Updating the DataSet with the new data
sqlDa.Fill(Me.Kalkyle1DataSet.Kunde)

Can anyone see something in my code where the problem is? I know that there is something wrong somewhere but I just can't find it..

 

[Element6]

First thing I noticed; didn't run the code just looking over your message.

The SQL syntax was off 2 of the SQL queries contain an additional ")" at the end. If that's just a typo in the message no biggie. Let me see what your doing, in code.

If not, then your passing bad SQL and receiving an exception for the bad SQL and not handling it.

Are you getting a SQL exception?