[RESOLVED] Copy records

[recpilu]

Hy again ,

I have another quastion :

How do i copy a specific record from one table to another?

I have the table : Products with ID , Name , Price , Code fields . And the table : Temporary with ID , Name , Price , Code fields.

When i search a product after it`s code and the product is in database , the program will copy the ID , Name , Price and Code of product in the table Temporary.

[sapator]

If i read correctly you need to create a temp table.
...Err that's my answer

[recpilu]

It`s called Temporary , it`s not a temp table.It could be called New or Purchased.

Can anybody help me ?

[jmcilhinney]

Are you trying to do this using SQL in the database or in an application? If it's the former, what database? If it's the latter, what language?

[recpilu]

I`m using Visual Basic 2005 (i`m student , and this is what i get for free )
And i`m using SQL in a database.

The Ideea : The operator puts a code in a text box , clicks on the add button , and the product copies in the table Temporary

[jmcilhinney]

That doesn't really answer my question.

[MarMan]

What jmc wanted you to say was that you are doing your SQL in an application. He is an advanced programmer and sometimes has trouble understanding learners.

I believe you want a SQL statement like this:

INSERT INTO DestinationTableName (DestinationFieldName1, DestinationFieldName2, ect)
SELECT SourceFieldName1, SourceFieldName2, ect
FROM SourceTableName
WHERE  Any valid where clause

[jmcilhinney]

What jmc wanted you to say was that you are doing your SQL in an application. He is an advanced programmer and sometimes has trouble understanding learners.

No, that's not what I wanted. I wanted an answer to my question. What you have posted there is specifically the first choice I offered, not what you say it is. It's SQL in the database. I specifically asked what database in that case. The language is only important if it's not being done in the database, i.e. data is specifically retrieved into the application, processed and then the result saved back to the database. In what you have posted above there will be no data processed in the application. It's all done in the database. I understand fine. It's others who don't understand when they don't read the actual words of my posts.

[MarMan]

The operator puts a code in a text box , clicks on the add button , and the product copies in the table Temporary

If that isn't enough information to indicate that it is a VB application then, well nuff said. It is quite obvious to me.

That SQL statement is very easily incorporated into an application, I know, I have done it, many times. And I believe recpilu has enough skill to use it as is, if not, then he'll let us know.

[recpilu]

INSERT INTO DestinationTableName (DestinationFieldName1, DestinationFieldName2, ect)
SELECT SourceFieldName1, SourceFieldName2, ect
FROM SourceTableName
WHERE Any valid where clause

At the last line

WHERE Any valid where clause

Here should be the textbox from where to take the code of product and find a match in my database to copy it in the Destination Table , no ?

Should it look like this :

WHERE textbox1.value ?

[MarMan]

Correct. If you have any problems post your code and we'll help.

[jmcilhinney]

If that isn't enough information to indicate that it is a VB application then, well nuff said. It is quite obvious to me.

That SQL statement is very easily incorporated into an application, I know, I have done it, many times. And I believe recpilu has enough skill to use it as is, if not, then he'll let us know.

So, VB is the only programming language that uses text boxes and buttons? There is nothing in the statement that you quoted that suggests VB over any other language. What say that it is VB is this:

I`m using Visual Basic 2005

That is made irrelevant by this though:

And i`m using SQL in a database.

I specifically asked:

Are you trying to do this using SQL in the database ...? ... what database?

You even posted SQL code that has no connection whatsoever to any application programming language, which supports my point. How do you know that the SQL you posted will be valid though, if you don't know what database it's for? You made assumptions because relevant information was omitted. Your assumption may be correct but, then again, it may not. If the information that was requested had been provided, no assumption would have been necessary. That is my point. I know that beginners don't know everything. That's why I ask questions. If they're answered, I can provide informed advice.

[GaryMazzone]

SQL IS A LANGUAGE NOT A DATABASE type. For Example there is SQL Server from MS, MySQL for the open source crowd, mSQL from Hughes Technologies, SQLite

and others

[MarMan]

Sorry, I didn't know you were stuck on tunnel vision. Just because I was able to determine what he wanted and you weren't is no reason to get flustered. Relax, we are supposed to be helping people, not bickering amongst ourselves. What will that really accomplish?

[recpilu]

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
INSERT INTO Temporary (Name, Price, Quantity)
SELECT Name, Price, Quantity
FROM Products
WHERE textbox1.value
End Sub

And it gives me 19 errors :

Error 1 'ProductsBindingSource' is not a member of 'Imobiliare.Temporary'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 2 'ProductsTableAdapter' is not a member of 'Imobiliare.Temporary'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 3 'ProductsDataSet' is not a member of 'Imobiliare.Temporary'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 4 'ProductsTableAdapter' is not a member of 'Imobiliare.Temporary'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 5 'ProductsDataSet' is not a member of 'Imobiliare.Temporary'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 6 Name 'INSERT' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 7 Method arguments must be enclosed in parentheses. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 8 Name 'INTO' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 9 Comma, ')', or a valid expression continuation expected. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 10 Name 'Pret' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 11 Name 'Cantitate' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 12 End of statement expected. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 13 'Select Case' must end with a matching 'End Select'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 14 Name 'Name' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 15 End of statement expected. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 16 Name 'FROM' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 17 Statements and labels are not valid between 'Select Case' and first 'Case'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 18 Name 'WHERE' is not declared. H:\Projects\Imobiliar\Imobiliar\Temporary.vb
Error 19 Statements and labels are not valid between 'Select Case' and first 'Case'. H:\Projects\Imobiliar\Imobiliar\Temporary.vb

[MarMan]

There is a link in jmc's signature that explains a bit on how to use the SQL in code.

Check that out and try it, then post your code if you are still having problems.

[akhileshbc]

@MarMan: I have a small doubt while reading the posts. In the WHERE part, don't we need to include the field (or attribute) of which we are describing the condition ?

For example:

SELECT name, address FROM employees WHERE empid=12345

Sorry for my stupid question. But I'm a newbie too..

[MarMan]

There is no such thing as a stupid question. If anyone ever tells you that it is their either ego getting in the way or someone told them that once and they wish to lash out at anyone. If someone doesn't know the answer to a question, then it is a valid question, ALWAYS.

And you are correct akhil, that SQL will fail, it needs to be in quotes, needs ADO. I saw that recpilu really didn't know how to use SQL as I though they did, so I just pointed them to a link.

[recpilu]

I used the code :

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim sql As String = "INSERT INTO Casa (ID , Nume , Pret , Cantitate , Cod) SELECT ID, Nume , Pret , Cantitate , Cod From Produse where Cod = TextBox1.value"
Dim myCommand As New SqlCommand(sql)


End Sub

It gives me no error , but it dosen`t work.
I read that page and another 10 forums and there is the same code without the "Dim sql As string" part and everybody says it works
Should i write something else or what?

Sorry if i annoy you with my dumb quastions but i realy want to do that in my program

[GaryMazzone]

Ok you created an SQL string command (your Dim sql)
You created a New SQLCommand.
You have not assigned the Command a Connection. You have not executed the Command.

You would you expect anything to happen?

[MarMan]

Your questions don't annoy me. But I am concerned that you are not grasping the method to use ADO. Let's make believe you have a value in the textbox of 1001 and it is valid. with this statement:

Dim sql As String = "INSERT INTO Casa (ID , Nume , Pret , Cantitate , Cod) SELECT ID, Nume , Pret , Cantitate , Cod From Produse where Cod = TextBox1.value"

You get
INSERT INTO Casa (ID , Nume , Pret , Cantitate , Cod) SELECT ID, Nume , Pret , Cantitate , Cod From Produse where Cod = TextBox1.value

But I don't think that is what you want, I think you want something like this:
INSERT INTO Casa (ID , Nume , Pret , Cantitate , Cod) SELECT ID, Nume , Pret , Cantitate , Cod From Produse where Cod = '1001'

Which is basic string building. If you do not get this down you will continue to have problems. What you want is something like this:

Dim sql As String = "INSERT INTO Casa (ID , Nume , Pret , Cantitate , Cod) SELECT ID, Nume , Pret , Cantitate , Cod From Produse where Cod = '" & TextBox1.value & "'"

But that is not the best way to go either. If you read the link that I posted from jmc's signature, it explains parameters. You really need to read the whole thing because the method I just showed you can cause problems. And you are missing a command that that FAQ provides, that's why it doesn't do anything.

I'm happy to help, but you also need to help. Read the WHOLE FAQ, because I can tell by your code that you did not.

[recpilu]

Ok MarMan , thank you for being patient.
I will read the FAQ again , I will read about ADO.NET because i don`t know what it is.

If i don`t succed tonight i will post tomorow.
If i succed i will let you know.

[akhileshbc]

There is no such thing as a stupid question. If anyone ever tells you that it is their either ego getting in the way or someone told them that once and they wish to lash out at anyone. If someone doesn't know the answer to a question, then it is a valid question, ALWAYS.

And you are correct akhil, that SQL will fail, it needs to be in quotes, needs ADO. I saw that recpilu really didn't know how to use SQL as I though they did, so I just pointed them to a link.

Thanks for clearing my doubt

And sorry for hijacking the thread...

[recpilu]

I didn`t got it resolved , i searched on google for my problem but didn`t got any result .
For 4 hours i`m searching 1 code that works and nothing , copy record from one table into another.
I run out of options, i really don`t know how to do it.In that SQL it tells us how to replace values , or what? I don`t get it.

[sapator]

Ok.
Forget about Visual studio for a moment.
Can you do what you want with an sql query?
If you can then you can simply make a stored procedure inside your sql server and use it in vb with SqlCommand.CommandType = CommandType.StoredProcedure.

[MarMan]

sapator has another good option. Post your code so we can guide you.

[recpilu]

Hello , i searched on msdn , google and asked some quastions and here is how i`ve come so far.

Dim con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=H:\Projects\WindowsApplication1\WindowsApplication1\Database1.m df;Integrated Security=True;Connect Timeout=30;User Instance=True")
con.Open()
Dim sAppendSql As String
sAppendSql = "INSERT INTO Casa (ID , Nume , Pret , Cod) SELECT ID, Nume , Pret , Cod FROM Produse WHERE Cod = '" & TextBox1.Text & "'"
Dim myCommand As New SqlCommand(sAppendSql)
myCommand.Connection = con
myCommand.ExecuteNonQuery()
con.Close()



The code dosen`t give any error but it is not doing anything , it dosen`t show me the record that i wanted to copy.
Or is not the correct code ?
ID = ID , Nume = Name , Pret = Price , Cod = Code <- the translation from Romanian to English

[akhileshbc]

Based on the tutorial: http://www.w3schools.com/sql/sql_insert.asp

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

I'll test it now.

[recpilu]

It`s still not working
I have this code :

Using con As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=H:\Projects\WindowsApplication1\WindowsApplication1\Database1.m df;Integrated Security=True;Connect Timeout=30;User Instance=True")
Try
con.Open()
Try
Using myCommand As New SqlCommand("INSERT INTO Casa (ID , Nume , Pret , Cod) SELECT ID, Nume , Pret , Cod FROM Produse WHERE Cod = @Cod")
myCommand.Connection = con
myCommand.Parameters.AddWithValue("@Cod", TextBox1.Text)
myCommand.ExecuteNonQuery()
End Using
Catch ex As Exception
MessageBox.Show("The insert gave an exception " & ex.Message)
End Try
Catch ex As Exception
MessageBox.Show("The Connection gave an exception " & ex.Message)
End Try
End Using

It works only with the records that i added with the "insert into .. values" code. When i add a new record using the form it saves it in the database but the COPY thing dosen`t work
And when i try to delete the records from the table , i delete them but when i restart the application they are still there. Any solutions ?

[MarMan]

Your code looks much better now! Nice work.

It works only with the records that i added with the "insert into .. values" code. When i add a new record using the form it saves it in the database but the COPY thing dosen`t work
And when i try to delete the records from the table , i delete them but when i restart the application they are still there. Any solutions ?

Of course it only works with the records you add with the "insert into", that is the only code you have that will add records. Unless you are discussing something other than what you posted.

I don't see any DELETE statement in your above code. So of course nothing can be deleted. Why would you want a copy method to delete? That is a move method.

You need to work on one thing at a time AND post the code you are working on, AND tell us what you want to do AND tell us what it does.

So pick ONE thing and we can help you with that. When that works we can go to the next one.

[recpilu]

I used what John McIlhinney said in his blog about parameters and it worked fine.
Now i need to refresh the form or data grid in order to view what i added in my table without restarting the application.



I tried Form2.Refresh()
Me.Refresh()
ProductsDataGridView.Refresh()
But none of them worked.

Any solution ?

[MarMan]

Is your grid bound or unbound?

[recpilu]

Yes it is bounded to the table Products

[MarMan]

Try setting the datasource to nothing then back to whatever you have it set to. You really should be supplying code snippets.