How to use SQL to add a record?

Hi, below is my ASP script so far. All it does at the moment is check your IP address and then compare it with a list of IPs it has in a database table. This all works fine.

Now what I want to do is make it save any IP address that it cant find in the IP list in the database.

Thanks for your help.


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%
Dim objRS, objConnect, grabIP, SQL_DetailsRetreival

grabIP = Request.ServerVariables("Remote_Addr")

Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"
SET objConnect = objConnect.execute(SQL_DetailsRetreival)


'if the recordset is not empty then show the IP'
If NOT(objConnect.BOF) and NOT(objConnect.EOF) then
Response.Write "The IP " & grabIP & "has been found."
Else
Response.Write "No IP found in database"
End If
%>

hi,

I jus put the procedure how to insert it, just connection object required to insert the recordset, U do not need extra recordset objects. Any special help see http://himadrish.cjb.net.

Regards,
Himadrish Laha

'if the recordset is not empty then show the IP'
If NOT(objConnect.BOF) and NOT(objConnect.EOF) then
Response.Write "The IP " & grabIP & "has been found."
Else
Response.Write "No IP found in database and it is inserted"

objConnect.execute("insert into <tablename> values(""))

End If
%>

Thank you so much for your help, but I would be grateful if you could be a little more precise with me because I am only a basic beginner with ASP.

This is how I modified my ASP using your guide:

'if the recordset is not empty then show the IP'
If NOT(objConnect.BOF) and NOT(objConnect.EOF) then
Response.Write "The IP " & grabIP & "has been found."
Else
Response.Write "No IP found in database and it is inserted"

objConnect.execute(insert into tblIP values("grabIP"))

End If

(the grabIP holds the IP address as a string) But I now get this error message:


Microsoft VBScript compilation error '800a03ee'

Expected ')'

/test.asp, line 24

objConnect.execute(insert into tblIP values("grabIP"))
--------------------------^

change this line
---------------------
objConnect.execute(insert into tblIP values("grabIP"))

To like here
-------------
If the grabIP is integer field

"insert into tblIP (<fieldname>) values(" & grabIP & ")"

If it is character field then

"insert into tblIP (<fieldname>) values(" & "' & grabIP & "'" & ")"

Pls chk this also

strQ="INSERT Tablename1 (ADD,OWN,NAME,FULLNAME,EMAIL) Values ("&AddrId&","&UserId&",'"&Name&"','"&FullName&"','"&Email&"')"

Ok ive changed everything you said and it still wont work, here is the code:

<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%
Dim objRS, objConnect, grabIP, SQL_DetailsRetreival

grabIP = Request.ServerVariables("Remote_Addr")
Response.Write grabIP
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"

SET objConnect = objConnect.execute(SQL_DetailsRetreival)


'if the recordset is not empty then show the IP'
If NOT(objConnect.BOF) and NOT(objConnect.EOF) then
Response.Write "The IP " & grabIP & "has been found."
Else
Response.Write "No IP found in database and it is inserted"
objConnect.execute("insert into tblIP (IPaddess) values (" & grabIP & ")")
End If
%>



and now here is the error message:


192.168.1.103No IP found in database and it is inserted
Microsoft VBScript runtime error '800a01b6'

Object doesn't support this property or method: 'execute'

/test.asp, line 24

Don't do Just make that line The way you had it, when you SET objConnect=.... you are changing the connection object, so that later, when you try to do , you are really doing which doesn't work.
I'd also make your insert statement a variable, then execute the variable, ie
And make sure you are escaping all your quotes and parentheses correctly.
HTH

Thanks karsh44, I think its almost there but i still seems to be having a couple of probs with it still. Do you think you could check this modified code and show me what is wrong with it? Many thanks for your time and effort.


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%
Dim objRS, objConnect, grabIP, SQL_DetailsRetreival, SQL_InsertData

grabIP = Request.ServerVariables("Remote_Addr")
Response.Write grabIP
Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"
SQL_InsertData = "insert into tblIP (IPaddess) values (" & grabIP & ")"

objConnect.execute(SQL_DetailsRetreival)


'if the recordset is not empty then show the IP'
If NOT(objConnect.BOF) and NOT(objConnect.EOF) then
Response.Write "The IP " & grabIP & "has been found."
Else
Response.Write "No IP found in database and it is inserted"
objConnect.execute(SQL_InsertData)
End If
%>

This isn't my thread but I'll jump in anyway

Where is your RECORDSET ?????????????

You're making a SELECT query, this means that when you execute it, you'd like to have RESULTS.

A *SELECT* query brings you back stuff, the stuff you asked for.

An *INSERT* *UPDATE* or *DELETE* query doesn't need to bring you back stuff, so you don't need a RECORDSET.

The RECORDSET's job is to HOLD the data you've ASKED for in your SELECT query but where is yours ??? I don't see it???

Ok FIRST you retrieve the value inside a variable like this:
grabIP = Request.ServerVariables("Remote_Addr")

Then you ASK for this:
SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"

Now since its a SELECT query you'll want to obtain a recordset object so do this:

SET objRst = objConnect.execute(SQL_DetailsRetreival)

The objRst becomes an **IMPLICIT** recordset object since you didn't EXPLICITLY declare one!

Then you do:

If objRst.EOF then

'Oho..the recordset is empty, there is no value inside the recordset, this means there is NO records for the given grabIP variable

Else

'Yeh!!!! we have found an existing(or more) record for the given grabIP variable

End If



Hope this helps!
Sincerely

Vlince

Vince thanks a lot! Your suggestions work PERFECTLY!

But I still have the problem of not knowing how to then save the IP address in to the database if a matching one was not found...here is my updated code:


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%
Dim objRS, objConnect, grabIP, SQL_DetailsRetreival, SQL_InsertData, objRst

grabIP = Request.ServerVariables("Remote_Addr")
Response.Write grabIP

Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"
SQL_InsertData = "insert into tblIP (IPaddess) values (" & grabIP & ")"


SET objRst = objConnect.execute(SQL_DetailsRetreival)



'if the recordset is not empty then show the IP'

If objRst.EOF then
Response.Write "No IP found in database, it has now been inserted"

objConnect.execute("insert into tblIP (IPaddess) values (" & grabIP & ")")

Else

Response.Write "The IP " & grabIP & "has been found, no IP was saved."
End If
%>


Thankyou!

You say the current code works perfectly? If it does, then the IP should go into the database with the "insert into tblIP..." line.

So if the code all works, then the IP is being added. Or are you saying that it works for existing IP's, but not for new ones? You don't need to "save" the IP into the database, the "INSERT" statement does all that is required.

Well you won't be needing the variable: Dim objRS anymore since you've declared objRst so don't forget to erase it from your code!

Now, are you getting any errors?

Is the INSERT working?
Try:


<%
. . .
. .
.

If objRst.EOF then

objConnect.execute SQL_InsertData, adExecuteNoRecords

Else

Response.Write "The IP " & grabIP & "has been found, no IP was saved."

End If


'Don't forget to close your objects...
objRst.Close
Set objRst = nothing

objConnect.Close
Set objConnect = nothing
%>

NOTE: since you have the adovbs.inc file included the variable *adExecuteNoRecords* should be ok.

Otherwise do this:
<%
. . .
. .
.

Const adExecuteNoRecords = 128

If objRst.EOF then

objConnect.execute SQL_InsertData, adExecuteNoRecords

Else

Response.Write "The IP " & grabIP & "has been found, no IP was saved."

End If

.
. .
. . .
%>

Also note that you already have inside the variable SQL_InsertData the INSERT query so use that variable!

Hope this helps!
Sincerely

Vlince

Thanks again guy but, Vlince, I get the following error now from the code:


192.168.1.103No IP found in database, it has now been inserted
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in number in query expression '192.168.1.103'.

/test.asp, line 28


As im sure you can see, the IP is being "grabbed" from my computer and then being displayed. But then when it comes down to actually saving it to the database I get the above error.

here is the updated code:


<%@ Language=VBScript %>
<% Option Explicit %>
<!--#include virtual="/adovbs.inc"-->
<!--#include file="dataConnect.asp"-->

<%
Dim objConnect, grabIP, SQL_DetailsRetreival, SQL_InsertData, objRst

grabIP = Request.ServerVariables("Remote_Addr")
Response.Write grabIP

Set objConnect = Server.CreateObject("ADODB.Connection")
objConnect.Open objConn

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"
SQL_InsertData = "insert into tblIP (IPaddess) values (" & grabIP & ")"


SET objRst = objConnect.execute(SQL_DetailsRetreival)



'if the recordset is not empty then show the IP'

If objRst.EOF then
Response.Write "No IP found in database, it has now been inserted"

objConnect.execute SQL_InsertData, adExecuteNoRecords

Else

Response.Write "The IP " & grabIP & "has been found, no IP was saved."
End If

objRst.Close
Set objRst = nothing

objConnect.Close
Set objConnect = nothing


%>


thanks!

Do you see it ????

I mean do you see why you are getting the error message ???


Look at this:

SQL_DetailsRetreival = "SELECT IPaddress FROM tblIP WHERE IPaddress ='" & grabIP & "'"

SQL_InsertData = "insert into tblIP (IPaddess) values (" & grabIP & ")"



Then look at this:

WHERE IPaddress ='" & grabIP & "'"

values (" & grabIP & ")"



then look at this:

'" & grabIP & "'"

" & grabIP & "


Notice anything different...

Hope this helps!
Sincerely

Vlince