insert error

[Stijn Goris]

Hi all,

I have a table containing an ID (unique, autoincrement) and some other
fields including IP. When I try to insert the 'ip' I get a "Duplicate entry
for key 1" error (using INSERT INTO tblip (ip) VALUES ('$REMOTE_ADDR
$_agent') ).
This table is needed to handle the amount of online users. The strange thing
is that this happens only with a minority of users. All other users of the
webiste have reported no problem whatsoever.

Someone has an idea whats going wrong here

kind regards
Stijn

[RG]

"Stijn Goris" <mepisto@hotmail.com> wrote in message
news:3f78316e$0$24155$ba620e4c@reader0.news.skynet .be...

> Hi all,
>
> I have a table containing an ID (unique, autoincrement) and some other
> fields including IP. When I try to insert the 'ip' I get a "Duplicate

entry

> for key 1" error (using INSERT INTO tblip (ip) VALUES ('$REMOTE_ADDR
> $_agent') ).
> This table is needed to handle the amount of online users. The strange

thing

> is that this happens only with a minority of users. All other users of the
> webiste have reported no problem whatsoever.
>
> Someone has an idea whats going wrong here
>
> kind regards
> Stijn
>
>

You may have the IP field set to unique.
This would explain what is happening.
Have a look
RG

[Stijn Goris]

"RG" <Me@NotTellingYa.com> wrote in message
news:3f784516$0$65581$65c69314@mercury.nildram.net ...

>
> "Stijn Goris" <mepisto@hotmail.com> wrote in message
> news:3f78316e$0$24155$ba620e4c@reader0.news.skynet .be...

> > Hi all,
> >
> > I have a table containing an ID (unique, autoincrement) and some other
> > fields including IP. When I try to insert the 'ip' I get a "Duplicate

> entry

> > for key 1" error (using INSERT INTO tblip (ip) VALUES ('$REMOTE_ADDR
> > $_agent') ).
> > This table is needed to handle the amount of online users. The strange

> thing

> > is that this happens only with a minority of users. All other users of

the

> > webiste have reported no problem whatsoever.
> >
> > Someone has an idea whats going wrong here
> >
> > kind regards
> > Stijn
> >
> >

>
> You may have the IP field set to unique.
> This would explain what is happening.
> Have a look
> RG
>
>
>

The field ip is not set to unique...

[Matthias Esken]

"Stijn Goris" <mepisto@hotmail.com> schrieb:

> INSERT INTO tblip (ip) VALUES ('$REMOTE_ADDR $_agent')

Please give us your table definition.

Regards,
Matthias

[RG]

"Stijn Goris" <mepisto@hotmail.com> wrote in message
news:3f78486d$0$31734$ba620e4c@reader1.news.skynet .be...

>
> "RG" <Me@NotTellingYa.com> wrote in message
> news:3f784516$0$65581$65c69314@mercury.nildram.net ...

> >
> > "Stijn Goris" <mepisto@hotmail.com> wrote in message
> > news:3f78316e$0$24155$ba620e4c@reader0.news.skynet .be...

> > > Hi all,
> > >
> > > I have a table containing an ID (unique, autoincrement) and some other
> > > fields including IP. When I try to insert the 'ip' I get a "Duplicate

> > entry

> > > for key 1" error (using INSERT INTO tblip (ip) VALUES ('$REMOTE_ADDR
> > > $_agent') ).
> > > This table is needed to handle the amount of online users. The strange

> > thing

> > > is that this happens only with a minority of users. All other users of

> the

> > > webiste have reported no problem whatsoever.
> > >
> > > Someone has an idea whats going wrong here
> > >
> > > kind regards
> > > Stijn
> > >
> > >

> >
> > You may have the IP field set to unique.
> > This would explain what is happening.
> > Have a look
> > RG
> >
> >
> >

>
> The field ip is not set to unique...
>
>

Oh well. It's not that then :)
RG

[Jeffrey Silverman]

On Mon, 29 Sep 2003 17:14:06 +0200, Stijn Goris wrote:

<snip!>

> The field ip is not set to unique...

It must be either unique or primary key. That is what the reported error
means.

need....more.....information.....
--
Jeffrey D. Silverman | jeffrey AT jhu DOT edu
Johns Hopkins University | Baltimore, MD
Website | [url]http://www.wse.jhu.edu/newtnotes/[/url]

[Rachel]

