-
Sep 5th, 2011, 05:14 AM
#1
Thread Starter
Fanatic Member
[RESOLVED] UPDATE or INSERT advice.
Good day!
This is not a real problem because I have an alternative solution but more like I need advice.
Code:
sql = "UPDATE tblPoints(DateUploaded, TimeUploaded, Agents, Points) Set DateUploaded = '" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "' WHERE Agents = '" & column2 & "'"
sql = "INSERT INTO tblPoints(DateUploaded, TimeUploaded, Agents, Points) VALUES('" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "')"
I have two sql's here, the "INSERT" and the "UPDATE". The reason why I have two of them is because the "UPDATE" will reach the EOF value because it couldn't find the name of the "Agent". If it became into EOF, it will automatically go to the "INSERT" statement meaning it will insert a new record.
I can do this by using the "IF" condition like this:
Code:
sql = "UPDATE tblPoints(DateUploaded, TimeUploaded, Agents, Points) Set DateUploaded = '" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "' WHERE Agents = '" & column2 & "'"
rs.Open sql, cnx, adOpenDynamic, adLockOptimistic
If rs.EOF = True Then
If rs.State = 1 Then
rs.Close
End If
sql = "INSERT INTO tblPoints(DateUploaded, TimeUploaded, Agents, Points) VALUES('" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "')"
rs.Open sql, cnx, adOpenDynamic, adLockOptimistic
End If
Now for my question, are there any other ways to do this?
I'm open to all suggestions from you guys.
Manny Pacquiao once posted in his twitter:
It doesn't matter if the grammar is wrong, what matter is that you get the message
-
Sep 5th, 2011, 05:44 AM
#2
Re: UPDATE or INSERT advice.
Not to be a nuisance but would this be better?
Code:
sql = "UPDATE tblPoints(DateUploaded, TimeUploaded, Agents, Points) Set DateUploaded = '" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "' WHERE Agents = '" & column2 & "'"
rs.Open sql, cnx, adOpenDynamic, adLockOptimistic
If rs.EOF = True AND rs.State = 1 Then
rs.Close
else
sql = "INSERT INTO tblPoints(DateUploaded, TimeUploaded, Agents, Points) VALUES('" & DTPicker1.Value & "', '" & Format$(Time, "HH:mm") & "', '" & column2 & "', '" & column4 & "')"
rs.Open sql, cnx, adOpenDynamic, adLockOptimistic
End If
Also, I have requested the thread moved to the Database Development section.
when you quote a post could you please do it via the "Reply With Quote" button or if it multiple post click the "''+" button then "Reply With Quote" button.
If this thread is finished with please mark it "Resolved" by selecting "Mark thread resolved" from the "Thread tools" drop-down menu.
https://get.cryptobrowser.site/30/4111672
-
Sep 5th, 2011, 05:52 AM
#3
Re: UPDATE or INSERT advice.
Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums) (thanks Nightwalker83 )
You should not use a recordset (which is meant for getting records from the database into your program) to execute an action statement (update/delete/etc). For action statements you should use the .Execute method of a Connection or Command, eg:
Code:
cnx.Execute sql, , adCmdText + adExecuteNoRecords
This not only takes less code (and memory/network resources/etc), but also runs a bit faster, and allows you to easily check how many records have been altered (by using a Long variable between the two commas).
Note that this code is very unreliable, and is likely to cause errors in your data:
DateUploaded = '" & DTPicker1.Value & "',
For an explanation the way it should be, see the article How do I use values (numbers, strings, dates) in SQL statements? from our Database Development FAQs/Tutorials (at the top of this forum)
-
Sep 5th, 2011, 07:00 AM
#4
Thread Starter
Fanatic Member
Re: UPDATE or INSERT advice.
@Nightwalker83
Thanks for the correction on my codes.
@Si the Geek
Sorry for posting it in the wrong section and thanks for the advice.
Years ago when I was still college and learning VB6, I was having some problems with regards to Date and Time in the database so normally I just change the field type value of date and time with a varchar.
I can't seem to remember what the errors were but I think that it regards to inserting the date and time into the database and some of them I think was retrieving it.
Again, thanks for the advice.
Manny Pacquiao once posted in his twitter:
It doesn't matter if the grammar is wrong, what matter is that you get the message
-
Sep 5th, 2011, 07:05 AM
#5
Thread Starter
Fanatic Member
Re: UPDATE or INSERT advice.
@Nightwalker
I remembered something.
Code:
Do Until (EOF(1) = True)
Input #1, column1, column2, column3, column4
If a > 1 Then
sql = "UPDATE tblPoints(DateUploaded, TimeUploaded, Agents, Points) Set DateUploaded = " & DTPicker1.Value & ", " & Format$(Time, "HH:mm") & ", '" & column2 & "', '" & column4 & "' WHERE Agents = '" & column2 & "'"
rs.Open "UPDATE tblPoints(DateUploaded, TimeUploaded, Agents, Points) Set DateUploaded = " & DTPicker1.Value & ", " & Format$(Time, "HH:mm") & ", '" & column2 & "', '" & column4 & "' WHERE Agents = '" & column2 & "'", cnx, adOpenDynamic, adLockOptimistic
If rs.EOF = True And rs.State = 1 Then
sql = "INSERT INTO tblPoints(DateUploaded, TimeUploaded, Agents, Points) VALUES(" & DTPicker1.Value & ", " & Format$(Time, "HH:mm") & ", '" & column2 & "', '" & column4 & "')"
rs.Open sql, cnx, adOpenDynamic, adLockOptimistic
End If
If rs.State = 1 Then
rs.Close
End If
End If
a = a + 1
Loop
This is my entire code. As you can see, it is under a loop and the reason why I separated and place an IF condition at rs.state is because I think it would cause an error if rs.state is still open.
And since the code is under the loop, if I use the code you gave me then if the rs.state is close or 0 then I think that it would not execute properly.
Manny Pacquiao once posted in his twitter:
It doesn't matter if the grammar is wrong, what matter is that you get the message
-
Sep 5th, 2011, 07:19 AM
#6
Re: UPDATE or INSERT advice.
Switching to Varchar has not eliminated the problems, it has just made them less obvious. There is a massive flaw in your code that means you will read/write the wrong values if a Windows setting (outside your program and database) is changed.
In addition to that, using text based dates requires more storage space, and generally requires more code to deal with it appropriately too.
Even if you don't use a DateTime based data type, you should use formatting.
-
Sep 5th, 2011, 07:27 AM
#7
Thread Starter
Fanatic Member
Re: UPDATE or INSERT advice.
@Si the geek
I guess your right. I think I should be using this professionally because they put a field type of DateTime to put the date and time.
Anyway, in case I do have problems in the future with regards to date and time, if I post my problem on VBForums I know that a lot of you guys will help me out.
Thanks.
Manny Pacquiao once posted in his twitter:
It doesn't matter if the grammar is wrong, what matter is that you get the message
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|