Digitproblem!

Rolleman · August 28th, 2003, 05:15 AM

I have a form which I use to add members to a database made in Access. The membernumbers are: 00001, 00002 and so on. Is there a way to just type "1" and get the zeros added and have a maximum of five digits? I wouldn't want 000020 later on since I use the numbers to login to a memberpage.

Rolleman · August 28th, 2003, 05:17 AM

I forgot... It is written in ASP using VBscript.

Imar · August 28th, 2003, 05:20 AM

Hi there,

If you always need 5 figures, you could try this:

Code:

<%

Dim UserID
Dim FullUserID
UserID = 123
FullUserID = Right("00000" & UserID, 5)

Response.Write(FullUserID)
' Outputs 00123

%>

This way, the UserID will be padded with zeros so the FullUserID will contain a 5 digit number with the UserID at the end.

HtH,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Rolleman · August 28th, 2003, 07:21 AM

It works... when I play around with it. But since I am a total idiot something makes it not working when I apply it to the application.

=====
HTML
=====
<HTML><title>Anmälan av Flamestoplådan</title>
<link href="default.css" rel="stylesheet" type="text/css">
<Body background="Img/main_bg.jpg">

<CENTER>
  <h1>Registrera medlem</h1>

  <img src="Img/NewLogo.gif" width="90" height="103">
    <br>
  <br>

<form method="Get" action="Provar.asp">
    <table width="300" border="0" cellspacing="1" cellpadding="1">
      <tr>
        <td align="right">Medlemsnummer: </td>
        <td><input type="text" size="35" name="BoxID">
        </td>
      </tr>
      <tr>
        <td align="right">FÃ¶rnamn: </td>
        <td><input type="text" size="35" name="Fornamn">
        </td>
      </tr>
      <tr>
        <td align="right">Efternamn: </td>
        <td><input type="text" size="35" name="Efternamn"></td>
      </tr>
      <tr>
        <td align="right">Adress: </td>
        <td><input type="text" size="35" name="Adress" ></td>
      </tr>
      <tr>
        <td align="right">Epost: </td>
        <td><input type="text" name="Email" size="35"></td>
      </tr>
      <tr>
        <td align="right">Postnummer: </td>
        <td><input type="text" size="35" name="Pnr"></td>
      </tr>
            <tr>
        <td align="right">Postadress: </td>
        <td><input type="text" size="35" name="Postadress"></td>
      </tr>
      <tr>
        <td align="right">Password:</td>
       <td><input type="password" size="35" name="Password"></td>
      </tr>
        <td height="24" align="right">Telefon:</td>
        <td align="left"><input type="text" size="35" name="Telefon"></td>
      </tr>
        <td height="24" align="right">Personnr:</td>
        <td align="left"><input type="text" size="35" name="Fnr"></td>
      </tr>
    <td height="24" align="right">Mor/Far ID:</td>
        <td align="left"><input type="text" size="35" name="MFID"></td>
      </tr>
    </table>
    <table width="300" border="0" cellpadding="1" cellspacing="1">
      <tr>
        <td width="179" align="right">
<input name="submit" type="submit" value=Spara>
        </td>
        <td width="10"></td>
        <td width="101"><input type=reset name=reset value=Reset></td>
      </tr>
    </table>
    <br>
  </FORM>
</center>

</BODY>
</HTML>

=====
ASP
=====

<?xml version="1.0" encoding="iso-8859-1"?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Untitled Document</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
</head>
<%

Dim MedlemsID
Dim FullMedlID
MedlemsID = Request.Form("BoxID")
'MedlemsID = 123
FullMedlID = Right("00000" & MedlemsID, 5)

'Request.QueryString("FullMedlID")
'Response.Write(FullMedlID)

' Outputs 00123

%>
<%
'FÃ¥ in "Form" variabler
Var1=request.querystring(FullMedlID)
Var2=request.querystring("Fornamn")
Var3=request.querystring("Efternamn")
Var4=request.querystring("Adress")
Var5=request.querystring("Pnr")
Var6=request.querystring("Postadress")
Var7=request.querystring("Email")
Var8=request.QueryString("PassWord")
Var9=request.QueryString("Telefon")
Var10=request.QueryString("Fnr")
Var11=request.QueryString("MFID")

Set Cn=Server.CreateObject("ADODB.Connection")
Cn.Open = "DSN=Flamer"

'SQL Statement
SQL = "INSERT INTO Box_ID (BoxID, FirstName, LastName, Adress, PostNr, PostAdress, EmailAdress, PassWord, TelNr, Fnr, MFID)"
SQL = SQL & "VALUES ('" & Var1 & "'"
SQL = SQL & " ,'" & Var2 & "'"
SQL = SQL & " ,'" & Var3 & "'"
SQL = SQL & " ,'" & Var4 & "'"
SQL = SQL & " ,'" & Var5 & "'"
SQL = SQL & " ,'" & Var6 & "'"
SQL = SQL & " ,'" & Var7 & "'"
SQL = SQL & " ,'" & Var8 & "'"
SQL = SQL & " ,'" & Var9 & "'"
SQL = SQL & " ,'" & Var10 & "'"
SQL = SQL & " ,'" & Var11 & "')"

' UtfÃ¶r sql statement
Set RS = Cn.Execute(SQL)

' StÃ¤ng anslutningen
'RS.Close
Cn.Close

' Meddelande till anvÃ¤ndaren
%>
<link href="default.css" rel="stylesheet" type="text/css">
<Body background="Img/main_bg.jpg">
<h1>Medlemskapet Ã¤r registrerat!</h1>

