By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,225 Members | 2,223 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,225 IT Pros & Developers. It's quick & easy.

Save a DWORD to SQL

P: n/a
I need to save a DWORD to the sql server, the below posts an error, any
suggestions on what I am doing wrong. I have the column in the sql server
defined as an int since unsigned int is not valid. Also trying to avoid
setting it to a bigint in the server. Casting an int to an uint use to work
in C++.
System.Data.SqlClient.SqlConnection cn = new
System.Data.SqlClient.SqlConnection(...);
cn.Open()
System.Data.SqlClient.SqlCommand cmd = new
System.Data.SqlClient.SqlCommand();
cmd.Connection = cn;

cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
cmd.Parameters["@PS"].Value = UInt32.MaxValue;
cmd.CommandText = "UPDATE Directories SET State = @PS WHERE AreaCode = ''
AND Number = '104'";

cmd.ExecuteNonQuery(); // error here !!!!
// An unhandled exception of
type 'System.OverflowException' occurred in system.data.dll
// Additional information: Value
was either too large or too small for an Int32.
cmd.Parameters.RemoveAt("@PS");
cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode = '' AND
Number = '104'";
System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
if(dr.Read())
{
uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here if I
force the value in
}
dr.Close();
cn.Close();

This works sort of:

UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND Number =
'104'
SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
Number = '104'

So I know the SQL server is storing the data correctly, the problem is .NET
can not convert it. Also if I look at the debuging code the system shows
this on the return value:

- dr["State"] {0xffffffff} System.Int32
+ System.ValueType {System.Int32} System.ValueType
m_value 0xffffffff int
MaxValue 0x7fffffff int
MinValue 0x80000000 int
Thanks,
John
Nov 15 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a

Hi John,

Just as the error message said, "Value was either too large or too small
for an Int32".
The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is 2147483647,
so the value your set is too large.
You should set a value in the range of the Int32

Hope this helps,

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| Subject: Save a DWORD to SQL
| Date: Fri, 17 Oct 2003 15:37:37 -0400
| Lines: 60
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:192183
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| I need to save a DWORD to the sql server, the below posts an error, any
| suggestions on what I am doing wrong. I have the column in the sql server
| defined as an int since unsigned int is not valid. Also trying to avoid
| setting it to a bigint in the server. Casting an int to an uint use to
work
| in C++.
|
|
| System.Data.SqlClient.SqlConnection cn = new
| System.Data.SqlClient.SqlConnection(...);
| cn.Open()
| System.Data.SqlClient.SqlCommand cmd = new
| System.Data.SqlClient.SqlCommand();
| cmd.Connection = cn;
|
| cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| cmd.CommandText = "UPDATE Directories SET State = @PS WHERE AreaCode =
''
| AND Number = '104'";
|
| cmd.ExecuteNonQuery(); // error here !!!!
| // An unhandled exception of
| type 'System.OverflowException' occurred in system.data.dll
| // Additional information:
Value
| was either too large or too small for an Int32.
|
|
| cmd.Parameters.RemoveAt("@PS");
| cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode = ''
AND
| Number = '104'";
| System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| if(dr.Read())
| {
| uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here if I
| force the value in
| }
| dr.Close();
| cn.Close();
|
| This works sort of:
|
| UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND Number =
| '104'
| SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
| Number = '104'
|
| So I know the SQL server is storing the data correctly, the problem is
.NET
| can not convert it. Also if I look at the debuging code the system shows
| this on the return value:
|
| - dr["State"] {0xffffffff} System.Int32
| + System.ValueType {System.Int32} System.ValueType
| m_value 0xffffffff int
| MaxValue 0x7fffffff int
| MinValue 0x80000000 int
|
|
| Thanks,
| John
|
|
|

Nov 15 '05 #2

P: n/a
No it does not help at all! I understood the error, my question was how to
get a DWORD into SQL? SQL does not support unsigned int but this was never
a problem until I started using .NET. In the past I have just forced the
data to int and then back again. Since both an int and a unsigned int are
the same size this should not be a problem. The problem is the .NET runtime
is doing an error check on the value and will not allow me to convert it.