I need to insert some form values into a database, I am
able to do so using a custom query in the DRW in
Frontpage ( 2002 ). ( Where form is page1.asp sending
results to page2.asp which actually does the insert )
But what I really want to do ( for additional
flexibility ) is to do the insert myself with asp code.
However, when I do so I recieve the SQL Server Error 8152
'String or Binary data will be truncated' . If I take
my values and insert them via the Query Analizer, it
works.
I have the same problem with two different insert
statements, so I don't believe it to be specific to the
statement itself. But, if I knew the answer, I wouldn't
be posting this.

My Insert code looks like this:

<%
Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Ads_ConnectionString")

Set objRS = objDC.Execute("INSERT INTO CustomerInfo
(LastName, FirstName, CustomerID, Address1, Phone1, Zip,
City, State) VALUES
('::LastName::', '::FirstName::', '::Email::', '::Address
::', '::Phone::', '::Zip::', '::City::', '::State::')")
%>
<%
' Close Data Access Objects and free DB variables
objRS.Close
Set objRS = Nothing
objDC.Close
Set objDC = Nothing
%>


Any Ideas??
Thanks so much!!

..

[Mark Schupp]

Are you actually trying to insert the string "::State::" in the column
STATE? Is STATE a 2 character column expecting the abr.. for the state name?

--
Mark Schupp
Head of Development
Integrity eLearning
[url]www.ielearning.com[/url]


"Rachel" <rkinder10@mchsi.com> wrote in message
news:10c301c3893a$49fea700$a101280a@phx.gbl...

> I need to insert some form values into a database, I am
> able to do so using a custom query in the DRW in
> Frontpage ( 2002 ). ( Where form is page1.asp sending
> results to page2.asp which actually does the insert )
> But what I really want to do ( for additional
> flexibility ) is to do the insert myself with asp code.
> However, when I do so I recieve the SQL Server Error 8152
> 'String or Binary data will be truncated' . If I take
> my values and insert them via the Query Analizer, it
> works.
> I have the same problem with two different insert
> statements, so I don't believe it to be specific to the
> statement itself. But, if I knew the answer, I wouldn't
> be posting this.
>
> My Insert code looks like this:
>
> <%
> Set objDC = Server.CreateObject("ADODB.Connection")
> objDC.Open Application("Ads_ConnectionString")
>
> Set objRS = objDC.Execute("INSERT INTO CustomerInfo
> (LastName, FirstName, CustomerID, Address1, Phone1, Zip,
> City, State) VALUES
> ('::LastName::', '::FirstName::', '::Email::', '::Address
> ::', '::Phone::', '::Zip::', '::City::', '::State::')")
> %>
> <%
> ' Close Data Access Objects and free DB variables
> objRS.Close
> Set objRS = Nothing
> objDC.Close
> Set objDC = Nothing
> %>
>
>
> Any Ideas??
> Thanks so much!!
>
> .
>
>

[Rachel]

I am trying to insert the value of the field named State
into the column named state. ( Yes, I know it should be
something like txtState ). Are you saying that I should
set the value of the field to a string variable and use
that in the insert statement?

>-----Original Message-----
>Are you actually trying to insert the string "::State::"

in the column

>STATE? Is STATE a 2 character column expecting the abr..

for the state name?

>
>--
>Mark Schupp
>Head of Development
>Integrity eLearning
>[url]www.ielearning.com[/url]
>
>
>"Rachel" <rkinder10@mchsi.com> wrote in message
>news:10c301c3893a$49fea700$a101280a@phx.gbl...

>> I need to insert some form values into a database, I am
>> able to do so using a custom query in the DRW in
>> Frontpage ( 2002 ). ( Where form is page1.asp sending
>> results to page2.asp which actually does the insert )
>> But what I really want to do ( for additional
>> flexibility ) is to do the insert myself with asp code.
>> However, when I do so I recieve the SQL Server Error

8152

>> 'String or Binary data will be truncated' . If I take
>> my values and insert them via the Query Analizer, it
>> works.
>> I have the same problem with two different insert
>> statements, so I don't believe it to be specific to the
>> statement itself. But, if I knew the answer, I

wouldn't

>> be posting this.
>>
>> My Insert code looks like this:
>>
>> <%
>> Set objDC = Server.CreateObject("ADODB.Connection")
>> objDC.Open Application("Ads_ConnectionString")
>>
>> Set objRS = objDC.Execute("INSERT INTO CustomerInfo
>> (LastName, FirstName, CustomerID, Address1, Phone1,

Zip,

>> City, State) VALUES
>>

('::LastName::', '::FirstName::', '::Email::', '::Address

>> ::', '::Phone::', '::Zip::', '::City::', '::State::')")
>> %>
>> <%
>> ' Close Data Access Objects and free DB variables
>> objRS.Close
>> Set objRS = Nothing
>> objDC.Close
>> Set objDC = Nothing
>> %>
>>
>>
>> Any Ideas??
>> Thanks so much!!
>>
>> .
>>
>>

>
>
>.
>

[Ray at]

A field from what? A posted form? I'll assume that.

Try code such as this:

<%
Function Safen(s)
'''to handle any ' characters in values - will mess up sql delimitation
Safen = Replace(s, "'", "''")
End Function

Dim objDC, sSQL
Dim sLastname, sFirstname, sEmail, sAddress, sPhone, sZIP, sCity, sState

sLastname = Safen(Request.Form("txtLastname"))
sFirstname = Safen(Request.Form("txtFirstname"))
sEmail = Safen(Request.Form("txtEmail"))
sAddress = Safen(Request.Form("txtAddress"))
sPhone = Safen(Request.Form("txtPhone"))
sZIP = Safen(Request.Form("txtZIP"))
sCity = Safen(Request.Form("txtCity"))
sState = Safen(Request.Form("txtState"))

sSQL = "INSERT INTO CustomerInfo
(LastName,FirstName,CustomerID,Address1,Phone1,ZIP ,City,State) VALUES (" & _
"'" & sLastname & "'," & _
"'" & sFirstname & "'," & _
"'" & sEmail & "'," & _
"'" & sAddress & "'," & _
"'" & sPhone & "'," & _
"'" & sZIP & "'," & _
"'" & sCity & "'," & _
"'" & sState & "')"


Set objDC = Server.CreateObject("ADODB.Connection")
objDC.Open Application("Ads_ConnectionString")
'''don't create a recordset when doing an insert
objDC.Execute sSQL
' Close Data Access Objects and free DB variables
objDC.Close
Set objDC = Nothing
%>

This assumes that those are your form field names and all your columns in
your DB are text types.

Ray at home



"Rachel" <rkinder10@mchsi.com> wrote in message
news:1eae801c38953$3c88b930$a601280a@phx.gbl...

> I am trying to insert the value of the field named State
> into the column named state. ( Yes, I know it should be
> something like txtState ). Are you saying that I should
> set the value of the field to a string variable and use
> that in the insert statement?
>
>

> >-----Original Message-----
> >Are you actually trying to insert the string "::State::"

> in the column

> >STATE? Is STATE a 2 character column expecting the abr..

> for the state name?

> >
> >--
> >Mark Schupp
> >Head of Development
> >Integrity eLearning
> >[url]www.ielearning.com[/url]
> >
> >
> >"Rachel" <rkinder10@mchsi.com> wrote in message
> >news:10c301c3893a$49fea700$a101280a@phx.gbl...

> >> I need to insert some form values into a database, I am
> >> able to do so using a custom query in the DRW in
> >> Frontpage ( 2002 ). ( Where form is page1.asp sending
> >> results to page2.asp which actually does the insert )
> >> But what I really want to do ( for additional
> >> flexibility ) is to do the insert myself with asp code.
> >> However, when I do so I recieve the SQL Server Error

> 8152

> >> 'String or Binary data will be truncated' . If I take
> >> my values and insert them via the Query Analizer, it
> >> works.
> >> I have the same problem with two different insert
> >> statements, so I don't believe it to be specific to the
> >> statement itself. But, if I knew the answer, I

> wouldn't

> >> be posting this.
> >>
> >> My Insert code looks like this:
> >>
> >> <%
> >> Set objDC = Server.CreateObject("ADODB.Connection")
> >> objDC.Open Application("Ads_ConnectionString")
> >>
> >> Set objRS = objDC.Execute("INSERT INTO CustomerInfo
> >> (LastName, FirstName, CustomerID, Address1, Phone1,

> Zip,

> >> City, State) VALUES
> >>

> ('::LastName::', '::FirstName::', '::Email::', '::Address

> >> ::', '::Phone::', '::Zip::', '::City::', '::State::')")
> >> %>
> >> <%
> >> ' Close Data Access Objects and free DB variables
> >> objRS.Close
> >> Set objRS = Nothing
> >> objDC.Close
> >> Set objDC = Nothing
> >> %>
> >>
> >>
> >> Any Ideas??
> >> Thanks so much!!
> >>
> >> .
> >>
> >>

> >
> >
> >.
> >

[Rachel]

Thanks so much!!!!!!!!!!!!!!!!!!!!!!

>-----Original Message-----
>A field from what? A posted form? I'll assume that.
>
>Try code such as this:
>
><%
>Function Safen(s)
> '''to handle any ' characters in values - will mess

up sql delimitation

> Safen = Replace(s, "'", "''")
>End Function
>
>Dim objDC, sSQL
>Dim sLastname, sFirstname, sEmail, sAddress, sPhone,

sZIP, sCity, sState

>
>sLastname = Safen(Request.Form("txtLastname"))
>sFirstname = Safen(Request.Form("txtFirstname"))
>sEmail = Safen(Request.Form("txtEmail"))
>sAddress = Safen(Request.Form("txtAddress"))
>sPhone = Safen(Request.Form("txtPhone"))
>sZIP = Safen(Request.Form("txtZIP"))
>sCity = Safen(Request.Form("txtCity"))
>sState = Safen(Request.Form("txtState"))
>
>sSQL = "INSERT INTO CustomerInfo
>

(LastName,FirstName,CustomerID,Address1,Phone1,ZIP ,City,St
ate) VALUES (" & _

> "'" & sLastname & "'," & _
> "'" & sFirstname & "'," & _
> "'" & sEmail & "'," & _
> "'" & sAddress & "'," & _
> "'" & sPhone & "'," & _
> "'" & sZIP & "'," & _
> "'" & sCity & "'," & _
> "'" & sState & "')"
>
>
>Set objDC = Server.CreateObject("ADODB.Connection")
>objDC.Open Application("Ads_ConnectionString")
>'''don't create a recordset when doing an insert
>objDC.Execute sSQL
>' Close Data Access Objects and free DB variables
>objDC.Close
>Set objDC = Nothing
>%>
>
>This assumes that those are your form field names and

all your columns in

>your DB are text types.
>
>Ray at home
>
>
>
>"Rachel" <rkinder10@mchsi.com> wrote in message
>news:1eae801c38953$3c88b930$a601280a@phx.gbl...

>> I am trying to insert the value of the field named

State

>> into the column named state. ( Yes, I know it should be
>> something like txtState ). Are you saying that I

should

>> set the value of the field to a string variable and use
>> that in the insert statement?
>>
>>

>> >-----Original Message-----
>> >Are you actually trying to insert the

string "::State::"

>> in the column

>> >STATE? Is STATE a 2 character column expecting the

abr..

>> for the state name?

>> >
>> >--
>> >Mark Schupp
>> >Head of Development
>> >Integrity eLearning
>> >[url]www.ielearning.com[/url]
>> >
>> >
>> >"Rachel" <rkinder10@mchsi.com> wrote in message
>> >news:10c301c3893a$49fea700$a101280a@phx.gbl...
>> >> I need to insert some form values into a database,

I am

>> >> able to do so using a custom query in the DRW in
>> >> Frontpage ( 2002 ). ( Where form is page1.asp

sending

>> >> results to page2.asp which actually does the

insert )

>> >> But what I really want to do ( for additional
>> >> flexibility ) is to do the insert myself with asp

code.

>> >> However, when I do so I recieve the SQL Server Error

>> 8152

>> >> 'String or Binary data will be truncated' . If I

take

>> >> my values and insert them via the Query Analizer, it
>> >> works.
>> >> I have the same problem with two different insert
>> >> statements, so I don't believe it to be specific to

the

>> >> statement itself. But, if I knew the answer, I

>> wouldn't

>> >> be posting this.
>> >>
>> >> My Insert code looks like this:
>> >>
>> >> <%
>> >> Set objDC = Server.CreateObject("ADODB.Connection")
>> >> objDC.Open Application("Ads_ConnectionString")
>> >>
>> >> Set objRS = objDC.Execute("INSERT INTO CustomerInfo
>> >> (LastName, FirstName, CustomerID, Address1, Phone1,

>> Zip,

>> >> City, State) VALUES
>> >>

>>

('::LastName::', '::FirstName::', '::Email::', '::Address

>>
>> ::', '::Phone::', '::Zip::', '::City::', '::State::')")

>> >> %>
>> >> <%
>> >> ' Close Data Access Objects and free DB variables
>> >> objRS.Close
>> >> Set objRS = Nothing
>> >> objDC.Close
>> >> Set objDC = Nothing
>> >> %>
>> >>
>> >>
>> >> Any Ideas??
>> >> Thanks so much!!
>> >>
>> >> .
>> >>
>> >>
>> >
>> >
>> >.
>> >

>
>
>.
>

[Ray at]

You're welcome. :]

Ray at home


"Rachel" <rkinder10@mchsi.com> wrote in message
news:0c1a01c38aeb$a7070a70$a101280a@phx.gbl...

> Thanks so much!!!!!!!!!!!!!!!!!!!!!!

[Abdlah]

:confused; My insert statement in making me crazy, I have checked my code over
and over and cannot seem to find out what is wrong. Please check my code and
help me with pointers.

As you can see from the debug information, the data seems to be reported to
colffusion alright, so what is causing the data mismatch error? I have checked
to make sure that the date fields are defined as date/time type and I have
checked the box Return TimeStamp as String in Adminidtrator. TIA

<cftransaction>
<cfquery datasource="#request.datasource#">
Insert into Consumer(User_id,
First_name,
Middle_name,
Last_name,
Gender,
Occupation,
DOB,
Cnsmr_Password,
Cnsmr_PasswordHint,
Date_created)
Values('#Trim(Form.loginID)#',
'#Trim(Form.firstName)#',
'#Trim(Form.middleName)#',
'#Trim(Form.lastName)#',
'#Trim(Form.gender)#',
'#Trim(Form.occupation)#',
#CreateODBCDate(Form.dateOfBirth)#,
'#Trim(Form.password)#',
'#Trim(Form.passwordHint)#',
#CreateODBCDate(Now())#)
</cfquery>

This is what the debug screen shows when I submit a form:

Insert into Consumer(User_id, First_name, Middle_name, Last_name, Gender,
Occupation, DOB,

Cnsmr_Password, Cnsmr_PasswordHint, Date_created) Values('zuori', 'zuori',
'ma''naa', 'Sadiiq',

'No', '', {d '2002-02-02'}, 'zuori22', 'zuori22', {d '2005-09-30'})
DATASOURCE eghanadb
VENDORERRORCODE -3030
SQLSTATE 22018

[mxstu]

1. Why is th e"Return TimeStamp as String" option checked?
2. What are the data types of the columns in your table:

User_id = text | numeric | ...?
First_name = ?
Middle_name = ?
Last_name = ?
Gender = ?
Occupation = ?
DOB = ?
Cnsmr_Password = ?
Cnsmr_PasswordHint = ?
Date_created = ?


3. Are you able to run the statement in Access? Note - date values are
reformatted

Insert into Consumer(User_id, First_name, Middle_name, Last_name, Gender,
Occupation, DOB,
Cnsmr_Password, Cnsmr_PasswordHint, Date_created) Values('zuori', 'zuori',
'ma''naa', 'Sadiiq',
'No', '', '2002-02-02', 'zuori22', 'zuori22', '2005-09-30')

[SQLMenace]

What DB are you using?

[mxstu]

SQLMenace,

I believe they are using Access (from the OP's other two threads).

[SQLMenace]

I believe that you have to put pound signs around dates for access

[mxstu]

The pound signs look right to me, since they are only needed around the
outer-most functions here.
....
#CreateODBCDate(Form.dateOfBirth)#
#CreateODBCDate(Now())#
....

I am thinking it is a problem with a column data type. Perhaps the Gender
column. Maybe it is "yes/no" and not "text". However, that is just a guess
because the OP didn't indicate the column data types in their other thread.

[mxstu]

Originally posted by: SQLMenace
I believe that you have to put pound signs around dates for access
Oh! You're talking about handling dates within the Access application itself.
It may be allowed, but I think CreateODBCDate() and CreateODBCDateTime() are
the preferred method of handling dates with cfquery + Access.

[Dan Bracuk]

Only if cold fusion is using odbc to connect. We used to use oledb when we had
access databases. We used octothorps in datefields. Had to use about 3 of
them if I remember correctly.

Originally posted by: mxstu
Originally posted by: SQLMenace
I believe that you have to put pound signs around dates for access
Oh! You're talking about handling dates within the Access application itself.
It may be allowed, but I think CreateODBCDate() and CreateODBCDateTime() are
the preferred method of handling dates with cfquery + Access.