# table query problems



## emagdnim (Jun 21, 2004)

I have two tables, table1 has a list of locations that are users will be at and those locations cooresponding ip address. Now using reqeust.servervariables I can get the ip address of the computer using the application. The application is used as inventory entry, and instead of having the user pic their location for reasons they could mess up the inventory if they make a wrong choice, i need to pull the location name out of table1 using the ip address and submit all the inventory information into table2. I cannot figure out how to query the one table. Any suggestions?


----------



## Gibble (Oct 10, 2001)

I'm assuming this is in a database and you need some SQL?

If so, your SQL would be something like

lsIPAddress = Request.ServerVariables("REMOTE_ADDR")
lsSQL = "SELECT * FROM table1 WHERE IPAddress = '"& lsIPAddress & "'"


----------



## Rockn (Jul 29, 2001)

If this is on an intranet you would want to use the Request.ServerVariables("LOCAL_ADDR") or all you will get is the web server address. It would actually be better to use a fixed list of locations that they are logging onto this application from.


----------



## Rockn (Jul 29, 2001)

You were correct Gibble...REMOTE_ADDR, LOCAL_ADDR gives the server address.


----------



## emagdnim (Jun 21, 2004)

thanks that worked perfect


----------



## emagdnim (Jun 21, 2004)

ok its not working right, as far as i can figure the variable i am creating in my recordset query is not passing the ipaddress properly.
I get the ip address as follows:

Dim strIpAddress
strIpAddress = Request.ServerVariables("remote_Addr")

later down I have it in a hidden field in a form as follows:

[TD]IP Address:[/TD]
[TD]<%Response.Write strIpAddress%>
">[/TD]

My recordset query is as follows:

SELECT ICName
FROM InfoCenters
WHERE 'varIpAddress' = IpAddress

the variable is:

varIpAddress % Request.Form("IpAddress")

but when i test the query i dont get the location from the table unless i declare the default value as one of the ipaddresses in the table


----------



## Gibble (Oct 10, 2001)

Have you checked to make sure that Request.Form("IpAddress") has the value you think it does?

What exactly is the code you are using to build your SQL?

Do a response.write of your SQL after you build it, and ensure it's correct.

Let us know the results of these things, and we can guide you further.


----------



## emagdnim (Jun 21, 2004)

yea if you look above i do a response.write strIpaddress and on my screen my ipaddress comes up then the hidden field follows right after. Ive even written the hidden field IpAddress to a database to make sure its getting read properly and sure enough my ipaddress came up again. But in my query when i use the variable Request.Form("Ipaddress") and run the query it says no data, it is not recieveing the ipaddress at runtime for some reason


----------



## Gibble (Oct 10, 2001)

Why are you getting the IP from A servervariable, then storing it in a form, then getting it from the form? Why not just get it straight from the servervariable for your SQL?


----------



## emagdnim (Jun 21, 2004)

do you mean makeing the varibale in my query request.servervariables("remote_addr") instead of reqeust.form("ipAddress")?? becuase that doesnt work either which is why i tried it the way you just said


----------



## Gibble (Oct 10, 2001)

Copy and paste your code for this...so I can review it. It would make this MUCh simpler


----------



## emagdnim (Jun 21, 2004)

if you would be willing to give me your email I will send you the necessary file(s)


----------



## Gibble (Oct 10, 2001)

Just copy and paste the pertinent parts and place them in [ php ] tags on this site.


----------



## emagdnim (Jun 21, 2004)

<%
Dim strIpAddress, strLocation, strItem, strStartInv, strSold, strAdj, strAdjMemo, strAdjBy, strEndInv, objNet

strIpAddress = Request.ServerVariables("remote_Addr")
strLocation = Request.Form("location")
strItem = Request.Form("item")
strStartInv = Request.Form("startinv")
strSold = Request.Form("sold")
strAdj = Request.Form("adj")	
strAdjMemo = Request.Form("adjmemo")
strAdjBy = Request.Form("adjby")
strEndInv = Request.Form("endinv")
%>

[TR] 
[TD]Date*:*[/TD]
[TD]<%=date()%> [/TD]
[/TR]
 [TR] 
[TD]Location:[/TD]
[TD]<%Response.Write strLocation%> "> 
[/TD]
[/TR]
[TR] 
[TD]Item:[/TD]
[TD]<%Response.Write strItem%> "> 
[/TD]
[/TR]
[TR] 
[TD]Starting Inventory:[/TD]
[TD]<%Response.Write strStartInv%> "> 
[/TD]
[/TR]
[TR] 
[TD]Items Sold:[/TD]
[TD]<%Response.Write strSold%> "> 
[/TD]
[/TR]
[TR] 
[TD]Adjustments:[/TD]
[TD]<%Response.Write strAdj%> "> 
[/TD]
[/TR]
[TR] 
[TD]Adjustments Memo:[/TD]
[TD]<%Response.Write strAdjMemo%> "> 
[/TD]
[/TR]
[TR] 
[TD]Adjusted By:[/TD]
[TD]<%Response.Write strAdjBy%> "> 
[/TD]
[/TR]
[TR] 
[TD]Ending Inventory:[/TD]
[TD]<%Response.Write strEndInv%> "> 
[/TD]
[/TR]
[TR] 
 [TD]IP Address:[/TD]