Regards,
John
""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:r0**************@cpmsftngxa06.phx.gbl...

Hi John,

Just as the error message said, "Value was either too large or too small
for an Int32".
The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is 2147483647,
so the value your set is too large.
You should set a value in the range of the Int32

Hope this helps,

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| Subject: Save a DWORD to SQL
| Date: Fri, 17 Oct 2003 15:37:37 -0400
| Lines: 60
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:192183 | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| I need to save a DWORD to the sql server, the below posts an error, any
| suggestions on what I am doing wrong. I have the column in the sql server | defined as an int since unsigned int is not valid. Also trying to avoid
| setting it to a bigint in the server. Casting an int to an uint use to
work
| in C++.
|
|
| System.Data.SqlClient.SqlConnection cn = new
| System.Data.SqlClient.SqlConnection(...);
| cn.Open()
| System.Data.SqlClient.SqlCommand cmd = new
| System.Data.SqlClient.SqlCommand();
| cmd.Connection = cn;
|
| cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| cmd.CommandText = "UPDATE Directories SET State = @PS WHERE AreaCode = ''
| AND Number = '104'";
|
| cmd.ExecuteNonQuery(); // error here !!!!
| // An unhandled exception of
| type 'System.OverflowException' occurred in system.data.dll
| // Additional information:
Value
| was either too large or too small for an Int32.
|
|
| cmd.Parameters.RemoveAt("@PS");
| cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode = ''
AND
| Number = '104'";
| System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| if(dr.Read())
| {
| uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here if I | force the value in
| }
| dr.Close();
| cn.Close();
|
| This works sort of:
|
| UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND Number = | '104'
| SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
| Number = '104'
|
| So I know the SQL server is storing the data correctly, the problem is
NET
| can not convert it. Also if I look at the debuging code the system shows | this on the return value:
|
| - dr["State"] {0xffffffff} System.Int32
| + System.ValueType {System.Int32} System.ValueType
| m_value 0xffffffff int
| MaxValue 0x7fffffff int
| MinValue 0x80000000 int
|
|
| Thanks,
| John
|
|
|

Nov 15 '05 #3

P: n/a
John J. Hughes II wrote:
No it does not help at all! I understood the error, my question was how to
get a DWORD into SQL? SQL does not support unsigned int but this was never
a problem until I started using .NET. In the past I have just forced the
data to int and then back again. Since both an int and a unsigned int are
the same size this should not be a problem. The problem is the .NET runtime
is doing an error check on the value and will not allow me to convert it.

Regards,
John
""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:r0**************@cpmsftngxa06.phx.gbl...
Hi John,

Just as the error message said, "Value was either too large or too small
for an Int32".
The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is 2147483647,
so the value your set is too large.
You should set a value in the range of the Int32

Hope this helps,

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| Subject: Save a DWORD to SQL
| Date: Fri, 17 Oct 2003 15:37:37 -0400
| Lines: 60
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl


microsoft.public.dotnet.languages.csharp:192183
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| I need to save a DWORD to the sql server, the below posts an error, any
| suggestions on what I am doing wrong. I have the column in the sql


server
| defined as an int since unsigned int is not valid. Also trying to avoid
| setting it to a bigint in the server. Casting an int to an uint use to
work
| in C++.
|
|
| System.Data.SqlClient.SqlConnection cn = new
| System.Data.SqlClient.SqlConnection(...);
| cn.Open()
| System.Data.SqlClient.SqlCommand cmd = new
| System.Data.SqlClient.SqlCommand();
| cmd.Connection = cn;
|
| cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| cmd.CommandText = "UPDATE Directories SET State = @PS WHERE AreaCode


