Database

bapu288929-Aug-08 2:54

29-Aug-08 2:54

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

waiging for your kind rep.

have a nice day

Ashfield29-Aug-08 3:14

Ashfield

29-Aug-08 3:14

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

bapu288929-Aug-08 4:13

29-Aug-08 4:13

hello again

this is the code for add image

btnSave.Enabled = False
     'get sql connection
     Dim ImageAdd As Integer

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

and 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 | :confused:

Wendelius29-Aug-08 7:34

29-Aug-08 7:34

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 ImagesTableAdapter and daImage?
- 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

bapu288929-Aug-08 7:57

29-Aug-08 7:57

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

waiting for your kind rep.

and thanks again

Wendelius29-Aug-08 8:32

29-Aug-08 8:32

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

   MsgBox("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 Sub

What do you get as result (information in messageboxes)?

bapu288929-Aug-08 9:50

29-Aug-08 9:50

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 If

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

Wendelius29-Aug-08 10:17

29-Aug-08 10:17

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?

bapu288929-Aug-08 10:57

29-Aug-08 10:57

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.

Wendelius29-Aug-08 11:15

29-Aug-08 11:15

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 If

I'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. Smile | :)

Mika

bapu288929-Aug-08 11:25

29-Aug-08 11:25

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 Smile | :)

bapu288930-Aug-08 4:22

30-Aug-08 4:22

hello again

yes i have tried new quary but still it's not working

waiting for your kind rep.

Wendelius30-Aug-08 4:31

30-Aug-08 4:31

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

bapu288930-Aug-08 4:58

30-Aug-08 4:58

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.

Wendelius30-Aug-08 5:07

30-Aug-08 5:07

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?

bapu288930-Aug-08 5:20

30-Aug-08 5:20

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 As Integer
      Try
         conImage = GetDBConnection()
         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()
         Me.ImagesTableAdapter.ClearBeforeFill = True
         Me.ImagesTableAdapter.Fill(Me.dsImage.Images)
         MessageBox.Show(ImageAdd.ToString & "  Image successfuly saved in database", _
         "Image Load")
     Catch ex As Exception
         MsgBox(ex.Message)

     End Try
     commImage.Dispose()
     commImage = Nothing
     conImage.Close()
     conImage.Dispose()
     Call LoadImages()
 End Sub

and yes I drag and drop data adaptor and then generate dataset

Wendelius30-Aug-08 5:25

30-Aug-08 5:25

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

bapu288930-Aug-08 5:34

30-Aug-08 5:34

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

Wendelius30-Aug-08 5:39

30-Aug-08 5:39

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

bapu288930-Aug-08 5:45

30-Aug-08 5:45

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

Wendelius30-Aug-08 6:04

30-Aug-08 6:04

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?

bapu288930-Aug-08 6:13

30-Aug-08 6:13

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.

Wendelius30-Aug-08 6:21

30-Aug-08 6:21

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

bapu288930-Aug-08 6:34