syntax problem with isNumeric

[gcapp]

I'm having a problem getting a record to insert into my database table. The field is a number field and I'm trying to use ISNumeric, but I don't know if I have it right.

This is what I have:

For the Function:
<script LANGUAGE="javascript">
function isNumeric(str)
{
for (var i=0; i < str.length; i++)
{
var ch = str.substring(i, i+1)
if( ch < "0" || ch>"9" || str.length == null)
{
return false
}
}
return true
}
</script>

and for my sql:

SQL = SQL & "Zip = " & IsNumeric(txtZip,",")


This is not correct, but I don't know where. I get invalid charcter errors in the function and I don't think the sql is right either.

Any help?

[whammy]

First of all, your javascript is all wrong, but I'm not going to get into that just yet...

IsNumeric() is a built-in VBScript function for validating that the variable you are using is numeric, not for inserting a numeric value into a database... what you're doing should be more like:


txtZip= Request.Form("txtZip")

to check if it's numeric:

If Len(txtZip) > 0 Then
If Country = USA AND IsNumeric(txtZip) AND _
(Len(txtZip) = 5 OR Len(txtZip) = 9) Then
Response.Write("YAY!")
'Or put your insert stuff here...
Else
Response.Write("You can't put alpha characters into the Zip field...")
End If
Else
Response.Write("You need to enter a Zip.")
End If

And then (if your database field type is an integer, etc.):

SQL = SQL & "Zip = " & txtZip & ","

Which also assumes you are using a numeric field in your database to store the Zip in... I would probably use a text or varchar field instead (what about Canadians, etc. eh?) and do it like:

SQL = SQL & "Zip = '" & txtZip & "',"

You don't have to convert the string into an integer in ASP in order to insert an integer into a database, you only need to do that if you're going to perform a mathematical operation on it in ASP, in which case you'd use CInt() or a similar conversion function (as opposed to a validation function like IsNumeric()).

SQL will try to treat whatever you're inserting as the datatype you've specified for the field, regardless of what it is in ASP!

But you do want to use IsNumeric() to validate it's a number before storing it in an integer field, or of course SQL will throw an error.

Hope that makes sense! The bolded parts above are just to illustrate the important concepts I think you're missing... basically you can insert a string into an integer field in SQL (as long as it's comprised of numbers), NO problem. You just have to make sure the SQL statement TREATS it as an integer (i.e. no single quotes around the variable), and that you validate it as an integer BEFORE you insert it (otherwise, SQL will definitely throw an error!).

:)

[gcapp]

Whammy,
I understand what you are saying. I understand the logisitics of what you're saying. My problem is in the context of my page, I'm not sure where to correct the code.

The code for this page works on tables that have text datatypes. So I am assuming there are just a few changes, but they don't work.

For instance, I have this code on my page for text inserting:
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

and for the sql:

SQL = SQL & "Name = " & CheckString(txtName,",")

Now when I had a field that was a Date/Time field, all I did was add another function like so:

FUNCTION CheckDate(d, endchar)
if not isDate(d) then
CheckDate = "null" & endchar
else
CheckDate="#" & DateValue(d) & "#" & endchar
end If
END FUNCTION

and change the SQL from CheckString to CheckDate like so:
SQL = SQL & "Start_Date = " & CheckDate(txtStart_Date,",")
and it works.

So for a number field, I would assume that I would add a function for numbers and then change the CheckString to CheckNumber.

So that doesn't work. So with the IF..Then clause you gave me, I'm not sure where it would go in the context of my page.

I don't believe it would go in the section where it has
If txtZip_Code = "" then
txtZip_Code = "-"
End If
because that section is if nothing is entered into the form when the record is updated or added.

I believe the SQL portion of my UPDATE and iNSERT INTO sections are correct.

So this is where I get confused. I don't want to re-write my page because I know it works with my other tables, so it must be just some minor changes.

I don't know if you can give me further assistance. I've been working on this page for two days and I keep getting the error of type mismatch.

I've done some research for CheckNumber and I see code thtais like this:
function CheckNumber()
{
if (isNumeric(document.businessform.Category.value))
return true
else
{
alert("Please enter a valid category number.")
return false
}
}

However, this is for one field. I have three fiedls that are numbers, so if this code is correct, would I use three of the IF statements??

This form is for my purposes only. I know what to insert into each field, so I'm not worried about others entering wrong information.

