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
| > | > |
| > | > |
| > | > |
| > | >
| > |
| > |
| > |
| >
|
|
|
