Save Image in SQL Database
-
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.
-
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.
This is confusing... I thought that now the delete operation would have deleted 1 row. I think I must get some sleep but in the mean time if you could test this variation with one picture in database (this should delete ALL pictures):
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End IfI'll get back to this sometime tomorrow afternoon so don't hurry with this. If I'm reading the clock correctly it's past 10pm at your timezone so you could use some rest too. :) Mika
-
This is confusing... I thought that now the delete operation would have deleted 1 row. I think I must get some sleep but in the mean time if you could test this variation with one picture in database (this should delete ALL pictures):
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End IfI'll get back to this sometime tomorrow afternoon so don't hurry with this. If I'm reading the clock correctly it's past 10pm at your timezone so you could use some rest too. :) Mika
-
Hello again yes you are right it's 10.30 pm now so better to rest and get back to work tomorrow but thank you very much for your help take care :)
-
hello again yes i have tried new quary but still it's not working waiting for your kind rep.
Hi again, What was the output from these (rowcount)?
MsgBox("Original image count in dataset: " & CStr(imageCount))
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If -
Hi again, What was the output from these (rowcount)?
MsgBox("Original image count in dataset: " & CStr(imageCount))
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If -
hello sir how are you in messagebox 1st original image count = 2 2nd msgbox delete not done, rows in datatable = 2 and when i click ok it shows SqlException was unhandled "Incorrect syntax near '*' wanting for your kind rep.
Where is that SqlException coming from? After the "Delete not done..." message box there should be no code to execute (only End If). Another thing, the dataset is defined in the project, am I correct? Could you post the part that selects the images or do you create the SELECT statement?
-
Where is that SqlException coming from? After the "Delete not done..." message box there should be no code to execute (only End If). Another thing, the dataset is defined in the project, am I correct? Could you post the part that selects the images or do you create the SELECT statement?
hello again the code which exacute is as bellow
Dim imageCount As Integer = dsImage.Images.Rows.Count
MsgBox("Original image count in dataset: " & CStr(imageCount))
conImage = GetDBConnection()
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), _
MsgBoxStyle.Information)End Ifand the code where error shows
If commImage.ExecuteNonQuery() <> 1 Then (this is the line)
MsgBox("Delete failed", MsgBoxStyle.Information)
End Ifthis is the part to get image
Private Sub GetImage()
Dim fs As FileStream = New FileStream(mImageFilePath.ToString(), FileMode.Open)
Dim img As Byte() = New Byte(fs.Length) {}
fs.Read(img, 0, fs.Length)
fs.Close()
mImageFile = Image.FromFile(mImageFilePath.ToString())
Dim imgHeight As Integer = picImage.Height 'mImageFile.Height
Dim imgWidth As Integer = picImage.Width 'mImageFile.Width
Dim imgLength As Integer = mImageFile.PropertyItems.Length
Dim imgType As String = Path.GetExtension(mImageFilePath)
mImageFile = Nothing
' image content
Dim pic As SqlParameter = New SqlParameter("@pic", SqlDbType.Image)
pic.Value = img
commImage.Parameters.Add(pic)' title Dim title As SqlParameter = New SqlParameter("@title", System.Data.SqlDbType.VarChar, 50) title.Value = txtTitle.Text.ToString() commImage.Parameters.Add(title) ' type Dim itype As SqlParameter = New SqlParameter("@itype", System.Data.SqlDbType.Char, 4) itype.Value = imgType.ToString() commImage.Parameters.Add(itype) ' height Dim iheight As SqlParameter = New SqlParameter("@iheight", System.Data.SqlDbType.Int) iheight.Value = imgHeight commImage.Parameters.Add(iheight) ' width Dim iwidth As SqlParameter = New SqlParameter("@iwidth", System.Data.SqlDbType.Int) iwidth.Value = imgWidth commImage.Parameters.Add(iwidth) End Subthis is select statment
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
btnSave.Enabled = False
'get sql connection
Dim ImageAdd -
hello again the code which exacute is as bellow
Dim imageCount As Integer = dsImage.Images.Rows.Count
MsgBox("Original image count in dataset: " & CStr(imageCount))
conImage = GetDBConnection()
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), _
MsgBoxStyle.Information)End Ifand the code where error shows
If commImage.ExecuteNonQuery() <> 1 Then (this is the line)
MsgBox("Delete failed", MsgBoxStyle.Information)
End Ifthis is the part to get image
Private Sub GetImage()
Dim fs As FileStream = New FileStream(mImageFilePath.ToString(), FileMode.Open)
Dim img As Byte() = New Byte(fs.Length) {}
fs.Read(img, 0, fs.Length)
fs.Close()
mImageFile = Image.FromFile(mImageFilePath.ToString())
Dim imgHeight As Integer = picImage.Height 'mImageFile.Height
Dim imgWidth As Integer = picImage.Width 'mImageFile.Width
Dim imgLength As Integer = mImageFile.PropertyItems.Length
Dim imgType As String = Path.GetExtension(mImageFilePath)
mImageFile = Nothing
' image content
Dim pic As SqlParameter = New SqlParameter("@pic", SqlDbType.Image)
pic.Value = img
commImage.Parameters.Add(pic)' title Dim title As SqlParameter = New SqlParameter("@title", System.Data.SqlDbType.VarChar, 50) title.Value = txtTitle.Text.ToString() commImage.Parameters.Add(title) ' type Dim itype As SqlParameter = New SqlParameter("@itype", System.Data.SqlDbType.Char, 4) itype.Value = imgType.ToString() commImage.Parameters.Add(itype) ' height Dim iheight As SqlParameter = New SqlParameter("@iheight", System.Data.SqlDbType.Int) iheight.Value = imgHeight commImage.Parameters.Add(iheight) ' width Dim iwidth As SqlParameter = New SqlParameter("@iwidth", System.Data.SqlDbType.Int) iwidth.Value = imgWidth commImage.Parameters.Add(iwidth) End Subthis is select statment
Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
btnSave.Enabled = False
'get sql connection
Dim ImageAddOk, First change the following
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)tocommImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)If you could then rerun the test where you had two images, what is the output now? The deletion wasn't done, because of the syntax error in SQL statement, so we don't know if the deletion works or not -
Ok, First change the following
commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage)tocommImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)If you could then rerun the test where you had two images, what is the output now? The deletion wasn't done, because of the syntax error in SQL statement, so we don't know if the deletion works or nothello with 2 images in database msgbox1 original row count = 2 msgobx2 delete not done , rows in datatable = 2 and then i click second msgbox it shows sql exception on this under line code it shows this error Incorrect syntax near ')'. If commImage.ExecuteNonQuery() <> 1 Then MsgBox("Delete failed", MsgBoxStyle.Information) End If
-
hello with 2 images in database msgbox1 original row count = 2 msgobx2 delete not done , rows in datatable = 2 and then i click second msgbox it shows sql exception on this under line code it shows this error Incorrect syntax near ')'. If commImage.ExecuteNonQuery() <> 1 Then MsgBox("Delete failed", MsgBoxStyle.Information) End If
Yeah, sorry. Instead of:
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)writecommImage = New Data.SqlClient.SqlCommand("DELETE FROM Images", conImage)There was an extra parenthesis at the end of the statement... Then let's try again -
Yeah, sorry. Instead of:
commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage)writecommImage = New Data.SqlClient.SqlCommand("DELETE FROM Images", conImage)There was an extra parenthesis at the end of the statement... Then let's try againhello ok no problem MsbBox1 = original image count = 1 msgbox2 = delete not done,rows in datatable = 1 msgbox3 = delete not done, rows in datatable = 1 and exit sub and then i click again so msgbox1= original image count = 1 msgbox2 = delete not done,rows in datatable = 1 msgbox3 = delete failed msgbox4 = delete not done, rows in datatable = 1 so this is the only difference waiting for your kind rep. thanks
-
hello ok no problem MsbBox1 = original image count = 1 msgbox2 = delete not done,rows in datatable = 1 msgbox3 = delete not done, rows in datatable = 1 and exit sub and then i click again so msgbox1= original image count = 1 msgbox2 = delete not done,rows in datatable = 1 msgbox3 = delete failed msgbox4 = delete not done, rows in datatable = 1 so this is the only difference waiting for your kind rep. thanks
:confused: I am missing it... For some reason seems like Fill is fetching different data... What is the connection used for filling dsImage? Is it the same that is used in deleting Another thing. I don't think we can get much further by posting through CodeProject. Is it possible for you to send me the whole project via e-mail?
-
:confused: I am missing it... For some reason seems like Fill is fetching different data... What is the connection used for filling dsImage? Is it the same that is used in deleting Another thing. I don't think we can get much further by posting through CodeProject. Is it possible for you to send me the whole project via e-mail?
-
hello again yes you are right and i dont mind to email this project and with empty database but size of zip folder is 42 MB so how can i e mail you ? waiting for your rep.
Wow, so big? If there's data in the database, could it be emptied. Pictures are not necessary. Also bin and obj folders are not necessary since they are created by compiler. Would that change the size say less than 5 MB. I think emailing can be done by pressing Email-link in a post instead of Reply. I'll send you a test message..
-
Wow, so big? If there's data in the database, could it be emptied. Pictures are not necessary. Also bin and obj folders are not necessary since they are created by compiler. Would that change the size say less than 5 MB. I think emailing can be done by pressing Email-link in a post instead of Reply. I'll send you a test message..
-
hello I have sent you mail as well so if remove bin then this will cut down up to 26mb but .mdf and log file for database both are 13 mb each but rest files are ok to attach with e mail
if you put the log and the mdf into zip folder is it still 26mb. They should compress quite well. The test message didn't come so I think we cannot use that. Also I don't know if there's any way to use attachments in cp email... Try to send me a little message to address mika dot wendelius at bdb dot fi
-
Hi again, What was the output from these (rowcount)?
MsgBox("Original image count in dataset: " & CStr(imageCount))
...
If (dsImage.Images.Rows.Count > 0) Then
MsgBox("Delete not done, rows in datatable: " & CStr(dsImage.Images.Rows.Count), MsgBoxStyle.Information)
End If