www.webdeveloper.com
Results 1 to 9 of 9

Thread: ASP & JavaScript Insert Problem...

  1. #1
    Join Date
    May 2003
    Posts
    599

    ASP & JavaScript Insert Problem...

    Hello,

    I have a webpage (see full code below) that attempts to pull variables from a form on the previous page, and Insert those variables into a DB table in SQL Server 2k. But I'm having 2 problems:

    1) The "Member_ID" variable is getting pulled into the page properly, but won't work when added to the "Values" section of the Insert query. I receive this error message:
    Object doesn't support this property or method
    2) The "DateTime" variable is from the previous form page that assigned the current date & time to a hidden field. But I'm getting this error message:
    Syntax error converting datetime from character string.

    Does anyone see something that I'm obviously doing wrong with this page? After a few hours of testing, I'm lost as to what the problem is. Any & all help is appreciated. Thanks.

    CODE FOR PAGE:
    Code:
    <%@LANGUAGE="JAVASCRIPT"%>
    <!--#include file="Connections/strConn_Bulletin.asp" -->
    <script runat=server language="vbscript" src="/ScriptLibrary/dotrim.vbs"></script>
    <%
    Response.Buffer = "True"
    
    //Pull in variables
    var Username = Session("Username");
    var Topic = DoTrim(Request.Form("txtTopic"), 50);
    var Post = DoTrim(Request.Form("txtPost"), 200);
    var URL = DoTrim(Request.Form("txtURL"), 50);
    var Subscription = DoTrim(Request.Form("rbSubscription"), 1);
    var DateTime = DoTrim(Request.Form("hfDateTime"), 22);
    var Insert = DoTrim(Request.Form("hfInsert"), 5);
    
    //Type_ID assignment
    var Type_ID = Session("Type_ID");
    if (Type_ID == "" || Type_ID == "undefined") {
    Type_ID = "New";}
    
    //Post_ID assignment
    var Post_ID = Session("Post_ID");
    if (Post_ID == "" || Post_ID == "undefined") {
    Post_ID = null;}
    
    //Cat_ID assignment
    if (Type_ID == "New" || Type_ID == "Reply") {
    var Cat_ID = DoTrim(Session("Cat_ID"), 20);
    }
    else if (Type_ID == "" || Type_ID == "undefined") {
    var Cat_ID = DoTrim(Request.Form("selectCat_ID"), 20);
    }
    
    //Member_ID assignment
    var Member_ID = Session("Member_ID");
    if (Member_ID == "" || Member_ID == "undefined") {
    Response.Redirect("http://SERVERNAME/BulletinBoard/register.asp?NotReg=true");
    }
    %>
    <%
    //Create session variables
    if (Topic != "") {
    Session("Topic");
    }
    else if (Topic == "") {
    Session("BadTopic") = "true";
    Session("Insert") = "false";
    }
    
    if (Post != "") {
    Session("Post");
    }
    else if (Post == "") {
    Session("BadPost") = "true";
    Session("Insert") = "false";
    }
    %>
    <%
    //Test variables
    Response.Write("<strong>Username: </strong>" + Username + "<br>");
    Response.Write("<strong>Member_ID: </strong>" + Member_ID + "<br>");
    Response.Write("<strong>Cat_ID: </strong>" + Cat_ID + "<br>");
    Response.Write("<strong>Post_ID: </strong>" + Post_ID + "<br>");
    Response.Write("<strong>Type_ID: </strong>" + Type_ID + "<br>");
    Response.Write("<strong>Topic: </strong>" + Topic + "<br>");
    Response.Write("<strong>Post: </strong>" + Post + "<br>");
    Response.Write("<strong>URL: </strong>" + URL + "<br>");
    Response.Write("<strong>Subscription: </strong>" + Subscription + "<br>");
    Response.Write("<strong>DateTime: </strong>" + DateTime + "<br>");
    Response.Write("<strong>Insert: </strong>" + Insert + "<br>");
    %>
    <%
    //Assign query
    if (Type_ID == "New") {
    var NewPost = "INSERT INTO dbo.BulletinBoard (Member_ID, Cat_ID, Topic, Post, Subscription, URL, DateTime)  VALUES ('"+ Member_ID.replace(/'/g, "''") + "', '"+ Cat_ID.replace(/'/g, "''") + "', '"+ Topic.replace(/'/g, "''") + "', '"+ Post.replace(/'/g, "''") + "', '"+ Subscription.replace(/'/g, "''") + "', '"+ URL.replace(/'/g, "''") + "', '"+ DateTime.replace(/'/g, "''") + "') ";
    Response.Write("The 2nd insert was successful.");//TEST OF INSERT
    }
    if (Type_ID == "Reply") {
    var NewPost = "INSERT INTO dbo.Replies (Member_ID, Cat_ID, Post_ID, Topic, Post, Subscription, URL, DateTime)  VALUES ('"+ Member_ID.replace(/'/g, "''") + "', '"+ Cat_ID.replace(/'/g, "''") + "', '"+ Post_ID.replace(/'/g, "''") + "', '"+ Topic.replace(/'/g, "''") + "', '"+ Post.replace(/'/g, "''") + "', '"+ Subscription.replace(/'/g, "''") + "', '"+ URL.replace(/'/g, "''") + "', '"+ DateTime.replace(/'/g, "''") + "') ";
    Response.Write("The 3rd insert was successful.");//TEST OF INSERT
    }
    if (Insert == "true") {
    //------------------INSERT STATEMENT BEGINS--------------------
    var Command1 = Server.CreateObject("ADODB.Command");
    Command1.ActiveConnection = MM_strConn_Bulletin_STRING;
    Command1.CommandText = NewPost;
    Command1.CommandType = 1;
    Command1.CommandTimeout = 0;
    Command1.Prepared = true;
    Command1.Execute();
    //Response.Write(Command1.Command1Text) Use to test if needed
    Command1.Close;
    //------------------INSERT STATEMENT ENDS--------------------
    
    //Redirect user to confirmation page
    Response.Redirect("http://SERVERNAME/BulletinBoard/thankyou.asp?Form_ID=AddPost");
    }
    else {
    //Redirect user back to New Post page
    Response.Redirect("http://SERVERNAME/BulletinBoard/newpost.asp");}
    %>
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  2. #2
    Join Date
    May 2003
    Posts
    599
    Hello again,

    I solved the 2nd DateTime issue on my own by instead inserting an ASP "GetDate()" value directly into the Insert statement, and it worked.

    So now my only problem is why the Member_ID is not Inserting properly into the DB table. The Member_ID column for both DB tables is varchar(50), and if I do a test "Response.Write(Member_ID)" on that same section of page, it displays correctly. Again any advice is appreciated.
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  3. #3
    Join Date
    Nov 2003
    Posts
    655
    In the querystring, you're using
    Code:
    Member_ID.replace(/'/g, "''")
    What do you get if you
    Code:
    Response.Write(Member_ID.replace(/'/g, "''"))
    ?

    I don't do jscript (and rarely do javascript), so I don't know what /'/g equates to...

    Since you've got SQL Server, I'd suggest that you use stored procedures instead of queries as much as possible.
    Last edited by CardboardHammer; 07-20-2004 at 03:42 PM.

  4. #4
    Join Date
    May 2003
    Posts
    599
    Hello CardboardHammer,

    I believe that the "(/'/g, "''") part replaces double-quotes with single quotes. Concerning adding Response.Write to the Insert statement, that woudn't work. That's because I assigned the query as a dependent variable named "NewPost", like this:
    Code:
    <%
    //Assign query
    if (Type_ID == "New") {
    var NewPost = "INSERT INTO dbo.BulletinBoard (Member_ID, Cat_ID, Topic, Post, Subscription, URL, DateTime)  VALUES ('1', '"+ Cat_ID.replace(/'/g, "''") + "', '"+ Topic.replace(/'/g, "''") + "', '"+ Post.replace(/'/g, "''") + "', '"+ Subscription.replace(/'/g, "''") + "', '"+ URL.replace(/'/g, "''") + "', GetDate()) ";
    Response.Write("The 2nd insert was successful.");//TEST OF INSERT
    }
    if (Type_ID == "Reply") {
    var NewPost = "INSERT INTO dbo.Replies (Member_ID, Cat_ID, Post_ID, Topic, Post, Subscription, URL, DateTime)  VALUES ('"+ Member_ID.replace(/'/g, "''") + "', '"+ Cat_ID.replace(/'/g, "''") + "', '"+ Post_ID.replace(/'/g, "''") + "', 'RE: ' + '"+ Topic.replace(/'/g, "''") + "', '"+ Post.replace(/'/g, "''") + "', '"+ Subscription.replace(/'/g, "''") + "', '"+ URL.replace(/'/g, "''") + "', GetDate()) ";
    Response.Write("The 3rd insert was successful.");//TEST OF INSERT
    }
    ...and then I added that variable value to the Insert job, like this:
    Code:
    if (Insert == "true") {
    //------------------INSERT STATEMENT BEGINS--------------------
    var Command1 = Server.CreateObject("ADODB.Command");
    Command1.ActiveConnection = MM_strConn_Bulletin_STRING;
    Command1.CommandText = NewPost;....
    If I do a test to see if the "Member_ID" variable is pulled just above or below the Insert statement, it correctly pulls in the variable. But it's not inserting into the DB table for some reason. If I replace the Member_ID value into a static value, it works properly:
    FROM: VALUES ('"+ Member_ID.replace(/'/g, "''") + "',...
    TO: VALUES ('1',...

    I'm just not sure why it's not working, because it should. Any more advice would be great.
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  5. #5
    Join Date
    Nov 2003
    Posts
    655
    I didn't mean "put Response.Write(Member_ID.replace(/'/g, "''")) in the querystring", I meant "what shows on the page if you do Response.Write(Member_ID.replace(/'/g, "''")) instead of Response.Write(Member_ID)?"

    Also, with SQL Server, it's single quotes that are the problem, not double quotes. Which would mean that Member_ID.replace("'", "''") is what you're looking for. Go with stored procedures, and you wouldn't even need to worry about that (unless you used EXEC on a string built from user input).

  6. #6
    Join Date
    May 2003
    Posts
    599
    My bad...

    I get the same error message on that line:
    Object doesn't support this property or method

    Concerning the Stored Procedures option, I have been researching using sp's instead of queries on ASP pages, but I needed to create this application somewhat quickly, so I decided to do it in the format that I've worked with before. The only thing holding me back from using SP's is getting used to the syntax.

    KWilliams
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  7. #7
    Join Date
    Nov 2003
    Posts
    655
    Just for testing purposes, try using the variables without replacing any characters...

  8. #8
    Join Date
    May 2003
    Posts
    599
    We must have ESP.

    I actually just tried your suggestion, of replacing the JS replace method of:
    Code:
    VALUES ('"+ Member_ID.replace(/'/g, "''") + "',...
    ...with this:
    Code:
    VALUES (" + Member_ID + ",...
    and it worked just fine. So I'll keep it like that for now. But it still is strange to me that the JS replace method worked fine for other variables, but not for this one. I figure that I've just made a mistake somewhere in the code, but I just can't see where it is. Thanks for all of your help in this matter...it's very much appreciated!!!
    KWilliams
    -----------------------
    It's the end of the world as we know it...and I feel fine

  9. #9
    Join Date
    Nov 2003
    Posts
    655
    You're welcome

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center



Recent Articles