[TD]<%Response.Write strIpAddress%> ">[/TD]
[/TR]
[/TABLE]

<%=(Recordset1.Fields.Item("ICName").Value)%>



<%
Recordset1.Close()
Set Recordset1 = Nothing
%>

my query in the recordset window is as follows:

SELECT ICName
FROM InfoCenters
WHERE IpAddress = 'MMColParam'

MMColParam 0 Request.Form("IpAddress")

where MMColParam is the variable name, 0 is the default value, and the last part is the runtime value


----------



## Gibble (Oct 10, 2001)

Lets see the code where you build the query.


----------



## emagdnim (Jun 21, 2004)

<%
Dim Recordset1
Dim Recordset1_numRows

Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_InventoryData_STRING
Recordset1.Source = "SELECT ICName FROM InfoCenters WHERE IpAddress = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()

Recordset1_numRows = 0
%>

keep in mind i didnt write this but dreamweaver esentially did, but i am guessing the flaw is in my query or the fact that im passing around the ip address so much

This is the error i get on the page:

ADODB.Field error '800a0bcd'

Either BOF or EOF is True, or the current record has been deleted. Requested operation requires a current record.

/ASPCode/Mike/CreateConfirm.asp, line 222

and that is based on the code line of code by the toward the bottom near the submit button


----------



## Gibble (Oct 10, 2001)

wtf is Replace(Recordset1__MMColParam, "'", "''")

Change that to Request.ServerVariables("REMOTE_ADDR") and see what happens.


----------



## emagdnim (Jun 21, 2004)

it may say Replace(Recordset1__MMColParam, "'", "''") but in the query window it is displayed as this:
SELECT ICName
FROM InfoCenters
WHERE IpAddress = 'MMColParam'

where the variable MMColParam is Request.Form("IpAddress")

i dont think thats the problem, i did try changing however, and the page no longer comes up


----------



## emagdnim (Jun 21, 2004)

to make the problem simpler, 
The query is as follows:

SELECT ICName
FROM InfoCenters
WHERE IpAddress = 'MMColParam'
and when i make MMColParam's default value an ipaddress in the table Infocenters, the query works. the problem is that Request.Form("ipaddress") is not passing the ipaddress of the computer into the query. So when I test it with no default value or something like 0 or 1, "no data" comes up in the test window instead of the cooresponding location value of my ipaddress which is in the table


----------



## Gibble (Oct 10, 2001)

oh...well...change it back the I suppose.

I don't know. I'm don't use dreamweaver ... but you aren't getting a result from your query, hence the BOF/EOF error.


----------



## Rockn (Jul 29, 2001)

Try hard coding an IP address and see if it works.


----------



## emagdnim (Jun 21, 2004)

it does

I get the feeling that request.servervariables and Dimenstioned strings dont work well with query's or I am getting the sytax wrong


----------



## Gibble (Oct 10, 2001)

Change this line
Recordset1.Source = "SELECT ICName FROM InfoCenters WHERE IpAddress = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"

To these lines

lsSQL = "SELECT ICName FROM InfoCenters WHERE IpAddress = '" + Replace(Recordset1__MMColParam, "'", "''") + "'"
Response.write "lsSQL : " & lsSQL & "
"
Recordset1.Source = lsSQL

And check what the value of lsSQL is and let us know.


----------



## emagdnim (Jun 21, 2004)

page cannot be found


----------



## Gibble (Oct 10, 2001)

what?


----------



## emagdnim (Jun 21, 2004)

the error comes up that says the page cannot be found


----------



## Gibble (Oct 10, 2001)

...well then you better find the page, cause you got yourself an entirely new problem...


----------



## emagdnim (Jun 21, 2004)

i got it, in my query i was putting 
IpAddress = ' Request.ServerVariables("remote_addr") ' when it should have been:

IpAddress = " ' &Request.ServerVariables("remote_addr")& ' " 

like i said i am not programmer and i didnt know i was supposed to concantenate it
.but thanks for all your helf Gibble


----------



## Gibble (Oct 10, 2001)

emagdnim said:


> i got it, in my query i was putting
> IpAddress = ' Request.ServerVariables("remote_addr") ' when it should have been:
> 
> IpAddress = " ' &Request.ServerVariables("remote_addr")& ' "
> ...


And that is why I say "The amount of time needed to solve a problem is inversely proportionate to the complexity of the solution"


----------



## emagdnim (Jun 21, 2004)

haha yea


----------

