Lo all <img src=../images/dmxzone/forum/icon_smile.gif border=0 align=middle>

After following the Tutorial on here that Waldo posted up on "Using Stored Procedures in MS Access" I've ran into a problem.

I've got a simple Table called "IPs" with fields "ID", "IPAddress" & "DateAdded"
With an Insert Query called "NewIP"

The query works fine in Access, I just can't get it to work in UDev.

<b>Error im getting is:</b>
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

Microsoft ODBC Microsoft Access Driver Syntax error in INSERT INTO statement.

/stored/storedproc.asp, line 17

<b>What I'm aiming to do is:</b>
Simply set up a simple Access DB to store IP's of visitors to a web page, so I can build a simple stats program.

<b> The Code which UD generates is:</b>
&lt;%@LANGUAGE="VBSCRIPT"%&gt;
&lt;!--#include file="Connections/myStoredProc.asp" --&gt;
&lt;%

if(Request.ServerVariables("REMOTE_ADDR" &lt;&gt; "" then myCommand__varIP = Request.ServerVariables("REMOTE_ADDR"

%&gt;
&lt;%

set myCommand = Server.CreateObject("ADODB.Command"
myCommand.ActiveConnection = MM_myStoredProc_STRING
myCommand.CommandText = "INSERT INTO (IPs ) VALUES (IPAddress ) WHERE IPAddress = "" + Replace(myCommand__varIP, "'", "''" + """
myCommand.CommandType = 1
myCommand.CommandTimeout = 0
myCommand.Prepared = true
myCommand.Execute()

%&gt;
This is using the "Command / Stored Procedure" function.

<b>and this... is the SQL for the command</b>
INSERT INTO (IPs)
VALUES ((IPAddress ) WHERE IPAddress = varIP)

and then varIP has a runtime value of "Request.ServerVariables("REMOTE_ADDR""

There may be an easier way to do this but i really want to learn how to do it with a Stored Procedure, but any input is welcomed.



Regards,
Dave

UD4 || Flash || Access ||Web Hosting

You may want to look at the Stored Proc and declare the variables before you call them in the procedure.

Not too sure how it is with access but in MS sql Server you need to put something like : (example SP)

Create Procedure logonuser
@username varchar(50),
@password varchar(12)
As
set nocount on
UPDATE members SET u_lastDate = GETDATE(), u_logcount = u_logcount + 1, rewardPoints = rewardPoints + 1 WHERE (u_name = @username) AND (u_password = @password)
select * from members where u_name=@username and u_password=@password

GO

Notice the @username and @password? These are variables I am calling in order to run the rest of the procedure. You would probably look at something like @IP to declare the IP address and insert it into the table. It comes down to knowing your SQL.

Check out this site for some ideas on Stored procs: www.sqlteam.com/FilterTopics.asp?TopicID=102
Helped me get an idea.

Cheers

I tried to declare the variables using the @ method but it seemed the problem i had was a "where" clause in the "insert into" statement which buggered everything up.

It's all sorted now though thanks.

Regards,
Dave

UD4 || Flash || Access ||Web Hosting