Need help to update database - with a button : The Official Microsoft ASP.NET Forums

Reply

smarts

Member

72 Points

Need help to update database - with a button

Nov 17, 2011 04:12 PM|LINK

Hello!

I'm currently working on a site to reserve seats. I have created a table where the seats is displayed, where you also can see if a seat is reserved or not. The thing is that I can't get my "Reserve" function to work, what I want is a button for each free seat that the users can click, wich will update the database.

(As you can see I have also created a "Owner button" for the seats that's allready reserved, but I will work on that one later)

My table in the db is called "signup", and I have the columns: "SeatId"(the seat number) "ReservedId"(true/false=reserved/available) "UserId"(Here I want to add the username of the user that reserve the seat"

Thanks for any help! Feel free to ask if there is anything confusing.

Here is what I got:

@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Plassreservering";
    
    var db = Database.Open ("StarterSite");
    
    var selectQueryString = "SELECT * FROM Signup ORDER BY SeatId";
    
    if(IsPost()){
    if(Request["action"] == "Reserve"){
     db.Execute("UPDATE signup SET UserId = @0 WHERE SeatId = @1", WebSecurity.GetUserId(WebSecurity.CurrentUserName), SeatId);
    } else {
        //coming later
    }
    

}
}
<table style="border: 1px solid black;">
    <thead>
        <tr>
           <th>Seatnumber</th>
        </tr>
    </thead>
    <tbody>
       @foreach(var row in db.Query(selectQueryString)) {

  <tr>
    <td>
       
      @if (row.ReservedId == true) {
        <div class="tableReserved">
            <p>@row.SeatId</p>
            <p>Reserved</p>
            <form action="" method="get">
            <p><input type="submit" value="Owner" /></p>
            </form>
        </div>
        
        }
         else {
            <div class="tableAvailable">
                <p>@row.SeatId</p>
                <p>Available</p>
                    <form action="" name="action" method="post">
                      <p><input type="submit" value="Reserve" /></p>
                    </form>
  
            </div>
        }
    </td>
  </tr>
}
    </tbody>

</table>

Reply

Mikesdotnett...

All-Star

154818 Points

19853 Posts

Moderator

MVP

Re: Need help to update database - with a button

Nov 17, 2011 08:32 PM|LINK

You seem to be a bit confused about how forms work and how values get transferred when a form is submitted. I recommend that you should concentrate on one piece of functionlaity at a time to reduce the confusion, and get rid of the "Owner" part for the time being.

If you want to be able to access form field values, the elements (text inputs, radios etc) need a name attribute. You also need to pass the SeatId, so a hidden field will do that:

{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Plassreservering";
    
    var db = Database.Open ("StarterSite");
    
    var selectQueryString = "SELECT * FROM Signup ORDER BY SeatId";
    
    if(IsPost()){
    if(!Request["SeatId"].IsEmpty()){
      var SeatId = Request["SeatId"];
      var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);
     db.Execute("UPDATE signup SET UserId = @0 WHERE SeatId = @1", user, SeatId);
    } else {
        //coming later
    }
    

}
}
<table style="border: 1px solid black;">
    <thead>
        <tr>
           <th>Seatnumber</th>
        </tr>
    </thead>
    <tbody>
       @foreach(var row in db.Query(selectQueryString)) {
    <tr>
      <td>
      @if (row.ReservedId != true) {
           <div class="tableAvailable">
                <p>@row.SeatId</p>
                <p>Available</p>
                    <form action="" method="post">
                      <p><input type="submit" value="Reserve" /></p>
                    <input type="hidden" name="SeatId" value="@row.SeatId" />
                    </form>
            </div>
        }
    </td>
  </tr>
}
    </tbody>
</table>

I haven't tested this, so there may be typos..

Regards, Mike
Beginning ASP.NET Web Pages with WebMatrix | My Site | Twitter

Marked as answer by smarts on Nov 20, 2011 11:59 AM

Reply

smarts

Member

72 Points

38 Posts

Re: Need help to update database - with a button

Nov 20, 2011 12:08 PM|LINK

Thank you Mike :)

