Jump to content
Sign in to follow this  
prichardson

Error converting data type varchar to int. - MsSQL

Recommended Posts

Hi! I wrote a simple stored procedure to add a few fields into a MsSQL table:

INSERT INTO dbo.xyz769_Blog_01_Comments						(Blog_01ID, Blog_01_Title, Username, Comment_Content, Email)VALUES	  (@Blog_01ID, @Blog_01_Title, @Username, @Comment_Content, @Email)

But whenever i try to insert data thru an ASP page i get the following error:

Microsoft OLE DB Provider for SQL Server error '80040e07'Error converting data type varchar to int./anti_sql/pages/blog_01/blog_item.asp, line 73

Line 73 on blog_item.asp is just my execution statement which is the following:

rsEnterRecord_cmd.Execute

I tested to see which parameter was causing the problem, and after I went thru each one, I found out that when I enabled the @Email parameter it gives me this error.My Parameters are created on the ASP page through following statements:

rsEnterRecord_cmd.Parameters.Append rsEnterRecord_cmd.CreateParameter("@Blog_01ID", 3, 1)rsEnterRecord_cmd.Parameters.Append rsEnterRecord_cmd.CreateParameter("@Blog_01_Title", 200, 1, 200)rsEnterRecord_cmd.Parameters.Append rsEnterRecord_cmd.CreateParameter("@Username", 200, 1, 50)rsEnterRecord_cmd.Parameters.Append rsEnterRecord_cmd.CreateParameter("@Comment_Content", 200, 1, 255)rsEnterRecord_cmd.Parameters.Append rsEnterRecord_cmd.CreateParameter("@Email", 200, 1, 200)

In the database table:Blog_01ID is IntegerBlog_01_Title is nvarcharEmail is nvarcharComment_Content is nvarcharUsername is nvarcharI really don't see where the problem is, and I've run out of ideas, please help ....

Share this post


Link to post
Share on other sites

It looks fine to me. Are you sure you should be sending the ID as a parameter, is the ID column in the table an autonumber? That's the only one it would need to convert to an int.

Share this post


Link to post
Share on other sites
It looks fine to me. Are you sure you should be sending the ID as a parameter, is the ID column in the table an autonumber? That's the only one it would need to convert to an int.
CommentID is the primary key/autonumberHOWEVER Blog_01ID is NOT an autonumber, it is a foreign key from another table, and an IntegerI'm still getting an error, cudnt' fix,Do you think it has something to do with the '@' sign in the E-mail and the '@' in the parameter name clashing or something :S i'm so confused ....Trying to prevent my site from SQL Injection ... so re-writing all the sql code with stored procedures.

Share this post


Link to post
Share on other sites

I'm not sure if the @ sign is a problem or not, I don't work a lot with parameterized queries in ASP. You can always try removing it though. Also try removing the ID variable and see if you still get the error.

Share this post


Link to post
Share on other sites
I'm not sure if the @ sign is a problem or not, I don't work a lot with parameterized queries in ASP. You can always try removing it though. Also try removing the ID variable and see if you still get the error.
Thanks for your reply!I have tried removing the @sign with no luck.I tried removing all the parameters from the SP and passed just the @Blog_01ID which was successful. I tried it for all the parameters and finally only the E-mail function gives me this error. As I mentioned earlier, the @Blog_01ID is NOT an autonumber variable, the autonumber in the corresponding table i'm inserting is the Blog's COMMENTS table. The @Blog_01ID is just there to identify which blog the comment refers to.I will keep trying, thanks for your help, and if anyone else knows extensive info on this problem, or help me ReWrite this code in any other way, please help :) :) Kind regards,Baysaa.

Share this post


Link to post
Share on other sites
Thanks for your reply!I have tried removing the @sign with no luck.I tried removing all the parameters from the SP and passed just the @Blog_01ID which was successful. I tried it for all the parameters and finally only the @Email parameter gives me this error. As I mentioned earlier, the @Blog_01ID is NOT an autonumber variable, the autonumber in the corresponding table i'm inserting is the Blog's COMMENTS table. The @Blog_01ID is just there to identify which blog the comment refers to.I will keep trying, thanks for your help, and if anyone else knows extensive info on this problem, or help me ReWrite this code in any other way, please help :) :) Kind regards,Baysaa.

Share this post


Link to post
Share on other sites

It doesn't make sense to me that it would try to convert a string to an int to store it in a varchar column. The only thing that would make sense is if the email column were an int.

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
Sign in to follow this  

×
×
  • Create New...