Insert Records

Hello! I'm having some trouble adding new records to my database. It just
won't do it. Please take a look and let me know if there are any ideas.


<%

mystring=request.form("employee")
Arremp=split(mystring, ",")
'Response.write Arremp ((0))



set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\d07ww001\apps\Overtime 2006 1.3.mdb"


Strsql = "SELECT EmpID, LastName, FirstName FROM tblEmployee WHERE AsgnCC =
'530f' AND OTRoster ='" & Request.Form("proj") & "' AND LastName='" & Arremp
((0)) & "' "
set rsAdd=conn.execute(Strsql,,1)
strid= rsAdd("EmpID")
Response.Write strid

sql="INSERT INTO tblOvertime (OTCostCtr,OTRoster,"
sql=sql & "EmpID,OTEmployee,OTDate,OTOp,OTHours,OTType,OTRefusal,OTNoC ontact,
OTLastNumCalled,OTTimeIn,OTComments)"
sql=sql & " VALUES "
sql=sql & "('" & Request.Form("CostCtr") & "',"
sql=sql & "'" & Request.Form("proj") & "',"
sql=sql & "" & rsAdd("EmpID") & ","
sql=sql & "'" & Request.Form("Employee") & "',"
sql=sql & "'" & Request.Form("evt") & "',"
sql=sql & "" & Request.Form("op") & ","
sql=sql & "" & Request.Form("OTHours") & ","
sql=sql & "'" & Request.Form("OTType") & "',"
sql=sql & "'" & Request.Form("OTRefusal") & "',"
sql=sql & "'" & Request.Form("ConType") & "',"
sql=sql & "'" & Request.Form("NumCalled") & "',"
sql=sql & "'" & Request.Form("TimeIn") & "',"
sql=sql & "'" & Request.Form("OTComments") & "')"

on error resume next
conn.Execute sql, recaffected

if err<>0 then
Response.Write("Sorry cannot add record!")
else
Response.Write("

" & recaffected & " record added

Re: Insert Records

Mike Will via WebmasterKB.com wrote:
> Hello! I'm having some trouble adding new records to my database.
> It just won't do it. Please take a look and let me know if there are
> any ideas.
>
>
> <%
>
> mystring=request.form("employee")
> Arremp=split(mystring, ",")
> 'Response.write Arremp ((0))
>
>
>
> set conn=Server.CreateObject("ADODB.Connection")
> conn.Provider="Microsoft.Jet.OLEDB.4.0"
> conn.Open "\\d07ww001\apps\Overtime 2006 1.3.mdb"

This database path (and filename) looks suspect. Have you confirmed that
it is actually connecting?

>
>
> Strsql = "SELECT EmpID, LastName, FirstName FROM tblEmployee WHERE
> AsgnCC = '530f' AND OTRoster ='" & Request.Form("proj") & "' AND
> LastName='" & Arremp ((0)) & "' "
> set rsAdd=conn.execute(Strsql,,1)
> strid= rsAdd("EmpID")
> Response.Write strid

If you are getting anything from this response.write, then that would be
a confirmation that the connection string works.

>
> sql="

I cannot debug this statement without
1. knowing what it is - the only way you can tell me that is to:
response.write sql
2. knowing the datatypes of the fields involved to verify that you've
used the correct delimiters

Usually, writing the statement to response pinpoints the problem. If
you've built the statement correctly you should be able to copy it to
the clipboard from the browser window, open your database in Access,
paste the statement into the SQL View of a new Access Query Builder
window and run it without modification.

Further points to consider:
Your use of dynamic sql is leaving you vulnerable to hackers using sql
injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

See here for a better, more secure way to execute your queries by using
parameter markers:
http://groups-beta.google.com/group/microsoft.public.inetser ver.asp.db/msg/72e36562fee7804e

Personally, I prefer using stored procedures, or saved parameter queries
as
they are known in Access:

Access:
http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&sel m=e6lLVvOcDHA.1204%40TK2MSFTNGP12.phx.gbl

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&c2coff=1& selm=eHYxOyvaDHA.4020%40tk2msftngp13.phx.gbl


--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Insert Records

Thanks Bob for all your help. I'll start taking a look at a few of the items
you mentioned.

Response.write strsql returns this:

SELECT EmpID, LastName, FirstName FROM tblEmployee WHERE AsgnCC = '530f' AND
OTRoster ='36-06' AND LastName='Banion'

Response.write sql returns this:

INSERT INTO tblOvertime (OTCostCtr,OTRoster,EmpID,OTEmployee,OTDate,OTOp,
OTHours,OTType,OTRefusal,OTNoContact,OTLastNumCalled,OTTimeI n,OTComments)
VALUES ('530f','36-06',32,'Banion, Alan','4/5/07','1',4.5,'Extended Shift','',
'','','','')

There seems to be a connection.

--
Message posted via http://www.webmasterkb.com

Re: Insert Records

Okay I'm trying your suggestion. I'm getting this error.

Microsoft VBScript compilation error '800a0414'

Cannot use parentheses when calling a Sub

/overtimeroster/AddEmployee.asp, line 59

adExecuteNoRecords)

