locked
Problem updating record, with Using and OLEDB RRS feed

  • Question

  • User651828378 posted

    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



    Monday, May 7, 2012 5:36 PM

Answers

  • User-1199946673 posted

    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 
    
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2012 4:51 AM
  • User-1965752605 posted

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

    if (i==0)

    {

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

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2012 6:32 AM

All replies

  • User1013750657 posted
    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

    Monday, May 7, 2012 6:18 PM
  • User-1199946673 posted

    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 
    
    

     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2012 4:51 AM
  • User651828378 posted

    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") !?

    Tuesday, May 8, 2012 6:25 AM
  • User-1965752605 posted

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

    if (i==0)

    {

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

    }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, May 8, 2012 6:32 AM