Select mysql if true then update, else insert

Question

I Need an check if ID is already in the database, if that is true: Update values Longitude, Latitude, Timestamp. If not then insert all in the database.

My script now is:

<%@ Import Namespace="System.Data"%>   
<%@ Import Namespace="System.Data.SqlClient"%>   
<%@ Import Namespace="System.Reflection"%>
<%@ Page Language="VB" %>  
<script runat="server">  


    Dim Con As New SqlConnection
    Dim Cmd As New SqlCommand
    Dim SQLCommand As String = ""
    Dim Reader As SqlDataReader

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If Not String.IsNullOrEmpty(Request.QueryString("data")) Then
        Dim data As String = Request.QueryString("data")

        Dim myObj As New MyObject
        Dim properties() As PropertyInfo = myObj.GetType().GetProperties()
        Dim values() As String = Server.UrlDecode(data).Replace("{", "").Replace("}", "").Replace(""":""", """=""").Split(New Char() {","}, StringSplitOptions.RemoveEmptyEntries)
        For Each value As String In values
            Dim keyValue() As String = value.Split(New Char() {"="}, StringSplitOptions.RemoveEmptyEntries)
            For Each prop As PropertyInfo In properties
                If prop.Name.ToLower = keyValue(0).ToLower.Replace("""", "") Then
                    prop.SetValue(myObj, keyValue(1).Replace("""", ""), Nothing)
                End If
            Next
        Next

        myObj.Save()
        lblText1.Text = String.Format("ID: {0}", myObj.ID)
        lblText2.Text = String.Format("Longitude: {0}", myObj.Longitude)
        lblText3.Text = String.Format("Latitude: {0}", myObj.Latitude)
        lblText4.Text = String.Format("Timestamp: {0}", myObj.Timestamp)


'cmd.Parameters.AddWithValue("@ID", ID)
'cmd.Parameters.AddWithValue("@Longitude", Longitude)
'cmd.Parameters.AddWithValue("@Latitude", Latitude)
'cmd.Parameters.AddWithValue("@Timestamp", Timestamp)

'Con.ConnectionString = "Data Source=sr04011389\sql02;Initial Catalog=LocatieBepaling_Ontw;Integrated Security=True"
    'Cmd.Connection = Con
    'Con.Open()

   '     Cmd.CommandText = "INSERT INTO dbo_Locatie(id, Longitude, Latitude, Timestamp) VALUES (1234, 4567, 789, 12-12-2012)"


' Reader = Cmd.ExecuteReader

 '        Reader.Close()
  '       Con.Close()
 '      Con.Dispose()



    End If

End Sub
'Dim values() As String = Server.UrlDecode(data).Replace("{", "").Replace("}", "").Replace(""":""", """=""").Split(New Char() {","}, StringSplitOptions.RemoveEmptyEntries)  For Each value As String In values 


Public Class MyObject
    Private _ID As String
    Private _Longitude As String
    Private _Latitude As String
    Private _Timestamp As String


    Public Property ID As String
        Get
            Return _ID
        End Get
        Set(value As String)
            _ID = value
        End Set
    End Property


    Public Property Longitude As String
        Get
            Return _Longitude
        End Get
        Set(value As String)
            _Longitude = value
        End Set
    End Property


    Public Property Latitude As String
        Get
            Return _Latitude
        End Get
        Set(value As String)
            _Latitude = value
        End Set
    End Property


    Public Property Timestamp As String
        Get
            Return _Timestamp
        End Get
        Set(value As String)
            _Timestamp = value
        End Set
    End Property


    Public Function Save() As Boolean
 Return True
    Using objConn As New SqlConnection("Server=sr04011389\sql02;database=LocatieBepaling_Ontw;Trusted_Connection=yes;Max Pool Size=400;Connect Timeout=600;")
      Try

            Dim cmd As New SqlCommand("csp_save_my_object")
            cmd.CommandType = CommandType.StoredProcedure
            cmd.Parameters.AddWithValue("@ID", ID)
            cmd.Parameters.AddWithValue("@Longitude", Longitude)
            cmd.Parameters.AddWithValue("@Latitude", Latitude)
            cmd.Parameters.AddWithValue("@Timestamp", Timestamp)
           Return cmd.ExecuteNonQuery() > 0
        Catch ex As Exception
           Return False
       End Try
    End Using

    End Function


    Public Function SaveWithoutSP() As Boolean
       Return True
        Using Con As New SqlConnection("Server=sr04011389\sql02;database=LocatieBepaling_Ontw;Trusted_Connection=yes;Max Pool Size=400;Connect Timeout=600;")
            Try
               Con.Open()
               Dim cmd As New SqlCommand("UPDATE dbo_Locatie SET (Longitude = @Longitude, Latitude = @Latitude,Timestamp = @Timestamp) WHERE id=@ID IF @@ROWCOUNT=0 INSERT INTO locatie VALUES (@ID, @Longitude, @Latitude, @Timestamp)")
              cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@ID", ID)
                cmd.Parameters.AddWithValue("@Longitude", Longitude)
                cmd.Parameters.AddWithValue("@Latitude", Latitude)
                 cmd.Parameters.AddWithValue("@Timestamp", Timestamp)
                Return cmd.ExecuteNonQuery() > 0
            Catch ex As Exception
                Return False

            End Try
        End Using
    End Function
End Class



 'Sub Page_UnLoad()   
 'objConn.Close()   
 'objConn = Nothing  
 'End Sub  

 </script>  

 <html>   
 <head>   
 <title>TracknTrace</title>   
 </head>   
 <body>   
 <form id="form1" runat="server">   
 <asp:Label id="lblText" runat="server"></asp:Label> 
 <br><asp:Label id="lblText1" runat="server"></asp:Label> </br>
 <br><asp:Label id="lblText2" runat="server"></asp:Label> </br> 
 <br><asp:Label id="lblText3" runat="server"></asp:Label> </br>
 <br><asp:Label id="lblText4" runat="server"></asp:Label> </br>
 </form> 

And the PHP code i used before was:

 $result = mysql_query("SELECT * FROM locatie WHERE id='$id'");
 if($result && mysql_num_rows($result)>0){
 {
 $query = "Update locatie 
 SET     longitude = '$longitude',
 latitude = '$latitude',
 timestamp = '$timestamp'
 WHERE id = '$id'";}
 else
 //Als het nummer bekend is

 {
  //Als het een nieuw nummer is
 $query = sprintf("INSERT INTO locatie (id, longitude, latitude, timestamp) VALUES ('% s', '%s', '%s', '%s') ", mysql_real_escape_string($id) , mysql_real_escape_string ($longitude),  mysql_real_escape_string($latitude), mysql_real_escape_string($timestamp));}



 $result = mysql_query($query) or die('Query failed: ' . mysql_error());
 echo "OK";

Answer 1

EDIT: For SQL Server

The sql text to use is the following

UPDATE locatie SET (longitude = @longitude, 
                    latitude = @latitude,
                    timestamp = @timestamp) 
WHERE ID=@ID 
IF @@ROWCOUNT=0 
    INSERT INTO locatie VALUES (@ID, @longitude, @latitude, @timestamp)

That's supposing the ID field is NOT an autoicrement column and timestamp is not a TimeStamp datatype column.

The text above works if saved as a storedprocedure, in VB.NET you write all inside a string, but should insert a semicolon after the WHERE ID=@ID

  Dim cmd As New SqlCommand("UPDATE dbo_Locatie SET (Longitude = @Longitude, " + 
                            "Latitude = @Latitude,Timestamp = @Timestamp) WHERE id=@ID; " + 
                            "IF @@ROWCOUNT=0 INSERT INTO locatie " + 
                            "VALUES (@ID, @Longitude, @Latitude, @Timestamp)")

And as a side note: Instead of catching an exception without doing anything with the exception, is better to let the exception throws. The error messages could be revealing.

Answer 2

You could try using a INSERT ON DUPLICATE KEY UPDATE type of query. The way it works is if the record doesn't exist it is inserted. If it does, the values are updated.

INSERT INTO locatie (...) VALUES (...)
ON DUPLICATE KEY UPDATE longitude = '$longitude', ...

Answer 3

INSERT INTO locatie (id, longitude, latitude, timestamp)
ON DUPLICATE KEY UPDATE 
longitude = '$longitude',latitude = '$latitude',timestamp = '$timestamp';

is this what you are asking for ???