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

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

no path is not fixed and files are in txt.

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

Also how do you get this filename?

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()

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.

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"

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

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

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

Parameter '?f''' must be defined.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.