=
''
| AND Number = '104'";
|
| cmd.ExecuteNonQuery(); // error here !!!!
| // An unhandled exception of
| type 'System.OverflowException' occurred in system.data.dll
| // Additional information:
Value
| was either too large or too small for an Int32.
|
|
| cmd.Parameters.RemoveAt("@PS");
| cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode = ''
AND
| Number = '104'";
| System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| if(dr.Read())
| {
| uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here if


I
| force the value in
| }
| dr.Close();
| cn.Close();
|
| This works sort of:
|
| UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND Number


=
| '104'
| SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
| Number = '104'
|
| So I know the SQL server is storing the data correctly, the problem is
NET
| can not convert it. Also if I look at the debuging code the system


shows
| this on the return value:
|
| - dr["State"] {0xffffffff} System.Int32
| + System.ValueType {System.Int32} System.ValueType
| m_value 0xffffffff int
| MaxValue 0x7fffffff int
| MinValue 0x80000000 int
|
|
| Thanks,
| John
|
|
|



Can you try either a SqlDbType.BigInt or SqlDbType.Variant?

--
Girish Bharadwaj

Nov 15 '05 #4

P: n/a

Hi John,

Thanks for your feedback.
In C++, the code is unmanged, you can do the memory operation yourself, but
it is easy for crash and overflow.
In .Net, all codes are managed, and it will check the type, so you can not
use the Int type's sign bit as UInt's data bit.
This is the feature of .Net Framework.

I think you should use the bigint in SqlServer. As you said, you want to
avoid use bigint, what is your concern?
Does this affect your design?

Thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| References: <eP**************@TK2MSFTNGP09.phx.gbl>
<r0**************@cpmsftngxa06.phx.gbl>
| Subject: Re: Save a DWORD to SQL
| Date: Mon, 20 Oct 2003 09:23:05 -0400
| Lines: 122
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#q**************@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-105.asm.bellsouth.net 66.20.184.105
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:192591
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| No it does not help at all! I understood the error, my question was how
to
| get a DWORD into SQL? SQL does not support unsigned int but this was
never
| a problem until I started using .NET. In the past I have just forced the
| data to int and then back again. Since both an int and a unsigned int are
| the same size this should not be a problem. The problem is the .NET
runtime
| is doing an error check on the value and will not allow me to convert it.
|
| Regards,
| John
|
|
| ""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
| news:r0**************@cpmsftngxa06.phx.gbl...
| >
| > Hi John,
| >
| > Just as the error message said, "Value was either too large or too small
| > for an Int32".
| > The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is
2147483647,
| > so the value your set is too large.
| > You should set a value in the range of the Int32
| >
| > Hope this helps,
| >
| > Best regards,
| > Jeffrey Tan
| > Microsoft Online Partner Support
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| >
| > --------------------
| > | From: "John J. Hughes II" <no@invalid.com>
| > | Subject: Save a DWORD to SQL
| > | Date: Fri, 17 Oct 2003 15:37:37 -0400
| > | Lines: 60
| > | Organization: Function International
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.dotnet.languages.csharp
| > | NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.languages.csharp:192183
| > | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
| > |
| > | I need to save a DWORD to the sql server, the below posts an error,
any
| > | suggestions on what I am doing wrong. I have the column in the sql
| server
| > | defined as an int since unsigned int is not valid. Also trying to
avoid
| > | setting it to a bigint in the server. Casting an int to an uint use
to
| > work
| > | in C++.
| > |
| > |
| > | System.Data.SqlClient.SqlConnection cn = new
| > | System.Data.SqlClient.SqlConnection(...);
| > | cn.Open()
| > | System.Data.SqlClient.SqlCommand cmd = new
| > | System.Data.SqlClient.SqlCommand();
| > | cmd.Connection = cn;
| > |
| > | cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| > | cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| > | cmd.CommandText = "UPDATE Directories SET State = @PS WHERE
AreaCode
| =
| > ''
| > | AND Number = '104'";
| > |
| > | cmd.ExecuteNonQuery(); // error here !!!!
| > | // An unhandled exception
of
| > | type 'System.OverflowException' occurred in system.data.dll
| > | // Additional information:
| > Value
| > | was either too large or too small for an Int32.
| > |
| > |
| > | cmd.Parameters.RemoveAt("@PS");
| > | cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode =
''
| > AND
| > | Number = '104'";
| > | System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| > | if(dr.Read())
| > | {
| > | uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here
if
| I
| > | force the value in
| > | }
| > | dr.Close();
| > | cn.Close();
| > |
| > | This works sort of:
| > |
| > | UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND
Number
| =
| > | '104'
| > | SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
| > | Number = '104'
| > |
| > | So I know the SQL server is storing the data correctly, the problem is
| > NET
| > | can not convert it. Also if I look at the debuging code the system
| shows
| > | this on the return value:
| > |
| > | - dr["State"] {0xffffffff} System.Int32
| > | + System.ValueType {System.Int32} System.ValueType
| > | m_value 0xffffffff int
| > | MaxValue 0x7fffffff int
| > | MinValue 0x80000000 int
| > |
| > |
| > | Thanks,
| > | John
| > |
| > |
| > |
| >
|
|
|

