'Parameters supplied for object 'AdminAssistant' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.'

Question

While inserting values into the database i am getting error which is said in subject dont know what i am missing.

        string ImagePath = "";

        string str = "insert into AdminAssistant() values('"+TextBox7.Text+ "','" + TextBox1.Text + "','" + TextBox2.Text + "','" + TextBox5.Text + "','" + TextBox6.Text + "','" + DropDownList2.Text + "','" + TextBox4.Text + "','" + DropDownList1.Text+ "','" + TextBox9.Text + "','"+ImagePath+"','"+DateTime.Now+"','Active')";
        SqlCommand cmd = new SqlCommand(str,con);
        con.Open();
        cmd.ExecuteNonQuery();
        con.Close();

        Label1.Text = "Admin Created Successfully.....!!!";

Below is the Query

   Create Table AdminAssistant
  (
 A_ID int identity(1,1) not null primary key,
Aname varchar(20),
Aphone varchar(16),
Amail varchar(20),
A_Address varchar(150),
A_City varchar(20),
A_Gender varchar(10)NOT NULL CHECK (A_Gender IN('Male', 'Female')),
A_Password varchar(20),
Aroll varchar(20)NOT NULL CHECK (Aroll IN('SuperAdmin', 'Admin')),
MetaDescription varchar(256),
Media varchar(40),
RegisterDate datetime,
A_Status varchar(20)NOT NULL CHECK (A_Status IN('Active', 'Disable'))

)

I think this values should inserted into database despite that it is giving error.

Don't inject your values, parametrise your statement. SQL Injection is not your friend. — Thom A, May 18 '19 at 13:03
You have committed the most heinous crime in sql world. Never concatenate strings with user inputs to build sql commands — Steve, May 18 '19 at 13:04
Really, the first thing to do is fix the injection at @Steve. If the OP stops getting the error, they'll be less likely to fix the *real* problem here. I've seen it enough times where an OP is given a fix to the problem, and then a few days later they asked a related question, injection issue still unresolved (and hence why they're getting an error). — Thom A, May 18 '19 at 13:07
: 'String or binary data would be truncated. The statement has been terminated.' After Removing () — Jitender, May 18 '19 at 13:08
Parsing, again a problem with string concatenation. Do you have a single quote in your inputs? And how is translated the DateTime.Now in your concatenated string? — Steve, May 18 '19 at 13:09
Show your new **parametrised** query in your question @Jitender. — Thom A, May 18 '19 at 13:09
Conversion failed when converting date and/or time from character string.' Now getting this — Jitender, May 18 '19 at 13:27
You are putting single quotes around the parameters placeholders. This makes them literal text — Steve, May 18 '19 at 13:28
I removed single quoted now starting error getting System.Data.SqlClient.SqlException: 'String or binary data would be truncated. The statement has been terminated.' — Jitender, May 18 '19 at 13:31
Thse are all clear signs that your query is still not parametrised @Jitender. Have a look at Steve's answer, and implement parametrisation. If you are still getting a truncation error after that, that means you are entering values that are too large for your columns. 20 characters for a name isn't much, so maybe that's overflowing. Also, take great note of Steve's comments on Passwords; **never** store plain text passwords in your database. They need to be salted and hashed. — Thom A, May 18 '19 at 13:34
You can check updated code in question i have parapeterized it dude — Jitender, May 18 '19 at 13:38
If you get a message about string truncation then some data that you are trying to save is too long for the field size. — Steve, May 18 '19 at 13:40
[Can we stop using AddWithValue already?](https://blogs.msmvps.com/jcoehoorn/blog/2014/05/12/can-we-stop-using-addwithvalue-already/) Steve uses `Parameters.Add`, and for good reason. — Thom A, May 18 '19 at 13:43
System.Data.SqlClient.SqlException: 'String or binary data would be truncated. The statement has been terminated.' not saving too long field size putting small size still getting same error — Jitender, May 18 '19 at 13:44
Most likely something you're trying to insert into one of your `varchar` columns is too long, @Jitender. YOu either need to limit the characters in your form to be the same as your database, or increase the size of your columns. — Thom A, May 18 '19 at 13:45
Please don't change your question to something else. An answer has been posted that answers your original question. If you move away from this problem and rewrite your question to revolve around your next problem, this answer is now meaningless. Additionally, people may not want to answer your question if you simply discard the problem and reuse this post. There are many questions here on SO about truncation, please restore your question and research this new problem before optionally leaving another question about it. — Lasse V. Karlsen, May 18 '19 at 13:45
If what you're really looking for is a way to start a discussion about this, there are chat-rooms available that have a much looser set of requirements, you may want to visit them instead. However, right now your question doesn't make any sense, as your whole question is now written in such a way that it asks "Why does my code produce this error?" whereas it no longer does. — Lasse V. Karlsen, May 18 '19 at 13:48

Steve · Answer 1 · 2019-05-18T13:25:04.263

The error is probably caused by the presence of the parenthesys after the name of the table, but this is a simple fix to do.
Your real problem lies in the string concatenation for your values.
This is a no-no in the sql world because it could be the source of many parsing bugs (for example, the presence of a single quote in the values could break the syntax and DateTime.Now is converted to a string following rules the not always are understood by the sql parser engine).

But most important is the possibility of Sql Injection.
Here some links to start your discovery for this big security risk
How can I explain Sql Injection without technical jargon.
How does the SQL injection from the “Bobby Tables” XKCD comic work?

So the only fix is through a parameterized query.

string str = "insert into AdminAssistant 
              (Aname,Aphone,Amail,A_Address,A_City,A_Gender,A_Password,
               Aroll,MetaDescription,Media,RegisterDate,A_Status)
               values(@name,@phone,@mail,@address,@city,@gender,@pass
                      @roll,@descr,@media,@regdata,'Active')";
using(SqlConnection con = new SqlConnection(......))
using(SqlCommand cmd = new SqlCommand(str, con))
{
    con.Open();
    cmd.Parameters.Add("@name", SqlDbType.NVarChar).Value = TextBox7.Text; 
    ... other varchar parameters
    cmd.Parameters.Add("@regdataq", SqlDbType.DateTime).Value = DateTime.Now; 
    ... complete the parameters collection
    cmd.ExecuteNonQuery();
}

And, I have forget to talk about storing passwords in clear text into a database. This is another very important consideration for your security. Here another link with useful info

Best way to store password in database

And let's add to this the explanation about avoiding [addwithvalue](http://www.dbdelta.com/addwithvalue-is-evil/). — SMor, May 18 '19 at 13:39
@SMor you are right of course. And also how the OP is totally ignoring the fact that the second parameter in AddWithValue is the **Value** not the **Type.** But his continuos shift of the original question has tired me — Steve, May 18 '19 at 14:11

'Parameters supplied for object 'AdminAssistant' which is not a function. If the parameters are intended as a table hint, a WITH keyword is required.'

1 Answers1