Solved

Check to see if IP is in Database Table SQL VBScript ACCESS

Posted on 2008-06-20
6
335 Views
Last Modified: 2008-06-24
Hi, this is very simple...

I am trying to find out if an IP address (that was saved in a table) is in a particular column called IP and if that is so...return a result

I just need the SQL to get it done

ADO connection here to whatever .mdb

i need the sql to determine whether or not the value is in the table or not...or at least how to find out ... evaluate and send a result bsed on that

i will take the result and then redirect all IP's in the list to a page prohibiting them from entering the site...

basically i just need a snipplet of how to check if the IP in already in the DB or not
0
Comment
Question by:Devario Johnson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 3

Expert Comment

by:DarrenMcCall
ID: 21835512
Looking for something like this?
'Datebase connection
set oConn=Server.CreateObject("ADODB.Connection")
oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open "Database.mdb" 'CHANGE DATABASE NAME!
set oRS = Server.CreateObject("ADODB.recordset")
sql="SELECT IP FROM Table1" 'CHANGE TABLE NAME!
oRS.Open sql, oConn
 
If oRS.EOF Then
	'IP Not Found!
Else
	Response.Redirect("http://www.google.com/")
End If
 
'Cleanup
oRS = Nothing
oConn = Nothing
oRS.Close
oConn.Close

Open in new window

0
 
LVL 2

Expert Comment

by:jgoeders
ID: 21835650
dim rs as recordset
set rs =currentdb.openRecordset("SELECT * FROM IP WHERE IP_COLUMN = ' " & sIPVariable & " ' "
if rs.eof then
'not in table
else
'in table
end if
0
 
LVL 29

Accepted Solution

by:
Göran Andersson earned 500 total points
ID: 21835688
DarrenMcCall,
- There is no condition in your query, so it will just check if there is anything in the table or not.
- If you do a redirect before the cleanup, the cleanup will not run.
- In the cleanup, you have to call Close before you set the references to Nothing.

This will work a little bit better:
' Declare variables
Dim objConnection, objRS, lngCnt, strIP
' Get IP
strIP = Request.ServerVariables("REMOTE_ADDR")
' Open connection
Set objConnection = Server.CreateObject("ADODB.Connection")
objConnection.Provider = "Microsoft.Jet.OLEDB.4.0"
objConnection.Open "Database.mdb"
' Count occurances of the IP
strSQL="select count(*) from Blocked where IP = '" & Replace(strIP, "'","''") & "'"
Set objRS = objConnection.Execute(strSQL)
' Get the result
lngCnt = objRS(0)
' Close the recordset
objRS.Close
Set objRS = Nothing
' Close connection
objConnection.Close
Set objConnection = Nothing
' Check result
If lngCnt = 0 Then
        'IP Not Found
Else
        Response.Redirect "Denied.asp"
End If

Open in new window

0
The Orion Papers

Are you interested in becoming an AWS Certified Solutions Architect?

Discover a new interactive way of training for the exam.

 
LVL 3

Expert Comment

by:DarrenMcCall
ID: 21837735
@GreenGhost, yes and yes. Nice catch, thanks.
0
 
LVL 5

Author Comment

by:Devario Johnson
ID: 21860361
Hi, guys, I got the concept from greenhost, and just wrote my own sql statement.  Thanks for all the help
0
 
LVL 5

Author Comment

by:Devario Johnson
ID: 21860835
this is the snipplet of code that I developed to achieve this task.  
Dim adoCon, ID, rsGuestbook, strSQL, banIP
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("guestbook_2003.mdb")
Set rsGuestbook = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT Banned.IP FROM Banned WHERE IP='" & Request.ServerVariables("REMOTE_ADDR") & "'"
rsGuestbook.Open strSQL, adoCon
 
Do While not rsGuestbook.EOF
	banIP = rsGuestbook("IP")
	if banIP <> "" then
	Response.Redirect "Denied.asp"
	end if
	rsGuestbook.MoveNext
loop
 
rsGuestbook.Close
Set rsGuestbook = Nothing
Set adoCon = Nothing

Open in new window

0

Featured Post

Containers & Docker to Create a Powerful Team

Containers are an incredibly powerful technology that can provide you and/or your engineering team with huge productivity gains. Using containers, you can deploy, back up, replicate, and move apps and their dependencies quickly and easily.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

What is Node.js? Node.js is a server side scripting language much like PHP or ASP but is used to implement the complete package of HTTP webserver and application framework. The difference is that Node.js’s execution engine is asynchronous and event…
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Learn how to set-up PayPal payment integration in your Wufoo form. Allow your users to remit payment through PayPal upon completion of your online form. This is helpful for collecting membership payments, customer payments, donations, and more.
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…

690 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question