an ASP question

[piertiong]

may i know if this is correct?

if rs.EOF then
Response.Write "There are no categories"
else
while not rs.EOF
%>
<a href=<%= rs("category") %>.asp"><%= rs("category") %></a>
<%
wend

[rdoekes]

you are almost there:
- double quote in the anchor tag href.
- explicitly tell the recordset to move to the next record
-end if

if rs.EOF then
Response.Write "There are no categories"
else
while not rs.EOF
%>
<a href="<%= rs("category") %>.asp"><%= rs("category") %></a>
<%
rs.MoveNext
wend
end if
%>


-Rogier Doekes

[piertiong]

hey thanks man.:)

[piertiong]

encountered another problem :(

Line 1: Incorrect syntax near ','.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','.

Source Error:


Line 92: sqlCmd1.CommandText = "INSERT INTO Customer(nameCust, address, gender, passwordCust, occupation, income, m_status, email, hPhone, oPhone, dob, tPhone, userId) VALUES('" + nameCust.Text + "', '" + address.Text + "', '" + gender.SelectedValue + "', '" + password.Text + "', '" + occupation.Text + "', '" + income.SelectedValue + "', '" + m_status.SelectedValue + "', '" + email.Text + "', " + hPhone.Text + ", " + oPhone.Text + ", '" + dob.Text + "', " + tPhone.Text + ", '" + userId.Text + "')"
Line 93: 'insertCmd.Connection = sqlConn
Line 94: sqlCmd1.ExecuteNonQuery()
Line 95: 'Dim name As String = sdrData("name")
Line 96: 'Session("userid") = name


Source File: d:\inetpub\wwwroot\WebApplication2\registration.aspx.vb Line: 94

-----------------------------------------------

nameCust - type nvarchar
address - type nvarchar
gender - type char
passwordCust - type nvarchar
occupation - type char
income - type money
m_status - type char
email - type nvarchar
hPhone - type numeric
oPhone - type numeric
dob - type datetime
tPhone - type numeric
userId - type nvarchar

[rdoekes]

income is of type money, which is numeric. You have the money value quoted which should not be the case.

[piertiong]

i really do not have any idea what's wrong now


Line 1: Incorrect syntax near ','.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near ','.

Source Error:


Line 92: sqlCmd1.CommandText = "INSERT INTO Customer(nameCust, address, gender, passwordCust, occupation, income, m_status, email, hPhone, oPhone, dob, tPhone, userId) VALUES('" + nameCust.Text + "', '" + address.Text + "', '" + gender.SelectedValue + "', '" + password.Text + "', '" + occupation.Text + "', " + income.SelectedValue + ", '" + m_status.SelectedValue + "', '" + email.Text + "', " + hPhone.Text + ", " + oPhone.Text + ", '" + dob.Text + "', " + tPhone.Text + ", '" + userId.Text + "')"
Line 93: 'insertCmd.Connection = sqlConn
Line 94: sqlCmd1.ExecuteNonQuery()
Line 95: 'Dim name As String = sdrData("name")
Line 96: 'Session("userid") = name


Source File: d:\inetpub\wwwroot\WebApplication2\registration.aspx.vb Line: 94

-----------------------------------------------

nameCust - type nvarchar
address - type nvarchar
gender - type char
passwordCust - type nvarchar
occupation - type char
income - type int
m_status - type nvarchar
email - type nvarchar
hPhone - type numeric
oPhone - type numeric
dob - type datetime
tPhone - type numeric
userId - type nvarchar

[rdoekes]

debug the page
put a breakpoint on line 94
print the sqlcommand
copy the command to the query analyzer of SQL server
run the code.

This will probably give you better inside where the error exactly is

btw: any quotes (') in your string variables? if so, you need to escape them. Also, any blank entries? will not work either.

-Rogier Doekes

[piertiong]

yes there are blanks because i allow nulls in the sql server:eek:

[rdoekes]

SQL server does not accept '' as an entry. That's where the error comes from.

If you have a blank value (test for String.Empty), explicitly set the value to null. This can be best achieved with a function

Function ConvertEmptyToNull(byval strDbFieldValue) as string
If strDBFieldValue = String.Empty Then
Return "Null"
Else
Return strDBFieldValue
End If
End Function
Now you can build the commandsting

.....+ ConvertEmptyToNull(value) + etc.

[rdoekes]

To make it all complete here a function and an enum you can use to ensure the right escaping occurs:

Public Enum DBValueEscape
hash
quote
none
End Enum

Public Function EscapeCharacters(ByVal DBFieldValue As String, _
ByVal Escape As DBValueEscape) As String

Return IIf(DBFieldValue = String.Empty, "Null", _
IIf(Escape = DBValueEscape.hash, _
"#" & DBFieldValue & "#", _
IIf(Escape = DBValueEscape.quote, _
"'" & Replace(DBFieldValue, "'", "''") & "'", _
DBFieldValue)))

End Function
You call this function
in case of a (var)char value
EscapeCharacters(Addres.Text, DBValueEscape.quote)
in case of a numeric value
EscapeCharacters(tPhone.Text, DBValueEscape.none)

the hash is if you use access, since the date/time field in access needs hashes (e.g. #10/11/2003#)

the replace function for the DBValueEscape.quote is escape quotes (also primary defence against SQL Insertion)


Hope this helps,
-Rogier Doekes

[piertiong]

Originally posted by rdoekes
SQL server does not accept '' as an entry. That's where the error comes from.

If you have a blank value (test for String.Empty), explicitly set the value to null. This can be best achieved with a function

Function ConvertEmptyToNull(byval strDbFieldValue) as string
If strDBFieldValue = String.Empty Then
Return "Null"
Else
Return strDBFieldValue
End If
End Function
Now you can build the commandsting

.....+ ConvertEmptyToNull(value) + etc.

thanks man you have really helped me alot. this function works perfectly.

i have created a variable of type date:
Dim bday As New Date(year.Text, month.Text, day.Text)

and i use this: " + bday + " to inside into the database, but in the database, it shows the date as 1/1/1900 for all the records that i have inserted, do you know why?:)