Database Discussion - Whats wrong with this code?

Print Page | Close Window

Whats wrong with this code?

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=25915
Printed Date: 28 March 2026 at 9:18am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: Whats wrong with this code?

Posted By: Nerz
Subject: Whats wrong with this code?
Date Posted: 29 June 2008 at 9:30pm

What i'm trying to do is check if data, particularly the IP address that is accessing the page. If the address exists it pretty much does nothing so far. If it doesn't it adds it to the database.

<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_check         'Holds the SQL query to check the database
'Dim sql_insert
Dim Address           'Hold IP Address

'Get ip address
Address= Request.ServerVariables("REMOTE_ADDR")

'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("ProjDatabase.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_check = "Select IP from tblIP where IP = Address"

'Open the recordset with the SQL query
rsProjDatabase.Open sql_check, adoCon

If rsProjDatabase.EOF Then

'Tell the recordset we are adding a new record to it
rsProjDatabase.AddNew

'Add a new record to the recordset
rsProjDatabase.Fields("IP") = Address

'Write the updated recordset to the database
rsProjDatabase.Update

Else
Response.Write ("It exist")

End If

'Reset server objects
rsProjDatabase.Close
Set rsProjDatabase = Nothing
Set adoCon = Nothing
%>

</body>
</html>

Replies:

Posted By: Nerz
Date Posted: 29 June 2008 at 9:41pm

It's giving me the error...

Error Type:
Microsoft JET Database Engine (0x80040E10)
No value given for one or more required parameters.
/checkexist.asp, line 38

line 38 is

rsProjDatabase.Open sql_check, adoCon

Posted By: socialanimal
Date Posted: 29 June 2008 at 9:46pm

Change

sql_check = "Select IP from tblIP where IP = Address"

To

sql_check = "Select IP from tblIP where IP = '" & Address &"'"

The way you've done it it's trying to match up the column IP to a column called address

Posted By: Nerz
Date Posted: 29 June 2008 at 9:56pm

It's now giving this error...

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/checkexist.asp, line 35, column 14
sql_check = ""Select IP from tblIP where IP = '" & Address &"'""
-------------^

Posted By: socialanimal
Date Posted: 29 June 2008 at 10:48pm

Yup cos now you have double quotes at the start of your query, where there should only be one

I wrote - sql_check = "Select IP from tblIP where IP = '" & Address &"'"
You used sql_check = ""Select IP from tblIP where IP = '" & Address &"'""

Posted By: Nerz
Date Posted: 30 June 2008 at 2:58pm

That worked thank you.

Posted By: Nerz
Date Posted: 01 July 2008 at 7:23pm

One more thing. I added to the sql statement. This is the line...

sql_check2 = "Select IP from tblIP where IP = '" & Address &"' and ID from tblIP where IP = Request.Form("ID")"

It says i'm getting this error

Error Type:
Microsoft VBScript compilation (0x800A0401)
Expected end of statement
/checkexist2.asp, line 49, column 106
sql_check2 = "Select IP from tblIP where IP = '" & Address &"' and ID from tblIP where IP = Request.Form("ID")""
---------------------------------------------------------------------------------------------------------^

I don't understand considering i have the proper amount of start and end "s

Posted By: socialanimal
Date Posted: 01 July 2008 at 7:51pm

But your SQL string isn't valid SQL

The following is but it still doesn't make sense if your unique identifier "ID" field should be the only WHERE parameter, if your wanting to bring back the "ID" field too then it should look like ....

sql_check2 = "Select ID, IP from tblIP where ID = " & Request.Form("ID")

Posted By: Nerz
Date 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 |

Posted By: socialanimal
Date 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,

Posted By: socialanimal
Date Posted: 01 July 2008 at 8:35pm

Ooops ...

strSQL = "SELECT id FROM table WHERE IP = '" & IP

Posted By: Nerz
Date 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 |

Posted By: socialanimal
Date 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

Posted By: Nerz
Date 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>

Posted By: socialanimal
Date 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