Inserting data into database, can't figure it out

Question

I'm trying to understand how to insert data into my database, so i looked at many tutorials, and i couldn't understand how to do it. one tutorial got me as far as this:

public partial class Register : System.Web.UI.Page{

public string ID, Pass, Email, BDYear, BDMonth, BDDay, FullName;
SqlCommand cmd;
SqlConnection con;
SqlDataAdapter da;
protected void Page_Load(object sender, EventArgs e)
{


    if (IsPostBack){

        ID = Request.Form["ID"];
        Pass = Request.Form["PW"];
        Email = Request.Form["EMAIL"];
        BDYear = Request.Form["BDYear"];
        BDMonth = Request.Form["BDMonth"];
        BDDay = Request.Form["BDDay"];
        FullName = Request.Form["FullName"];

        cmd = new SqlCommand("INSERT INTO UserInfo (ID, Pass, Email, BDYear, BDMonth, BDDay, FullName) VALUES (ID, Pass, Email,BDYear, BDMonth, BDDay, FullName)");

    }

}
}

But it doesn't actually work, or shows a sign of it working, and i think i need help of someone telling me exactly what to do in my situation. I don't know if any of what is written here is correct, but please i need guidance. All the variables are set in the aspx page according to those names.

Why do you use Request.Form ? And where did you declare the variable Request ? — Adam Cherti, Dec 21 '14 at 09:55
I used request.form to take the value of an input on my webform — Yuval Roth, Dec 21 '14 at 09:56

marc_s · Accepted Answer · 2014-12-21T10:13:51.003

You should try something like this:

set up your query statement as a string
put your SqlCònnection and SqlCommand into using(..) { ... } blocks to ensure proper disposal
define parameters with explicit types, set their values
open the connection, execute query, close connection

This would be the code to use:

   -- your INSERT statement:
   string query = "INSERT INTO UserInfo(ID, Pass, Email, BDYear, BDMonth, BDDay, FullName) " + 
                  "VALUES (@ID, @Pass, @Email, @BDYear, @BDMonth, @BDDay, @FullName);";

   -- define your connection to the database
   using (SqlConnection conn = new SqlConnection("server=.;database=test;Integrated Securiy=SSPI;"))
   -- define your SqlCommand
   using (SqlCommand cmd = new SqlCommand(query, conn))
   {
       -- define the parameters and set their values
       cmd.Parameters.Add("@ID", SqlDbType.Int).Value = ID;
       cmd.Parameters.Add("@Pass", SqlDbType.VarChar, 50).Value = Pass;
       cmd.Parameters.Add("@Email", SqlDbType.VarChar, 255).Value = Email;
       cmd.Parameters.Add("@BDYear", SqlDbType.Int).Value = BDYear;
       cmd.Parameters.Add("@BDMonth", SqlDbType.Int).Value = BDMonth;
       cmd.Parameters.Add("@BDDay", SqlDbType.Int).Value = BDDay;
       cmd.Parameters.Add("@Fullname", SqlDbType.VarChar, 200).Value = Fullname;

       -- open connection, execute query, close connection
       conn.Open();

       int rowsAffected = cmd.ExecuteNonQuery();

       conn.Close();
   }

Of course, with the parameters, I could only guess what datatypes they would be - you might need to adjust that! Also: the connection string in the constructor of the SqlConnection object of course needs to be adapted to your needs - again, I was just guessing what it might be like - adapt as needed!

Can you tell me how to find the connection string to my database? it's called Database.mdb , if it matters — Yuval Roth, Dec 21 '14 at 11:09
If it's `.mdb`, then it's MS Access and you **cannot** use the `SqlConnection`/`SqlCommand` for that - you'll need to check out the `OleDbConnection` and `OleDbCommand` classes - there are **plenty** of beginner's tutorials out there showing you exactly how to do that - just do some **research!** — marc_s, Dec 21 '14 at 14:22
thanks, i also found out that my teacher gave us a dalaccess.cs page which already has all this. in the dalaccess page it does indeed use OleDB things, which is why the newer commands didn't work on my .mdb database. i also converted it to an .accdb . so, thank you for bringing it into my attention. either way you helped me alot, thanks. — Yuval Roth, Dec 23 '14 at 10:47

score 0 · Answer 2 · answered Dec 21 '14 at 10:00

You have not connected to the database and also you haven't executed the command.

Here is an example from MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnection%28v=vs.110%29.aspx

private static void CreateCommand(string queryString,
    string connectionString)
{
    using (SqlConnection connection = new SqlConnection(
               connectionString))
    {
        SqlCommand command = new SqlCommand(queryString, connection);
        command.Connection.Open();
        command.ExecuteNonQuery();
    }
}

You should provide connection string which depends on your DB type and location.

score 0 · Answer 3 · edited May 23 '17 at 12:23

You need to define a sqlconnection first else how will the .net framework know which database to use, where its located etc.

sqlconnection con;
sqlcommand cmd;

con = new sqlconection("your connection string goes here");
cmd = new sql command("your query", con); //we are telling cmd that you need to
// fire the query using con which is a connection object which ultimately
// contains database connection information
con.open();
cmd.ExecuteNonQuery();
con.close();

I don't think data adapter is required here for inserting data. Data adapter is generally used when performing "select" queries. Data adapter generally fills the dataset.

More info about creating a connection string can be found on the below links:-

How to create a connection string in asp.net c#

score -1 · Answer 4 · answered Dec 21 '14 at 09:56

        cmd = new SqlCommand("INSERT INTO UserInfo (ID, Pass, Email, BDYear, BDMonth, BDDay, FullName) VALUES ("+ID+", "+Pass+", "+Email+","+BDYear+", "+BDMonth+", "+BDDay+", "+FullName+")");

this may work but i suggest use SqlCommand with parameter.

this articles can help you .

http://msdn.microsoft.com/tr-tr/library/system.data.sqlclient.sqlcommand.parameters(v=vs.110).aspx http://www.csharp-station.com/Tutorial/AdoDotNet/lesson06

Inserting data into database, can't figure it out

4 Answers4