Problem updating record, with Using and OLEDB RSS

• • Reply
  

  siraero

  Member

  419 Points

  604 Posts

  Problem updating record, with Using and OLEDB

  May 07, 2012 09:36 PM|LINK

  Hi
  I cant get this to work, im getting a Value missing error for the updatecmd.ExecuteNonQuery()

  And when i look in Locals in MS VWD2010E then i have this strSQL
  "UPDATE news_users SET newsuser_status = ? WHERE newsuser_pass =bd1a4f04"

  And the record is in the DB and its like i write it, can someone help me !?
  
  Code is

  Dim strSQL As String = ""
          strSQL = "" & _
          "UPDATE news_users " & _
          "SET newsuser_status = ?" & _
          "WHERE newsuser_pass =" & TextBox2.Text & ""
  
          Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("MasterConnStr").ConnectionString)
              Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
                  updatecmd.CommandType = CommandType.Text
                  updatecmd.Parameters.AddWithValue("newsuser_status", 1)
                  updatecmd.Connection.Open()
                  updatecmd.ExecuteNonQuery()
              End Using
          End Using

  
  
  

• • Reply
  

  ruipedromach...

  Member

  258 Points

  84 Posts

  Re: Problem updating record, with Using and OLEDB

  May 07, 2012 10:18 PM|LINK

  Dim strSQL As String = ""
          strSQL = "" & _
          "UPDATE news_users " & _
          "SET newsuser_status = @newsuser_status " & _
          "WHERE newsuser_pass =" & TextBox2.Text & ""
   
          Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("MasterConnStr").ConnectionString)
              Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
                  updatecmd.CommandType = CommandType.Text
                  updatecmd.Parameters.AddWithValue("@newsuser_status", "1")
                  updatecmd.Connection.Open()
                  updatecmd.ExecuteNonQuery()
              End Using
          End Using

  RuiMachado
• • Reply
  

  hans_v

  All-Star

  35986 Points

  6550 Posts

  Re: Problem updating record, with Using and OLEDB

  May 08, 2012 08:51 AM|LINK

  String values need to be enclosed with single quotes, so the result should be:

  "UPDATE news_users SET newsuser_status = ? WHERE newsuser_pass ='bd1a4f04'"

  so the where should be:

  "WHERE newsuser_pass ='" & TextBox2.Text & "'"

  But you already use a parameter, the better option is to always use parameters, to avoid sql injections!

  http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

  Dim strSQL As String = ""
          strSQL = "" & _
          "UPDATE news_users " & _
          "SET newsuser_status = @newsuser_status" & _
          "WHERE newsuser_pass = @newsuser_pass"
   
          Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("MasterConnStr").ConnectionString)
              Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
                  updatecmd.CommandType = CommandType.Text
                  updatecmd.Parameters.AddWithValue("newsuser_status", 1)
                  updatecmd.Parameters.AddWithValue("newsuser_pass", TextBox2.Text )
                  updatecmd.Connection.Open()
                  updatecmd.ExecuteNonQuery()
              End Using
          End Using

   

  • Edited by hans_v on May 08, 2012 08:52 AM
  • Edited by hans_v on May 08, 2012 08:54 AM
  • Marked as answer by siraero on May 12, 2012 06:24 PM
• • Reply
  

  siraero

  Member

  419 Points

  604 Posts

  Re: Problem updating record, with Using and OLEDB

  May 08, 2012 10:25 AM|LINK

  hans_v

  String values need to be enclosed with single quotes, so the result should be:

  "UPDATE news_users SET newsuser_status = ? WHERE newsuser_pass ='bd1a4f04'"

  so the where should be:

  "WHERE newsuser_pass ='" & TextBox2.Text & "'"

  But you already use a parameter, the better option is to always use parameters, to avoid sql injections!

  http://www.mikesdotnetting.com/Article/26/Parameter-Queries-in-ASP.NET-with-MS-Access

  Dim strSQL As String = ""
          strSQL = "" & _
          "UPDATE news_users " & _
          "SET newsuser_status = @newsuser_status" & _
          "WHERE newsuser_pass = @newsuser_pass"
   
          Using connection As OleDbConnection = New OleDbConnection(ConfigurationManager.ConnectionStrings("MasterConnStr").ConnectionString)
              Using updatecmd As OleDbCommand = New OleDbCommand(strSQL, connection)
                  updatecmd.CommandType = CommandType.Text
                  updatecmd.Parameters.AddWithValue("newsuser_status", 1)
                  updatecmd.Parameters.AddWithValue("newsuser_pass", TextBox2.Text )
                  updatecmd.Connection.Open()
                  updatecmd.ExecuteNonQuery()
              End Using
          End Using

   

  Hi Hans_V

  Thx again

  One question if i type the wrong newsuser_pass, how/where do i then put in a handel error that response.write("Sry u dident type the right Password") !?

• • Reply
  

  tusharrs

  Contributor

  3230 Points

  668 Posts

  Re: Problem updating record, with Using and OLEDB

  May 08, 2012 10:32 AM|LINK

  int i = (int)updatecmd.ExecuteNonQuery();

  if (i==0)

  {

  // here you can give the user mesage that wrong password is entered

  }

  Regards
  
  ( Mark as Answer if it helps you out )
  
  View my Blog
  • Marked as answer by decker dong - msft on May 13, 2012 01:51 AM