Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[XTGeminiman]

I'm getting an error on when i try to add a new record in mysql...

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]Duplicate entry '0' for key 1

/replyprocess.asp, line 32

I have no idea what this error means, so that could be a start. How can I fix it tho?



<%
Option Explicit
Dim sql, boardquer, rspost, rsboard, username, topicid, title, description, ip, rsuser, timeposted, lasttime, game, rsboardnum

username = Request.cookies("username")
game=request.querystring("game")
topicid=request.querystring("topicid")
boardquer=request.querystring("boardquer")
title = Request.Form("title")
description = Request.Form("message")
ip=request.servervariables("remote_addr")
%>
<!--#include file="conn.asp"-->
<%
sql = "SELECT * FROM Users WHERE username = '" & username & "'"
Set rsuser = Server.CreateObject("ADODB.Recordset")
rsuser.Open sql, conn, 3, 3
rsuser("points")=rsuser("points")+1
rsuser("boardposts")=rsuser("boardposts")+1
rsuser("status")=rsuser("status")+1
rsuser.update

Set rspost = Server.CreateObject("ADODB.Recordset")
rspost.open "SELECT * from " & game & "reply", conn, 3, 3
rspost.AddNew
rspost("author") = username
rspost("title") = Formattext(title)
rspost("topicid")=topicid
rspost("description") = Formattext(description)
rspost("ip")=ip
rspost("timeposted") = now()
rspost.Update

Set rsboard = Server.CreateObject("ADODB.Recordset")
rsboard.open "SELECT * FROM " & game & "topic where topicid Like '" & topicid & "%'", conn, 3, 3
rsboard("timeposted")=now()
rsboard("lasttime")=now()
rsboard("replies")=rsboard("replies") + 1
rsboard.update

Set rsboardnum = Server.CreateObject("ADODB.Recordset")
rsboardnum.open "SELECT * FROM list where boardlink Like '" & game & "%'", conn, 3, 3
rsboardnum("lastpost")=now()
rsboardnum("posts")=rsboardnum("posts") + 1
rsboardnum.update

response.redirect("board.asp?boardquer="&boardquer&"&game="&game&"")
%>

<%
rsboardnum.close
set rsboardnum = nothing
rsboard.close
set rsboard = nothing
rspost.close
set rspost = nothing
rsuser.close
set rsuser = nothing
conn.close
set conn = nothing
%><!--#include file="aspedge_formatfunctions.asp"-->


Line 32 is...


rspost.Update

[fractalvibes]

The
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
error basically means that the database doesn't know what you are requesting of it...

Try doing response.writes of all the values and the sql you are attempting to execute. Chances are something is missing, or improperly quoted (or not) or the datatypes are incorrect.

Probably a very simple error.

fv

[miranda]

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]Duplicate entry '0' for key 1

Is telling you that you have a Unique Key set on the 1st column in the table, and that you already have an entry in the database with that value.

[XTGeminiman]

yeah miranda, that's what i have. I have an int field (ID) that's my primary key and I just realized that the auto increment is off. I transferred these tables from access and when I click on the checkbox for autoincrement (in navicat) it gives me the following error...

1062 - Duplicate entry '1' for key 1

How can I get around this and make the id field an autoincrement?

[miranda]

you already have it set as unique so try this Make a new page with just this on it and open the page. This is assuming that your field type is INT

<!--#include file="conn.asp"-->
<%
Dim SQL
SQL = Conn.Execute("ALTER TABLE Users CHANGE ID ID INT( 11 ) DEFAULT '0' NOT NULL AUTO_INCREMENT"

If Err.Number = 0 Then
Response.Write "ID field changed to Auto_Increment"
End If
Conn.Close
Set Conn = Nothing
%>


BTW if you have PHP available on the server get phpmyadmin and use it to make changes to the database that way

[XTGeminiman]

It's supposed to be like this right? The id's actually called replyid.


<!--#include file="conn.asp"-->
<%
Dim SQL
SQL = Conn.Execute("ALTER TABLE xboxreply CHANGE replyid ID INT( 11 ) DEFAULT '0' NOT NULL AUTO_INCREMENT")

If Err.Number = 0 Then
Response.Write "ID field changed to Auto_Increment"
End If
Conn.Close
Set Conn = Nothing
%>


If thats right I'm getting this error

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]Duplicate entry '1' for key 1

/chane2.asp, line 4

[miranda]

actually the syntax is ALTER TABLE your_table_name CHANGE your_field_name your_field_name DATATYPE DEFAULT '0' NOT NULL AUTO_INCREMENT


<!--#include file="conn.asp"-->
<%
Dim SQL
SQL = Conn.Execute("ALTER TABLE xboxreply CHANGE replyid replyid INT( 11 ) DEFAULT '0' NOT NULL AUTO_INCREMENT")

If Err.Number = 0 Then
Response.Write "replyid field changed to Auto_Increment"
End If
Conn.Close
Set Conn = Nothing
%>

[XTGeminiman]

ok, i'm still getting this error...

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[MySQL][ODBC 3.51 Driver][mysqld-4.0.17-nt]Duplicate entry '1' for key 1

/chane.asp, line 4

:(

[miranda]

This is going to sound crazy but perhaps someone in the PHP forum can give you the syntax for the ALTER TABLE statement. Then you can just apply the code to your asp page... Then again if php is available on your server just go to phpMyAdmin (http://www.phpmyadmin.net/home_page/index.php) get that tool and make the changes there. Much easier to change it there.

[miranda]

It occured to me a while ago that the reason you were getting the error is because you have a record in the table already so the part that says DEFAULT '0' is failing because there is records already there. change the default to a number higher than the largest number you have listed, it should work then.