Answered by:
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()
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 thisHere'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.aspxAll 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
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 thisHere'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