SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size
10,074
Solution 1
You can should add your parameter like this instead:
command.Parameters.Add(@"v1", SqlDbType.VarChar).Value = TextBox1.Text;
command.Parameters.Add(@"v2", SqlDbType.Int).Value = 0;
command.Parameters.Add(@"v3", SqlDbType.Int).Value = 0;
command.Parameters.Add(@"v4", SqlDbType.VarBinary).Value = FileUpload1.FileBytes;
command.Parameters.Add(@"v5", SqlDbType.VarChar).Value = TextBox3.Text;
command.Parameters.Add(@"v6", SqlDbType.VarChar).Value = TextBox4.Text;
command.Parameters.Add(@"v7", SqlDbType.VarChar).Value = TextBox5.Text;
command.ExecuteNonQuery();
conn.Close();
But personnaly I prefer to add my parameter like this:
cmd.Parameters.AddWithValue(@"v1", TextBox1.Text);
cmd.Parameters.AddWithValue(@"v2", 0);
cmd.Parameters.AddWithValue(@"v3", 0);
cmd.Parameters.AddWithValue(@"v4", FileUpload1.FileBytes);
cmd.Parameters.AddWithValue(@"v5", TextBox3.Text);
cmd.Parameters.AddWithValue(@"v6", TextBox4.Text);
cmd.Parameters.AddWithValue(@"v7", TextBox5.Text);
Solution 2
you should set max size of variable length parameters
replace all varchar (and varbinary) parameters:
command.Parameters.Add(new SqlParameter("v1", SqlDbType.VarChar));
with:
command.Parameters.Add(new SqlParameter("v1", SqlDbType.VarChar, 50));
Solution 3
Setting the parameter.Size
to the database's maximum size of the column corrected the problem for me when I ran into this.
For Each parameter As SqlClient.SqlParameter In command.Parameters
parameter.Value = row.Item(parameter.SourceColumn)
parameter.Size = Me.mData.Columns.Item(parameter.SourceColumn).MaxLength
Next
Comments
-
Aan almost 2 years
I tried to insert some values in SQL database by the code:
var connstring = ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString; ; var conn = new SqlConnection(connstring); conn.Open(); SqlCommand command = new SqlCommand("INSERT INTO [Trainer] (Name, ID, [Trainee Counter], image, [Mobile NO], Email, Password) VALUES(:v1, :v2,:v3,:v4,:v5,:v6,:v7);", conn); command.Parameters.Add(new SqlParameter("v1", SqlDbType.VarChar)); command.Parameters.Add(new SqlParameter("v2", SqlDbType.Int)); command.Parameters.Add(new SqlParameter("v3", SqlDbType.Int)); command.Parameters.Add(new SqlParameter("v4", SqlDbType.VarBinary)); command.Parameters.Add(new SqlParameter("v5", SqlDbType.VarChar)); command.Parameters.Add(new SqlParameter("v6", SqlDbType.VarChar)); command.Parameters.Add(new SqlParameter("v7", SqlDbType.VarChar)); command.Prepare(); command.Parameters[0].Value = TextBox1.Text; command.Parameters[1].Value = 0; command.Parameters[2].Value = 0; command.Parameters[3].Value = FileUpload1.FileBytes; command.Parameters[4].Value = TextBox3.Text; command.Parameters[5].Value = TextBox4.Text; command.Parameters[6].Value = TextBox5.Text; command.ExecuteNonQuery(); conn.Close();
But I always get this exception:
SqlCommand.Prepare method requires all variable length parameters to have an explicitly set non-zero Size.
Actually I have ported this code from one of my C++/CLI project with PostgreSQL, and it works fine on that project.
-
Aan over 10 yearsSame issue. no benefit.
-
Brian over 10 yearsDoesn't using
.AddWithValue()
leave it up to ADO to determine the datatype? Is that the best approach in this case? -
meda over 10 yearsyes, I prefer it because if you have the wrong parameter type you will be screwed when you execute your command, second approach saves you from trouble
-
Aan over 10 years@meda What is wrong in my way? Why it works with C++/CLI but not in C#?
-
Aan over 10 years@meda So what mistake I did?
-
meda over 10 years@Aan to be honest I cant give you a real answer few things can go wrong when you do things like that manually(type mismatch in your app or database field), also you should
prepare()
only after you set your command and parameter, in your post you do it before you set your parameter