locked
Update field in db RRS feed

  • Question

  • User1977720592 posted

    I am facing problem in my update statement, can anyone help me. Please help me to fix it, thank you.

     

    Dim abc As String

            abc = Request.QueryString("str")

            Dim userName As String = CStr(Session("user"))

     

            Label2.Text = userName

     

            Dim MyConnection As New OdbcConnection(ConnStr)

            MyConnection.Open()

            Dim MyCommand As New OdbcCommand

            MyCommand.Connection = MyConnection

     

            If MyCommand.CommandText = "Select userid from week where userid ='" & userName & "'" Then

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

            Else

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            End If

     

     

            MyCommand.ExecuteNonQuery()

            MyConnection.Close()

    Sunday, March 13, 2011 11:52 PM

Answers

  • User1977720592 posted

    I have get the solution by using ExecuteReader to do this. Below is my coding

    Dim ConnStr As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=calendar;uid=root;option=3"

     

            Dim abc As String

            abc = Request.QueryString("str")

            Dim userName As String = CStr(Session("user"))

     

            Label2.Text = userName

     

            Dim MyConnection As New OdbcConnection(ConnStr)

            MyConnection.Open()

            Dim MyCommand As New OdbcCommand

     

            Dim name As String

            name = "Select userid from week where userid ='" & userName & "'"

            MyCommand = New OdbcCommand(name, MyConnection)

            Dim check As OdbcDataReader

            check = MyCommand.ExecuteReader

            If Not check.HasRows Then

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            Else

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

     

            End If

     

            check.Close()

            MyCommand.ExecuteNonQuery()

            MyConnection.Close()

    Anyway, thank you Jerry and Bhaarat give suggestion how to do.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 8:56 AM
  • User-821857111 posted

    I have get the solution by using ExecuteReader to do this
    Here's how to do it properly with parameters:

    Dim ConnStr As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=calendar;uid=root;option=3"
    Dim abc As String
    abc = Request.QueryString("str")
    Dim userName As String = CStr(Session("user"))
     
    Label2.Text = userName
     
    Dim MyConnection As New OdbcConnection(ConnStr)
    MyConnection.Open()
    Dim MyCommand As New OdbcCommand
    Dim name As String
    name = "Select userid from week where userid = ?"
    MyCommand = New OdbcCommand(name, MyConnection)
    MyCommand.Parameters.AddwithValue("", userName)
    Dim check As OdbcDataReader
    check = MyCommand.ExecuteReader
          
    MyCommand.Parameters.Clear()
    MyCommand.Parameters.AddWithValue("", abc)
    MyCommand.Parameters.AddWithValue("", username)
    
    If Not check.HasRows Then
        MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES(?,?)"
    Else
        MyCommand.CommandText = "Update week Set value = ? where userid = ?"
    End If
    check.Close()
    MyCommand.ExecuteNonQuery()
    MyConnection.Close()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 3:11 PM

All replies

  • User-821857111 posted

    MyCommand.CommandText will never be "Select userid from week where userid ='" & userName & "'". How can it be?

    By the way, you must use parameters: http://www.mikesdotnetting.com/Article/113/Preventing-SQL-Injection-in-ASP.NET

     

    Monday, March 14, 2011 1:22 AM
  • User1977720592 posted

    Thank you, now I save my query in a string, but how can I check if it is exist in my database? I have to write as below:

    Dim query As String

            query = "Select * from week where userid = '" & userName & "'"

     

            If query Then

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

            Else

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            End If

    But the coding is still functioning, anyone can help me?

    Monday, March 14, 2011 9:42 AM
  • User-610330605 posted

     

    Thank you, now I save my query in a string, but how can I check if it is exist in my database? I have to write as below:

     

    Dim query As String

     

            query = "Select * from week where userid = '" & userName & "'"

     

            If query Then

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

            Else

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            End If

    But the coding is still functioning, anyone can help me?

     

    Paste the error

    Also use parameterized query as suggested by Mike

    Monday, March 14, 2011 9:49 AM
  • User1977720592 posted

    Actually I am not understand how to use parameter, and I try write as below:

    Dim abc As String

            abc = Request.QueryString("str")

            Dim userName As String = CStr(Session("user"))

     

            Label2.Text = userName

     

            Dim MyConnection As New OdbcConnection(ConnStr)

            MyConnection.Open()

            Dim MyCommand As New OdbcCommand

            MyCommand.Connection = MyConnection

            Dim ccc As String

            ccc = "Select userid from week where userid = '" & userName & "'"

     

            If ccc = "userName" Then

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

            Else

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            End If

     

     

            MyCommand.ExecuteNonQuery()

            MyConnection.Close()

    But the function not running for the update statement, hope anyone can help me.

    Monday, March 14, 2011 10:14 AM
  • User-610330605 posted

    I see strange code. Can you explain what you are trying to do in the lines below?

            Dim ccc As String

            ccc = "Select userid from week where userid = '" & userName & "'"

             If ccc = "userName" Then

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

            Else

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            End If

    I don't think your code will ever enter the If block for the update statement. It will always go to the Else block.

    Monday, March 14, 2011 10:20 AM
  • User1977720592 posted

    You are correct, the function just will go to the else statement.

    The ccc query is for me to check whether there is userid in my week table.

    The if statement I am not sure how to write, and I just simply write it. Actually my if statement is to check my userid in week table whether match with the userName. Can you tell me how should I write for my if statement? 

    Monday, March 14, 2011 10:25 AM
  • User1983487377 posted

    I see strange code. Can you explain what you are trying to do in the lines below?

    i think he is tring to Check if record exists in  ccc = "Select userid from week where userid = '" & userName & "'" Query

    and accordingly he want to execute rest two queries depending on conditions

     

    so he shld execute all queries to get output before impolrementing

    in this case i suggest to use Stored Procedure..

    please try

    IF EXISTS (SELECT * FROM TableName WHERE PID = @Id)
    BEGIN
        --UPDATE HERE
    END
    ELSE
    BEGIN
       -- INSERT HERE
    END
    
    http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx

     

    All the Best

    Monday, March 14, 2011 10:41 AM
  • User-610330605 posted

     

    You are correct, the function just will go to the else statement.

    The ccc query is for me to check whether there is userid in my week table.

    The if statement I am not sure how to write, and I just simply write it. Actually my if statement is to check my userid in week table whether match with the userName. Can you tell me how should I write for my if statement? 

     

    First things first. Always give a sensible name to your variable. Otherwise you will confuse yourself and everyone else.

    Follow the steps below

    - Declare the SELECT query to check if username exists

    - Use ExecuteScalar to execute the query

    - Then use the value which you get from ExecuteScalar in your IF condition

    Monday, March 14, 2011 10:47 AM
  • User1977720592 posted

    Bhaarat, I need something like that. But now I am doing in vb code, its not allow me to write IF EXISTS(Query). In vb coding how should I write?

    JerryJoseph, thanks for your suggestion and for my following word I will give a sensible name

    Monday, March 14, 2011 11:17 AM
  • User-610330605 posted

    Bhaarat, I need something like that. But now I am doing in vb code, its not allow me to write IF EXISTS(Query). In vb coding how should I write?

    You cannot Check like IF EXISTS(query). You have to get the result of running the select query by using ExecuteScalar. Then you have to do the check.

    Monday, March 14, 2011 11:25 AM
  • User1983487377 posted

    create a Stored procedure for that and write that code in Stored Procedure

    and follow the link http://www.c-sharpcorner.com/UploadFile/gtomar/storedprocedure12052007003126AM/storedprocedure.aspx

     

    Thought it is in C# you can canvert it to Vb using

    http://converter.telerik.com/

    All the Best

    Monday, March 14, 2011 11:34 AM
  • User1977720592 posted

    Because I am new in vb, and I found some coding in the internet about ExecuteScalar, but I do not know how to apply it. Can anyone show me how to write the coding?

    Tuesday, March 15, 2011 3:45 AM
  • User-821857111 posted

    I noticed you are using an OdbcConnection. What database are you working with?

     

    Tuesday, March 15, 2011 4:12 AM
  • User1977720592 posted

    I am using mySql as my database, MySQL Workbench 5.2 CE.

    Tuesday, March 15, 2011 6:21 AM
  • User1983487377 posted

    try to understand the link i provided, it is good for novide users...

    and it surely fulfil your requirements..

     

    and if possible do learn Stored Procedure... it is better way of your problem..

    All The Best

    Tuesday, March 15, 2011 7:36 AM
  • User1977720592 posted

    I have get the solution by using ExecuteReader to do this. Below is my coding

    Dim ConnStr As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=calendar;uid=root;option=3"

     

            Dim abc As String

            abc = Request.QueryString("str")

            Dim userName As String = CStr(Session("user"))

     

            Label2.Text = userName

     

            Dim MyConnection As New OdbcConnection(ConnStr)

            MyConnection.Open()

            Dim MyCommand As New OdbcCommand

     

            Dim name As String

            name = "Select userid from week where userid ='" & userName & "'"

            MyCommand = New OdbcCommand(name, MyConnection)

            Dim check As OdbcDataReader

            check = MyCommand.ExecuteReader

            If Not check.HasRows Then

                MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES('" & abc & "','" & userName & "')"

            Else

                MyCommand.CommandText = "Update week Set value = '" & abc & "' where userid ='" & userName & "'"

     

            End If

     

            check.Close()

            MyCommand.ExecuteNonQuery()

            MyConnection.Close()

    Anyway, thank you Jerry and Bhaarat give suggestion how to do.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 8:56 AM
  • User1983487377 posted

    all right... congrates...

    you got solution.... but this is not prefered way.. anyways Good luck..

     

    Wednesday, March 16, 2011 1:00 PM
  • User-821857111 posted

    I have get the solution by using ExecuteReader to do this
    Here's how to do it properly with parameters:

    Dim ConnStr As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=calendar;uid=root;option=3"
    Dim abc As String
    abc = Request.QueryString("str")
    Dim userName As String = CStr(Session("user"))
     
    Label2.Text = userName
     
    Dim MyConnection As New OdbcConnection(ConnStr)
    MyConnection.Open()
    Dim MyCommand As New OdbcCommand
    Dim name As String
    name = "Select userid from week where userid = ?"
    MyCommand = New OdbcCommand(name, MyConnection)
    MyCommand.Parameters.AddwithValue("", userName)
    Dim check As OdbcDataReader
    check = MyCommand.ExecuteReader
          
    MyCommand.Parameters.Clear()
    MyCommand.Parameters.AddWithValue("", abc)
    MyCommand.Parameters.AddWithValue("", username)
    
    If Not check.HasRows Then
        MyCommand.CommandText = "INSERT INTO week (value, userid) VALUES(?,?)"
    Else
        MyCommand.CommandText = "Update week Set value = ? where userid = ?"
    End If
    check.Close()
    MyCommand.ExecuteNonQuery()
    MyConnection.Close()

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, March 16, 2011 3:11 PM