Web Wiz - Green Windows Web Hosting
Web Wiz Homepage Search Web Wiz

  New Posts New Posts RSS Feed - Whats wrong with this code?
  FAQ FAQ  Forum Search   Events   Register Register  Login Login

Whats wrong with this code?
 Post Reply Post Reply Page  <12
Author
  Topic Search Topic Search  Topic Options Topic Options
Nerz View Drop Down
Newbie
Newbie


Joined: 23 June 2008
Status: Offline
Points: 28 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Nerz Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 8:20pm
on this table it doesn't have a primary key. therefore an ip can have more then 1 ID for it. I need to sql statement to test whether a pair such as 127.0.0.1 has been Test1 has already been added because i dont want the same pair twice.
 
    IP                 ID    
______      __ _______
  127.0.0.1     |  Test1    |
  127.0.0.1     |  Test2    |
265.32.98.21 |  Test2    |
 
Back to Top
socialanimal View Drop Down
Newbie
Newbie
Avatar

Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote socialanimal Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 8:30pm
Ok so you want it to make sure that each IP address is only in the table once ? What database are you using ? Access / Microsoft SQL Server or MySQL ? If MSSQL you can do this in one line but I take it your also learning ASP as you go. Try this

strSQL = "SELECT COUNT(id) FROM table WHERE IP = '" & IP

' If end of file (ie no records) then insert it
If rs.eof then
    ' Insert would go here
' Otherwise update the record, or what ever you want to do
Else
   ' Update would go here
End If

I would highly recommend looking at SQL structure and the general basics around it though considering this is what you had issues with. W3schools.com is highly recommended for a learner such as yourself to start off with,
Back to Top
socialanimal View Drop Down
Newbie
Newbie
Avatar

Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote socialanimal Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 8:35pm
Ooops ...

strSQL = "SELECT id FROM table WHERE IP = '" & IP
Back to Top
Nerz View Drop Down
Newbie
Newbie


Joined: 23 June 2008
Status: Offline
Points: 28 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Nerz Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 8:54pm

its Access / Microsoft. it can have IP twice. like this sample table up there. the sample below however. the data with the * will not be accepted because it is duplicated.  i'm wondering if to check i should select all of the matching IP 's, for instance 127.0.0.1, and from those selected, check the ID. not quite sure how to do it yet. maybe that's what ur MSSQL is doin. sorry just not familiar with it.

 IP                 ID    
______      __ _______
  127.0.0.1     |  Test1    |
  127.0.0.1     |  Test2    |
*127.0.0.1     |  Test2    |
265.32.98.21 |  Test2    |
Back to Top
socialanimal View Drop Down
Newbie
Newbie
Avatar

Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote socialanimal Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 9:31pm
What I think will be easiest for you then is to bring back a count of records where the ID and IP are the same so pass through like so:

strSQL = "SELECT COUNT(*) FROM table WHERE IP = '"& IP &"' and ID = " & ID

' You're always only going to get one record  returned from the database and that will only have one field

rs.open strSQL, adocon

' If no records have that ID / IP then insert them
If rs(0) = 0 Then
    ' Do an insert or whatever here
' We're not putting an else in because we only want to insert when no records are available
End If
Back to Top
Nerz View Drop Down
Newbie
Newbie


Joined: 23 June 2008
Status: Offline
Points: 28 		Post Options Post Options   Thanks (0) Thanks(0)   Quote Nerz Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 11:24pm
Hmm now i'm having a problem with it saying it exists even if it doesn't.
 
Here's the code...
 
<html>
<head>
<title>Check Page</title>
</head>
<body bgcolor="white" text="black">
<%
'Dimension variables
Dim adoCon         'Holds the Database Connection Object
Dim rsProjDatabase    'Holds the recordset for the records in the database
Dim sql_check2         'Holds the SQL query to check the database
Dim IP
Dim ID

'Create an ADO connection object
Set adoCon = Server.CreateObject("ADODB.Connection")

'Set an active connection to the Connection object using a DSN-less connection
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Server.MapPath("ProjDatabase2.mdb")
'Create an ADO recordset object
Set rsProjDatabase = Server.CreateObject("ADODB.Recordset")
'Set the cursor type we are using so we can navigate through the recordset
rsProjDatabase.CursorType = 2
'Set the lock type so that the record is locked by ADO when it is updated
rsProjDatabase.LockType = 3
'Initialise the strSQL variable with an SQL statement to query the database
'sql_check2 = "SELECT COUNT(*) As Total FROM tblAll WHERE IP = '127.0.0.1' AND ID = 'Test1'"
sql_check2 = "SELECT COUNT(*) FROM tblAll WHERE IP = '127.0.0.1' and ID = 'Test1' "
                
'Open the recordset with the SQL query
rsProjDatabase.Open sql_check2, adoCon
If rsProjDatabase.EOF Then
Response.Write ("It doesn't exist!")
'Write the updated recordset to the database
rsProjDatabase2.Update

Else
Response.Write ("It exist!")
End If

'Reset server objects
rsProjDatabase.Close
Set rsProjDatabase = Nothing
Set adoCon = Nothing
%>
</body>
</html>
 
Back to Top
socialanimal View Drop Down
Newbie
Newbie
Avatar

Joined: 14 October 2007
Location: Leeds, UK
Status: Offline
Points: 39 		Post Options Post Options   Thanks (0) Thanks(0)   Quote socialanimal Quote  Post ReplyReply Direct Link To This Post Posted: 01 July 2008 at 11:34pm
Of course its going to say it does exist, you haven't looked properly into the last code block I sent you. It returns a count of records with that IP AND ID .... its going to have returned an integer (numeric) value.

rsProjDatabase.Open sql_check2, adoCon
If rsProjDatabase(0) = 0 Then
Response.Write ("It doesn't exist!")
'Write the updated recordset to the database
rsProjDatabase2.Update

Else
Response.Write ("It exist!")
End If

'Reset server objects
rsProjDatabase.Close
Set rsProjDatabase = Nothing
Set adoCon = Nothing
Back to Top
 Post Reply Post Reply Page  <12

Forum Jump Forum Permissions View Drop Down

Forum Software by Web Wiz Forums® version 12.08
Copyright ©2001-2026 Web Wiz Ltd.

Become a Fan on Facebook Follow us on X Connect with us on LinkedIn Web Wiz Blogs
About Web Wiz | Contact Web Wiz | Terms & Conditions | Cookies | Privacy Notice

Web Wiz is the trading name of Web Wiz Ltd. Company registration No. 05977755. Registered in England and Wales.
Registered office: Web Wiz Ltd, Unit 18, The Glenmore Centre, Fancy Road, Poole, Dorset, BH12 4FB, UK.

Prices exclude VAT at 20% unless otherwise stated. VAT No. GB988999105 - $, € prices shown as a guideline only.

Copyright ©2001-2026 Web Wiz Ltd. All rights reserved.