Cannot Update Sqlite table

[nightlyfe]

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?

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

 

[nightlyfe]

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?

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

 

[Smee]

Command.CommandText="SELECT action_name, Action_used FROM Actions WHERE Action_Name=@ActionName"

End Sub[/CODE]

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

 

[nightlyfe]

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.

 

[Erel]

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.

 

[nightlyfe]

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