Save Image in SQL Database
-
Hello again thanks for your rep.
Private Function GetDBConnection()
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim DBname As String = Application.StartupPath()
DBname = DBname.Substring(0, DBname.LastIndexOf("\bin"))
DBname = DBname & "\Images.mdf"
' Compose the connect string.
Dim connect_string As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
DBname & ";Integrated Security=True;User Instance=True"
' Open a database connection.
Dim SqlConn As New Data.SqlClient.SqlConnection(connect_string)
SqlConn.Open()
' Return the connection.
Return SqlConn
End Functionwell in the message box it says record 1 as bellow
RecordAffected = commImage.ExecuteNonQuery()
MsgBox(CStr(RecordAffected) & "Image successfuly deleted from database", MsgBoxStyle.Information)i set a brakpoint and stepin so variable RecordAddected was 1 waiting for your kind rep. thanks again
-
When you run the application and delete the image, open Task Manager and go to processes tab. How many sqlservr processes you see?
Hello again Thanks for your promt rep. yes I have delete one image and i can see as bellow 1- sqlbrowser.exe -- 652k 2- sqlservr.exe --- 25540k 3- sqlservr.exe -- 5900k 4- msftesql.exe --1724k 5- sqlservr.exe ---54084k this is what i can see in task manager waiting for your kind rep. thanks
-
Hello again Thanks for your promt rep. yes I have delete one image and i can see as bellow 1- sqlbrowser.exe -- 652k 2- sqlservr.exe --- 25540k 3- sqlservr.exe -- 5900k 4- msftesql.exe --1724k 5- sqlservr.exe ---54084k this is what i can see in task manager waiting for your kind rep. thanks
You have two SQL Server instances running at the same time. I don't know if they are separate databases, but in worst case they are separate user instances of the same database (you had User Instance=true in your connection string). Instead of getting a new connection to the db in every sub, try to change the logic so that you get the connection only once. For example create the connection when the window loads and use that connection in every sub. Does anything change?
-
You have two SQL Server instances running at the same time. I don't know if they are separate databases, but in worst case they are separate user instances of the same database (you had User Instance=true in your connection string). Instead of getting a new connection to the db in every sub, try to change the logic so that you get the connection only once. For example create the connection when the window loads and use that connection in every sub. Does anything change?
hello again thanks for promt rep. well to be very honest I have just started sql so i am not expert but as you said I will try to change logic and let you know what I understand according to you that I need to open connection when form load and but do I need to close it or not but upto now what i have learnt i have to close the connection but i will see how it goes is it possible to help me with this logic that where to start i dont want answer i will try it waiting for your kind rep. have a nice time thanks
-
hello again thanks for promt rep. well to be very honest I have just started sql so i am not expert but as you said I will try to change logic and let you know what I understand according to you that I need to open connection when form load and but do I need to close it or not but upto now what i have learnt i have to close the connection but i will see how it goes is it possible to help me with this logic that where to start i dont want answer i will try it waiting for your kind rep. have a nice time thanks
No problem, Perhaps the easiest way is to make the connection static so that every time you connect to the database, you get the same connection. Something like (may contain errors):
Private Function GetDBConnection()
Static SqlConn = NothingIf SqlConn Is Nothing Then
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim DBname As String = Application.StartupPath()
DBname = DBname.Substring(0, DBname.LastIndexOf("\bin"))
DBname = DBname & "\Images.mdf"
' Compose the connect string.
Dim connect_string As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
DBname & ";Integrated Security=True;User Instance=True"
' Open a database connection.
SqlConn = New Data.SqlClient.SqlConnection(connect_string)
SqlConn.Open()
End If
' Return the connection.
Return SqlConn
End FunctionAnd when using this, don't close and don't dispose the connection, so comment the lines
conImage.Close()andconImage.Dispose()inbtnDelete_ClickandLoadImagesandbtnSave_ClickThe idea is to test that you are using the same database all the time. If this doesn't help then we change back to the original logic and try something else. -
No problem, Perhaps the easiest way is to make the connection static so that every time you connect to the database, you get the same connection. Something like (may contain errors):
Private Function GetDBConnection()
Static SqlConn = NothingIf SqlConn Is Nothing Then
' Compose the database file name.
' Modify this if the database is somewhere else.
Dim DBname As String = Application.StartupPath()
DBname = DBname.Substring(0, DBname.LastIndexOf("\bin"))
DBname = DBname & "\Images.mdf"
' Compose the connect string.
Dim connect_string As String = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
DBname & ";Integrated Security=True;User Instance=True"
' Open a database connection.
SqlConn = New Data.SqlClient.SqlConnection(connect_string)
SqlConn.Open()
End If
' Return the connection.
Return SqlConn
End FunctionAnd when using this, don't close and don't dispose the connection, so comment the lines
conImage.Close()andconImage.Dispose()inbtnDelete_ClickandLoadImagesandbtnSave_ClickThe idea is to test that you are using the same database all the time. If this doesn't help then we change back to the original logic and try something else.Hello sir thanks for promt rep. yes i have change the logic the way you said but still it's same problem i mean i can save or delete image from sql database but i have to close application to see the changes and one more thing i would like to tell you that i have created another application without image but it works fine if i add or delete first name and last name it shows the changes same time and i have used same method with this application but i am not sure but i sow one difference in it that project has one table with three columns ID, FirstName, LastName and this is button event to change enable = false
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Button2.Enabled = False
Button3.Enabled = False : Button4.Enabled = False : Button5.Enabled = False
Button1.Enabled = True
TextBox1.Enabled = True : TextBox2.Enabled = True
TextBox1.BackColor = Color.Aquamarine : TextBox2.BackColor = Color.Aquamarine
Try
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()
Catch ex As Exception
MsgBox(ex.Message)
End Try
EndSub but with this there is only one difference
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()so do you think this is the issue waiting for your kind rep. have a nice time thanks again
-
Hello sir thanks for promt rep. yes i have change the logic the way you said but still it's same problem i mean i can save or delete image from sql database but i have to close application to see the changes and one more thing i would like to tell you that i have created another application without image but it works fine if i add or delete first name and last name it shows the changes same time and i have used same method with this application but i am not sure but i sow one difference in it that project has one table with three columns ID, FirstName, LastName and this is button event to change enable = false
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Button2.Enabled = False
Button3.Enabled = False : Button4.Enabled = False : Button5.Enabled = False
Button1.Enabled = True
TextBox1.Enabled = True : TextBox2.Enabled = True
TextBox1.BackColor = Color.Aquamarine : TextBox2.BackColor = Color.Aquamarine
Try
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()
Catch ex As Exception
MsgBox(ex.Message)
End Try
EndSub but with this there is only one difference
Me.StudentBindingSource.EndEdit()
Me.StudentBindingSource.AddNew()so do you think this is the issue waiting for your kind rep. have a nice time thanks again
If you have corresponding BeginInit somewhere, you should call EndInit. Trying that is a good idea. Also one possibility is that even if you delete the image from database, it isn't correctly refreshed in the dataset (dsImage). However you do call Clear-method so it should work. If calling EndInit doesn't help, can you re-create the dataset (set dsImage to Nothing and create a new dsImage) and fill it after that.
-
If you have corresponding BeginInit somewhere, you should call EndInit. Trying that is a good idea. Also one possibility is that even if you delete the image from database, it isn't correctly refreshed in the dataset (dsImage). However you do call Clear-method so it should work. If calling EndInit doesn't help, can you re-create the dataset (set dsImage to Nothing and create a new dsImage) and fill it after that.
hello again as you said that to refresh dsimage so for example if there is 4 images in database and if i delete image no 3 so after deleting image it's refreshing dataset because in picture box it shows first image but still you can go forward or backword up to image 4 but when i close application and rerun it then it shows 3 images in database so that means it refreshing dataset properly and there is no corresponding in begininit at all so i think i dont need to endinit so we have only last option so i think i will try tomorrow but any way i learn lot from you i realy appriciate that thanks a lot :) if i need any help i will contact you tomorrow good night
-
hello again as you said that to refresh dsimage so for example if there is 4 images in database and if i delete image no 3 so after deleting image it's refreshing dataset because in picture box it shows first image but still you can go forward or backword up to image 4 but when i close application and rerun it then it shows 3 images in database so that means it refreshing dataset properly and there is no corresponding in begininit at all so i think i dont need to endinit so we have only last option so i think i will try tomorrow but any way i learn lot from you i realy appriciate that thanks a lot :) if i need any help i will contact you tomorrow good night
-
You're welcome. :) I'll go and have some sleep also... :zzz: One thing you can try tomorrow is to add
AcceptChanges()- call to dataset after deletion or fill. In case that the record is deleted but changes are still pending MIka -
Hello sir good morning how are you I have tried every thing but still it's same problem so now I don't know what to do I have also tried AcceptChanges() but still it's same waiting for your kind rep. :confused:
-
I couldn't be bothered to read all the posts, but if the data is actually being deleted in the database (check from Management Studio) then you need to refresh your dataset and rebind it to your control. Hope this helps
Bob Ashfield Consultants Ltd
-
hello sir could you please guide me how to do this because i have just started sql so i dont know much about it i mean i dont want code but just want to know where to start :confused: waiging for your kind rep. have a nice day
Its not in sql, its in your code. I assume yuo load data from the database into a dataset and then bind the datatable(s) within the dataset to controls. When you add or delete data you need to probably (depending on how you update your database) refresh your dataset and/or rebind the datatable(s) to the controls.
Bob Ashfield Consultants Ltd
-
Its not in sql, its in your code. I assume yuo load data from the database into a dataset and then bind the datatable(s) within the dataset to controls. When you add or delete data you need to probably (depending on how you update your database) refresh your dataset and/or rebind the datatable(s) to the controls.
Bob Ashfield Consultants Ltd
hello again this is the code for add image
btnSave.Enabled = False
'get sql connection
Dim ImageAdd As IntegerTry conImage = GetDBConnection() dsImage.BeginInit() Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & \_ "@pic, @title, @itype, @iheight, @iwidth)" commImage = New Data.SqlClient.SqlCommand(sSQL, conImage) Call GetImage() ImageAdd = commImage.ExecuteNonQuery() Me.daImage.Update(Me.dsImage, "Images") dsImage.AcceptChanges() MessageBox.Show(ImageAdd.ToString & " Image successfuly saved in database", "Image Load") dsImage.EndInit() Catch ex As Exception MsgBox(ex.Message) End Try commImage.Dispose() ' commImage = Nothing conImage.Close() conImage.Dispose() Call LoadImages() End Suband when ever i add or delete image it refresh the the dataset i mean if ther is 3 images in database and if i want to delete image no. 3 after deleting image 3 it shows image no. 1 in picture that means it's reloading image that is what i am thinking :confused:
-
hello again this is the code for add image
btnSave.Enabled = False
'get sql connection
Dim ImageAdd As IntegerTry conImage = GetDBConnection() dsImage.BeginInit() Dim sSQL As String = "INSERT INTO Images (Pic,Title, IType, Height, Width) VALUES(" & \_ "@pic, @title, @itype, @iheight, @iwidth)" commImage = New Data.SqlClient.SqlCommand(sSQL, conImage) Call GetImage() ImageAdd = commImage.ExecuteNonQuery() Me.daImage.Update(Me.dsImage, "Images") dsImage.AcceptChanges() MessageBox.Show(ImageAdd.ToString & " Image successfuly saved in database", "Image Load") dsImage.EndInit() Catch ex As Exception MsgBox(ex.Message) End Try commImage.Dispose() ' commImage = Nothing conImage.Close() conImage.Dispose() Call LoadImages() End Suband when ever i add or delete image it refresh the the dataset i mean if ther is 3 images in database and if i want to delete image no. 3 after deleting image 3 it shows image no. 1 in picture that means it's reloading image that is what i am thinking :confused:
Hi again, Few additional questions - about the binding. Have you bound the dsImage dataset to a BindingSource and again a picture box to that BindingSource? - in LoadImages sub, what is the difference between
ImagesTableAdapteranddaImage? - in INSERT statement, you don't specify a value for ImageID-column, but that column is used in DELETE statement. How the ImageID get's it's value -
Hi again, Few additional questions - about the binding. Have you bound the dsImage dataset to a BindingSource and again a picture box to that BindingSource? - in LoadImages sub, what is the difference between
ImagesTableAdapteranddaImage? - in INSERT statement, you don't specify a value for ImageID-column, but that column is used in DELETE statement. How the ImageID get's it's valuehello again how are you thanks for your rep. yes i have dound picturebox to binding source , and i am trying so many things so i add imagestable adapter but daImage is i have drag and drop on form and went through adapter wizard and when i created Image table i have set primary key and identity specification set to yes so every time when i add new image to database it increment 1 by it self and in delete statement i have find record to delete it so it gets the ImageID from textbox all this is works fine but you know the problem so now i have no idea what to do :confused: waiting for your kind rep. and thanks again
-
hello again how are you thanks for your rep. yes i have dound picturebox to binding source , and i am trying so many things so i add imagestable adapter but daImage is i have drag and drop on form and went through adapter wizard and when i created Image table i have set primary key and identity specification set to yes so every time when i add new image to database it increment 1 by it self and in delete statement i have find record to delete it so it gets the ImageID from textbox all this is works fine but you know the problem so now i have no idea what to do :confused: waiting for your kind rep. and thanks again
Hmm, let's try to simplify this. Can you create a button and in that button call this sub (it may contain compilation errors since I wrote it in notepad). Let's see what you get with this:
Private Sub TestMethod()
Dim imageCount As Integer = dsImage.Images.Rows.CountMsgBox("Original image count in dataset: " & CStr(imageCount))
conImage = GetDBConnection()
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text, conImage)
If commImage.ExecuteNonQuery() <> 1 Then
MsgBox("Delete failed", MsgBoxStyle.Information)
End If
dsImage.Images.Clear()
dsImage.Images.AcceptChanges()
If dsImage.Images.Rows.Count <> 0 Then
MsgBox("Datatable not empty", MsgBoxStyle.Information)
End If
Me.ImagesTableAdapter.ClearBeforeFill = True
Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
If (imagecount - 1 <> dsImage.Images.Rows.Count) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If
End SubWhat do you get as result (information in messageboxes)?
-
Hmm, let's try to simplify this. Can you create a button and in that button call this sub (it may contain compilation errors since I wrote it in notepad). Let's see what you get with this:
Private Sub TestMethod()
Dim imageCount As Integer = dsImage.Images.Rows.CountMsgBox("Original image count in dataset: " & CStr(imageCount))
conImage = GetDBConnection()
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images WHERE ImageID = " & txtImageFile.Text, conImage)
If commImage.ExecuteNonQuery() <> 1 Then
MsgBox("Delete failed", MsgBoxStyle.Information)
End If
dsImage.Images.Clear()
dsImage.Images.AcceptChanges()
If dsImage.Images.Rows.Count <> 0 Then
MsgBox("Datatable not empty", MsgBoxStyle.Information)
End If
Me.ImagesTableAdapter.ClearBeforeFill = True
Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
If (imagecount - 1 <> dsImage.Images.Rows.Count) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If
End SubWhat do you get as result (information in messageboxes)?
hello again sorry for late i have test this and result are as bellow WITH 0 DATA COUNT FIRST MESSAGEBOX 1- Original Count in Database = 0 2- Error because there is no row in database so it shows error because of this
If commImage.ExecuteNonQuery() <> 1 Then
MsgBox("Delete failed", MsgBoxStyle.Information)
End Ifafter that i close application and rerun it and add one image in database so FIRST MSGBOX 1- Original Count in DB = 1 SECOND MSGBOX 2- Delete failed THIRD MSGBOX 3- Delete not done, Rows in datatable = 1 thanks again waiting for your rep.
-
hello again sorry for late i have test this and result are as bellow WITH 0 DATA COUNT FIRST MESSAGEBOX 1- Original Count in Database = 0 2- Error because there is no row in database so it shows error because of this
If commImage.ExecuteNonQuery() <> 1 Then
MsgBox("Delete failed", MsgBoxStyle.Information)
End Ifafter that i close application and rerun it and add one image in database so FIRST MSGBOX 1- Original Count in DB = 1 SECOND MSGBOX 2- Delete failed THIRD MSGBOX 3- Delete not done, Rows in datatable = 1 thanks again waiting for your rep.
No problem... So the problem in this case was that there wasn't a row to delete matching the condition. Try to change the delete statement:
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images WHERE ImageID = (SELECT MAX(ImageID) FROM Images)", conImage)
This should delete the last image in images. Any difference?
-
No problem... So the problem in this case was that there wasn't a row to delete matching the condition. Try to change the delete statement:
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images WHERE ImageID = (SELECT MAX(ImageID) FROM Images)", conImage)
This should delete the last image in images. Any difference?
hello again ok with empty DB 1 msgbox Original image count = 0 2 msgbox delete failed 3 msgbox Delete not done, rows in BD =0 and without closing application I add one image with original method and it shows msgbox that 1 image is saved, and then without closing application i click button to use your method still it shows that first msgbox= original image count = 0 and in second = Delete not done row in datatable=0, and when i click button second time without closing application so first msgbox is original image count =0 second msgbox is delete failed therd msgbox is row i datatable=0 so that means image has been saved in database and deleted when i click button to use your method and when i click button second so result was different and then add one image and rerun the application so it show one image in database and i can see the image in picturebox as well now i click button for your method so msgbox 1 Original Image count =1 msgbox2 = delete not done, row in datatable = 1 now i have click button second time without closing application so msgbox 1 Original Image count =1 msgbox 2 Delete Failed msgbox 3 delete not done, row in datatable = 1 so this is some thing else i think we are going to the right direction thanks waiting for your kind rep.