|
I get this error:
Description Line 1: Incorrect syntax near '='.
Explantation Syntax error in reserved words!
SQL Statement
SELECT m.[Firstname], m.[Lastname], m.[Address], m.[PostalCode], p.[PostalOffice], m.[Email], m.[MemberImage], m.[categoryid]
FROM [Member] m, [Post] p WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=
I use this code
sqlStatement = ""<br />
sqlStatement = sqlStatement & "SELECT m.[Firstname], m.[Lastname], m.[Address], m.[PostalCode], p.[PostalOffice], m.[Email], m.[MemberImage], m.[categoryid] "<br />
sqlStatement = sqlStatement & "FROM [Member] m, [Post] p "<br />
sqlStatement = sqlStatement & "WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=" & intMemberId
Have someone poposal whats it's wrong 
Thanks for any help
Erik Gjertsen
|
|
|
|
|
|
intMemberId is not a proper SQL variable.
Try @intMemberID.
See Colin's comment above - I have to second that (LOUDLY).
|
|
|
|
|
Change the intMemberId with number,
Erik Gjertsen wrote:
sqlStatement = sqlStatement & "WHERE m.[PostalCode]=p.[PostalCode] AND m.[MemberId]=2"
From there you will probably find out what went wrong.
<italic>Work hard, Work effectively and a bit of luck is the key to success.
|
|
|
|
|
As part of my "newbee" problems I discovered I can't compile visual basic as cut and paste from examples:
The example:
Dim selectCMD As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
has be modified as such in order to compile:
Dim selectCMD As System.Data.OleDb.OleDbCommand = New System.Data.OleDb.OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
Am I missing some header file or something else?
Thanks,
Ilan
|
|
|
|
|
|
I have a number of SQL queries to execute on an Orcale database, all of which are based on a common subset of a large table. I have the SQL to create a temp table then use the temp table to drive the first of these queries, and this works through Oracle SQL worksheet.
I'm trying to get this to work through ADO.NET, by sending a series of SQL commands via OleDbCommand.
This is the SQL:
DROP TABLE ODSWIP;<br />
<br />
COMMIT WORK;<br />
<br />
CREATE GLOBAL TEMPORARY TABLE ODSWIP<br />
ON COMMIT PRESERVE ROWS<br />
AS SELECT <br />
<br />
RMAM.RMA_NBR AS RMAM_RMA_NBR, <br />
RMAD.LN_NBR AS RMAD_LN_NBR, <br />
SORD.ORD_NBR AS SORM_ORD_NBR, <br />
SORD.LN_NBR AS SORD_LN_NBR, <br />
RMAD.OUT_PART_NBR, <br />
SORD.PRODUCT_ID, <br />
RMAD.OUT_TRACE_ID, <br />
RMAD.DT_RECEIVED, <br />
SORD.DT_CREATED, <br />
ZORD.ORD_NBR AS ZORD_ORD_NBR, <br />
ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR<br />
FROM RMAM, RMAD, SORD, ZORD<br />
WHERE RMAM.RMA_NBR=RMAD.RMA_NBR<br />
AND RMAD.RMA_NBR=SORD.ORD_NBR<br />
AND SORD.PRODUCT_ID=ZORD.PART_NBR<br />
AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR<br />
AND SORD.REC_CD='DT'<br />
AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' <br />
AND TRIM(SORD.CNCL_ITM_RSN_CD) Is Null<br />
AND RMAD.DT_RECEIVED Is Not Null <br />
AND TRIM(SORD.INVOICE_ID) is null <br />
AND ZORD.PART_NBR Not Like '*-R *'<br />
AND ZORD.SERV_ORDER='Y';<br />
<br />
COMMIT WORK;<br />
<br />
SELECT RMAM.RMA_NBR, SORD.LN_NBR, TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR), <br />
SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE, ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, <br />
OORD.ACTUAL_OPEN_DATE<br />
FROM ODSWIP, RMAM, RMAD, SORD, ZORD, OORD<br />
WHERE ODSWIP.RMAM_RMA_NBR = RMAM.RMA_NBR<br />
AND ODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR AND ODSWIP.RMAD_LN_NBR = RMAD.LN_NBR<br />
AND ODSWIP.SORM_ORD_NBR = SORD.ORD_NBR AND ODSWIP.SORD_LN_NBR = SORD.LN_NBR<br />
AND ODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR<br />
AND ZORD.ORD_NBR=OORD.ORD_NBR AND ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR<br />
ORDER BY SORD.ORD_NBR DESC;
My problem is the COMMIT WORK underlined. I *know* this is necessary to actually populate the temp table, and I don't get an error from executing this command using ExecuteNonQuery. However, the results from the final SELECT as returned through OleDbDataAdapter gives me the column headers - i.e. an empty dataset, as if the COMMIT WORK didn't.
Apologies for the long post, but here's the .net code I'm using:
Private Sub btnStartDate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStartDate.Click<br />
' Query the database and cache the result<br />
Dim myConn As OleDbConnection<br />
Dim strconn As String = Session.Item("optSrcDBConnect")<br />
<br />
Dim dcSQL As OleDbCommand<br />
Dim strSQL As String<br />
Dim drSQL As OleDbDataReader<br />
<br />
WriteLog("Open connection")<br />
Try<br />
myConn = New OleDbConnection(strconn)<br />
myConn.Open()<br />
Catch ex As Exception<br />
WriteLog("Error opening connection " & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "DROP TABLE ODSWIP"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
' Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "CREATE GLOBAL TEMPORARY TABLE ODSWIP ON COMMIT PRESERVE ROWS AS SELECT /*+ ORDERED */ RMAM.RMA_NBR AS RMAM_RMA_NBR, RMAD.LN_NBR AS RMAD_LN_NBR, SORD.ORD_NBR AS SORM_ORD_NBR, SORD.LN_NBR AS SORD_LN_NBR, RMAD.OUT_PART_NBR, SORD.PRODUCT_ID, RMAD.OUT_TRACE_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, ZORD.ORD_NBR AS ZORD_ORD_NBR, ZORD.SALES_SUB_ORD_NBR AS ZORD_SALES_SUB_ORD_NBR FROM RMAM, RMAD, SORD, ZORD WHERE RMAM.RMA_NBR = RMAD.RMA_NBR AND RMAD.RMA_NBR=SORD.ORD_NBR AND SORD.PRODUCT_ID=ZORD.PART_NBR AND RMAM.RMA_NBR=ZORD.SALES_ORD_NBR AND SORD.REC_CD='DT' AND TRIM(SORD.PRODUCT_ID) NOT LIKE '%=_' AND TRIM(SORD.CNCL_ITM_RSN_CD) Is Null AND RMAD.DT_RECEIVED Is Not Null AND TRIM(SORD.INVOICE_ID) is null AND ZORD.PART_NBR Not Like '*-R *' AND ZORD.SERV_ORDER='Y'"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "COMMIT WORK"<br />
WriteLog(strSQL)<br />
dcSQL = New OleDbCommand(strSQL, myConn)<br />
dcSQL.ExecuteNonQuery()<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL " & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
Exit Sub<br />
End Try<br />
<br />
Try<br />
strSQL = "SELECT /*+ ORDERED */ RMAM.RMA_NBR, SORD.LN_NBR, TRIM(RMAD.OUT_TRACE_ID), TRIM(RMAM.CUST_ID), TRIM(RMAD.OUT_PART_NBR), SORD.PRODUCT_ID, RMAD.DT_RECEIVED, SORD.DT_CREATED, SORD.DT_LST_UPDATE, ZORD.ORD_NBR, ZORD.SALES_SUB_ORD_NBR, OORD.ACTUAL_OPEN_DATE FROM ODSWIP, RMAM, RMAD, SORD, ZORD, OORD WHERE ODSWIP.RMAM_RMA_NBR = RMAM.RMA_NBR And ODSWIP.RMAM_RMA_NBR = RMAD.RMA_NBR And ODSWIP.RMAD_LN_NBR = RMAD.LN_NBR AND ODSWIP.SORM_ORD_NBR = SORD.ORD_NBR AND ODSWIP.SORD_LN_NBR = SORD.LN_NBR AND ODSWIP.ZORD_ORD_NBR = ZORD.ORD_NBR AND ZORD.ORD_NBR=OORD.ORD_NBR AND ZORD.SUB_ORD_NBR=OORD.SUB_ORD_NBR"<br />
WriteLog(strSQL)<br />
Dim daOrders = New OleDbDataAdapter(strSQL, strconn)<br />
'WriteLog(txtSpecificQuery.Text)<br />
If myDS.Tables.Contains("SpecificQuery") Then myDS.Tables("SpecificQuery").Dispose()<br />
daOrders.fill(myDS, "SpecificQuery")<br />
WriteLog("Cincom specific extract complete" & vbCrLf & myDS.Tables("SpecificQuery").Rows.Count & " rows returned", EventLogEntryType.Information)<br />
Catch ex As Exception<br />
WriteLog("Error processing SQL" & vbCrLf & strSQL & vbCrLf & ex.Message)<br />
End Try<br />
<br />
Session.Item("myDS") = myDS<br />
Radiobutton8.Checked = True<br />
DataGrid2.CurrentPageIndex = 0<br />
DataGrid2.DataSource = myDS.Tables("SpecificQuery")<br />
DataBind()<br />
End Sub
Any suggestions gratefully accepted!
|
|
|
|
|
i have a table with 8 columns. the first seven columns form a key.
i need to select 7 of them, and join another table :
SELECT mt.c1, mt.c2, mt.c3, mt.v4, mt.c5, mt.c6, mt.c7, ot.ot5 MyTable mt
LEFT OUTER JOIN OtherTable ot ON
mt.c1 = ot.ot1
mt.c2 = ot.ot2
mt.c3 = ot.ot3
mt.c4 = ot.ot4
WHERE mt.c1=0
i'll get something like this:
c1 c2 c3 c4 c5 c6 c7 ot5
------------------------
1 1 1 1 1 1 1 91
1 1 1 1 1 1 2 92
1 1 1 1 1 2 1 93
1 1 1 1 1 3 1 94
simple so far.
since the the key to MyTable is composed of 7 columns, it's possible (and likely) that for any given row, the first 6 of those columns will match the first 6 from some other row (and that's why the key is composed of 7 columns, obviously). ex., see the first and second rows in my little result set.
but, i need to get rows such that the first 6 columns in any row will be unqiue in the result set. in other words, if two or more rows match in the first 6 columns, i need to discard all but the first (or all but the last, it doesn't matter, as long as there is only one):
c1 c2 c3 c4 c5 c6 c7 ot5
------------------------
1 1 1 1 1 1 1 91
1 1 1 1 1 1 2 92 <---- eliminate this row
1 1 1 1 1 2 1 93
1 1 1 1 1 3 1 94
so, is there a way to do this?
GROUP BY won't do it. DISTINCT doesn't help.
Image Toolkits | Image Processing | Cleek
|
|
|
|
|
SELECT a.c1, a.c2, a.c3, a.c4, a.c5, a.c6, a.c7, ot5
FROM MyTable AS a
INNER JOIN (SELECT c1, c2, c3, c4, c5, c6, MAX(c7) AS c7 -- or MIN(c7) if you prefer
FROM MyTable
GROUP BY c1, c2, c3, c4, c5, c6) AS b ON
a.c1 = b.c1 AND a.c2=b.c2 AND a.c3=b.c3 AND a.c4=b.c4 AND a.c5=b.c5 AND a.c6=b.c6 AND a.c7=b.c7
DISCLAIMER: I have not tried this. Its all in my head (which has been known to be fallable sometimes)
My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
|
|
|
|
|
|
This question has to be easy, but I can't seem to find the answer. I'm working with Visual Basic and I've defined a form with a button, an OleDbConnection, an OleDbDataAdapter and a Dataset.
The DataAdapter has a select command like SELECT * from patients where name LIKE 'm%'. (It too me much sweat to figure out to use m% and not m*, but that is another story.)
The next step is use an edit box where I can enter the patient name. Then I want to take that and either change the adapter command text, or use something like
SELECT * from patients where name LIKE ?
and fill in the ?.
There must be an easy way to do this, as this is bread and butter, but I have yet to find it.
Thanks,
Ilan
|
|
|
|
|
The problem I refer to above boils down to the fact that it doesn't know "As OleDbConnection", "As OleDbCommand" or "As OleDbDataAdapter" in mysub().
It knows the same functions as New commands, and it works find with Button1_Click which actually uses OleDbConnection1, sty1 (an OleDbDataAdapter) and stds (a Dataset).
I can't figure out why it gives me the compilation errors. If it were a C++, I would say some include file is missing.
Below is the code, and I'm stuck.....
Thanks for any help.
Public Class Form1
Inherits System.Windows.Forms.Form
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
OleDbConnection1.Open()
sty1.Fill(stds, "studies")
OleDbConnection1.Close()
End Sub
Private Sub mysub()
Dim nwindConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;" & _
"Integrated Security=SSPI;Initial Catalog=northwind")
Dim selectCMD As OleDbCommand = New OleDbCommand("SELECT CustomerID, CompanyName FROM Customers", nwindConn)
selectCMD.CommandTimeout = 30
Dim custDA As OleDbDataAdapter = New OleDbDataAdapter
End Sub
End Class
|
|
|
|
|
Hello,
I'm trying to read BLOB field that contains RTF-data (Microsoft Word file) using VC++ and ADO. Actually, i have the code:
CoInitialize(NULL);
_ConnectionPtr spConn(__uuidof(Connection));
_RecordsetPtr spRs;
CString cnctstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=d:\\test.mdb; Persist Security Info=False;";
CString Qry = "select * from testtable";
spConn->Open((_bstr_t) cnctstr, "", "", adConnectUnspecified);
spRs = spConn->Execute((_bstr_t) Qry, NULL, adCmdText);
//
// BLOB data reading section
//
spConn->Close();
CoUninitialize();
Should I use a "_variant_t" data type or anything like that to access the data that I need? Please help to solve this problem.
All the comments will be appreciated, thanks in advance.
|
|
|
|
|
|
Hi, authoring a task report with Microsoft SQL Server's Reporting Service. It consists of Projects, Tasks and subtasks. So, naturally, I group tasks by:
1. Project
2. Task Owner.
The trouble is, "grouping" inevitably lead to a drilldown box for each group - Drilldown button is okay on "Preview", I clicked and things get expanded as expected. I then deployed the report and view from http://localhost/reports. The reports get loaded and I clicked on the drilldown button -- I can tell there's a postback everytime I click drilldown. But nothing gets expanded in response. Is there any work around for this? I have already applied Reporting Service's SP1.
Thanks in advance.
Norman Fung
|
|
|
|
|
Help! Child-Parent relationship -- Key happens to be a composite key
Hi, I'm having a lot of trouble trying to get this work. I have two classes:
1. AppProperty (Parent)
2. AppPropertyValue (Child)
1. AppProperty class' PK column is PropertyUIN (Int32).
2. AppPropertyValue class' composite key column is {PropertyUIN (Int32), PropertyValue (String)}
3. One AppProperty instance can relate to MANY AppPropertyValue instance. The two are related by "PropertyUIN".
Trouble is, when I tried to persist the child class, I get:
SqlException wrapped around by a NHibernate.ADOException
Message "Column name 'PropertyUIN' appears more than once in the result column list.
Statement(s) could not be prepared." String
"PropertyUIN" appears twice because it's part of the composite key AND at the same time it appears for the second time in AppPropertyValue's hbm file for I need to declare "many-to-one" between AppProperty and AppPropertyValue. What should I do to resolve this situation?
Thanks in advance.
Code Fragment:
Public Class AppProperty
Implements ISerializable
Protected _propertyUIN As Int32
...
...
Public Property PropertyUIN() As Int32
Get
Return _propertyUIN
End Get
Set(ByVal Value As Int32)
_propertyUIN = Value
End Set
End Property
...
End Class
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="application.utilities.settings.AppProperty, Utility" table="AppProperty">
<id name="PropertyUIN" column="PropertyUIN" type="Int32" >
<generator class="identity" />
</id>
... stuff ...
<set name="Values" inverse="true" cascade="all" table="AppPropertyValue">
<key column="PropertyUIN"/>
<one-to-many class="application.utilities.settings.AppPropertyValue, Utility"/>
</set>
... the rest of it ...
</class>
</hibernate-mapping>
Public Class AppPropertyValue
Implements ISerializable
Protected _propertyUIN As Int32
Protected _propertyValue As String
Protected _parentProperty As AppProperty
...
...
Public Property PropertyUIN() As Int32
Get
Return _propertyUIN
End Get
Set(ByVal Value As Int32)
_propertyUIN = Value
End Set
End Property
Public Property PropertyValue() As String
Get
Return _propertyValue
End Get
Set(ByVal Value As String)
_propertyValue = Value
End Set
End Property
Public Property ParentProperty() As AppProperty
Get
Return _parentProperty
End Get
Set(ByVal Value As AppProperty)
_parentProperty = Value
End Set
End Property
...
End Class
<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0">
<class name="application.utilities.settings.AppPropertyValue, Utility" table="AppPropertyValue">
<composite-id>
<key-property name="PropertyUIN" column="PropertyUIN" type="Int32"/>
<key-property name="PropertyValue" column= "PropertyValue" type="String" length="2500"/>
</composite-id>
<many-to-one name="ParentProperty" column="PropertyUIN" class="application.utilities.settings.AppProperty, Utility" />
... stuff ...
</class>
</hibernate-mapping>
CREATE TABLE AppProperty (
PropertyUIN INTEGER IDENTITY(1,1),
...
PRIMARY KEY (PropertyUIN)
)
CREATE TABLE AppPropertyValue (
PropertyUIN INTEGER,
PropertyValue VARCHAR(2500),
...
)
|
|
|
|
|
I have developed an account system. and when i deploy it a message appeared "Some files are out of date". i just countinued but on target mechine a message appeared "Some files are out of date do you want to update them click yes to restart" i did and after loading windows again when i started setup.exe again same message appeared again so i am still unable to install that application . Target plate form is Microsoft Windows 98.
Salman Sheikh
|
|
|
|
|
Hi, I have this stored procedure that launches an executable using xp_cmdshell. This stored proc is triggered by a job:
EXEC sp_SYN_CONTACT
The stored proc itself is:
EXEC master..[xp_cmdshell] 'C:\Inetpub\wwwroot\VendorManagementWeb\bin\SyncContact.exe'
SyncContact is a VB.NET console app that loads contacts into outlook contact list. Trouble is, it seems hangs/freezes everytime when it executes the following:
folder = ns.GetDefaultFolder(msoutlook.OlDefaultFolders.olFolderContacts)
Why is it having trouble retrieving default folder? I tried setting SQL Server agent to impersonate "System" account - but nothing works. btw, the console app works if I launch it myself instead of launching it as a SQL Server job - suspecting it's got to do with security/DCOM/Impersonation level...? But I'm not familiar with this subject.
Thanks in advance.
Norman Fung
|
|
|
|
|
Hi, I really need help on this. 
I have an ORACLE 9i release 2 database.
I've created a table where one of its column is using the new Oracle Timestamp datatype.
I try to access this table using OracleCommand from OracleClient namespace and found a strange behaviour. Some of the value returned for the Timestamp column was not correct. When the fraction behinds the seconds value (the millisecond value) was not zero, it was returned correctly. But when it has no fraction on it, sometime it was not returned correctly (there are some fraction on it).
Here are some examples of it.
-----------------
On Oracle database :
-----------------
create table test (testaudittime timestamp(3));<br />
insert into TEST values(To_Timestamp('2005010112303000', 'YYYYMMDDHH24MISSFF'));<br />
insert into TEST values(To_Timestamp('20050101123030123', 'YYYYMMDDHH24MISSFF'));<br />
insert into TEST values(To_Timestamp('2005010112454500', 'YYYYMMDDHH24MISSFF'));<br />
insert into TEST values(To_Timestamp('20050101124545123', 'YYYYMMDDHH24MISSFF'));
----------------
On C# App :
----------------
OracleCommand ocmd = this.oracleConnection2.CreateCommand();<br />
ocmd.CommandText = "SELECT * FROM TEST ORDER BY TESTTIMESTAMP";<br />
this.oracleConnection2.Open();<br />
OracleDataReader ord = ocmd.ExecuteReader();<br />
while (ord.Read())<br />
{<br />
DateTime testTimeStamp = (DateTime)ord["TESTTIMESTAMP"];<br />
Console.WriteLine("RESULT : " + testTimeStamp.ToString("dd-MMM-yy hh.mm.ss.fffffff tt"));<br />
}<br />
ord.Close();<br />
this.oracleConnection2.Close();
---------------
And this is the value on the console output :
---------------
RESULT : 01-Jan-05 12.30.30.0000000 AM
RESULT : 01-Jan-05 12.30.30.1230000 AM
RESULT : 01-Jan-05 12.45.45.0015903 PM
RESULT : 01-Jan-05 12.45.45.1230000 PM
---------------
I don't know where that fraction in result number 3 come from? 
Anybody can help me pls?
|
|
|
|
|
Hi All,
Anyone ever put together a DB using GUID's for the PK values? Any thoughts on the matter? My client is interested in using GUID's as the PK so that clients can work disconnected from the server's DB, so as to avoid auto-increment identity collisions when merging the tables. There's other reasons too, but they get really involved in the architecture.
Marc
MyXaml
Advanced Unit Testing
YAPO
|
|
|
|
|
Well, I never actually use true "auto-increment" fields because they can be problematic for a variety of reasons. Instead I write my own routines to assign a unique auto-increment style key.
Using a GUID certainly guarantees a unique key. My big question would be how it affects performance. I don't know - you could do some tests maybe. But my uneducated guess would be that if the data tables get pretty large, indexing on a 128 bit value could make things start to really grind when working with a large data set.
Robert
|
|
|
|
|
It works like a charm!
We have a database with about 100 tables, all of which have GUIDs as their primary key.
In order to improve performance, we use a special form of GUID we call "Smart GUID" internally. We found this information somewhere on the web [^]. Basically, the GUID is generated based on the date/time of the client machine. It helps sorting and indexing things because it groups rows created around the same time together. It improves indexing drastically.
Anyway, here's the code we came up with.
<br />
<br />
using Microsoft.VisualBasic;<br />
using System;<br />
using System.Collections;<br />
using System.Data;<br />
using System.Diagnostics;<br />
namespace AdonisTech.Adonis2005.Core.Business {<br />
[ Serializable() ]<br />
public sealed class SmartGuid { <br />
private System.Guid mGuid; <br />
<br />
public SmartGuid() { <br />
mGuid = NewComb(); <br />
} <br />
public SmartGuid( byte[] b ) { <br />
mGuid = new Guid( b ); <br />
} <br />
public SmartGuid( UInt32 a, UInt16 b, UInt16 c, byte d, byte e, byte f, byte g, byte h, byte i, byte j, byte k ) { <br />
mGuid = new Guid( a, b, c, d, e, f, g, h, i, j, k ); <br />
} <br />
public SmartGuid( string g ) { <br />
mGuid = new Guid( g ); <br />
} <br />
public SmartGuid( int a, short b, short c, byte[] d ) { <br />
mGuid = new Guid( a, b, c, d ); <br />
} <br />
public SmartGuid( int a, short b, short c, byte d, byte e, byte f, byte g, byte h, byte i, byte j, byte k ) { <br />
mGuid = new Guid( a, b, c, d, e, f, g, h, i, j, k ); <br />
} <br />
<br />
public override int GetHashCode() { <br />
return mGuid.GetHashCode(); <br />
} <br />
<br />
<br />
public override bool Equals( object o ) { <br />
return mGuid.Equals( o ); <br />
} <br />
<br />
public static bool Equals( object objA, object objB ) { <br />
return System.Guid.Equals( objA, objB ); <br />
} <br />
<br />
<br />
public int CompareTo( object value ) { <br />
return mGuid.CompareTo( value ); <br />
} <br />
<br />
<br />
public override string ToString() { <br />
return mGuid.ToString(); <br />
} <br />
<br />
public string ToString( string format ) { <br />
return mGuid.ToString( format ); <br />
} <br />
<br />
public string ToString( string format, System.IFormatProvider provider ) { <br />
return mGuid.ToString( format, provider ); <br />
} <br />
<br />
<br />
public static System.Guid NewGuid() { <br />
return NewComb(); <br />
} <br />
<br />
private static Guid NewComb() { <br />
byte[] guidArray = System.Guid.NewGuid().ToByteArray(); <br />
DateTime baseDate = new DateTime( 1900, 1, 1 ); <br />
DateTime now = DateTime.Now; <br />
<br />
TimeSpan days = new TimeSpan( now.Ticks - baseDate.Ticks ); <br />
TimeSpan msecs = new TimeSpan( now.Ticks - ( new DateTime( now.Year, now.Month, now.Day ).Ticks ) ); <br />
<br />
<br />
byte[] daysArray = BitConverter.GetBytes( days.Days ); <br />
byte[] msecsArray = BitConverter.GetBytes( System.Convert.ToInt64( msecs.TotalMilliseconds / 3.333333 ) ); <br />
<br />
Array.Reverse( daysArray ); <br />
Array.Reverse( msecsArray ); <br />
<br />
Array.Copy( daysArray, daysArray.Length - 2, guidArray, guidArray.Length - 6, 2 ); <br />
Array.Copy( msecsArray, msecsArray.Length - 4, guidArray, guidArray.Length - 4, 4 ); <br />
<br />
return new System.Guid( guidArray ); <br />
} <br />
<br />
<br />
public DateTime GetDateTime() { <br />
return GetCombDateTime( mGuid ); <br />
} <br />
<br />
public static DateTime GetDateTime( Guid o ) { <br />
return GetCombDateTime( o ); <br />
} <br />
<br />
<br />
private static DateTime GetCombDateTime( Guid guid ) { <br />
DateTime baseDate = new DateTime( 1900, 1, 1 ); <br />
byte[] daysArray = new byte[ 5 ]; <br />
byte[] msecsArray = new byte[ 5 ]; <br />
byte[] guidArray = guid.ToByteArray(); <br />
<br />
Array.Copy( guidArray, guidArray.Length - 6, daysArray, 2, 2 ); <br />
Array.Copy( guidArray, guidArray.Length - 4, msecsArray, 0, 4 ); <br />
<br />
Array.Reverse( daysArray ); <br />
Array.Reverse( msecsArray ); <br />
<br />
int days = BitConverter.ToInt32( daysArray, 0 ); <br />
int msecs = BitConverter.ToInt32( msecsArray, 0 ); <br />
<br />
DateTime myDate = baseDate.AddDays( days ); <br />
myDate = myDate.AddMilliseconds( msecs * 3.333333 ); <br />
<br />
return myDate; <br />
} <br />
<br />
} <br />
<br />
} <br />
|
|
|
|
|
C a r l wrote:
Anyway, here's the code we came up with.
This looks great! Thanks for the info and the code example. We'll definitely make use of this! ![Jig | [Dance]](https://www.codeproject.com/script/Forums/Images/jig.gif)
Marc
MyXaml
Advanced Unit Testing
YAPO
|
|
|
|
|
In my experience generating the keys on the client to send to the server makes things much easier. The databases I've used it on have only had tables on the order of a million records per table and smaller, database size only several GB, so I can't say much about performance. The performance penalties for missing indexes usually dwarf those from data size anyway, so I wouldn't be too concerned.
They are espeically convienient for things like web applications where you want to eliminiate double post issues and things like that. Since you have a unique transaction ID, you can easily check that you aren't duplicating operations.
I can imagine the sinking feeling one would have after ordering my book,
only to find a laughably ridiculous theory with demented logic once the book arrives - Mark McCutcheon
|
|
|
|
|
Yes. I've done it both ways, and I definitely prefer using unique IDs to sequences/identity columns.
The reasons have been pretty well covered here. There are some minor drawbacks, such as making visual inspection of the data a little more difficult (e.g. you're trying to glance at a small sample of data to get an idea of how related records hang together), and it takes a little getting used to in that respect, but the benefits outweigh the aggravations for me.
The value of using a guaranteed "unique" id was underscored for me recently while I was working on a project using Oracle, which has no Guid type intrinsically. The designers had established sequences and insert triggers to keep the sequences incrementing however, after some 50,000 rows of data or so, one of the sequences got dropped and recreated with an arbitrary seed value lower than the highest previous sequence value. That was bad enough; what made it worse was that the code responsible for inserting rows into the affected table did not differentiate between inserts and updates. The method was passed a PK value (integer), which in the case of new records was the next value in the sequence, and for existing records was their current PK value. The logic was similar to:
1. Does that sequence value exist in the database?
2. If step 1 is False, insert a new record (correct behavior at all times)
3. If step 1 is True, update the record with the provided PK value with all of the data provided (this was a killer, since the renumbered sequence was now sometimes providing "used" PK values for new records, overwriting entire records inadvertently - it was even harder to locate the problem since deletions made "holes" in the tables PK range, so sometimes new records with out-of-sequence PKs would insert just fine).
Guids are goooood.
Hope this helps.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
