Results 1 to 7 of 7

Thread: [RESOLVED] UPDATE or INSERT advice.

  1. #1

    Thread Starter
    Fanatic Member louvelle's Avatar
    Join Date
    Jun 2008
    Posts
    513

    Resolved [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

  2. #2
    PowerPoster Nightwalker83's Avatar
    Join Date
    Dec 2001
    Location
    Adelaide, Australia
    Posts
    13,344

    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

  3. #3
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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)

  4. #4

    Thread Starter
    Fanatic Member louvelle's Avatar
    Join Date
    Jun 2008
    Posts
    513

    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

  5. #5

    Thread Starter
    Fanatic Member louvelle's Avatar
    Join Date
    Jun 2008
    Posts
    513

    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

  6. #6
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,929

    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.

  7. #7

    Thread Starter
    Fanatic Member louvelle's Avatar
    Join Date
    Jun 2008
    Posts
    513

    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
  •  



Click Here to Expand Forum to Full Width