PDA

Click to See Complete Forum and Search --> : SQL Int question

CrazyGaz
06-13-2003, 01:28 PM
I have a "comments" page where it requests from a database(mySQL not that it matters) and in the table in the database I have user_name, user_comment and id.

I have it reading from the database but I want it to automatically find out the number of the highest id in the table and add one to it, e.g.



|user_name___|user_comment___|id|
|Gaz__________|This is a test_____|1|
|Gary_________|Another test______|2|
|Gazzer_______|Yet Another Test__|3|


(not the best looking ascii table in the world eh :))

so the id increases by one everytime, I have it at the moment writing it all but the id is always "1".

help appreciated.

Cheers, Gaz

so fair I have

<%
aid=RS("id")+1
%>

and then I write it to the database but it always displays the id as 1, then the rest 2's.
Motabobo
06-13-2003, 01:41 PM
Why don't you just use an auto-increment type of field ?
CrazyGaz
06-13-2003, 01:48 PM
I wasn't aware of that command, so is there any ASP I need to do?

when I try to make the id column auto_increment I get the following error.

"mysql> alter table people_comments add id int auto_increment;
ERROR 1075: Incorrect table definition; There can only be one auto column and it
must be defined as a key"

cheers, Gaz.

|EDIT|I got the error sorted out, now just for the ASP|EDIT|

Cheers mate, that's perfect.
khaki
06-13-2003, 01:55 PM
hi Crazy...

are you sure that the id field is being recognized as a numeric field?

maybe try this...
instead of finding the highest value...
maybe you could just count the records.

something like:

SELECT COUNT(id) AS aid FROM ...

and then use your:

<%
aid= aid +1
%>

at least you know that you are getting the highest number of id's from COUNT.

just a thought...
;) k
CrazyGaz
06-13-2003, 02:10 PM
One problem down one to go. I now have it updating the id.

I now want it to take me to the comment from the list. In the list I have


<% while not RS.EOF and not RS.BOF%>
<tr>
<td><a href="view.asp?id=<%=RS("id")%>"><%=RS("user_name")%></a></td>
<%RS.movenext
wend %>


then in the view page I have


<%
Dim CS
Dim RS
Set CN=Server.CreateObject("ADODB.Connection")
CN.Open "DSN=mysql_dsn;driver={mysql};uid=gaz;password=;Database=comments"
sql= "Select * from people_comments where id=" & request("id")
Set RS = CN.Execute (sql)
Set RS2 = CN.Execute ("Select user_name, user_comment FROM people_comments where id=" & RS("id") )
%>

<html>
<table border=1>
<tr>
<td width="100">User Name</td>
<td width="400">Comment</td>
</tr>
<% while not RS.EOF and not RS.BOF %>
<tr>
<td><%=RS("user_name")%></td>
<td><%=RS("user_comment")%></td>
<%RS.movenext
wend %>
</tr>
</table>
</html>


now I would have thought that it would have only displayed the text for the message with a particular id...I thought wrong.

any suggestions?

sorry for the edit...again but I have realised I need Request.QueryString and not just request.