none
vb express not updating local database

    Question

  • hi guys,

    i'm using a copy of visual basic express for my home projects. i'm just started one that is needing a local database, so i added one to the project and created the tables with with colums no problems.

    i created an 'Admin' user straight into the database and use that to log in. i have also created an a form so that you can add other users and view existing users in a datagrid view.

    the original source code i used was supplied in on this forum page:

    http://social.msdn.microsoft.com/forums/en/vbgeneral/thread/37bb8421-4d7b-4e70-a9a6-f19ec4ee3765/

    its all well and good, and works fine, right up to the point when i shut the program down and then re-open it the next day, all the users i created the previous day arent there anymore.

    the code for creating the users is this :

     Dim con As SqlCeConnection = New SqlCeConnection(My.Settings.Database1ConnectionString)
           
     con.Open()
            Dim access As String = ""
            Select Case cbxSecurity.Text
                Case "Administrator"
                    access = "Admin"
                Case "Supervisor"
                    access = "Super"
                Case "User"
                    access = "User"
            End Select
            Dim sqlstring As String
            If cbPasswordReset.Checked = True Then
                sqlstring = "INSERT INTO Users(f_name, s_name, Level, pass, pass_reset, user_n) VALUES('" & tbF_name.Text _
                    & "', '" & tbS_name.Text & "', '" & access & "', '" & tbPass1.Text & "', '1', '" & tbUsername.Text & "')"
            Else
                sqlstring = "INSERT INTO Users(f_name, s_name, Level, pass, pass_reset, user_n) VALUES('" & tbF_name.Text _
                    & "', '" & tbS_name.Text & "', '" & access & "', '" & tbPass1.Text & "', '0', '" & tbUsername.Text & "')"
            End If
            Dim tran As SqlCeTransaction = con.BeginTransaction(IsolationLevel.ReadCommitted)
            Dim cmd As SqlCeCommand = con.CreateCommand
            cmd.Transaction = tran
            Try
                cmd.CommandText = sqlstring
                cmd.ExecuteNonQuery()
                tran.Commit()
            Catch ex As Exception
                MessageBox.Show("failed to commit", "", MessageBoxButtons.OK, MessageBoxIcon.Information)
                tran.Rollback()
            End Try
            con.Close()
            con = Nothing
    any help with this is greatly appreciated as ive been wrestling with this for a couple of days
    Tuesday, March 13, 2012 10:04 PM

Answers

  • hi cor,

    thanks for the reply.

    but you have to remember, some of us are still learning and might not appreciate being told to 'make first your code a more reasonable. It does pain to my eyes'. plus before you critisize someones code, make sure you have all the relevant details.

    1. when i inserted the code block to create the post it reformatted the code and omitted a lot of my line spaces that i had in place.

    2. i dont see why i should create sql variables, write my values into them and then have them passed to my string, when its a lot less code to have my values coded straight into the string, as what you wrote in your code makes no difference to how the project works.

    3. as this is a personal project it will only be installed on a personal computer, and as such only one person will ever be accessing the database file at any given point, therefore it doesnt matter if my transaction blocks off another user. and i only had that in there to try and force the update/insert to save to the database.

    as it turns out, the solution also has nothing to do with my connection either, as i found out on this forum page :-

    http://social.msdn.microsoft.com/forums/en-us/Vsexpressinstall/thread/AC13D33F-63C3-4D5E-9881-F48B670B1717

    as i said, thank you for your reply, and im not ungrateful for you trying to help. but common curtesy doesnt cost anything but it goes a long way.

    • Marked as answer by morgannus Wednesday, March 14, 2012 8:57 PM
    Wednesday, March 14, 2012 8:57 PM

All replies

  • Hello, 

    The problem you can find in your connection string, but make first your code a more reasonable. It does pain to my eyes. 

    For instance the transaction you need if you are doing in one transaction more than one command, however, you do only one. This means that you senseless lock the server.

    Also use paramters and make your code more from today.

    Be aware you have to incorporate this bellow the End Select and to add some things yourself.

    Using con As New SqlCeConnection(My.Settings.Database1ConnectionString) Dim sqlstring = "INSERT INTO Users(f_name, s_name, Level, pass, pass_reset, user_n) VALUES(@f_name, @s_name, level, pass_reset, user_n)" Using cmd As New SqlCECommand(sqlstring, con) cmd.Parameters.AddWithValue("@f_name", tbf_name.Text) 'this for all the parameters and then for level reset If cbPasswordReset.Checked = True Then cmd.Parameters.AddWithValue("@pass_reset", False) Else cmd.Parameters.AddWithValue("@pass_reset", True) End If Try

    con.open cmd.ExecuteNonQuery() Catch ex As Exception MessageBox.Show("failed to commit", "", MessageBoxButtons.OK, MessageBoxIcon.Information) End Try End Using End Using


    For your connection problem go to Project -> Project Properties -> Setting and look what the connectionstring is, if that is a fancy one for debugging time and release time, than I would make in your case simple one from that, this extra has in my opinion only made it more difficult for new users than to make it easier where it was intended for.


    Success
    Cor





    Wednesday, March 14, 2012 9:25 AM
  • hi cor,

    thanks for the reply.

    but you have to remember, some of us are still learning and might not appreciate being told to 'make first your code a more reasonable. It does pain to my eyes'. plus before you critisize someones code, make sure you have all the relevant details.

    1. when i inserted the code block to create the post it reformatted the code and omitted a lot of my line spaces that i had in place.

    2. i dont see why i should create sql variables, write my values into them and then have them passed to my string, when its a lot less code to have my values coded straight into the string, as what you wrote in your code makes no difference to how the project works.

    3. as this is a personal project it will only be installed on a personal computer, and as such only one person will ever be accessing the database file at any given point, therefore it doesnt matter if my transaction blocks off another user. and i only had that in there to try and force the update/insert to save to the database.

    as it turns out, the solution also has nothing to do with my connection either, as i found out on this forum page :-

    http://social.msdn.microsoft.com/forums/en-us/Vsexpressinstall/thread/AC13D33F-63C3-4D5E-9881-F48B670B1717

    as i said, thank you for your reply, and im not ungrateful for you trying to help. but common curtesy doesnt cost anything but it goes a long way.

    • Marked as answer by morgannus Wednesday, March 14, 2012 8:57 PM
    Wednesday, March 14, 2012 8:57 PM