Nov 15 '05 #5

P: n/a
Thanks of your response. I am trying to avoid big int mostly because I have
some rather large systems installed and don't want to run a convert on the
records. The second reason would have to deal with trying to avoid the
rather large system becoming even larger. int 4 bytes vis bit int 8
bytes... I have tables with more then a few so the records would almost
double in size. And lastly I receive the data as a DWORD and send it that
way which is not really a problem but it adds to the mess.

Now I had though about casting it to a big int but the value still comes in
a -1 which is not correct or useful.

I have the following code which works but as you can imagine it does add
some overhead and does not work with default .NET functions.

static public Int32 ToInt32(UInt32 v)
{
byte[] tmp = new byte[4];

tmp[0] = (byte)(v & 0x000000ff);
tmp[1] = (byte)(v & 0x0000ff00 >> 8);
tmp[2] = (byte)(v & 0x00ff0000 >> 16);
tmp[3] = (byte)(v & 0xff000000 >> 24);

return BitConverter.ToInt32(tmp, 0);
}

static public UInt32 ToUInt32(Int32 v)
{
byte[] tmp = new byte[4];

tmp[0] = (byte)(v & 0x000000ff);
tmp[1] = (byte)(v & 0x0000ff00 >> 8);
tmp[2] = (byte)(v & 0x00ff0000 >> 16);
tmp[3] = (byte)(v & 0xff000000 >> 24);

return BitConverter.ToUInt32(tmp, 0);
}
Regards,
John
""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:%2****************@cpmsftngxa06.phx.gbl...

Hi John,

Thanks for your feedback.
In C++, the code is unmanged, you can do the memory operation yourself, but it is easy for crash and overflow.
In .Net, all codes are managed, and it will check the type, so you can not
use the Int type's sign bit as UInt's data bit.
This is the feature of .Net Framework.

I think you should use the bigint in SqlServer. As you said, you want to
avoid use bigint, what is your concern?
Does this affect your design?

