Copy Table To New Database ?

  • (2 Pages)
  • +
  • 1
  • 2

22 Replies - 11378 Views - Last Post: 02 March 2012 - 06:11 AM Rate Topic: -----

#1 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Copy Table To New Database ?

Posted 26 February 2012 - 01:11 AM

Help me to migration from vb6 to vbnet

dim Con As OleDbConnection
dim newCon As OleDbConnection
CopyTblToNewDb(Con, "Users", newCon)        
      CopyTblToNewDb(Con, "Product", newCon, "ListID")


then my CopyTblToNewDb in vb6 like this
How to change to oldeb
plz help, or any module like this in oldeb
Public Sub CopyTblToNewDb(sourceConn As Connection, tbl As String, destConn As Connection, Optional SaveID As String)
Dim rsS As New Recordset, rsD As New Recordset
Dim ssql As String, i As Long
On Error GoTo ErrPoint
ssql = "select * from " & tbl
rsS.CursorLocation = adUseClient
rsS.Open ssql, sourceConn, adOpenForwardOnly, adLockReadOnly
rsD.CursorLocation = adUseClient
rsD.Open ssql, destConn, adOpenStatic, adLockBatchOptimistic
 Do Until rsS.EOF
If rsD.EOF Then rsD.AddNew
      For i = 1 To rsS.Fields.Count - 1
         If FieldExist(rsD, rsS.Fields(i).Name) >= 0 And Not IsNull(rsS.Fields(i).Value) Then
         rsD(rsS.Fields(i).Name).Value = rsS.Fields(i).Value
         End If
       Next
       If SaveID <> "" Then
       rsD(SaveID ).Value = rsS.Fields(0).Value
       End If
 rsS.MoveNext
 rsD.MoveNext
 Loop
 rsD.UpdateBatch adAffectAllChapters
 rsD.Close
 Set rsD = Nothing
 rsS.Close
 Set rsS = Nothing
 Exit Sub
ErrPoint:
rsD.CancelUpdate
Set rsD = Nothing
Set rsS = Nothing
End Sub


tx

Is This A Good Question/Topic? 0
  • +

Replies To: Copy Table To New Database ?

#2 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 26 February 2012 - 05:52 AM

I guess I first have to ask if you even know how to work with a database in vb.net.
Because I would pursue looking into executing a SELECT INTO statement.

Then is should just be a matter of building your SELECT INTO statement and executing it using your data access objects.

If you aren't familiar with .Net methods then I suggest these two tutorials from our tutorial section.
SQL Basics in VB.Net
or
for Microsoft Access databases
OleDb Basics In VB.Net
Was This Post Helpful? 0
  • +
  • -

#3 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:17 AM

  Dim ssql As String = "Insert into Payment(PaymentNomer,DatePaid,total,) values " & _
            "('" & txtPayNo.Text & "','" & txtdate.Text & "','" & txtTotal.Text &  "')"
            cmd = New OleDbCommand(ssql, mConn)
            cmd.ExecuteNonQuery()


First I want to explain... that I use to close month priod

this my closed month , it can copy new database, then old database only product, users , kredit ext..
 Private Sub mnuExport_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles mnuExport.Click
        Dim sFIle As String
        Dim iFile As String, ConnectString As String
        Dim ce As New SaveFileDialog
        On Error GoTo Access7Err
        iFile = AppPath & "\Ginvent.ddb"
        With ce
            .Title = "Tutup Buku Ke Database Baru"
            .Filter = "Database Files (*.idb)|*.idb"
            .InitialDirectory = AppPath
            .FileName = "D-" & Format(Now, "dd-MMMM-yy") & ".idb"
            .DefaultExt = ".idb"
            .ShowDialog()
            If Len(.FileName) = 0 Then
                Exit Sub
            End If
            sFIle = .FileName
        End With
        If sFIle = ReadIniFile(AppPath & "\InventEx.ini", "InventoryMain", "Database", "") Then
            MsgBox("You can not load data to the current database file. Select other file")
            Exit Sub
        End If
        If Dir(sFIle) <> "" Then
            If MsgBox("Apakah Mau di Salin ke Data Tersebut, Data yang Lama akan Hilang Di Ganti dengan Yang Baru?", "Tutup Buku") = False Then
                Exit Sub
            End If
        End If
        FileCopy(iFile, sFIle)   
        ConnectString = ""Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sFile & ";" & _
        "Persist Security Info=True;" & _
        "Jet OLEDB:Database Password=12345"

