error with unique identifier and sql data provider

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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();
 
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 ?
 
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);
Click to expand...


--
____________________________________
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.
__________________________________


Dimitris Iliopoulos said:
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
Click to expand...
 
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.

W.G. Ryan eMVP said:
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
Dimitris Iliopoulos said:
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
Click to expand...
Click to expand...
 
Back
Top