Copy data to another table

[drifters086]

Hi guys,im working on this project as shown below.will need some help with the codings. I'll have two .forms now. One is 'Login', the other is 'Main'.

this is my 'Login

[SIZE=2][COLOR=#0000ff]
Imports[/COLOR][/SIZE][SIZE=2] System.Data.SQL
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data
[/SIZE][SIZE=2][COLOR=#0000ff]Partial[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] _Default
[/SIZE][SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Form
[/SIZE][SIZE=2][COLOR=#0000ff]Protected[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Object[/COLOR][/SIZE][SIZE=2], [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
[/SIZE][SIZE=2][COLOR=#008000]'Connection String
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] strConn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"MY CONNECTION STRING;"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlConnection(strConn)
conn.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Sql [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"Select * from Login where username="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & TextBox1.Text & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"and password="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & TextBox2.Text & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"and diploma="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & ComboBox1.Text & [/SIZE][SIZE=2][COLOR=#800000]"'"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] cmd [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Data.SqlClient.SqlCommand(Sql, conn)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] dr [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Data.IDataReader
dr = cmd.ExecuteReader()

[/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2] dr.Read <> 0 [/SIZE][SIZE=2][COLOR=#0000ff]Then
[/COLOR][/SIZE][SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"Logged in successfully!"[/COLOR][/SIZE][SIZE=2], MsgBoxStyle.Information, [/SIZE][SIZE=2][COLOR=#800000]"Login Successful"[/COLOR][/SIZE][SIZE=2])
[/SIZE][SIZE=2][COLOR=#008000]' Show 'Main' form
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Main [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] Main()
Main.ShowDialog()
[/SIZE][SIZE=2][COLOR=#008000]' Hide the Login Form
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Me[/COLOR][/SIZE][SIZE=2].Close()
[/SIZE][SIZE=2][COLOR=#0000ff]Else
[/COLOR][/SIZE][SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"One of the fields is invalid!"[/COLOR][/SIZE][SIZE=2], MsgBoxStyle.Information, [/SIZE][SIZE=2][COLOR=#800000]"Error"[/COLOR][/SIZE][SIZE=2])
conn.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]If
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

This is my 'Main'

Need to do this:

 

[cjard]

We dont usually do homework here, but we will help with specific problems..

Please start by reading the PQ link in my signature.. Hopefully it will help you avoid a potentially huge problem with your code.
Then, read the DW2 link, there are a lot of articles, but they all walk through and build up from simple beginnings. Try simple app walkthrough first, then read on to fetching data, displaying data, displaying related data, sending updates etc..

 

[drifters086]

We dont usually do homework here, but we will help with specific problems..

Please start by reading the PQ link in my signature.. Hopefully it will help you avoid a potentially huge problem with your code.
Then, read the DW2 link, there are a lot of articles, but they all walk through and build up from simple beginnings. Try simple app walkthrough first, then read on to fetching data, displaying data, displaying related data, sending updates etc..

Thanks for the guide. I've followed it and with some help from the books,i come up with this code.

[SIZE=2][COLOR=#0000ff]
Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlException
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlCommand
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlConnection
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlDataReader
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient.SqlDataAdapter
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.SqlClient
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data.DataTableCollection
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Collections
[/SIZE][SIZE=2][COLOR=#0000ff]Imports[/COLOR][/SIZE][SIZE=2] System.Data
 
[/SIZE][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] Main
[/SIZE][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][SIZE=2] Button1_Click([/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][SIZE=2] Button1.Click
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connStr1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"server=abc05;uid=sqladmin;pwd=abc;database=Prod;"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(connStr1)
conn.Open()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] query1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"select from Module WHERE Module Code="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"Name="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(query1, conn)
command.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] reader [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlDataReader
reader = command.ExecuteReader()
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]While[/COLOR][/SIZE][SIZE=2] reader.Read()
Console.WriteLine((reader.GetString(0).ToString & [/SIZE][SIZE=2][COLOR=#800000]","[/COLOR][/SIZE][SIZE=2] & reader.Item([/SIZE][SIZE=2][COLOR=#800000]"module"[/COLOR][/SIZE][SIZE=2])))
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]While
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Finally
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] connStr2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"server=abc05;uid=sqladmin;pwd=abc;database=Testing;"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] conn2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlConnection = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection(connStr2)
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] query2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"inserts into Module2"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"(Module Code, Name) values ('"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"','"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"')"
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] command2 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlCommand = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand(query2, conn)
command2.ExecuteNonQuery()
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] param [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlParameter
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] rowsAffected [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Integer
 
 
 
 
[/COLOR][/SIZE][SIZE=2]param = [/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlClient.SqlParameter([/SIZE][SIZE=2][COLOR=#800000]"module"[/COLOR][/SIZE][SIZE=2], SqlDbType.VarChar)
command.Parameters.Add(param)
[/SIZE][SIZE=2][COLOR=#0000ff]Try
[/COLOR][/SIZE][SIZE=2]rowsAffected = command.ExecuteNonQuery()
Console.WriteLine([/SIZE][SIZE=2][COLOR=#800000]"Rows affected = "[/COLOR][/SIZE][SIZE=2] & rowsAffected)
[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] SqlClient.SqlException
Console.WriteLine(ex.Message)
[/SIZE][SIZE=2][COLOR=#0000ff]Finally
[/COLOR][/SIZE][SIZE=2]conn.Close()
reader.Close()
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Try
 
[/COLOR][/SIZE][SIZE=2][/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Sub
End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class
 
Partial[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE][SIZE=2] _Default
[/SIZE][SIZE=2][COLOR=#0000ff]Inherits[/COLOR][/SIZE][SIZE=2] System.Windows.Forms.Form
[/SIZE][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2] [/SIZE][SIZE=2][COLOR=#0000ff]Class
[/COLOR][/SIZE]

But still i got error.

Any solution to my code?

 

[cjard]

[COLOR=#0000ff]Dim[/COLOR][SIZE=2] query1 [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = [/SIZE][SIZE=2][COLOR=#800000]"select from Module WHERE Module Code="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"Name="[/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#800000]"'"[/COLOR][/SIZE]

If you'd read PQ, you would not have written this query

[COLOR=#0000ff]Public[SIZE=2]Class[/SIZE][/COLOR][SIZE=2] Main[/SIZE]
[SIZE=2][COLOR=#0000ff]... snip ...[/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Class[/COLOR][/SIZE]

If you'd read DW2, at all, you would not have written any of this code

"You can lead a donkey to water, but you cannot make it drink" - Anon

 

[drifters086]

If you'd read PQ, you would not have written this query




If you'd read DW2, at all, you would not have written any of this code

"You can lead a donkey to water, but you cannot make it drink" - Anon

PQ:
I've read it. *Note,im not selecting any data from any textbox.Im selecting from this database to another.

DW2:
I've read some of the topics in DW2 section which i thought is related.But still cant help!?Maybe you can tell me which topic should i read? Or you mean all the topics in DW2 section?

 

[cjard]

PQ:
I've read it. *Note,im not selecting any data from any textbox.Im selecting from this database to another.

Um, we dont usually select from textboxes, but we might use what is written in a textbox to restrict a search. For example, write "smith" in the text box, then fetch all records having a last name starting with "smith". Youre transferring data from one database to another, via the client app?

DW2:
I've read some of the topics in DW2 section which i thought is related.But still cant help!?Maybe you can tell me which topic should i read? Or you mean all the topics in DW2 section?

Though I'm not 100% sure of what youre trying to achieve, I'm assuming that you are at least going to perform the following during the run of your app:

Read (Fetching) data into your app from a database
Write changes back to a database

Sections exist for both these, in DW2

 

[drifters086]

Um, we dont usually select from textboxes, but we might use what is written in a textbox to restrict a search. For example, write "smith" in the text box, then fetch all records having a last name starting with "smith". Youre transferring data from one database to another, via the client app?



Though I'm not 100% sure of what youre trying to achieve, I'm assuming that you are at least going to perform the following during the run of your app:

Read (Fetching) data into your app from a database
Write changes back to a database

Sections exist for both these, in DW2

Sorry,maybe i did not make it clear.Hmm,yup,now i have two database under the same server 'abc05'. The two database is 'Prod' and 'Testing'. Now i wish to copy a table called 'Module' from 'Prod' database to 'Testing' database which consist of a table called 'Module2',but the fields are empty.

Also to say that everytime i enter NEW fields in 'Prod' database table('Module'), with a single click of the button (Load Student),it will copy the fields in the 'Prod' database table('Module') to 'Testing' database table('Module2').

 

[cjard]

Oh, that doesnt even need any client interaction..

SELECT * 
INTO Testing.Module2
FROM Prod.Module

Moving only new records is harder. Either delete the entire table data, or do:

SELECT pm.* 
INTO Testing.Module2
FROM Prod.Module pm LEFT OUTER JOIN Testing.Module2 tm ON [B]<id columns>[/B]
WHERE tm.[B]Any_ID_Column[/B] IS NULL

I dont know the names of your id columns