how to attach multiple files in vb.net and save attach files in mysql [Resolved]

I think you should save data variable instead of fInfo. data is having bytes of file content.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

attach the files in the database ? not getting what you want? do you want to save file path in db or save all content of file in db or what?

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Rajni Shekhar

in my form i have to attach files and put it in a listview and the problem is i want to insert all the listview items im my database which is mysql..
i have a table there named nclattachment..i only have to insert the attach files in the table column called afilename.
waiting for any help!


thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Refer

http://www.codeproject.com/Articles/8528/ASP-NET-email-with-multiple-attachments

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

i already viewed your referal but I think it's applicable to me because i'm developing an application not a web design,but thanks a lot for trying to help..

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

1. Create object of FileStream
FileStream fs=new FileStream(@"filepath", FileMode.Open);
byte[] fileBytes=new byte[fs.Length];
fs.Read(fileBytes,0,(int)fs.Length);
fs.close();

now send fileByte as parameter in your query using MySqlCommand Text

MySqlConnection conn= new MySqlConnection(connectionstring);
MySqlCommand cmd=new MySqlCommand("insert into tbl(afilename) values(@fileName);");
cmd.Parameters.AddWithValue("@fileName",fileBytes);
conn.open();
cmd.ExecuteNonQuery();
conn.close();

check it, may be it will work.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

i already tried ur your code but i got error on creating object filestream

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

can you please give me another way to solve my problem

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

can u post your error?

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

filepath not found,what should I put in that part,I store my attach files in a listview

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Jerome

Hooo... Sorry

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

this is the error of filepath, check your file path where you kept files, in application folder or in c:\drive or where?

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Access to the path 'C:\Users\jerome\Desktop\NCL1' is denied. this the error I dont know what's the cause..kindly help me


thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

there could be read only permission to this folder, right click on the folder and give full permission, and uncheck read only.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

To grant ASP.NET write access to a path, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group (typically {MACHINE}\ASPNET on IIS 5 or Network Service on IIS 6). Highlight the needed account, and check the boxes for the desired access.

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Rajni Shekhar,

Hi, sorry but I think I'm just misleading you...I tried your code but I only get the filename as I retrieve it from database.What I wan actually is save filename to the database together with it's content so that I can view the file content as I retrieve it...
Hope you consider my fault!

Thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

HI Jerome,

Its ok, you can do onething, take a reader stream and read all content in that and save it to the database with fale name

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

can you show me how?if you don't mind..

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Jerome,
You can do one more thing, keep your file in application root, and save your filename in db, when retriving file, get filename from db and extract data from saved file in your appliction root. It can also solve your problem.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

in above code, filebyte takes content in bytes and when you retrive it, convert bytes to string, you will get data, i have checked it.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

can you show me the whole thing as in the whole code coz I'm using a listview where I put my attachments using opendialog before I store it in database..
I have two buttons,one for attaching files named"btnAttachfile" and a save button for inserting the whole attachment to my database.I can attach as many files as I can in the listview but the problem is how to insert it in my database and retrieve the attach files also.

Waiting for your help...I realy don't have an idea about this...

thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

post your code here... i will try to resolve it.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

"im using a DAL dll"

Dim dal As New DAL.DataAccess
dal.ConnectionString = ls_connectionString
dal.Provider = EnumProviders.MySQL
dal.BeginTrans(IsolationLevel.ReadCommitted)


Dim data As Byte() = Nothing
Dim row As DataRow
Dim i As Integer
Dim listofcolumn As New List(Of String)()
For Each column As ColumnHeader In ListView1.Columns
listofcolumn.Add(column.ToString())
Next

Dim table As New DataTable("nclattachments")
For Each column As String In listofcolumn
table.Columns.Add(column, GetType(String))
Next

For Each item As ListViewItem In ListView1.Items
row = table.NewRow()
row(listofcolumn(i)) = item.Text
Dim fInfo As New FileInfo(item.Text)
Dim numbytes As Long = fInfo.Length
Dim fStream1 As New FileStream(item.Text, FileMode.Open, FileAccess.Read)
Dim br As New BinaryReader(fStream1)
data = br.ReadBytes(CInt(numbytes))
Dim attach As String = "INSERT INTO trialpic (picfile) VALUES (@trial)"

Dim Arlst As New Dictionary(Of String, Object)