Here is the block of code that I'm trying to execute:

set cmd=createobject("adodb.command")
with cmd
.CommandText=sSQL
.CommandType=adCmdText
Set .ActiveConnection=CN
on error resume next
.Execute(,array(a_CostCtr, a_OTRoster, a_EmpID, a_OTEmployee, a_OTDate,
a_OTOp, a_OTHours, a_OTType, a_OTRefusal, a_OTNoCon, a_OTNumCalled,
a_OTTimeIn, a_OTComments), _
adExecuteNoRecords)
if err<>0 then
Response.Write("Your record has been added to the database")
end if
end with

--
Message posted via http://www.webmasterkb.com

Re: Insert Records

Mike Will via WebmasterKB.com wrote:
> Okay I'm trying your suggestion. I'm getting this error.
>
> Microsoft VBScript compilation error '800a0414'
>
> Cannot use parentheses when calling a Sub
>
> /overtimeroster/AddEmployee.asp, line 59
>
> adExecuteNoRecords)
>
> Here is the block of code that I'm trying to execute:
>
> set cmd=createobject("adodb.command")
> with cmd
> .CommandText=sSQL
> .CommandType=adCmdText
> Set .ActiveConnection=CN
> on error resume next
> .Execute(,array(a_CostCtr, a_OTRoster, a_EmpID, a_OTEmployee,
> a_OTDate, a_OTOp, a_OTHours, a_OTType, a_OTRefusal, a_OTNoCon,
> a_OTNumCalled, a_OTTimeIn, a_OTComments), _
> adExecuteNoRecords)
> if err<>0 then
> Response.Write("Your record has been added to the database")
> end if
> end with
>

So remove the parentheses (is that so hard? :-) )

..Execute ,array(a_CostCtr, a_OTRoster, a_EmpID, a_OTEmployee,
a_OTDate, a_OTOp, a_OTHours, a_OTType, a_OTRefusal, a_OTNoCon,
a_OTNumCalled, a_OTTimeIn, a_OTComments), _
adExecuteNoRecords

PS, you have made sure all of those "a_" variables contain the values
you expect them to contain, correct?



--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Insert Records

Mike Will via WebmasterKB.com wrote:
> Thanks Bob for all your help. I'll start taking a look at a few of
> the items you mentioned.
>
> Response.write strsql returns this:
>
> SELECT EmpID, LastName, FirstName FROM tblEmployee WHERE AsgnCC =
> '530f' AND OTRoster ='36-06' AND LastName='Banion'

So this is what you expect, correct? is this query functioning
correctly?

>
> Response.write sql returns this:
>
> INSERT INTO tblOvertime
> (OTCostCtr,OTRoster,EmpID,OTEmployee,OTDate,OTOp,
>
OTHours,OTType,OTRefusal,OTNoContact,OTLastNumCalled,OTTimeI n,OTComments
)
> VALUES ('530f','36-06',32,'Banion, Alan','4/5/07','1',4.5,'Extended
> Shift','', '','','','')
>
> There seems to be a connection.
>

Why? Is this not the query you expected to be created? If not, show us
what it should look like.

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Insert Records

Yeah I smacked myself after replying. I removed the parentheses and it took
off. The only things is it adding the record to the database.

--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp-db/200704/1

Re: Insert Records

Okay the when everything looks great. The variables are storing the
information from the form. I response.write sSQL and it looks correct.
There are no errors. The message reads "Your record has been added to the
database!". But I check the database and record isn't being added.

Mike Will wrote:
>Yeah I smacked myself after replying. I removed the parentheses and it took
>off. The only things is it adding the record to the database.

--
Message posted via http://www.webmasterkb.com

Re: Insert Records

Mike Will via WebmasterKB.com wrote:
> Okay the when everything looks great. The variables are storing the
> information from the form. I response.write sSQL and it looks
> correct. There are no errors. The message reads "Your record has
> been added to the database!". But I check the database and record
> isn't being added.
>
When you run the query that was written to Response in the Access Query
Builder, is the record added? If so, there is still a problem with your
ADO code. I'm not sure what the problem is. Are you pointing your
connection at the correct database file? Could it be this?
http://support.microsoft.com/?kbid=240317
http://support.microsoft.com/kb/200300
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Re: Insert Records

I ran the query in Access. There was a type conversion failure. I was
inputting a text field for OTTimeIn when it should have been a date field.
Problem fixed. Thanks for the helpful advice!!

--
Message posted via WebmasterKB.com
http://www.webmasterkb.com/Uwe/Forums.aspx/asp-db/200704/1