error with unique identifier and sql data provider

[Guest]

i am trying to add a new row in a sql server 2005 that has an id of type
uniqueidentifier using this piece of code:

thecommand = new System.Data.SqlClient.SqlCommand();
thecommand.Connection=thecon;
thecommand.CommandText="INSERT INTO
users(ID,uname,username,password,description,persettings) VALUES(
@ID,'Administrator','Administrator',@pwd,'The administrator of the
timokatalogos database','')";

thecommand.Parameters.Add("@ID",System.Data.SqlDbType.UniqueIdentifier);
thecommand.Parameters.Add("@pwd",System.Data.SqlDbType.NVarChar);

thecommand.Parameters["@ID"].Value=System.Guid.NewGuid();
thecommand.Parameters["@pwd"].Value=pwd;
thecommand.ExecuteNonQuery();

and i get na error: "String or binary data would be truncated. the statement
has been terminated", then i try to create the row directly from SQL Server
Management Studio and i copy a GUID created from visual studio in the id
field and i get the same error that the messagebox tells that is generated
from the .net sql data provider. Anybody knows anything about it?

thanks in advance

 

[W.G. Ryan eMVP]

What is the type of the ID field in the DB?

 

[W.G. Ryan eMVP]

Dimtris:

This isn't the optimal way to do it, but here's an example that would work:

String sql = "INSERT INTO users (TestColumn) VALUES (@ID)";

SqlCommand cmd = new SqlCommand(sql, cn);

cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);

String GuidVal = Guid.NewGuid().ToString();

cmd.Parameters[0].Value = new SqlGuid(GuidVal);

cn.Open();

cmd.ExecuteNonQuery();

cn.Close();

 

[Guest]

Hi Dimitris,

Did you check if your description, password, and/or login values do not
exceed the length of the field in the DB ?

Is there a typo in your code sample ? or are you actually missing the value
of persettings in the parameters?
[thecommand.CommandText="INSERT INTO
users(ID,uname,username,password,description,persettings) VALUES(
@ID,'Administrator','Administrator',@pwd,'The administrator of the
timokatalogos database','')";]

Is this : database','')"; ... a typo too ?

 

[William \(Bill\) Vaughn]

I would make a small change... when you specify NVarChar or any
variable-length string datatype you need to provide the length as well...

thecommand.Parameters.Add("@pwd",System.Data.SqlDbType.NVarChar,50);

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

i am trying to add a new row in a sql server 2005 that has an id of type
uniqueidentifier using this piece of code:

thecommand = new System.Data.SqlClient.SqlCommand();
thecommand.Connection=thecon;
thecommand.CommandText="INSERT INTO
users(ID,uname,username,password,description,persettings) VALUES(
@ID,'Administrator','Administrator',@pwd,'The administrator of the
timokatalogos database','')";

thecommand.Parameters.Add("@ID",System.Data.SqlDbType.UniqueIdentifier);
thecommand.Parameters.Add("@pwd",System.Data.SqlDbType.NVarChar);

thecommand.Parameters["@ID"].Value=System.Guid.NewGuid();
thecommand.Parameters["@pwd"].Value=pwd;
thecommand.ExecuteNonQuery();

and i get na error: "String or binary data would be truncated. the
statement
has been terminated", then i try to create the row directly from SQL
Server
Management Studio and i copy a GUID created from visual studio in the id
field and i get the same error that the messagebox tells that is generated
from the .net sql data provider. Anybody knows anything about it?

thanks in advance

 

[Guest]

i will try this but the problem is that the exact same code on .net framework
1.1 and sql server 2000 works allready in a production environment. That's
why i ask if there is any known issue about this because i don't think that
the code isn't good because even in the MSDN says that sql uniqueidentifier
type maps to GUID. And the same error i get when i try to insert a row
directly from SQL server management studio.

Dimtris:

This isn't the optimal way to do it, but here's an example that would work:

String sql = "INSERT INTO users (TestColumn) VALUES (@ID)";

SqlCommand cmd = new SqlCommand(sql, cn);

cmd.Parameters.Add("@ID", SqlDbType.UniqueIdentifier);

String GuidVal = Guid.NewGuid().ToString();

cmd.Parameters[0].Value = new SqlGuid(GuidVal);

cn.Open();

cmd.ExecuteNonQuery();

cn.Close();


--
W.G. Ryan, MVP

www.tibasolutions.com | www.devbuzz.com | www.knowdotnet.com

i am trying to add a new row in a sql server 2005 that has an id of type
uniqueidentifier using this piece of code:

thecommand = new System.Data.SqlClient.SqlCommand();
thecommand.Connection=thecon;
thecommand.CommandText="INSERT INTO
users(ID,uname,username,password,description,persettings) VALUES(
@ID,'Administrator','Administrator',@pwd,'The administrator of the
timokatalogos database','')";

thecommand.Parameters.Add("@ID",System.Data.SqlDbType.UniqueIdentifier);
thecommand.Parameters.Add("@pwd",System.Data.SqlDbType.NVarChar);

thecommand.Parameters["@ID"].Value=System.Guid.NewGuid();
thecommand.Parameters["@pwd"].Value=pwd;
thecommand.ExecuteNonQuery();

and i get na error: "String or binary data would be truncated. the
statement
has been terminated", then i try to create the row directly from SQL
Server
Management Studio and i copy a GUID created from visual studio in the id
field and i get the same error that the messagebox tells that is generated
from the .net sql data provider. Anybody knows anything about it?

thanks in advance