Reputation: 1379
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";
Upvotes: 0
Views: 1297
Reputation: 216358
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.
Upvotes: 1
Reputation:
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', ...
Upvotes: 0
Reputation: 14263
INSERT INTO locatie (id, longitude, latitude, timestamp)
ON DUPLICATE KEY UPDATE
longitude = '$longitude',latitude = '$latitude',timestamp = '$timestamp';
is this what you are asking for ???
Upvotes: 0