Cannot Update Sqlite table

Discussion in 'Questions (Windows Mobile)' started by nightlyfe, Oct 9, 2009.

  1. nightlyfe

    nightlyfe Member

    Hello. I'm having difficulty with an update statement I was hoping someone could help me with. When I run the following code, all the data seems to follow through the variables like it's supposed to (I checked by inserting a msgbox at the end), but the data simply doesn't appear in my table. What am I missing here?

    Code:
    Sub Chkbx_click


       basestring = 
    Sender
       newstring = StrReplace(basestring,
    "chkbx","")
       newactionname = 
    Control("btn" & newstring).text
          
          
    If Control("chkbx" & newstring).checked = True Then
              newactionused = 
    "True"
              
    Msgbox(newactionname & " " & newactionused)
          
    Else newactionused = "False"
          
    End If   
       
       Connection.BeginTransaction
       
       Command.AddParameter(
    "ActionName")
       Command.SetParameter(
    "ActionName", newActionname)
       Command.AddParameter(
    "ActionUsed")
       Command.SetParameter(
    "ActionUsed", newActionused)

       Command.CommandText = 
    "UPDATE Actions SET Action_Used=@ActionUsed WHERE Action_name=@ActionName"
       
       Command.ExecuteNonQuery
       
       Connection.EndTransaction

    End Sub
    I'm attaching the whole program so if someone wants to take a stab at it they can. What's supposed to happen is that when a checkbox is clicked on the Actions form the database updates action_used to true.

    Thanks to any and all for looking at it.

    -David
     
  2. nightlyfe

    nightlyfe Member

    I updated the code for testing...

    So it now displays a msg box at the end with the values from the table.
    They appear correctly up until the point when the program is closed, and then everything reverts to its original value. Is there a way to save changes to table that I'm missing?

    Code:
    Sub Chkbx_click


       basestring = 
    Sender
       newstring = StrReplace(basestring,
    "chkbx","")
       newactionname = 
    Control("btn" & newstring).text
          
          
    If Control("chkbx" & newstring).checked = True Then
              newactionused = 
    "True"
          
    Else newactionused = "False"
          
    End If   
       
       Connection.BeginTransaction
       
       Command.AddParameter(
    "ActionName")
       Command.SetParameter(
    "ActionName", newActionname)
       Command.AddParameter(
    "ActionUsed")
       Command.SetParameter(
    "ActionUsed", newActionused)
       
       Command.CommandText = 
    "UPDATE Actions SET Action_Used=@ActionUsed WHERE Action_name=@ActionName"
       
       Command.ExecuteNonQuery
       
       Command.CommandText=
    "SELECT action_name, Action_used FROM Actions WHERE Action_Name=@ActionName"
       
    'Fetch data
       reader.New1
       Reader.Value = Command.ExecuteReader
       
       Connection.EndTransaction
       
    Msgbox(reader.GetValue(0)& " " & reader.GetValue(1))
       
       reader.Close

       
    End Sub
     
  3. Smee

    Smee Well-Known Member Licensed User

    Look i' am still only a noob at this language myself but in previous experience with sql the quotes would be closed earlier than they are;
    Something like

    WHERE Action_Name='" & @ActionName & "'"

    i am not sure of the syntax yet but i would think that the ActionName would be outside of the quote.

    Hope this helps

    Good Luck

    Joe
     
  4. nightlyfe

    nightlyfe Member

    I'm not sure exactly why, but it's working now. Same SQL string, but it's functioning. The only thing I changed in the code was moving a sub call (countme) from App_Start to the top of the posted sub...


    Beats the heck out of me why that worked. It really does.
     
  5. Erel

    Erel Administrator Staff Member Licensed User

    The problem is probably in sub contem. It misses an EndTransaction.

    There is no need to use StartTransaction/EndTransaction in every query.
    It is only useful (=faster) when you do batch updates.
     
  6. nightlyfe

    nightlyfe Member

    Thank you!

    That was definitely it. When I moved countem I corrected the end transaction without really thinking about it, so that explains why it started working. ::eyeroll::

    Thank you so much for taking the time to look at it!

    -David
     
Loading...
  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice