Update field in db

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()

Answer 1

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

 

Answer 2

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?

Answer 3

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

Answer 4

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.

Answer 5

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.

Answer 6

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? 

Answer 7

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

Answer 8

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

Answer 9

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

Answer 10

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.

Answer 11

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

Answer 12

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?

Answer 13

User-821857111 posted

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

 

Answer 14

User1977720592 posted

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

Answer 15

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

Answer 16

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.

Answer 17

User1983487377 posted

all right... congrates...

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

 

Answer 18

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()