We help IT Professionals succeed at work.

winform update sql not working on real DB but fine in form!

Hi i am making a desktop windows application, students and courses and grades.
i use VS2010 ultimate, windows 8.
the database i used is from within VS2010 (.mdf) i added it as new item to the project (service based DB).

all works fine for select statements and showing data in gridviews and whatnot...
until i tried to update records through a button. after updating it should reflect and show the new values in the datagridview ....

Problem:
update works ok on the winform (provided i have to click update twice dont know why!) and that is while form is open.
when i close the form and re-open it the records shown as the previous ones and old values were not updated.

my code for the button:

   Dim myGridTable As New DataTable()
                conn.Open()
              
                ' i assumed it needs this---not working---> PssDBDataSet.EnforceConstraints = False

                gridAdapter.SelectCommand = New SqlCommand("update enroll set MID = '" & lblRezMid.Text & "' where cid ='" & cidCB.Text & "' ", conn)
                gridAdapter.Fill(myGridTable)

                ' i assumed it needs this---not working--->  gridAdapter.Fill(PssDBDataSet, "Enroll")
                ' i assumed it needs this---not working--->  gridAdapter.Update(PssDBDataSet, "Enroll")

            Catch ex As Exception
                MsgBox(ex.ToString)
            Finally
                If gridAdapter IsNot Nothing Then
                    gridAdapter.Dispose()
                End If

            End Try

Open in new window


there are strings and numbers but like i mentioned, during form operation it shows good interactivity, but changes wont reflect on the real DB when i open it from visual studio (database name pssDB.mdf)

please help!
thanks
Comment
Watch Question

' i assumed it needs this---not working--->  gridAdapter.Update(PssDBDataSet, "Enroll")

Yes, it needs this - how is it not working?
You have to mention update command separately. Refer to below link

http://csharp.net-informations.com/dataadapter/updatecommand-sqlserver.htm

Thanks

Author

Commented:
thanks you guys, well, here is what i did:
@Vadim, i tried as per your confirmation in a brand new clean code with Access DB and works like a charm, but in my app using *.mdf DB the confusing part which i think it is (im not really an expert lol) as i did some reading, i have a dataset made from within vs2010 and im making adapters and datatables that have different datasets! im only guessing.

@srikanthreddyn143, Great piece of code, the one  i had before makes the changes, while im working, i go back and forth between tabs and all data is in place and seems to work well, until i close the app, then ecerything zeros back.
the link you provided made it that even if i close the app, restart it i still have the data in there. it is AMAZING.

BUT; here is the main issue which i hope you experts here can either "explain the issue" or help me find a solution to it,
now everything works fins and data IS saved, but when i open the .mdf DB or preview the data from the  database designer / viewer in VS2010, (my db is called pssDB) i preview from "pssDBDataSet.xsd" whatever changes i made are not in there UNLESS i eneter them manually they stay!!!!?
but i open the app and view the data i can see and manipulate data and gets saved fine no problems.
so, it looks to me like its being saved somewhere or different location or something.

please provide me with helpful explanations/ solutions. and please let me know if it is right to just leave it as it is. or maybe it is suppose to be like that?!!
i googled alot but no luck to me finding info in this regard.

many many thanks.
One possible reason is that your mdf database in the project directory is being copied to the bin directory on every build, overwriting the one that has been already modified by the application. Set property "Copy to output directory" to "if newer" or even "never" and see if it helps.

Author

Commented:
well, ok thanks you guys, literally both your suggestions worked!
unfortunately,  and i promise i changed nothing here, but it worked for  a while then now i place some datagridview and i only see only the data that i entered by hand directly into the mdf table from within VS2010...
then tried again to re-do the Do not Copy option, and when istart debugging it gived me this error:
 "An attempt to attach an auto-named database for file C:\Users\....\...\"

so i will wait for another day or so, do some more reading and all if not i will just accept the solutions given above and open a new question about the latter issue. oh and on a side note, my folder permissions are all ok and full control.

many thanks in advance.

Author

Commented:
and sorry, this is my connstring:

Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\pssDB.mdf;Integrated Security=True;User Instance=True

and the error i get if i set "Do not Copy":

An attempt to attach an auto-named database for file C:\Users\Admin\documents\visual studio 2010\Projects\pss\pss\App_Data\pssDB.mdf failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.

Author

Commented:
thanks