Ok, Can someone please tell me where to correct this code and please show 
me??? 

I am trying to edit and insert a record into my database.  The problem I 
am having is that the field I am trying to insert into is a number 
datatype. 

How I have code that has worked on several tables, but all those tables 
did not have number datatype fields.  So I know it is the number datatype 
that is the problem. 

How with the code I am putting at the bottom of this note, could someone 
please show me where to correct the code.  I understand the logistics of 
what I'm supposed to do, but i don't know where to correct the code. 

This is what I am using to input records into text datatype fields: 

FUNCTION CheckString (s, endchar) 
    pos = InStr(s, "'") 
    While pos > 0 
        s = Mid(s, 1, pos) & "'" & Mid(s, pos + 1) 
        pos = InStr(pos + 2, s, "'") 
    Wend 
   CheckString="'" & s & "'" & endchar 
END FUNCTION 

Now first of all, do I need a function for numbers?? 

Next these are the lines that I'm using for the SQL(Phone is a number 
field, so I need the code to be fixed): 
SQL = "SELECT * FROM Businesslistings WHERE ID =" & txtID & "" 
txtPhone = RS("Phone") 
                 
If txtPhone = "" then 
txtPhone = "-" 
End If 


SQL = "UPDATE Businesslistings " 
SQL = SQL & "Phone = " & CheckString(txtPhone,",") 

Now i know this code is wrong and if someone could "fill in the blanks" 
for me, I would appreciate it. 

If I need an IF..Then clause somewhere, again if someone could help, I 
would appreciate it. 

Thanks, 
Gary 

Hi Gary,

When including numbers in a SQL string, you should not quote them.  Your
CheckString function is surrounding the value with single quotes.  It's good
if the field is a text field, but not good if it's a number.

Also, this is wrong:

SQL = "SELECT * FROM Businesslistings WHERE ID =" & txtID & ""

If txtID is a numeric field (stored in the database as a number or auto
number), then this should be:

SQL = "SELECT * FROM Businesslistings WHERE ID = " & txtID

If this is a text or memo field, then this should be:

SQL = "SELECT * FROM Businesslistings WHERE ID = '" & txtID & "'"

Or you could use your CheckString function to surround txtID with single
quotes.  Note however that your CheckString function is somewhat
inefficient.  Try this instead:

FUNCTION CheckString (s) 
   s = Replace(s, "'", "''")
   CheckString = "'" & s & "'"
END FUNCTION 

I usually call this function "sql_quote", since it's essentially prepping a
string to be quoted within a SQL statement, but the naming convention is up
to you. :)

Now, the big mistake that you're making is that you're trying to store a
phone number in a numeric field.  This is a no-no.  Phone numbers contain
numeric digits, but you don't plan to do any calculations with them, do you?
:)  They can also contain non-numeric digits, such as () and - and spaces.
For example, 
(xxx) xxx-xxxx
^   ^^   ^

See the non-numeric character data?  This can't be put into a numeric field.
And if the phone number is omitted, you can't store "-" in the numeric
field.  Your solution here would be to store it in the database as character
data.

Hope this helps.
Regards,
Peter



-----Original Message-----
From: Gary Cappelletti [mailto:gacapp@a...]
Sent: Saturday, October 19, 2002 3:50 PM
To: ASP Databases
Subject: [asp_databases] need help with number datatype


Ok, Can someone please tell me where to correct this code and 
please show 
me??? 

I am trying to edit and insert a record into my database.  The 
problem I 
am having is that the field I am trying to insert into is a number 
datatype. 

How I have code that has worked on several tables, but all those tables 
did not have number datatype fields.  So I know it is the 
number datatype 
that is the problem. 

How with the code I am putting at the bottom of this note, 
could someone 
please show me where to correct the code.  I understand the 
logistics of 
what I'm supposed to do, but i don't know where to correct the code. 

This is what I am using to input records into text datatype fields: 

FUNCTION CheckString (s, endchar) 
    pos = InStr(s, "'") 
    While pos > 0 
        s = Mid(s, 1, pos) & "'" & Mid(s, pos + 1) 
        pos = InStr(pos + 2, s, "'") 
    Wend 
   CheckString="'" & s & "'" & endchar 
END FUNCTION 

Now first of all, do I need a function for numbers?? 

