Big problems with my current schema/syntax and coding

I have several problems: And im really getting stuck here.

Ok first problem is with my jquery bound to my button. The function works but when its bound to my asp control button (Button1) I cant for some reason use the code behind to update mysql.

<script type="text/javascript">
    $(function () {

        $('[name*= "Button2"]').click(function () {
            var x = $('[name*= "TextBox1"]').val();
            var newdiv = $("<div></div>").html(x).attr('id', 'test');

            $('#test1').append(newdiv);
            $('[name*= "Table1"]').text($('#test1').html());
            $('[name*= "TextBox1"]').val('');

            return false;

        });
    });  
</script>
<p>
    <asp:TextBox ID="TextBox1" name="TextBox1" runat="server" Rows="3" 
        Height="47px" Width="638px"></asp:TextBox>
</p>
<p>
    <asp:Button ID="Button1" runat="server" Text="Post Message" Width="98px" 
        onclick="Button1_Click" />
    <asp:Button ID="Button2" runat="server" onclick="Button2_Click" Text="Button" />
    </p开发者_StackOverflow社区>
<p>
    <asp:Table ID="Table1" name="Table1" runat="server" Width="488px"></asp:Table>
</p>

<div id="test1"></div>

</asp:Content>

I tryed solving this by taking out return false; this worked in that it did save to the database as the userid was there but Wallpostings had an empty field. This is due to the page reloading when I click my asp button which is in turn due to the java/jquery. If I keep return false I get nothing in my sql.

    {
        string theUserId = Session["UserID"].ToString();
        OdbcConnection cn = new OdbcConnection("Driver={MySQL ODBC 3.51 Driver}; Server=localhost; Database=gymwebsite; User=root; Password=commando;");
        cn.Open();

        OdbcCommand cmd = new OdbcCommand("INSERT INTO WallPosting (UserID, Wallpostings) VALUES ("+theUserId+", '" + TextBox1.Text + "')", cn);
        cmd.ExecuteNonQuery();
    }
}

Using this code I can insert into my database but when I try to insert again if I want to add another comment on my wall post I get an error: Duplicate entry '1' for key 'PRIMARY' so I think I need to use update but I dont know how to do an Update for mysql or sql syntax? It also says when I create the schema that on update no action (not sure if this is the same thing) see below for script)

Also does update do the same thing as insert if there is nothing there to begin with? And if I update how do I add the data sequentialy (not sure thats the correct word) i.e if I have apples already in my Wallpostings and I update something new in my textbox like oranges how do I get it to layout like this:

(Wallposting Table)

| UserID | Wallpostings |
....1........Oranges
.............Apples  

Ignore the dots (just for spacing)

    SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0;

SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;

SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='TRADITIONAL';



CREATE SCHEMA IF NOT EXISTS `gymwebsite` DEFAULT CHARACTER SET latin1 COLLATE latin1_swedish_ci ;

USE `gymwebsite` ;



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

-- Table `gymwebsite`.`User`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`User` (

  `UserID` INT NOT NULL AUTO_INCREMENT ,

  `Email` VARCHAR(245) NULL ,

  `FirstName` VARCHAR(45) NULL ,

  `SecondName` VARCHAR(45) NULL ,

  `DOB` VARCHAR(15) NULL ,

  `Location` VARCHAR(45) NULL ,

  `Aboutme` VARCHAR(245) NULL ,

  `username` VARCHAR(45) NULL ,

  `password` VARCHAR(45) NULL ,

  PRIMARY KEY (`UserID`) )

ENGINE = InnoDB;





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

