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?

 

[johmolan]

I have put the code in a button_click
when I press the button nothing happends, no exeption or nothing.
But it is clearly not working since it still is the same amount of customers in the table

 

[InertiaM]

Look at your code, and think about what you are trying to do.

1. A query returns records. A non-query executes a statement. So why are you doing:-

strSQL = "SELECT * FROM Kunde WHERE (KundeID = 1))"
cmSQL = New SqlClient.SqlCommand(strSQL, cnSQL)
cmSQL.ExecuteNonQuery()

2. Where are you picking up the value for @Kundenavn (and others)? They dont come out of thin air you have to allocate them yourself.

 

[johmolan]

Ok, I have misunderstood....

I thought I had to run cmSQL.ExecuteNonQuery() for the sqlcommand to run.

But I thought I put the Selected data into the dataadapter

But I guess you see what I want to do.

Select data form a table,
increase the ID to max+1 and then insert the data back again.

What else am I missing?

 

[Element6]

Well the problem is I don't think your using the SQLAdapter correctly.

cmSQL.ExecuteNonQuery() only returns the number of rows a transaction returns not the transaction result; so it returned something but there was nothing to do with the return.

You may not have noticed the transaction programmatically; but I think the SQL should have executed; minus that SQL syntax I still think is errored.

I think you want to do this :
.
.
.
Dim sqlDataset As SqlClient.SqlDataReader
.
.
.
strSQL = "SELECT * FROM Kunde WHERE (KundeID = 1)"
cmSQL.CommandText = strSQL
sqlDataset = cmSQL.ExecuteReader(CommandBehavior.SequentialAccess)

Not sure though; because I haven't executed that statement. Just a shot in the dark. The way you were doing it I think the executed statement should have actually done something on the SQL end without a return to work with other then the affected row count and you didn't do anything with that.

 

[Element6]

Well what should have happened the way I read it is.

Click Button
Connection made to SQL
PASS 3 SQL Statements
Close Connection

So if there is no updates in the appropriate table; I don't know what to tell you except that the SQL statement had errors and that is obvious in the copy and paste CODE block you put in here; so a zero record return would indicate that SQL DID NOT execute.

Try putting a msgbox around the ExecuteNonQuery with .ToString and see what the row count return is. If it's zero; it means that the SQL failed from what you intended it to do.

 

[johmolan]

I put the msg box around as you said, and the strings returned are all -1

 

[Element6]

That tells me that the ExecuteNonQuery actually worked; and the SQL returned a -1 rows affected.

Hmm. If I remember correctly :

0 implies that the SQL statement had run and there were no rows affected.
and a -1 return implies that the SQL failed to execute.

Check your SQL syntax; I saw the extra ")" at the end of two of the SQL statements. Remove the ")"'s then reexecute your code.

 

[johmolan]

I now removed those syntax errors but I still get the same resault.

Is there a way to determin if the connection really is open??
and f.ex display the resault of "Select Kundenavn from Kunde WHERE KundeID = 1" to be sure that the connection really is working?

 

[Element6]

SqlConnection.State Property

Of course; almost everything that has synchronous or ascyncronous connections carries some sort of state.

In this case the property is "state"

 

[johmolan]

Ok, now we have determined that the connection is open.
next step would be to check that it is reading correctly.

If I use the selectcommand like this: "select Kundenavn from kunde where kundeID=1"
thet it should return only 1 kundenavn. it it possible to display this in a msgBox??

 

[Element6]

Too low level for this thread; so after this unless it's .NET related I don't want to keep answering.

As a good programming debug practice you should ALWAYS check against the source. In this case, check that the SQL you are passing is working at the server level. Actually go to the server, open SQL and try to run the SQL Syntax; I think you'll find out how to debug your SQL syntax better there anyway; usually they have syntax checkers of some sort; and online help just like Microsoft does with intellisense and error warnings.

Since you know that .NET opened a connection; and passed a transaction statement and returned some sort of output; you know that .NET is working.

As for displaying the results in the MsgBox; use .ExecuteScalar() this returns the first column from the first row at least you can see if it returned anything on the fly with .ToString.

 

[johmolan]

It looks like I have really looked at this the wrong way.
And I am really sorry for the time you have spent trying to help me because I have done so.
I allready have a tableadapter in my dataset linked to my database.

I read now that I can proberbly use something like:

Dim KundeTableAdapter As New Kalkyl1DataSetTableAdapters.KundeTableAdapter
KundeTableadapter.Insert(4, "KundeID")

But is it possible to add multiple records into multiple rows in the same statement here as well?

 

[johmolan]

Ok I almost solved the first step with the code

Dim KundeTableAdapter As New Kalkyle1DataSetTableAdapters.KundeTableAdapter
KundeTableAdapter.Insert((select max(KundeID) from Kunde), "", "", "")

The only problem left here is that I can not use the selectsentence this way to get the max value in the KundeID column and then add 1.

 

[Element6]

Oh no trouble for the time; just that the thread might go off topic.

As for me I typically never use control sub functions; I do mostly enterprise level development so I see no need to use them. Good to know they can do that but not useful in large systems.

 

[johmolan]

Ok, then I figured that one out as well.

The code I used here was:

conn.Open()
myScalarQuery = " Select Max(KundeID) As MaxID From Kunde"
Dim myCommand As New SqlCommand(myScalarQuery, conn)
'myCommand.Connection.Open()
MaxID = myCommand.ExecuteScalar()
conn.Close()
MsgBox(MaxID)
MaxKundeID = MaxID + 1
MsgBox(MaxKundeID)


Dim KundeTableAdapter As New Kalkyle1DataSetTableAdapters.KundeTableAdapter
KundeTableAdapter.Insert(MaxKundeID, "", "", "")

This way I got a new row with the right KundeID and empty customer fields.