Help. Conversion foxpro to MYsql

[tutypruty]

hi guys. can you please check my codes..

i am currently working on a migration of a foxpro to a mysql database
but i have an error when i am executing my insert query. help me out.

Dim myConnection As New MySqlConnection
Dim foxConnection As New OleDbConnection

Dim test1 As String = "z:\foxpro\" 'where resides the foxpro table

myConnection = New MySqlConnection("SERVER=" & My.Settings.DBSERVER & ";" _
& "DATABASE=testdata;" _
& "UID=user;" _
& "PWD=pass;")


Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

Dim strSql As String = "insert into dept " & xFoxpro

Dim connCommand As New MySqlCommand
connCommand.CommandText = strSql & " Provider=VFPOLEDB;" + _
"Data Source= " & test1 & " ;" & _
"Collating Sequence=general;"

connCommand.Connection = myConnection
If connCommand.Connection.State <> ConnectionState.Open Then
connCommand.Connection.Open()
End If
connCommand.ExecuteNonQuery()

 

[Hack]

What is the error?

 

[tutypruty]

actually it does say i have error in my syntax,

it is probably this code:

Dim xFoxpro As String = "select strtran(dept_code,chr(39),'') as dept_code, strtran(desc,chr(39),'') as descr from dept"

Dim strSql As String = "insert into dept " & xFoxpro

i am thinking of opening first the connection of my foxpro query, but the same error comes in.

T.T

 

[Hack]

Your syntax for an INSERT INTO is wrong. It goes

INSERT INTO yourtablename (field1,field2, field3, etc) VALUES (val1, val2, val3, etc)

Take a moment and have a look at this.

SQL INSERT INTO Statement

 

[tutypruty]

actually i am inserting a selected table (TABLE2) to TABLE1.

take a look at this site:
SQL INSERT INTO Statement

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

this INSERT will be only successful if the 2 tables are with the same structure and number of columns otherwise it will prompt you an error.