Next these are the lines that I'm using for the SQL(Phone is a number 
field, so I need the code to be fixed): 
SQL = "SELECT * FROM Businesslistings WHERE ID =" & txtID & "" 
txtPhone = RS("Phone") 
                 
If txtPhone = "" then 
txtPhone = "-" 
End If 


SQL = "UPDATE Businesslistings " 
SQL = SQL & "Phone = " & CheckString(txtPhone,",") 

Now i know this code is wrong and if someone could "fill in the blanks" 
for me, I would appreciate it. 

If I need an IF..Then clause somewhere, again if someone could help, I 
would appreciate it. 

Thanks, 
Gary 

Great feedback Peter...only one last comment I would add is to stay away
from using the SELECT *.  Better to specify the columns you want to
better the performance of the query.

-----Original Message-----
From: Peter Foti (PeterF) [mailto:PeterF@S...]
Sent: Monday, October 21, 2002 9:38 AM
To: ASP Databases
Subject: [asp_databases] RE: need help with number datatype


Hi Gary,

When including numbers in a SQL string, you should not quote them.  Your
CheckString function is surrounding the value with single quotes.  It's
good if the field is a text field, but not good if it's a number.

Also, this is wrong:

SQL =3D "SELECT * FROM Businesslistings WHERE ID =3D" & txtID & ""

If txtID is a numeric field (stored in the database as a number or auto
number), then this should be:

SQL =3D "SELECT * FROM Businesslistings WHERE ID =3D " & txtID

If this is a text or memo field, then this should be:

SQL =3D "SELECT * FROM Businesslistings WHERE ID =3D '" & txtID & "'"

Or you could use your CheckString function to surround txtID with single
quotes.  Note however that your CheckString function is somewhat
inefficient.  Try this instead:

FUNCTION CheckString (s)
   s =3D Replace(s, "'", "''")
   CheckString =3D "'" & s & "'"
END FUNCTION

I usually call this function "sql_quote", since it's essentially
prepping a string to be quoted within a SQL statement, but the naming
convention is up to you. :)

Now, the big mistake that you're making is that you're trying to store a
phone number in a numeric field.  This is a no-no.  Phone numbers
contain numeric digits, but you don't plan to do any calculations with
them, do you?
:)  They can also contain non-numeric digits, such as () and - and
spaces. For example,
(xxx) xxx-xxxx
^   ^^   ^

See the non-numeric character data?  This can't be put into a numeric
field. And if the phone number is omitted, you can't store "-" in the
numeric field.  Your solution here would be to store it in the database
as character data.

Hope this helps.
Regards,
Peter



-----Original Message-----
From: Gary Cappelletti [mailto:gacapp@a...]
Sent: Saturday, October 19, 2002 3:50 PM
To: ASP Databases
Subject: [asp_databases] need help with number datatype


Ok, Can someone please tell me where to correct this code and
please show
me???

I am trying to edit and insert a record into my database.  The
problem I
am having is that the field I am trying to insert into is a number
datatype.

How I have code that has worked on several tables, but all those tables
did not have number datatype fields.  So I know it is the
number datatype
that is the problem.

How with the code I am putting at the bottom of this note,
could someone
please show me where to correct the code.  I understand the
logistics of
what I'm supposed to do, but i don't know where to correct the code.

This is what I am using to input records into text datatype fields:

FUNCTION CheckString (s, endchar)
    pos =3D InStr(s, "'")
    While pos > 0
        s =3D Mid(s, 1, pos) & "'" & Mid(s, pos + 1)
        pos =3D InStr(pos + 2, s, "'")
    Wend
   CheckString=3D"'" & s & "'" & endchar
END FUNCTION

Now first of all, do I need a function for numbers??

Next these are the lines that I'm using for the SQL(Phone is a number
field, so I need the code to be fixed):
SQL =3D "SELECT * FROM Businesslistings WHERE ID =3D" & txtID & ""
txtPhone =3D RS("Phone")
                
If txtPhone =3D "" then
txtPhone =3D "-"
End If


SQL =3D "UPDATE Businesslistings "
SQL =3D SQL & "Phone =3D " & CheckString(txtPhone,",")

Now i know this code is wrong and if someone could "fill in the blanks"
for me, I would appreciate it.

If I need an IF..Then clause somewhere, again if someone could help, I
would appreciate it.

Thanks,
Gary