How can I at the same time set the value in my ReservedId row to "true"? I tried this, but it didn't work..

if(IsPost){
    if(!Request["SeatId"].IsEmpty()){
      var SeatId = Request["SeatId"];
      var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);
      var ReservedId = Request["ReservedId"];
      var updateReservedId = "Set ReservedId = true";
     db.Execute(updateReservedId,"UPDATE signup SET UserId = @0 WHERE SeatId = @1", user, SeatId);
    } else {
        //coming later
    }
    

}

Reply

stevelydford

Member

64 Points

13 Posts

Re: Need help to update database - with a button

Nov 20, 2011 01:33 PM|LINK

I haven't seen your database, but I am guessing that the ReservedId column has a "bit" data type?

Try this. I havent tested it, but it shouldn't be far off:

if(IsPost){
    if(!Request["SeatId"].IsEmpty()){
      var SeatId = Request["SeatId"];
      var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);
      var ReservedId = true;
     db.Execute("UPDATE signup SET ReservedId = @0, UserId = @1 WHERE SeatId = @2", ReservedId, user, SeatId);
    } else {
        //coming later
    }

Marked as answer by smarts on Nov 20, 2011 01:37 PM

Reply

smarts

Member

72 Points

38 Posts

Re: Need help to update database - with a button

Nov 20, 2011 02:07 PM|LINK

Thank you Steve! That worked very well :)

Next off, I want to let the user see what seat he/she has reserved. So I'm thinking something like:

           if (row.UserId == user){
            <p>@row.SeatId</p>
            <p>Your seat</p>
        }

refering to the "var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);". How can I make a if-query like that?

I tried this:

@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Plassreservering";
    
    var db = Database.Open ("StarterSite");
    
    var selectQueryString = "SELECT * FROM Signup ORDER BY SeatId";
    
if(IsPost){
    if(!Request["SeatId"].IsEmpty()){
      var SeatId = Request["SeatId"];
      var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);
      var ReservedId = true;
     db.Execute("UPDATE signup SET ReservedId = @0, UserId = @1 WHERE SeatId = @2", ReservedId, user, SeatId);
    } else {
        //coming later
    }
    

}
}
<table style="border: 1px solid black;">
    <thead>
        <tr>
           <th>Seatnumber</th>
        </tr>
    </thead>
    <tbody>
       @foreach(var row in db.Query(selectQueryString)) {
  <tr>
    <td>
      @if (row.ReservedId != true) {
        <div class="tableAvailable">
            <p>@row.SeatId</p>
            <p>Available</p>
            <form action="" method="post">
            <p><input type="submit" value="Reserve" /></p>
                <input type="hidden" name="SeatId" value="@row.SeatId" />
            </form>
        </div>
        }
    
        @if (row.ReservedId == true) {
           if (row.UserId == user){
            <p>@row.SeatId</p>
            <p>Your seat</p>
        }
        else{
        <div class="tableReserved">
            <p>@row.SeatId</p>
            <p>Reserved</p>
        </div>
        }
        }
    </td>
  </tr>
}
    </tbody>

</table>

Thank you!

Edited by smarts on Nov 20, 2011 02:08 PM

Reply

stevelydford

Member

64 Points

13 Posts

Re: Need help to update database - with a button

Nov 21, 2011 10:15 PM|LINK

Looking at your code it is possible that you are storing userIDs in the signup table of your database, but testing it for equality against a user name?

Also, you need to check through your code for matching braces ({.....}). I think you might have a couple out of place.

What output did you get from that code? Any error message?

Edited by stevelydford on Nov 21, 2011 10:22 PM
Edited by stevelydford on Nov 21, 2011 10:56 PM

Reply

smarts

Member

72 Points

38 Posts

Re: Need help to update database - with a button

Nov 22, 2011 03:50 PM|LINK

Thank you for your reply.

Yes, in my DB I have a table with a column called UserId. When a user reserves a seat, his UserID will get copied to this column.

In this case I want the users to be able to see what seat they have reserved, by looking on the UserId of the current user logged in, and compare it to the UserId-row in signup.

