4 Replies - 1266 Views - Last Post: 28 September 2012 - 08:10 AM Rate Topic: -----

#1 rbflowers  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 48
  • Joined: 10-February 12

Updating database with a form

Posted 26 September 2012 - 02:42 PM

Hey,
I'm trying to add a comment section for my webpage. To do this, I've set up a table in my .mdf database named Comments.
The columns are ID (prim. key), MetricName, Time, Name, Email, Comment and Likes.

I've written the form:
@using WebMatrix.Data;

@{//title of tab
    ViewBag.Title = "Comments";
}

@{
    var name = ""; var email = ""; var comment = "";  DateTime time = DateTime.Now; var metric = ""; int likes = 0;

    if(IsPost){
        name = Request.Form["name"];
        if (name == ""){
            name = "Anonymous";
        }
        email = Request.Form["email"];
        comment = Request.Form["comment"];
        time = DateTime.Now;
        metric = "Merch/Cust Reported Bugs"; 
          
    
        var db = Database.Open("Metrics");
        var insertCommand = "INSERT INTO Comments (MetricName, Time, Name, Email, Comment, Likes) Values(@metric, @time, @name, @email, @comment, @likes)";
        db.Execute(insertCommand, metric, time, name, email, comment, likes);
        Response.Redirect("~/Comments");   
    }
}


<h1>Add a comment</h1>

<form action ="" method="post">
    <fieldset>
        <legend>Comment</legend>
        
        <label for="name">Name:</label>
         <input type="text" name="name" value="@Request.Form["name"]" />

      <label for="email">Email:</label>
         <input type="text" name="email" value="@Request.Form["email"]" />

         <br />
      <label for="comment">Comment:</label>
        <textarea rows="3" cols="40" name = "comment" value="@Request.Form["comment"]"></textarea>
          
      <p><input type="submit" name="buttonsubmit" value="Add Comment" /></p>
    </fieldset>

</form>



I'm really new to database stuff, and have no idea what I'm doing, but I'm getting this error when I try adding a comment:

Quote

Must declare the scalar variable "@metric".

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Must declare the scalar variable "@metric".

Source Error:


Line 21: var db = Database.Open("Metrics");
Line 22: var insertCommand = "INSERT INTO Comments (MetricName, Time, Name, Email, Comment, Likes) Values(@metric, @time, @name, @email, @comment, @likes)";
Line 23: db.Execute(insertCommand, metric, time, name, email, comment, likes);
Line 24: Response.Redirect("~/Comments");
Line 25: }

Source File: c:\Users\rflowers\Documents\Visual Studio 2010\Projects\L1DashboardRedone\L1DashboardRedone\Views\Home\Comments.cshtml Line: 23


Is This A Good Question/Topic? 0
  • +

Replies To: Updating database with a form

#2 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1175
  • View blog
  • Posts: 1,661
  • Joined: 24-August 11

Re: Updating database with a form

Posted 27 September 2012 - 08:27 AM

I'm not familiar with the method of database interaction that you're using (var db = Database.Open, db.Execute(...)), but I don't see where you're declaring the values of your parameters. This is how it would look in the method I'm familiar with:

// Note - if you're not familiar with string literals, I use the '@' at the start of my command
// strings so I can format them the way I would in SSMS (across multiple lines)
var insertCommand = @"INSERT INTO Comments (MetricName, Time, Name, Email, Comment, Likes)
                      Values (@metric, @time, @email, @comment, @likes)";

using (var con = *your SqlConnection*)
using (var cmd = new SqlCommand(insertCommand, con)
{
    cmd.Parameters.AddWithValue("@metric", *the value of metric);
    cmd.Parameters.AddWithValue("@time", *the value of time); // etc

    con.Open();
    cmd.ExecuteNonQuery();
    con.Close();
}



I know the example isn't following your methods, but when you use parameterized queries, you have to declare the parameters. Here's a good tutorial on how to use parameterized queries: tutorial.
Was This Post Helpful? 0
  • +
  • -

#3 November-06  Icon User is offline

  • D.I.C Regular

Reputation: 45
  • View blog
  • Posts: 376
  • Joined: 04-January 11

Re: Updating database with a form

Posted 28 September 2012 - 04:54 AM

If I am not mistaken, var is a dynamic datatype.

Usually, you use it for results of LINQ queries but in your example, I think it would be better to use strong datatypes like string, integer and double.
Was This Post Helpful? 0
  • +
  • -

#4 h4nnib4l  Icon User is offline

  • The Noid
  • member icon

Reputation: 1175
  • View blog
  • Posts: 1,661
  • Joined: 24-August 11

Re: Updating database with a form

Posted 28 September 2012 - 05:50 AM

Var is implicitly (rather than explicitly) typed, but it is still strongly typed. The type is simply chosen by the compiler.

MSDN

Here's a conversation about var from a while back in the C# forum (that contains links to other conversations about var). Personally, the only time I really don't use var (when I can) is when I'm posting a code in reply to a lower-level question, and I don't want to confuse the OP. In this case, the OP used var, so I felt safe in assuming he/she understood it.
Was This Post Helpful? 0
  • +
  • -

#5 rbflowers  Icon User is offline

  • New D.I.C Head

Reputation: 2
  • View blog
  • Posts: 48
  • Joined: 10-February 12

Re: Updating database with a form

Posted 28 September 2012 - 08:10 AM

I figured it out:

var insertCommand = "INSERT INTO Comments (MetricName, Time, Name, Email, Comment, Likes) Values(@0, @1, @2, @3, @4, @5)";

Instead of:

var insertCommand = "INSERT INTO Comments (MetricName, Time, Name, Email, Comment, Likes) Values(@metric, @time, @name, @email, @comment, @likes)";

works
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1