No Value given for one or more required parameters in registration form

Question

this is a follow up from my last quistion (Whenever i try to run this code it gives me an error. [Asp.net SQL]) So I did what you told me and added parameters, but now i get a diffrent error. (it works without the parameters, but i cant insert '@' into the database without the parameters) Here is the full code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.OleDb;

public partial class ASPX_register : System.Web.UI.Page
{
    protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            string username = Request.Form["username"];
            string password = Request.Form["password"];
            string email = Request.Form["email"];
            OleDbConnection dbCon = new OleDbConnection();
            OleDbCommand dbCmd = new OleDbCommand();
            String Path = Server.MapPath(@"../App_Data/ShakedDB.mdb;");
            dbCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data source=" + Path;
            dbCmd.Connection = dbCon;
            OleDbParameter pUser = new OleDbParameter();
            OleDbParameter pPass = new OleDbParameter();
            OleDbParameter pEmail = new OleDbParameter();
            pUser.ParameterName = "@Username";
            pUser.Value = username;
            pPass.ParameterName = "@Password";
            pPass.Value = password;
            pEmail.ParameterName = "@Email";
            pEmail.Value = email;
            OleDbDataAdapter dataA = new OleDbDataAdapter(dbCmd);
            dbCmd.CommandText = String.Format("SELECT * FROM Members WHERE username = @Username");


            DataTable dataT = new DataTable();
            dataA.Fill(dataT);
            if (dataT.Rows.Count == 0)
            {
                dbCmd.Parameters.Add(pUser);
                dbCmd.Parameters.Add(pPass);
                dbCmd.Parameters.Add(pEmail);
                dbCmd.CommandText = String.Format("INSERT INTO Members (username, [password], email) VALUES ('@Username','@Password','@Email');");
                dbCon.Open();
                dbCmd.ExecuteNonQuery();
                dbCon.Close();
            }
            else
            {
                Response.Write("That username is alredy taken");
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex);
        }
    }
}

And here is the error: http://prntscr.com/6fg029 . The hewbrew part translates into "No value was set for one or more of the required parameters". Please help me, Thank you. :)

Remove the `'` quotes in the SQL statement around the paramaters — Alex K., Mar 11 '15 at 14:18
_"No value was set for one or more of the required parameters"_ - so, place a breakpoint and inspect `pUser`, `pPass` and `pEmail`'s `Value` properties. Also, don't store passwords unencrypted, don't use a DataTable, and so on. — CodeCaster, Mar 11 '15 at 14:19
[MSDN: Building and Debugging](https://msdn.microsoft.com/en-us/library/ms173083%28v=vs.90%29.aspx). — CodeCaster, Mar 11 '15 at 14:21
@CodeCaster Never mind, i magned to fix it, the problem was that i didnt add the parameters to 'dbCmd', they were only added when the connection to the database was open. thank you for your help, my problem is solved :) — Shaked Dahan, Mar 11 '15 at 14:34
Please use a [using statement](http://www.hanselman.com/blog/WhyTheUsingStatementIsBetterThanASharpStickInTheEyeAndASqlConnectionRefactoringExample.aspx) when using OleDbConnection and OleDbCommand etc. Look at [this example](http://stackoverflow.com/questions/21285568/csv-reading-using-oledb-command). You should also separate the `select` and the `insert` logic. — BCdotWEB, Mar 11 '15 at 14:43
@BCdotWEB I dont understand what you said. the code works fine now, but what is that 'using statement'? that example that you gave me just confused me more, i got no idea what is going on there. isnt 'using' should be used to add .dll files? — Shaked Dahan, Mar 11 '15 at 18:13

score 0 · Answer 1 · answered Mar 11 '15 at 14:50

0

Firstly, you are using @Username as a parameter in your SELECT statement without adding it to that query before you fill the data table. Ie. you need,

 dbCmd.Parameters.Add(pUser);
 DataTable dataT = new DataTable();

In your if condition, I would suggest creating a new OleDbCommand, rather than trying to recycle the previous one. Also you need to remove the single quotes from around the parameters in your insert statement (the parameterization handles that automatically). Ie.,

 dbCmd.CommandText = String.Format("INSERT INTO Members (username, [password], email) VALUES (@Username,@Password,@Email);");

You are also doing some weird things with your DB connection. You are reopening and closing it in your "if" statement for some reason. Just open the connection at the top and close it outside of the if/else condition.

I found an example that looks similar to what you want here:

C# OleDBCommand Tutorial

answered Mar 11 '15 at 14:50

Mark DeVries

1

shouldnt we save 'power' and open the connection to the DB only when we execute commands? my teacher told me that we open it, execute a command and then we close it. – Shaked Dahan Mar 11 '15 at 18:09
You are running a SELECT which requires an open connection anyway or it can't run. This whole routine is about DB interaction. You will use more "power" by incurring the extra overhead of repeatedly opening and closing connections in a short routine that is all about the database anyway. – Mark DeVries Mar 11 '15 at 19:19
Im only opening the connection 1 time and closing it 1 time, and that is only in case that the username is not alredy in use. – Shaked Dahan Mar 11 '15 at 19:34
And how do you think you are figuring out if the username is in use? "SELECT * FROM Members WHERE username = @Username". Looks like a database call to me. – Mark DeVries Mar 11 '15 at 20:53

No Value given for one or more required parameters in registration form

1 Answers1