I am trying to write a simple ASP page that will submit user information to a MSSQL database, however I keep getting an error message when I hit the register button "Error Type: ADODB.Recordset (0x800A0BB9) Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another. /mine/testsignup.asp, line 54" Below is the code: <head> <title>Sign-Up</title> </head> <body bgcolor="white"> <table width=575 align=center><tr><td> <!-- #include file="../mine/includes/connectstring.asp" --> <!-- #include file="../adovbs.inc" --> <% If IsEmpty(Request.Form("submit")) Then %> <form name="signup" action="testsignup.asp" method="post"> <table width="50%" align="center" cellspacing=0 cellpadding=0 border=0> <tr> <td colspan=2 align="center"><h2><div class="color">Sign-Up</div></h2></td> </tr> <tr> <td><div class="color">First Name:</div></td> <td><input type="text" name="fname" size=15></td> </tr> <tr> <td><div class="color">Last Name:</div></td> <td><input type="text" name="lname" size=15></td> </tr> <tr> <td><div class="color">Email:</div></td> <td><input type="text" name="email" size=15"></td> </tr> <tr> <td colspan=2> </td> </tr> <tr> <td colspan=2 align="center"> <input type="submit" name="submit" value="register"> <input type="reset" name="reset" value="clear"> </td> </tr> </table> </form> <% Else FName = Request.Form("fname") LName = Request.Form("lname") Email = Request.Form("email") Set rs = server.createobject("adodb.recordset") rs.open "user",cn rs.addnew rs("email") = Email rs("fname") = FName rs("lname") = LName rs.update rs.close Set rs = nothing Response.Write "<font color='white'>Thank You, You have been added to our database!</font>" End If conn.Close Set conn = Nothing %> </td></tr></table> </body> </html> line 54 is rs.open "user",cn Any help/suggestions would be greatly appreciated
The error messages in ASP refer to the action line where there is an issue. Since line 54 is the controlling line, it will be the error line even if the error is later in the insert. What is the table structure? What are the field names and types? What is the field length? Is there a key field? Do you have it set to autogenerate? When you try to submit this form, are you filling it out completely or are you just hitting submit? You may need to do some data manipulation before you try to enter it in the database.
druidelder-thanks for taking the time to respond. The table has three columns Fname, LName, Email. They are all text fields that are 16 characters in length.There is no key field. It is a MSSQL database.
If they are text fields, they are well over 16 chars in length (unless you mean you set a 16 character limit on the html textbox). The text datatype is for fairly large text and can exceed the row size limitation. MSSQL shows this strangely. When looking at ints or chars the size is in bytes. This is not true for the text datatype. Since you are using MSSQL, I would suggest using varchar(50). Varchars are great in that they only allot the memory needed for what is actually in them. A char(50) always allots memory for 50 characters regardless of what is in there. Change the datatype and see if that helps. Some languages don't handle the text datatype well.
Thanks for the suggestion. I changed the datatype in the table from text to varchar, but I am still getting the same error message. Thanks again for taking the time to try to help me troubleshoot the problem.
Change the code so that instead of inserting, you write out the variables. response.write(email) response.write(fname) response.write(lname) Then let me know what you input and what was written out on the resulting page. That will help us determine if it is a data issue.
druidelder, I wrote them out and ended up with the information submitted written back to me on the page correctly.
But what was the input/output? Without knowing what was entered, it is impossible to tell if it is a data issue.
the data input was my first name, last name and email and the data output was my first name, last name and email.
That doesn't really help. To see if it could be a data issue, I would need to see the actual data, not what it represents. Try inputting just a single letter in each a,b,c. Are there any other fields in the table that are not being used here? When you changed the datatype, what length value did you give? Are you sure that your connection strings are pointing in the right place?
Are you supplying a lock type in your connection string? If you are, make sure it is not a readOnly locktype. If you do not specify one, the default is readOnly and could throw that error. adLockOptimistic
druidelder, I input single letters and in the Response.Write code and the output was single letters. there are only three fields in the table: FName LName Email the varchar length is 50 and I have put an adovbs.inc file in my include folder so locktypes shouldn't be a problem. I have been working on this for 3 days and I am stumped. Thanks for you help!
The adovbs.inc only defines the lockTypes. You still have to select one for the connection or it goes to its default value which is a readOnly type. Try: rs.open "user",cn, 1, 2
I added ,1,2 to the code, but still got the same error. I am pretty sure that the problem is in the recordset code, but I am struggling to figure out what it might be. It's pointing to "user" which is the name of the table, has the correctly named fields, but ultimately refuses to add the new record.
I'm pretty sure the recordset code is right. If it were somehow pointing to the wrong table or database it would have an error stating the db object could not be found. Have you checked the table permissions to make sure you can write to it? That should give a permission error if that were the issue, but you never know. Have you thought about using <%Option Explicit%> and properly declaring your variables? I know ASP allows you not to, but it will cause you headaches in the future. What about switching to using stored procedures (or does your host not give you that access?
When you did this, did you put letters in the response.write code or request.form("email"), etc.... email = request.form("email") response.write(email) not response.write("a") When you did this, did you also try to do the addnew code with just single letters being input?
druidelder, I do have access to stored procedures within MSSQL, but have limited knowledge of writing them. I added <%Option Explicit%> to declare my variable, but till got the same error. I also double checked the permissions on the table and they were checked for write.
okay, I think the code is failing before it gets to the table. I changed the name of the table to "users" and got the same exact error message.
Can you show me what your connection stuff looks like (mine/includes/connectstring.asp)? Change anything in there with specific data to your site (password, ip, etc...) to x's so that nobody can use it to get to your site.
here ya go... <% dim conn, SQLStringQWERTY Set conn = Server.CreateObject("ADODB.Connection") conn.Open("Provider=SQLOLEDB; Data Source=xxxxxx; Initial Catalog=testdb; " & "User Id=xxxx; Password=xxxxxx;") %>