Call ExportStocks()


Private Sub ExportStocks(ConnectString As String, type_Prov As String)
Dim rsC As Recordset, i As Long
Dim rsT As Recordset, newConn As Connection
Dim rsP As Recordset
Dim rsU As Recordset
Dim rsQ As Recordset
Dim loc_SQLString As String
Dim rsR As Recordset, recNo As String, oldWareh As Long, newWareh As Long, pbLocation As Long, nameWareh As String
Dim rsH As Recordset, rsRD As Recordset, tts As Double
On Error GoTo Access7Err
Screen.MousePointer = vbHourglass
Set newConn = New Connection
newConn.Open ConnectString
CopyTableToNewDatabase mConn, "Users", newConn
CopyTableToNewDatabase mConn, "Company", newConn
CopyTableToNewDatabase mConn, "CustomerType", newConn
CopyTableToNewDatabase mConn, "VendorType", newConn
CopyTableToNewDatabase mConn, "Product", newConn, "ListID"
CopyTableToNewDatabase mConn, "Warehouse", newConn
CopyTableToNewDatabase mConn, "Vendor", newConn, "ListID"
CopyTableToNewDatabase mConn, "Customer", newConn, "ListID"
'-----create initial receiving
loc_SQLString = "select * from Receiver where ReceiverKey =0"
Set rsR = New Recordset
rsR.CursorLocation = adUseClient
rsR.Open loc_SQLString, newConn, adOpenStatic, adLockOptimistic
rsR.AddNew
rsR("Date").Value = date
rsR!DateReceived = date
rsR("ReceiverNomer").Value = "Opening-Stock"
rsR!KeyCode = "Beginning-Stock"
rsR!TxnType = "2"
recNo = rsR!ReceiverNomer
ext.......

this exportstock from vb6....
Please help
tx

 Call ConnectAccess()
        da = New OleDbDataAdapter("select Customer,InvNomer,Date,BalanceDue,PaidAmount,Notes,Cuskey,InvKey ,paydetKey,PaymentNomer from PaymentDetail where PaymentNomer='" & PbPaymentNo & "' ", mConn)
        ds = New DataSet
        da.Fill(ds, "PaymentDetail")
        DGV.DataSource = ds.Tables("PaymentDetail")

Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:21 AM

Did you follow the link I provided and read up on the SELECT INTO Statement?

How you determine what you're copying to a new table is entirely up to you. That is why there is a WHERE Clause capability in SELECT statements.

You can send your required information to the function and react to it by inserting the values into your sql statment string and executing it.

IF you understand the VB6 function you showed and you understand what SELECT INTO is doing I'm pretty sure you can tweak that function to work for you in .Net
Was This Post Helpful? 0
  • +
  • -

#5 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:27 AM

Just because I dont know any thing, vb6 or vb net
I just try and try and try........
I never learn vb6 or vb net.....

plz help
Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:40 AM

That's OK, It's good to try, it's a good step in the learning process. There is something I don't see in your last comment and that is Read and Research.

Do a search in our tutorials section for OleDB Basics or SQL Basics depending on what database engine you're using and become familiar with processing the SQL commands (SELECT, INSERT, UPDATE & DELETE).

What you can do with Select Into is

SELECT column_name(s)
INTO new_table_name [IN externaldatabase]
FROM old_tablename