Compiler error message: CS0103: The name user does not exist in the current context

Source of errors:

Line 42:     
Line 43:         @if (row.ReservedId == true) {
Line 44: if (row.UserId == user){
Line 45:             <p>@row.SeatId</p>
Line 46:             <p>Your seat</p>


"Off topic": I also want to add a kind of restriction, I only want to let the users reserve one seat. How should I solve this?

Reply

stevelydford

Member

64 Points

13 Posts

Re: Need help to update database - with a button

Nov 23, 2011 12:34 AM|LINK

Your "user" variable only has local scope within the if statement block. You need to declare it at the page level.

Marked as answer by smarts on Dec 04, 2011 04:50 PM

Reply

smarts

Member

72 Points

38 Posts

Re: Need help to update database - with a button

Dec 04, 2011 04:56 PM|LINK

Thank you! I didn't notice that.

Now I got a new error:

Errordetails: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: The operator == can't be used in operands of the kind string and int

Source error:

Linje 47:         </div>
Linje 48:         }
Linje 49: @if (row.UserId == user){ Linje 50:             <p>@row.SeatId</p>
Linje 51:             <p>Your seat</p>

However, in my DB the data type of my UserId row is nvarchar.

Here is my complete code, think I've cleaned it up a bit:

@{
    Layout = "~/_SiteLayout.cshtml";
    Page.Title = "Seat registration";
    
    var db = Database.Open ("StarterSite");
    
    var selectQueryString = "SELECT * FROM Signup ORDER BY SeatId";
    var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);
    
if(IsPost){
    if(!Request["SeatId"].IsEmpty()){
      var SeatId = Request["SeatId"];
      var ReservedId = true;
     db.Execute("UPDATE signup SET ReservedId = @0, UserId = @1 WHERE SeatId = @2", ReservedId, user, SeatId);
    } else {
        //coming later
    }
    

}
}
<table style="border: 1px solid black;">
    <thead>
        <tr>
           <th>Seatnumber</th>
        </tr>
    </thead>
    <tbody>
       @foreach(var row in db.Query(selectQueryString)) {
  <tr>
    <td>
      @if (row.ReservedId != true) {
        <div class="tableAvailable">
            <p>@row.SeatId</p>
            <p>Available</p>
            <form action="" method="post">
            <p><input type="submit" value="Reserve" /></p>
                <input type="hidden" name="SeatId" value="@row.SeatId" />
            </form>
        </div>
        }
    
        @if (row.ReservedId == true) {
        <div class="tableReserved">
            <p>@row.SeatId</p>
            <p>Reserved</p>
        </div>
        }
        @if (row.UserId ==  user){
            <p>@row.SeatId</p>
            <p>Your seat</p>
        }
    </td>
  </tr>
}
    </tbody>

</table>

Reply

GmGregori

Contributor

5438 Points

730 Posts

Re: Need help to update database - with a button

Dec 05, 2011 12:42 PM|LINK

Pay attention to the field data type for UserId.

It looks like you have selected nchar or nvarchar for this field and not int, the data type returned by WebSecurity.GetUserId

Else you must add the command

string stuser = Convert.ToString(user);

just after

var user = WebSecurity.GetUserId(WebSecurity.CurrentUserName);

and modify line

@if (row.UserId == user){

as follows

@ if (row.UserId.Trim() == stuser){

Marked as answer by smarts on Dec 06, 2011 07:17 PM

Need help to update database - with a button RSS

19 replies

smarts

Need help to update database - with a button

Mikesdotnett...

Re: Need help to update database - with a button

smarts

Re: Need help to update database - with a button

stevelydford

Re: Need help to update database - with a button

smarts

Re: Need help to update database - with a button

stevelydford

Re: Need help to update database - with a button

smarts

Re: Need help to update database - with a button

stevelydford

Re: Need help to update database - with a button

smarts

Re: Need help to update database - with a button

GmGregori

Re: Need help to update database - with a button

This site is managed for Microsoft by Neudesic, LLC. | © 2013 Microsoft. All rights reserved.

Follow Us On:

Microsoft