<center><img src="Img/NewLogo.gif" width="90" height="103"> <br>
<br>
  <% Response.Write Request.QueryString("Fornamn")%>
  är registrerad!<br>
<br>
  De uppgifter som registrerades var:<br>
</center>
<tr>
<tr>
  <table width="640" border="0" align="center" cellpadding="1" cellspacing="1">
    <tr>
      <td width="320"><div align="right">Medlems ID:</div></td>
      <td><div align="left">
          <% Response.Write Request.QueryString("BoxID")%>
        </div></td>
    </tr>
    <tr>
      <td><div align="right">Namn:</div></td>
      <td><div align="left">
          <% Response.Write Request.QueryString("Fornamn")%><% Response.Write " " %><% Response.Write Request.QueryString("Efternamn")%>
        </div></td>
    </tr>

    <tr>
      <td><div align="right">Epost:</div></td>
      <td><div align="left">
          <% Response.Write Request.QueryString("Email")%>
        </div></td>
    </tr>
    <tr>
      <td><div align="right">Adress:</div></td>
      <td><div align="left">
          <% Response.Write Request.QueryString("Adress")%>
        </div></td>
    </tr>
    <tr>
      <td><div align="right">Postadress:</div></td>
      <td><div align="left">
          <% Response.Write Request.QueryString("Pnr")%><% Response.Write " " %> <% Response.Write Request.QueryString("Postadress")%>
        </div></td>
    </tr>

    <tr>
      <td><div align="right">Password:</div></td>
      <td><div align="left"><% Response.Write Request.QueryString("Password")%></div></td>
    </tr>
    <tr>
      <td><div align="right">Telefon:</div></td>
      <td><div align="left"><% Response.Write Request.QueryString("Telefon")%></div></td>
    </tr>
  <tr>
      <td><div align="right">Personnr:</div></td>
      <td><div align="left"><% Response.Write Request.QueryString("Fnr")%></div></td>
    </tr>
  </table>
  <p align="center">Medlemsnumret fungerar som användarnamn!<br>
    <br>
    Sidan automatiskt skapad <%=Now%>
    </p>

<tr>
  <div align="center"><a href="#" onClick="window.print()" ><img src="Img/printpage.gif" alt="Skriv ut den här sidan" border="0" align="top" ></a>

  </div>
</body>
</html>

============
Microsoft OLE DB Provider for ODBC Drivers fel '80040e14'
/myweb/Provar.asp, row 55

Can someone see what is wrong right away? I suck at this so all help is welcome.

Rolleman · August 28th, 2003, 07:29 AM

Checked the database and the testapplication wasn't rightly setup with the variables... fixed that but I don't get the right returns when I submit the membernumbers... instead of 00012 I get 12.

Imar · August 28th, 2003, 07:32 AM

Hi there,

You may want to take a look at this faq as it deals with error number 80040e14.

One thing to check is the SQL statement. Make the following modification to your code:

Code:

' UtfÃ¶r sql statement
Response.Write(SQL)
Response.End()
'Set RS = Cn.Execute(SQL)

This allows you to see the SQL statement in the browser before you send it to the database. This may help in finding the problem.

I found the code a bit too much (including all those old and deprecated tags) to find the error for you, but by writing out the SQ statement you should be able to find the problem.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Imar · August 28th, 2003, 07:37 AM

If the ID field is a number in the database, the database will truncate the leading zeros again. converting 00012 to the number 12.

Either change the UserID to a string (which may or may not be applicable in your situation), or append the leading zeros again at all locations where you display them on the page (you could use a generic function for this)

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Rolleman · August 28th, 2003, 07:46 AM

This is what I get:

INSERT INTO Box_ID (BoxID, FirstName, LastName, Adress, PostNr, PostAdress, EmailAdress, PassWord, TelNr, Fnr, MFID)VALUES ('35' ,'Arvid' ,'Karlsson' ,'Hemmavid' ,'78548' ,'Hemskta' ,'[email protected]' ,'' ,'0224-84562' ,'890206-7777' ,'00025')

The red is what comes from the input and when I check the database 35 is all there is.

I change the code in the beginning:

<%
Dim MedlemsID
Dim FullMedlID
MedlemsID = Request.Form("BoxID")

FullMedlID = Right("00000" & MedlemsID, 5)

'FÃ¥ in "Form" variabler
Var1=request.querystring("BoxID")
Var2=request.querystring("Fornamn") a s o

Do you understand what I want?

Imar · August 28th, 2003, 08:05 AM

I don't completely understand, but what is the datatype of BoxID in the database? If it's a number, drop the ' around 35:

Code:

INSERT INTO Box_ID (BoxID, FirstName, LastName, Adress, PostNr, PostAdress, EmailAdress, PassWord, TelNr, Fnr, MFID)
VALUES (35 ,'Arvid' ,'Karlsson' ,'Hemmavid' ,'78548' ,'Hemskta' ,'[email protected]' ,'' ,'0224-84562' ,'890206-7777' ,'00025')

The same may apply for the MFID column; if it's a number, remove the ' surrounding 00025. You may end up with 25 in the database in that case.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

Rolleman · August 28th, 2003, 08:21 AM

The datatype for both 35 where I want it to be 00035 and for 00025 is set to text. I am a complete newbie to both database and asp so I am still learning all this the hard way.

BoxID is where MedlemsID is supposed to go.