error while inserting user to database

[uglyeyes]

Hi All, All my field values are stored in array and i am trying to insert a record in a database but i am receiving a error that says: ****** There was an error creating your profile. Please try again later.System.Data.SqlClient.SqlException: The identifier that starts with 'INSERT INTO profiles (CreationDate,ModificationDate,ImageName,ImageCaption,UserName,Password,Email,GenderID,Age,SexualOrientatio' is too long. Maximum length is 128. The name 'INSERT INTO profiles (CreationDate,ModificationDate,ImageName,ImageCaption,UserName,Password,Email,GenderID,Age,SexualOrientatio' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted. The identifier that starts with 'System.InvalidOperationException: The connection is already Open (state=Open). at System.Data.SqlClient.SqlConnection.Open()' is too long. Maximum length is 128. at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at CreateProfileCode.Page_Load() ******** below are my code to add new record ----------------------------------- 'Continue If the User Name has not been used and the e-mail address is neither used nor in the Abusers table. If (blnUserNameIsUnique AND blnUserIsNotAbuser AND blnEmailNotUsed) Then blnPrintForm = false Dim strColumns As New StringBuilder() Dim strValues As New StringBuilder() Dim intField As Integer 'Array of all the columns in the Profiles table to be recorded. Dim ProfileFields(16,1) As String ProfileFields(0,0) = "UserName" ProfileFields(0,1) = "text" ProfileFields(1,0) = "Password" ProfileFields(1,1) = "password" ProfileFields(2,0) = "Email" ProfileFields(2,1) = "text" ProfileFields(3,0) = "GenderID" ProfileFields(3,1) = "number" ProfileFields(4,0) = "Age" ProfileFields(4,1) = "number" ProfileFields(5,0) = "SexualOrientationID" ProfileFields(5,1) = "number" ProfileFields(6,0) = "EthnicID" ProfileFields(6,1) = "number" ProfileFields(7,0) = "Height" ProfileFields(7,1) = "number" ProfileFields(8,0) = "BodyTypeID" ProfileFields(8,1) = "number" ProfileFields(9,0) = "NeighborhoodID" ProfileFields(9,1) = "number" ProfileFields(10,0) = "AboutMe" ProfileFields(10,1) = "textarea" ProfileFields(11,0) = "WantActivityPartner" ProfileFields(11,1) = "checkbox" ProfileFields(12,0) = "WantFriend" ProfileFields(12,1) = "checkbox" ProfileFields(13,0) = "WantDate"

[Steve Pullan]

You've got embedded double quotes somewhere in your command text which is causing SQL Server to consider it as an identifier rather than a command. Run it in debug and check the entire contents of strInsert just before the ExecuteNonQuery(). ...Steve 1. quod erat demonstrandum 2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once :-) (Translation: I'll show you the way, but not write the code for you.)

[uglyeyes]

after careful reading of code i came up with those issues, i think the problem likes sum where in here while converting a text area field ------ Case "textarea" strFormValue = Server.HtmlEncode(strFormValue) strFormValue = strFormValue.Replace("""","&" & "quot;") strValues.Append(",""" & strFormValue & """") --------- which produces this insert query INSERT INTO profiles (CreationDate,ModificationDate,ImageName,ImageCaption, UserName,Password,Email,GenderID,Age,SexualOrientationID,EthnicID, Height,BodyTypeID,NeighborhoodID,AboutMe,WantActivityPartner,WantFriend, WantDate,WantLTR,ForwardMessages,SignID,ActivationCode) VALUES (DateTime.Now()),DateTime.Now(),NULL,NULL, 'hello',MD5('cocacola'),'bikash557@hotmail.com',2,23,1,2,49,2,2,"i not not me",1,1,1,1,1,1,"tq45z3xlg5554f34") both "i am not me" and the activation id should've been inside single comma and i dont know if MD5 works in sql server 2000. any idea any one please help me. thanks i am still trying to know how to catch fish.

[Steve Pullan]

foremorecoast1 wrote:

i think the problem likes sum where in here while converting a text area field ------ Case "textarea" strFormValue = Server.HtmlEncode(strFormValue) strFormValue = strFormValue.Replace("""","&" & "quot;") strValues.Append(",""" & strFormValue & """") ---------

Yep - you're on the right track. Good luck. ...Steve 1. quod erat demonstrandum 2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once :-) (Translation: I'll show you the way, but not write the code for you.)

[uglyeyes]

i tried hard spend almost few hours i realy dont know whats wrong with that code and MD5 doesnt work too. i am really at lost.

[kayos592]

Try MD5_STRING() instead of MD5()... Also, replace the double quotes in your statement with single quotes.... Let me know if that does it... -Kay

[uglyeyes]

i tried the above code with ---------- Select Case strFieldType Case "text" strValues.Append(",'" & strFormValue & "'") Case "password" 'Use 128-bit encryption. strValues.Append(",('" & strFormValue & "')") Case "textarea" strFormValue = Server.HtmlEncode(strFormValue) strFormValue = strFormValue.Replace("''","&" & "quot;") strValues.Append(",''" & strFormValue & "''") Case "number" strValues.Append("," & strFormValue) Case "checkbox" If strFormValue = "on" Then strValues.Append(",1") End If End Select ----- but i still get this error. and md5_string doesnt work so i wont bother encrypting the password please help . many thanks in forwad.

[uglyeyes]

finally its working but my MD5 is not working. i tried md5_string but no help. is it okay if i leave the password unencrypted whats wrong with that?

[kayos592]

So how did you fix it? For everyone's knowledge?(besides the encryption). -Kay

[Steve Pullan]

It's really only a problem if someone gains access to your database. Then they could gain access to any user account since the user name and password are in the table in plaintext. Encrypting the password at least makes it much harder for any hacker to get access to anyone else's account. MD5() and MD5_STRING() arn't working because they are not TSQL functions. You can't use it in the manner in which you've coded it. ...Steve 1. quod erat demonstrandum 2. "Give a man a fish and you've fed him for a day. Teach him how to fish and you've fed him for life." I read that somewhere once :-) (Translation: I'll show you the way, but not write the code for you.)