Thanks

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| References: <eP**************@TK2MSFTNGP09.phx.gbl>
<r0**************@cpmsftngxa06.phx.gbl>
| Subject: Re: Save a DWORD to SQL
| Date: Mon, 20 Oct 2003 09:23:05 -0400
| Lines: 122
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <#q**************@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-105.asm.bellsouth.net 66.20.184.105
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:192591 | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| No it does not help at all! I understood the error, my question was how
to
| get a DWORD into SQL? SQL does not support unsigned int but this was
never
| a problem until I started using .NET. In the past I have just forced the | data to int and then back again. Since both an int and a unsigned int are | the same size this should not be a problem. The problem is the .NET
runtime
| is doing an error check on the value and will not allow me to convert it. |
| Regards,
| John
|
|
| ""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
| news:r0**************@cpmsftngxa06.phx.gbl...
| >
| > Hi John,
| >
| > Just as the error message said, "Value was either too large or too small | > for an Int32".
| > The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is
2147483647,
| > so the value your set is too large.
| > You should set a value in the range of the Int32
| >
| > Hope this helps,
| >
| > Best regards,
| > Jeffrey Tan
| > Microsoft Online Partner Support
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| >
| > --------------------
| > | From: "John J. Hughes II" <no@invalid.com>
| > | Subject: Save a DWORD to SQL
| > | Date: Fri, 17 Oct 2003 15:37:37 -0400
| > | Lines: 60
| > | Organization: Function International
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| > | Newsgroups: microsoft.public.dotnet.languages.csharp
| > | NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.languages.csharp:192183
| > | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
| > |
| > | I need to save a DWORD to the sql server, the below posts an error,
any
| > | suggestions on what I am doing wrong. I have the column in the sql
| server
| > | defined as an int since unsigned int is not valid. Also trying to
avoid
| > | setting it to a bigint in the server. Casting an int to an uint use
to
| > work
| > | in C++.
| > |
| > |
| > | System.Data.SqlClient.SqlConnection cn = new
| > | System.Data.SqlClient.SqlConnection(...);
| > | cn.Open()
| > | System.Data.SqlClient.SqlCommand cmd = new
| > | System.Data.SqlClient.SqlCommand();
| > | cmd.Connection = cn;
| > |
| > | cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| > | cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| > | cmd.CommandText = "UPDATE Directories SET State = @PS WHERE
AreaCode
| =
| > ''
| > | AND Number = '104'";
| > |
| > | cmd.ExecuteNonQuery(); // error here !!!!
| > | // An unhandled exception of
| > | type 'System.OverflowException' occurred in system.data.dll
| > | // Additional information: | > Value
| > | was either too large or too small for an Int32.
| > |
| > |
| > | cmd.Parameters.RemoveAt("@PS");
| > | cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode =
''
| > AND
| > | Number = '104'";
| > | System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| > | if(dr.Read())
| > | {
| > | uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here if
| I
| > | force the value in
| > | }
| > | dr.Close();
| > | cn.Close();
| > |
| > | This works sort of:
| > |
| > | UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND
Number
| =
| > | '104'
| > | SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND | > | Number = '104'
| > |
| > | So I know the SQL server is storing the data correctly, the problem is | > NET
| > | can not convert it. Also if I look at the debuging code the system
| shows
| > | this on the return value:
| > |
| > | - dr["State"] {0xffffffff} System.Int32
| > | + System.ValueType {System.Int32} System.ValueType
| > | m_value 0xffffffff int
| > | MaxValue 0x7fffffff int
| > | MinValue 0x80000000 int
| > |
| > |
| > | Thanks,
| > | John
| > |
| > |
| > |
| >
|
|
|

Nov 15 '05 #6

P: n/a

"Girish Bharadwaj" <girishb@nowhere> wrote in message
news:%2****************@TK2MSFTNGP11.phx.gbl...
John J. Hughes II wrote:
No it does not help at all! I understood the error, my question was how to get a DWORD into SQL? SQL does not support unsigned int but this was never a problem until I started using .NET. In the past I have just forced the data to int and then back again. Since both an int and a unsigned int are the same size this should not be a problem. The problem is the .NET runtime is doing an error check on the value and will not allow me to convert it.
Regards,
John
""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
news:r0**************@cpmsftngxa06.phx.gbl...
Hi John,

Just as the error message said, "Value was either too large or too small
for an Int32".
The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is 2147483647,so the value your set is too large.
You should set a value in the range of the Int32

Hope this helps,

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
--------------------
| From: "John J. Hughes II" <no@invalid.com>
| Subject: Save a DWORD to SQL
| Date: Fri, 17 Oct 2003 15:37:37 -0400
| Lines: 60
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| Xref: cpmsftngxa06.phx.gbl


microsoft.public.dotnet.languages.csharp:192183
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| I need to save a DWORD to the sql server, the below posts an error, any| suggestions on what I am doing wrong. I have the column in the sql


