Issues with OleDbCommand, I always get the same error

Question

I'm trying to create a command that will be executed in the database, but i always get the same error when i try to execute it.

first of all, lets start with the code, maybe there is something wrong in there that i cant see:

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"].Replace("'","''");
            string password = Request.Form["password"].Replace("'", "''");
            string fname = Request.Form["fname"].Replace("'", "''");
            string lname = Request.Form["lname"].Replace("'", "''");
            string email = Request.Form["email"].Replace("'", "''");
            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();
            OleDbParameter rDate = new OleDbParameter();
            OleDbParameter pfname = new OleDbParameter();
            OleDbParameter plname = new OleDbParameter();
            pUser.ParameterName = "@Username";
            pUser.Value = username;
            pPass.ParameterName = "@Password";
            pPass.Value = password;
            pfname.ParameterName = "@pfname";
            pfname.Value = fname;
            plname.ParameterName = "@plname";
            plname.Value = lname;
            pEmail.ParameterName = "@Email";
            pEmail.Value = email;
            rDate.ParameterName = "@RDate";
            rDate.Value = DateTime.Now.ToString();
            dbCmd.Parameters.Add(pUser);
            dbCmd.Parameters.Add(pPass);
            dbCmd.Parameters.Add(pEmail);
            dbCmd.Parameters.Add(rDate);
            dbCmd.Parameters.Add(pfname);
            dbCmd.Parameters.Add(plname);
            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.CommandText = String.Format("INSERT INTO Members (username,[password],[firstName],[lastName],email,registerDate) VALUES (@Username,@Password,@pfname,@plname,@Email,@RDate);");
                dbCon.Open();
                dbCmd.ExecuteNonQuery();
                dbCon.Close();
                Response.Write(" " + fname + " " + lname);
                Response.Write("נרשמת לאתר בהצלחה" + "<br />");
                Response.Write("שם המשתמש שלך הוא:" + username + "<br />");
                Response.Write("הסיסמא שלך היא:" + password + "<br />");
                Response.Write("האימייל שאיתו נרשמת הוא:" + email + "<br />");
                Response.Write("<a href='../ASPX/main.aspx' target='mainFrame'>בחזרה לעמוד הראשי לחץ כאן</a>");
            }
            else
            {
                Response.Write("שם המשתמש הזה כבר תפוס. נסה שם אחר." + "<br />");
                Response.Write("<a href='main.aspx' target='mainFrame'>בחזרה לעמוד הראשי לחץ כאן</a>");
            }
        }
        catch (Exception ex)
        {
            Response.Write(ex);
        }
    }
}

ignore the Hebrew part please, its just tells the user that he registered. Here is the error that i get: http://prntscr.com/6gh189 The Hebrew part says "A mismatch of the expression data type criteria". I want to say that the code works perfectly without the 'pfname' and 'plname' parameters, for some reason when i add them this error occurs. I also tried to write this 'insert' command and it worked, so it must be something with the parameters, but i cant find it:

INSERT INTO Members (username,[password],[firstName],[lastName],email,registerDate) VALUES (@Username,@Password,'asdfasdfasdf','asdfasdf',@Email,@RDate);

the code above works and i get no error if i use it.

Some troubleshooting tips: it's always best to simplify things when you don't know where the problem is. It would be simpler (fewer things to reason about) if you had two separate `OldDbCommand` instances. It would also be simpler if you were to add the parameters in the order in which they appear in the `INSERT` statement. Once you try these and _still_ get the same exception, you can consider that `OleDbCommand` doesn't use named parameters like `SqlCommand` does. See http://stackoverflow.com/questions/5870146/error-in-executing-an-oledbcommand-must-declare-the-scalar-variable-maxid. — John Saunders, Mar 14 '15 at 01:25
@JohnSaunders i dont understand, how does the oledbparameters works? if i only use '?' then how does the command know what parameter im reffering to? i mean, if i add 2 paratmeters with diffrent values and then i enter this command 'INSERT INTO Members (username,[password]) VALUES (?,?);' how does the database know that the first '?' containts the username value and not the password value? they have the same name, so it could be both of them. i tried to search google but i didnt manged to get answer. can you explaine it to me? also, if the '@' dont work on oledb how did it worked for me? — Shaked Dahan, Mar 14 '15 at 12:31
@ does not work in select statements. parameters must be added in the correct order and it's up to you to make sure. — wazz, Mar 15 '15 at 01:32
When you use query parameters, you don't need to do the `Replace(variable, "'", "''")` thing. — Joel Coehoorn, Mar 15 '15 at 02:07
Did you read http://stackoverflow.com/questions/5870146/error-in-executing-an-oledbcommand-must-declare-the-scalar-variable-maxid? — John Saunders, Mar 16 '15 at 01:07

score 1 · Answer 1 · edited May 02 '15 at 19:25

1

You are using OleDbParameter Constructor (String, Object) overload

DateTime rDate = new DateTime();

rDate = Convert.ToDateTime(DateTime.Now.ToString("MM/dd/yyy hh:mm:ss"));

...

dbCmd.Parameters.Add(rDate);

edited May 02 '15 at 19:25

Weather Vane

33,872
7
36
56

answered May 02 '15 at 19:17

Osvaldo A de Paula

11
1

thank for the answer, but this is a very old quistion and i alredy solved it long ago. – Shaked Dahan May 02 '15 at 19:20

score 0 · Answer 2 · answered Mar 15 '15 at 01:45

"SELECT * FROM Members WHERE username = @Username"

becomes:

"SELECT * FROM Members WHERE username = ?"

move your parameters after setting the commandtext (and set everything in one line):

dbCmd.Parameters.Add("@Username", OleDbType.VarChar, 15).Value = username;

for the inserts try something like this:

"INSERT INTO Members (username,[password],[firstName],[lastName],email,registerDate) VALUES (?,?,?,?,?,?);"

cmd.Parameters.AddWithValue("UserName", username);
cmd.Parameters.AddWithValue("Password", password);
etc.

that's just how it is with access dbs.

nice link: http://www.mikesdotnetting.com/article/26/parameter-queries-in-asp-net-with-ms-access. actually he says @Username could work instead of [?], but i haven't seen that before. another link: https://msdn.microsoft.com/en-us/library/system.data.oledb.oledbcommand.parameters%28v=vs.110%29.aspx — wazz, Mar 15 '15 at 01:56

Issues with OleDbCommand, I always get the same error

2 Answers2