If you can provide any other help, I'd appreciate it.

[whammy]

Can you send me everything you're using? If you don't feel like posting it here email me at robkdavis@hotmail.com and I will see what I can do.

[whammy]

Change line 372 on businesses2.asp (which is a blank line right now) to this:


Response.Write(SQL) :Response.End


And run it and post the string... without the database, I can't test it myself (unless you give me the field datatypes and layout).

[gcapp]

Whammy,
Here is the string:

INSERT INTO Businesslistings (Image_ID, Category, Business, Address, City, State, Zip, Phone, Phone2, Fax, Website, Email, Information) VALUES ( '-','1400','test',', ','-','-','-','-','-','-','-','-','-')

My database layout is like this:

Database name: Businesses

Table name: Businesslistings

Table fields and type:

ID - autonumber
Image_ID - text
Category - number
Business - text
Address - text
City - number
State - text
Zip_Code - number
Phone - text
Phone2 - text
Fax - text
Website - text
Email - text
Information - text

Hope this helps.

If not I can send you on your personal email, the link to go and login and test it yourself.

Thanks,
Gary

[whammy]

Well, right away, you have Category as a number (integer ?)field... but if you look at the string, you have single quotes around it.

someinteger = 0, somestring = 'somestring'

Is how that needs to look.


That's the first error.

Also, I was creating a database to test it and noticed this:

City - number

:confused:

That isn't right is it?

[gcapp]

Whammy,
I should have mentioned because I knew you would ask. Yes, Category, City and Zip are number because they are inner-joined on other tables that I use on different pages. So, like i said this page does work.


I'm tryign to get ideas fromother people as well, so I'm not just waiting to se eif oyu can help, but i hope you can.

Gary

[whammy]

So did you try correcting the SQL statement as mentioned above? It's not an "idea"... I do this for a living, and I KNOW that you're trying to insert a string into an integer field!

(i.e. taking out the single quotes around the integer you're inserting? - that tells SQL that it's a string - not an integer.)

Also, you'll probably have problems with City, because you're inserting a string too... and even if you take out the single quotes, then you'll be inserting a "minus" sign.. I don't know offhand how the database will react to that.

Try correcting the things above, and running it again (just comment out the Response.Write(SQL) : Response.End statement), and see if you still get an error (every developer I know debugs like this, in conjunction with SQL Query Analyzer if you have it).

If you still get an error, uncomment the Response.Write and see what else needs fixing.

Alternatively, you could even split each variable in the SQL statement into an array and write each one to the page, which might even be better!:

<%
SQL = "INSERT INTO tablename (blah,blah1,blah2,) VALUES ('blah',blah1,'blah2')"
SQLdebug = Split(SQL,",")
For i = 0 to UBound(SQLDebug)
Response.Write(i+1 & ": " & SQLDebug(i) & "<br />" & vbLf)
Next
Response.End
%>

:)

[gcapp]

Whammy,
Well I don't have any single quotes around the Category, City or Zip statements.
This is what I don't get. I have my original page that will work editing and adding records to a table that has text datatype fields. Now, I have another table that has a Date/Time datatype. So after figuring out how to do that I added a function and changed CheckString to CheckDate and it works.
Now am I wrong in thinking that I can add a function called CheckNumber and change the CheckString to CheckNumber like i did with the Date/Time field??


I know oyu do this for aliving, so I'm not taking your ideas as "off the cuff".

I'll do this, so maybe you can see first hand what is going on. On your personal email, I'll send you the links to my pages with the login and then let you see how the page is working.

Because I am new, maybe I can't explain things like I should and if you see it you can tell what is up.

I'd like to keep the page working like it is, so to speak.

I don't know if this will help, but i thought maybe if you saw how my page looks, it would help - maybe not.

[whammy]

The reason I say you're putting single quotes around an integer is because you posted this:

INSERT INTO Businesslistings (Image_ID, Category, Business, Address, City, State, Zip, Phone, Phone2, Fax, Website, Email, Information) VALUES ( '-','1400','test',', ','-','-','-','-','-','-','-','-','-')

Now, you said "Category" is an integer field... in which case, you cannot put '1400' into it, it has to be 1400 - and the same goes for any other integer field.

P.S. You might want to check out a couple of links that John Krutsch posted in the MySQL forum:

http://www.sqlcourse.com
http://www.sqlcourse2.com