server
| defined as an int since unsigned int is not valid. Also trying to avoid| setting it to a bigint in the server. Casting an int to an uint use towork
| in C++.
|
|
| System.Data.SqlClient.SqlConnection cn = new
| System.Data.SqlClient.SqlConnection(...);
| cn.Open()
| System.Data.SqlClient.SqlCommand cmd = new
| System.Data.SqlClient.SqlCommand();
| cmd.Connection = cn;
|
| cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| cmd.CommandText = "UPDATE Directories SET State = @PS WHERE AreaCode

=
''
| AND Number = '104'";
|
| cmd.ExecuteNonQuery(); // error here !!!!
| // An unhandled exception
of| type 'System.OverflowException' occurred in system.data.dll
| // Additional information:
Value
| was either too large or too small for an Int32.
|
|
| cmd.Parameters.RemoveAt("@PS");
| cmd.CommandText = "SELECT State FROM Directories WHERE AreaCode = ''AND
| Number = '104'";
| System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| if(dr.Read())
| {
| uint val = Convert.ToUInt32(dr["PhoneState"]); // same error here

if
I
| force the value in
| }
| dr.Close();
| cn.Close();
|
| This works sort of:
|
| UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND
Number
=
| '104'
| SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = '' AND
| Number = '104'
|
| So I know the SQL server is storing the data correctly, the problem is
NET
| can not convert it. Also if I look at the debuging code the system


shows
| this on the return value:
|
| - dr["State"] {0xffffffff} System.Int32
| + System.ValueType {System.Int32} System.ValueType
| m_value 0xffffffff int
| MaxValue 0x7fffffff int
| MinValue 0x80000000 int
|
|
| Thanks,
| John
|
|
|



Can you try either a SqlDbType.BigInt or SqlDbType.Variant?

--
Girish Bharadwaj


I was trying not to convert existing databases or make them larger by using
big int... I might look at variant, since I have never used it before I am
not sure if it's an option.

Thanks,
John
Nov 15 '05 #7

P: n/a

Hi John,

I think your workaround of converting the UInt32 to Int32 programmatic is a
good solution, but it will make your application complex.
While using the bigint, you program logic is simple, but the database is
bigger.
So I think you should count the cost between these 2 ways.

Thanks for your feedback.

Best regards,
Jeffrey Tan
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