In other words,
column_name would be an argument you pass to the function (it is the column in your current database.
new_table_name may or may not be an argument. If it's always the same it can just be put here
externaldatabase may or may not be an argument. If it's always the same it can just be put here
old_tablename the table in your current database. Again, can be passed to the function.
Now if you were to want to limit the result to only wanting a certain date range (ie, last month) ten that is where you would include a WHERE clause
WHERE TransactionDate BETWEEN date1 AND date2

Again, date1 and date2 being information you pass into it.

This post has been edited by CharlieMay: 26 February 2012 - 06:40 AM

Was This Post Helpful? 0
  • +
  • -

#7 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:44 AM

help me to make example
the database is can make new
I cannot copy from old database to new table
and insert from old datatable to new data table,

  loc_SQLString = "select * from Receiver where ReceiverKey =0"
        Dim ds As DataSet = New DataSet()
        da.Fill(ds, "Receiver")
        cmd = newConn.CreateCommand
        cmd.CommandText = "Insert into Receiver(ReceiverNomer,KeyCode,TxnType) values " & _
"('" & "Opening-Stock" & "Beginning-Stock" & " 2" & "')"

The ConnectionString property has not been initialized.
Was This Post Helpful? 0
  • +
  • -

#8 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:52 AM

Can you make one example....
because my english = poor
this like
my iFile and sFile = olddatabase and new database

all database have make new in folder, only I cannot copy the data product, customer, vendor ext

tx CharlieMay

see you tommorow. I'll go home now.
Was This Post Helpful? 0
  • +
  • -

#9 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 26 February 2012 - 06:54 AM

Read the tutorials, If you're asking why you have a ConnectionString error, you don't appear to have the basic concepts down yet. I can't help you if you don't understand this better other than just fixing it for you and what would you learn from that? Other than the obvious, ask someone to do it for me.

Quote

because my english = poor


That's fine but this isn't English, it's a Programming Language and while the tokens used are english words, their verbs still perform an action. I understand that our tutorials and such are english, but the code inside those are static visual basic "words" if you will, that are a part of that language. If you take those and search for help on them in your own native language, it should produce you an explanation you can understand. I just don't think you're taking that step.

This post has been edited by CharlieMay: 26 February 2012 - 07:03 AM

Was This Post Helpful? 1
  • +
  • -

#10 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 27 February 2012 - 06:34 AM

I get it
for insert to database
I try 2 table one is good
but another one, have only half data... but I'll try again
copy table not yet.
can you help me copy table
CopyTableToNewDatabase mConn, "Users", newConn
CopyTableToNewDatabase mConn, "Customer", newConn, "ListID"


tx very much
CharlieMay
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 27 February 2012 - 09:29 AM

OK, I'll be glad to look over the code and see if I can find something that might cause this. Let's see your updated .Net code for CopyTableToNewDatabase.
Was This Post Helpful? 0
  • +
  • -

#12 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 27 February 2012 - 10:14 PM

  sSql = "SELECT * from Users "
        Dim test As String = "Insert * INTO Users [db_to] FROM Users [db_from])"
        cmd = New OleDbCommand(test, DB_TO)
        cmd.ExecuteNonQuery()
'Syntax error in INSERT INTO statement.

or
        Dim cmdtest1 As New OleDbCommand("SELECT * from Users", mConn)
        Dim dr8 As OleDbDataReader
        dr8 = cmdtest1.ExecuteReader
        cmdtest1 = DB_FROM.CreateCommand
        Dim cmdtest As New OleDbCommand("Insert * INTO Users")
        cmdtest1.ExecuteNonQuery()
'Command text was not set for the command object.



This my InvPay2C have 204 row
then insert to my paymentdetail only 13 row.
Dim cmd3 As New OleDbCommand
        Dim cmd4 As New OleDbCommand
        Dim PaymentNom As String
        PaymentNom = "OP-" & Format(Now, "MM-YY") & "-01"
        Dim t As String = Convert.ToString(PaymentNom)
        cmd4 = New OleDbCommand("select * from  " & "InvPay2C ", mConn)

        Dim dr4 As OleDbDataReader
        dr4 = cmd4.ExecuteReader
        For j = 0 To dr4.FieldCount - 1
            If dr4.Read = True Then
                cmd3 = DB_FROM.CreateCommand
                Dim c As String = Convert.ToString(dr4(2))
                cmd3.CommandText = "Insert into PaymentDetail(PaymentNomer,InvNomer,Date,BalanceDue,notes,Status,Customer,Cuskey) values " & _
   "('" & Convert.ToString(PaymentNom) & "','" & Convert.ToString(dr4(1)) & "','" & Convert.ToString(dr4(2)) & "','" & Convert.ToString(dr4(8)) & "','" & Convert.ToString(dr4(5)) & "','" & "4" & "','" & Convert.ToString(dr4(4)) & "','" & Convert.ToString(dr4(3)) & "')"             
                cmd3.ExecuteNonQuery()
            End If
        Next j


and how to manipulation date??
CDate(dr4(2)) or Format(dr4(2), "MM/dd/yyy")'= Syntax error in INSERT INTO statement.

Convert.ToInt32(dr4(2))'=Invalid cast from 'DateTime' to 'Int32'.

Was This Post Helpful? 0
  • +
  • -

#13 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 28 February 2012 - 05:18 AM

A date in Access should be wrapped with # instead of ' so for all your dates you would use:
... & "','" & Convert.ToString(dr4(1)) & "',#" & Convert.ToString(dr4(2)) & "#,'" & Convert.ToString(dr4(8)) & "','" & Convert.ToString(dr4(5)) & "','" & "4" & " ...

Also, it think your field named date will need to be wrapped with [] as I'm pretty sure it's a keyword in access.

You know, if you would use parameters, a lot of this code would be unnecessary and easier to read.

Also, I think you need to wrap the date field in your insert statement with [] as I'm pretty sure access uses it as a keyword.
Was This Post Helpful? 1
  • +
  • -

#14 damdimdum   User is offline

  • New D.I.C Head

Reputation: 1
  • View blog
  • Posts: 42
  • Joined: 20-February 12

Re: Copy Table To New Database ?

Posted 28 February 2012 - 05:45 AM

Now I can insert All row
 For i As Integer = 0 To dr4.FieldCount - 1
            If dr4.HasRows Then
                While dr4.Read
                    cmd3 = NewConn.CreateCommand
                    cmd3.CommandText = "Insert into PaymentDetail(PaymentNomer,InvNomer,date,BalanceDue,notes,Status,Customer,Cuskey) values " & _
        "('" & Convert.ToString(PaymentNom) & "','" & Convert.ToString(dr4(1)) & "',#" & Convert.ToString(dr4(2)) & "#,'" & Convert.ToString(dr4(8)) & "','" & Convert.ToString(dr4(5)) & "','" & "4" & "','" & Convert.ToString(dr4(4)) & "','" & Convert.ToString(dr4(3)) & "')"
                    cmd3.ExecuteNonQuery()
                End While
            End If
        Next i


Syntax error in INSERT INTO statement.
If I not insert date, then I ceck my table all have date, but date auto today now...?
maybe you know this problem

& Convert.ToString[dr4](1) & "',#" &
end of statement expeted
How to insert wrap []

Copy table not yet...!!!!
  'DB_FROM = New OleDbConnection(sSql)
        '' Dim cmdtest As New OleDbCommand
        'Try
        '    cmd = New OleDbCommand(" Insert into DB_To.Product  select * from mconn.Product ")
        '    cmd.ExecuteNonQuery()
        'Catch ex As Exception

        'End Try

Was This Post Helpful? 0
  • +
  • -

#15 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1734
  • View blog
  • Posts: 5,710
  • Joined: 25-September 09

Re: Copy Table To New Database ?

Posted 28 February 2012 - 05:54 AM

View Postdamdimdum, on 28 February 2012 - 07:45 AM, said:

How to insert wrap []

cmd3.CommandText = "Insert into PaymentDetail(PaymentNomer,InvNomer,[date],BalanceDue,notes,Status,Customer,Cuskey) values " & _


The wrapping of the fields informs access that what is contained inside is denoted to be a field so as not to confuse the parser.

It is also used when a table includes a space (which I don't recommend ever doing)
Was This Post Helpful? 1
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2