Save Image in SQL Database

bapu2889

hello again yes i have tried new quary but still it's not working waiting for your kind rep.

Wendelius

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

bapu2889

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.

Wendelius

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?

bapu2889

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 If

and the code where error shows

If commImage.ExecuteNonQuery() <> 1 Then (this is the line)
MsgBox("Delete failed", MsgBoxStyle.Information)
End If

this 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 Sub

this 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

Wendelius

Ok, First change the following commImage = New Data.SqlClient.SqlCommand("DELETE * FROM Images)", conImage) to commImage = 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

bapu2889

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

Wendelius

Yeah, sorry. Instead of: commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images)", conImage) write commImage = New Data.SqlClient.SqlCommand("DELETE FROM Images", conImage) There was an extra parenthesis at the end of the statement... Then let's try again

bapu2889

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

Wendelius

: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?

bapu2889

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.

Wendelius

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..

bapu2889

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

Wendelius

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

bapu2889

Hi Mika It's done now and it's working fine so thanks for your help to solve this issue and teaching me a lot :-D :rose:

Wendelius

You're welcome :)