--------------------
| From: "John J. Hughes II" <no@invalid.com>
| References: <eP**************@TK2MSFTNGP09.phx.gbl>
<r0**************@cpmsftngxa06.phx.gbl>
<#q**************@TK2MSFTNGP10.phx.gbl>
<#C**************@cpmsftngxa06.phx.gbl>
| Subject: Re: Save a DWORD to SQL
| Date: Tue, 21 Oct 2003 11:54:13 -0400
| Lines: 236
| Organization: Function International
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| Message-ID: <OW**************@TK2MSFTNGP10.phx.gbl>
| Newsgroups: microsoft.public.dotnet.languages.csharp
| NNTP-Posting-Host: adsl-20-177-139.asm.bellsouth.net 66.20.177.139
| Path:
cpmsftngxa06.phx.gbl!cpmsftngxa10.phx.gbl!TK2MSFTN GXA05.phx.gbl!TK2MSFTNGP08
.phx.gbl!TK2MSFTNGP10.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.dotnet.languages.csharp:192911
| X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
|
| Thanks of your response. I am trying to avoid big int mostly because I
have
| some rather large systems installed and don't want to run a convert on the
| records. The second reason would have to deal with trying to avoid the
| rather large system becoming even larger. int 4 bytes vis bit int 8
| bytes... I have tables with more then a few so the records would almost
| double in size. And lastly I receive the data as a DWORD and send it that
| way which is not really a problem but it adds to the mess.
|
| Now I had though about casting it to a big int but the value still comes
in
| a -1 which is not correct or useful.
|
| I have the following code which works but as you can imagine it does add
| some overhead and does not work with default .NET functions.
|
| static public Int32 ToInt32(UInt32 v)
| {
| byte[] tmp = new byte[4];
|
| tmp[0] = (byte)(v & 0x000000ff);
| tmp[1] = (byte)(v & 0x0000ff00 >> 8);
| tmp[2] = (byte)(v & 0x00ff0000 >> 16);
| tmp[3] = (byte)(v & 0xff000000 >> 24);
|
| return BitConverter.ToInt32(tmp, 0);
| }
|
| static public UInt32 ToUInt32(Int32 v)
| {
| byte[] tmp = new byte[4];
|
| tmp[0] = (byte)(v & 0x000000ff);
| tmp[1] = (byte)(v & 0x0000ff00 >> 8);
| tmp[2] = (byte)(v & 0x00ff0000 >> 16);
| tmp[3] = (byte)(v & 0xff000000 >> 24);
|
| return BitConverter.ToUInt32(tmp, 0);
| }
|
|
| Regards,
| John
|
|
| ""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
| news:%2****************@cpmsftngxa06.phx.gbl...
| >
| > Hi John,
| >
| > Thanks for your feedback.
| > In C++, the code is unmanged, you can do the memory operation yourself,
| but
| > it is easy for crash and overflow.
| > In .Net, all codes are managed, and it will check the type, so you can
not
| > use the Int type's sign bit as UInt's data bit.
| > This is the feature of .Net Framework.
| >
| > I think you should use the bigint in SqlServer. As you said, you want to
| > avoid use bigint, what is your concern?
| > Does this affect your design?
| >
| > Thanks
| >
| > Best regards,
| > Jeffrey Tan
| > Microsoft Online Partner Support
| > Get Secure! - www.microsoft.com/security
| > This posting is provided "as is" with no warranties and confers no
rights.
| >
| > --------------------
| > | From: "John J. Hughes II" <no@invalid.com>
| > | References: <eP**************@TK2MSFTNGP09.phx.gbl>
| > <r0**************@cpmsftngxa06.phx.gbl>
| > | Subject: Re: Save a DWORD to SQL
| > | Date: Mon, 20 Oct 2003 09:23:05 -0400
| > | Lines: 122
| > | Organization: Function International
| > | X-Priority: 3
| > | X-MSMail-Priority: Normal
| > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | Message-ID: <#q**************@TK2MSFTNGP10.phx.gbl>
| > | Newsgroups: microsoft.public.dotnet.languages.csharp
| > | NNTP-Posting-Host: adsl-20-184-105.asm.bellsouth.net 66.20.184.105
| > | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP10.phx.gbl
| > | Xref: cpmsftngxa06.phx.gbl
| microsoft.public.dotnet.languages.csharp:192591
| > | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
| > |
| > | No it does not help at all! I understood the error, my question was
how
| > to
| > | get a DWORD into SQL? SQL does not support unsigned int but this was
| > never
| > | a problem until I started using .NET. In the past I have just forced
| the
| > | data to int and then back again. Since both an int and a unsigned int
| are
| > | the same size this should not be a problem. The problem is the .NET
| > runtime
| > | is doing an error check on the value and will not allow me to convert
| it.
| > |
| > | Regards,
| > | John
| > |
| > |
| > | ""Jeffrey Tan[MSFT]"" <v-*****@online.microsoft.com> wrote in message
| > | news:r0**************@cpmsftngxa06.phx.gbl...
| > | >
| > | > Hi John,
| > | >
| > | > Just as the error message said, "Value was either too large or too
| small
| > | > for an Int32".
| > | > The UInt32.MaxValue is 4294967295, while the Int32.MaxValue is
| > 2147483647,
| > | > so the value your set is too large.
| > | > You should set a value in the range of the Int32
| > | >
| > | > Hope this helps,
| > | >
| > | > Best regards,
| > | > Jeffrey Tan
| > | > Microsoft Online Partner Support
| > | > Get Secure! - www.microsoft.com/security
| > | > This posting is provided "as is" with no warranties and confers no
| > rights.
| > | >
| > | > --------------------
| > | > | From: "John J. Hughes II" <no@invalid.com>
| > | > | Subject: Save a DWORD to SQL
| > | > | Date: Fri, 17 Oct 2003 15:37:37 -0400
| > | > | Lines: 60
| > | > | Organization: Function International
| > | > | X-Priority: 3
| > | > | X-MSMail-Priority: Normal
| > | > | X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
| > | > | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
| > | > | Message-ID: <eP**************@TK2MSFTNGP09.phx.gbl>
| > | > | Newsgroups: microsoft.public.dotnet.languages.csharp
| > | > | NNTP-Posting-Host: adsl-20-184-22.asm.bellsouth.net 66.20.184.22
| > | > | Path:
cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP09.phx.gbl
| > | > | Xref: cpmsftngxa06.phx.gbl
| > | microsoft.public.dotnet.languages.csharp:192183
| > | > | X-Tomcat-NG: microsoft.public.dotnet.languages.csharp
| > | > |
| > | > | I need to save a DWORD to the sql server, the below posts an
error,
| > any
| > | > | suggestions on what I am doing wrong. I have the column in the
sql
| > | server
| > | > | defined as an int since unsigned int is not valid. Also trying to
| > avoid
| > | > | setting it to a bigint in the server. Casting an int to an uint
use
| > to
| > | > work
| > | > | in C++.
| > | > |
| > | > |
| > | > | System.Data.SqlClient.SqlConnection cn = new
| > | > | System.Data.SqlClient.SqlConnection(...);
| > | > | cn.Open()
| > | > | System.Data.SqlClient.SqlCommand cmd = new
| > | > | System.Data.SqlClient.SqlCommand();
| > | > | cmd.Connection = cn;
| > | > |
| > | > | cmd.Parameters.Add("@PS", System.Data.SqlDbType.Int);
| > | > | cmd.Parameters["@PS"].Value = UInt32.MaxValue;
| > | > | cmd.CommandText = "UPDATE Directories SET State = @PS WHERE
| > AreaCode
| > | =
| > | > ''
| > | > | AND Number = '104'";
| > | > |
| > | > | cmd.ExecuteNonQuery(); // error here !!!!
| > | > | // An unhandled
| exception
| > of
| > | > | type 'System.OverflowException' occurred in system.data.dll
| > | > | // Additional
| information:
| > | > Value
| > | > | was either too large or too small for an Int32.
| > | > |
| > | > |
| > | > | cmd.Parameters.RemoveAt("@PS");
| > | > | cmd.CommandText = "SELECT State FROM Directories WHERE
AreaCode =
| > ''
| > | > AND
| > | > | Number = '104'";
| > | > | System.Data.SqlClient.SqlDataReader dr = cmd.ExecuteReader();
| > | > | if(dr.Read())
| > | > | {
| > | > | uint val = Convert.ToUInt32(dr["PhoneState"]); // same error
| here
| > if
| > | I
| > | > | force the value in
| > | > | }
| > | > | dr.Close();
| > | > | cn.Close();
| > | > |
| > | > | This works sort of:
| > | > |
| > | > | UPDATE Directories SET State = 0xffffffff WHERE AreaCode = '' AND
| > Number
| > | =
| > | > | '104'
| > | > | SELECT CONVERT(Binary, State) FROM Directories WHERE AreaCode = ''
| AND
| > | > | Number = '104'
| > | > |
| > | > | So I know the SQL server is storing the data correctly, the
problem
| is
| > | > NET
| > | > | can not convert it. Also if I look at the debuging code the
system
| > | shows
| > | > | this on the return value:
| > | > |
| > | > | - dr["State"] {0xffffffff} System.Int32
| > | > | + System.ValueType {System.Int32} System.ValueType
| > | > | m_value 0xffffffff int
| > | > | MaxValue 0x7fffffff int
| > | > | MinValue 0x80000000 int
| > | > |
| > | > |
| > | > | Thanks,
| > | > | John
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|

Nov 15 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.