load bulk data files into mysql dynamically

[jagpreet]

i want to load bulk data files dynamically inot mysql database by using visual basic as front end.
i want to get the filepaths dynamically i.e i just enter the file name into textbox and it will automatically load the data into the database.
i have written this code

myCommand.CommandText = "LOAD DATA INFILE '?filename' into table configparams columns terminated by ',' enclosed by '""' lines starting by '""'"

myCommand.Connection = conn

myCommand.Parameters.AddWithValue("?filename", filename)
but with this code is not able to get the desired filepath

can anyone help me out plz

[binoj_daniel]

Do you have a fixed path for the data files? And in what format are these files? .CSV or TXT?

[jagpreet]

no path is not fixed and files are in txt.

[binoj_daniel]

So how do you know where to look for the file if you dont know the path?

[binoj_daniel]

Also how do you get this filename?

[jagpreet]

this is what i want to do dynamically with the help of parameters.
what i want is that user just enter the filename along with the path in the textbox and then the code automatically put the data of the file into database.

i can also fix the path the path of the file but i want the filename dynamically.

i have written this code.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim conn As New MySqlConnection
Dim myCommand As New MySqlCommand
Dim myAdapter As New MySqlDataAdapter
Dim myData As New DataTable

conn = New MySqlConnection()
conn.ConnectionString = "server=localhost " & ";" _
& "user id= root " & ";" _
& "password= cricket" & ";" _
& "database=systemconfiguration"

Dim filename As String

Dim fs As FileStream = New FileStream(textbox.text, FileMode.Open, FileAccess.Read)
(textbox.text is a textbox through which i want to give the filename or full filepath.)

FileSize = fs.Length
rawData = New Byte(FileSize) {}
fs.Read(rawData, 0, FileSize)
fs.Close()

myCommand.Connection = conn
myCommand.CommandText = "LOAD DATA INFILE '?f' into table configparams columns terminated by ',' enclosed by '""' lines starting by '""'"

myCommand.Parameters.AddWithValue("?f", filename)

conn.Open()
myCommand.ExecuteNonQuery()

[jagpreet]

this is what i want to do dynamically with the help of parameters. what i want is that user just enter the filename along with the path in the textbox and then the code automatically put the data of the file into database.

i can also fix the path the path of the file but i want the filename dynamically.

i have written this code.

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

Dim conn As New MySqlConnection Dim myCommand As New MySqlCommand Dim myAdapter As New MySqlDataAdapter Dim myData As New DataTable

conn = New MySqlConnection() conn.ConnectionString = "server=localhost " & ";" _ & "user id= root " & ";" _ & "password= cricket" & ";" _ & "database=systemconfiguration"

Dim filename As String

Dim fs As FileStream = New FileStream(textbox.text, FileMode.Open, FileAccess.Read) (textbox.text is a textbox through which i want to give the filename or full filepath.)

FileSize = fs.Length rawData = New Byte(FileSize) {} fs.Read(rawData, 0, FileSize) fs.Close()

(i can also get the filepath by this syntex given below

filePath = Me.filename.Text

MsgBox(filePath) )

myCommand.Connection = conn myCommand.CommandText = "LOAD DATA INFILE '?f' into table configparams columns terminated by ',' enclosed by '""' lines starting by '""'"

myCommand.Parameters.AddWithValue("?f", filename)

conn.Open() myCommand.ExecuteNonQuery()

everytime it is not able to find the correct path,infact it shows this error

File 'C:\Program Files\MySQL\MySQL Server 4.1\Data\systemconfiguration\?f' not found (Errcode: 22)

but when i hardcode the file path in the load query it works fine as it shown below

myCommand.CommandText = "load data infile 'C:/Documents and Settings/jagpreet/d2.txt' into table configparams columns terminated by ',' enclosed by '""' lines starting by '""'"

but whern i try to do it dynamically it gives the error.

[binoj_daniel]

Try to use a path which does not contain any spaces in the folder name. Let me know what happens, For eg: use the file path as :

"C:\Data\filename.txt"

[binoj_daniel]

It seems you are trying to connect to MySQL Database for this purpose.

[binoj_daniel]

Also Try replacing the single quote with 2 single quotes or a double quote. That should work.

[jagpreet]

i have done all those changes.
now it is giving the following error

Parameter '?f''' must be defined.