Arlst.Add("@trial", fInfo)

dal.ExecNonQuery(attach, CommandType.Text, Arlst)

Next

dal.CommitTrans()

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Hi jerome,
I have checked below code in my system (for one file only) using sql server Db and it is working fine.

FileStream fs = new FileStream(@"C:\TestFile.txt", FileMode.Open);
byte[] fileBytes=new byte[fs.Length];
fs.Read(fileBytes,0,(int)fs.Length);
fs.Close();

SqlConnection conn = new SqlConnection("server=E1DV205\\SQLEXPRESS;Database=MyDB;Trusted_Connection=True;");
SqlCommand cmd = new SqlCommand("insert into tblFile(FileName,FileContent) values(@fileName,@FileContent);");
cmd.Parameters.AddWithValue("@FileContent", fileBytes);
cmd.Parameters.AddWithValue("@fileName", "TestFile.txt");
conn.Open();
cmd.Connection = conn;
cmd.ExecuteNonQuery();
conn.Close();

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Rajni Shekhar,

I'll try your suggestion, I think I also had a problem in my dll..I'll just fix it..I will just post if I got a problem again...


thanks a lot,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Rajni Shekhar,


Hi,I already can attach fils to my database with the code I posted by just changing data variable instead of fInfo,and I also had a problem with my dll but I already solved it..What's my problem now is how to retrive those attached file and view it one by one in a picture box..

I realy appreciate your help..Thanks a lot...

cheers,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Hi Jerome,
You need to get content from database for a perticular filename :

select filecontent from tbl where filename=@filename

get it in a string/byte (need to check) varible.
and then write to the file.

Jerome i told you best approach, you should save file in your application root-. in a folder and when you want to extract just extract it from there. it is a fast approach. but if your req. is to save in db then you can extract it and again write it to the notepad and show it.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

I already done with the retrival of file but I could not have all the files it only gives me one file..What I want is all the files with the same Id number should be retrieved and placed it in the listview after saving the file in a folder..

here is my code...

Private Sub getPic()
Dim dal As New DAL.DataAccess
Dim dr As MySqlDataReader
dal.ConnectionString = ls_connectionString
dal.Provider = EnumProviders.MySQL

Dim sql As String = "select * from trialpic"
dr = dal.ExecReader(sql, CommandType.Text)
If dr.Read Then
Try
Dim lb As Byte() = CType(dr.Item("picfile"), Byte())
selectImage(lb, 2)
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Warning)
End Try
End If
End Sub
Private Function selectImage(ByVal rawdata() As Byte, ByVal ImageIndex As Integer) As String
Dim str As String
Dim fs As FileStream
Dim pathImg As String = "C:\Users\jerome\Desktop\NCL1\NCL\bin\Debug\attachedFiles\"
Dim filename As String = Format(Now, "yyyyMMddhhmmss")
Try
str = pathImg & filename & ".png"
ListView1.Items.Add(str)
fs = New FileStream(str, FileMode.Create, FileAccess.Write)
fs.Write(rawdata, 0, UBound(rawdata) - 1)
fs.Close()

selectImage = str
Catch ex As Exception
MessageBox.Show(ex.Message, "Error Message", MessageBoxButtons.OK, MessageBoxIcon.Warning)
selectImage = ""
End Try

End Function
Private Sub viewAttachments_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles viewAttachments.Click
If GetINI2() = False Then
MessageBox.Show("Unable to read serverEly.INI", "System Message", MessageBoxButtons.OK, MessageBoxIcon.Warning)
Else
getPic()
End If
End Sub

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Datareader read statement one by one, so need to write code in loop, it will extract data from db one by one in onnected mode. If you can take dataset take it.

in Try block, need to apply a loop to read data from datareader one by one.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

ok I'll try your sugestion..I encountered another problem in my insert file because I cant insert files which is 1mb size up..How to set up max_allowed_packets?



thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

in db column, set varchar(max) if it is possible in mySql, no idea about it.

Thanks,
Rajni Shekhar

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

im not using varchar in my db column,i'm using BLOB..


thanks,
jerome

Jerome, if this helps please login to Mark As Answer. | Alert Moderator

Jerome

Big Discussion in This Question

Life is a Race
Thanks & Regards
By
Sabari Mahesh P M

Jerome, if this helps please login to Mark As Answer. | Alert Moderator