-- Table `gymwebsite`.`Pictures`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`Pictures` (

  `UserID` INT NOT NULL ,

  `picturepath` VARCHAR(245) NULL ,

  PRIMARY KEY (`UserID`) ,

  INDEX `fk_Pictures_Userinfo1` (`UserID` ASC) ,

  CONSTRAINT `fk_Pictures_Userinfo1`

    FOREIGN KEY (`UserID` )

    REFERENCES `gymwebsite`.`User` (`UserID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;





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

-- Table `gymwebsite`.`WallPosting`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`WallPosting` (

  `UserID` INT NOT NULL AUTO_INCREMENT ,

  `Wallpostings` VARCHAR(2500) NULL ,

  INDEX `fk_WallPostings_Userinfo1` (`UserID` ASC) ,

  PRIMARY KEY (`UserID`) ,

  CONSTRAINT `fk_WallPostings_Userinfo1`

    FOREIGN KEY (`UserID` )

    REFERENCES `gymwebsite`.`User` (`UserID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;





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

-- Table `gymwebsite`.`DietPlan`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`DietPlan` (

  `UserID` INT NOT NULL ,

  PRIMARY KEY (`UserID`) ,

  INDEX `fk_DietPlan_Userinfo1` (`UserID` ASC) ,

  CONSTRAINT `fk_DietPlan_Userinfo1`

    FOREIGN KEY (`UserID` )

    REFERENCES `gymwebsite`.`User` (`UserID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;





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

-- Table `gymwebsite`.`WorkoutPlan`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`WorkoutPlan` (

  `UserID` INT NOT NULL ,

  PRIMARY KEY (`UserID`) ,

  INDEX `fk_WorkoutPlan_Userinfo1` (`UserID` ASC) ,

  CONSTRAINT `fk_WorkoutPlan_Userinfo1`

    FOREIGN KEY (`UserID` )

    REFERENCES `gymwebsite`.`User` (`UserID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;





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

-- Table `gymwebsite`.`Friends`

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

CREATE  TABLE IF NOT EXISTS `gymwebsite`.`Friends` (

  `idFriends` INT NOT NULL AUTO_INCREMENT ,

  `UserID` INT NOT NULL ,

  PRIMARY KEY (`idFriends`, `UserID`) ,

  INDEX `fk_Friends_Userinfo1` (`UserID` ASC) ,

  CONSTRAINT `fk_Friends_Userinfo1`

    FOREIGN KEY (`UserID` )

    REFERENCES `gymwebsite`.`User` (`UserID` )

    ON DELETE NO ACTION

    ON UPDATE NO ACTION)

ENGINE = InnoDB;







SET SQL_MODE=@OLD_SQL_MODE;

SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS;

SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS;

What I may be trying to do is update but at the same time insert? If that makes any sense?

1. "return false;" in your js is like saying preventDefault(), it prevents further code from running (and it keeps the browser from posting forms and following links, etc). In this case it's preventing the ASP.net code from executing a postback, and that keeps your server side code from running your SQL statement. You need to remove that return false line.

Update: You dynamic content disappears because you require a postback to do the SQL update. That means the page gets redrawn from scratch and all the changes you made with jQuery disappear. To correct this you either need to make an AJAX call to run the server side code, or remove the JS function and write some server side code to display the div after you make the SQL call.

1. UserId should not be the primary key for WallPosting table, it should only be the primary key for the User table. You should create an new auto incrementing field called WallPostingId, and this would allow you to have multiple WallPostings for each user (do this with other tables too, obviously changing the name of the field to be appropriate for each table), then you can sort them by CreatedDate (another useful field to have). It’s alright to have UserId as a foreign key constraint in other tables, but that just verifies that any given UserId exists.

YOUR ODBC COMMAND IS VULNERABLE TO SQL INJECTION ATTACK! A crafty user could use a command like that to take control of your database, potentially stealing, corrupting, and destroying data for other users.

You should not use string concatenation (e.g. "Insert ... " + UserText + "...") to build SQL commands. I would suggest creating stored procedures or Linq statements for each or your data operations that deal with user created data. Think about it, what if I entered this into the text box:

‘); drop table User; --

Looking at your schema, it looks like your WallPosting table has the UserId identified as the Primary Key. You can't insert another record into that table with a duplicate primary key, as whatever your primary key is has to be unique on the table. Your wallposting table needs a different primary key; your schema would look something like (WallPostingId, UserId, Wallposting) with the WallPostingId being the primary key.

Secondly, an update will not insert a record if none exists - insert and update are two distinct operations. It sounds like you're looking for an "upsert" - basically, an update statement, if rowcount == 0, then insert operation.