[C#] How usind uniqueidentifier in a table

[Guest]

Hi,

On Pocket PC 2003 in C#, I have created a table which is using an
uniqueidentifier type of data :

------------------------------------------------------------------------
SqlCeConnection connDB = new SqlCeConnection();
SqlCeCommand cmndDB = new SqlCeCommand();
....
cmndDB.CommandText =
" CREATE TABLE ENL " +
" ( ID uniqueidentifier " +
" CONSTRAINT PKENL PRIMARY KEY " +
" , CAB_DISTRIB nchar(20) not null " +
" , CAB_REPRISE nchar(20) not null " +
" , DATE nchar(11) not null " + // HH:MM
" , HEURE nchar(6) not null " + // JJ/MM/AAAA
" , CODE_DPT nchar(3) not null " +
" , MOTIF nchar(51) not null " +
" , TRANSMIT_DATA nchar(1)" +
" , TRANSMIT_IMG nchar(1)" +
" )";
cmndDB.ExecuteNonQuery();
.....
-------------------------------------------------------------------------

Then I insert rows in the table like that :

----------------------------------------------------------
SqlCeConnection connDB = new SqlCeConnection(strConn);
....
string req = "INSERT ENL
(ID,CAB_DISTRIB,CAB_REPRISE,DATE,HEURE,CODE_DPT,MOTIF,TRANSMIT_DATA) VALUES
(NEWID(),'"+pa+"','"+rep+"','"+strDate+"','"+strHour+"','N','"+m+"','N')";
SqlCeCommand cmndDB = new SqlCeCommand();

try
{ connDB.Open();

cmndDB.Connection = connDB;
cmndDB.CommandText = req;
if(cmndDB.ExecuteNonQuery() == 1) status = true;

// Fermeture de la connexion
connDB.Close();
}
....
--------------------------------------------------

On that point, all is going OK.
Now I want to get only a list of my ID of the table in order to make an
update.

I get the id in a arraylist (is it well to do that ?) :

-----------------------------------------------------
public ArrayList getEnlTransmit()
{
SqlCeConnection connDB = new SqlCeConnection(strConn);
WHERE TRANSMIT_DATA='N'";
string req = "SELECT ID FROM ENL WHERE TRANSMIT_DATA='N'";
SqlCeCommand cmndDB = new SqlCeCommand(req, connDB);
SqlCeDataReader drdrDB;
bool status = false;
ArrayList arList = new ArrayList();

try
{
// Ouverture de la connexion
connDB.Open();

// Exécution de la requête
drdrDB = cmndDB.ExecuteReader();

while ( drdrDB.Read() )
{
arList.Add(drdrDB["ID"]);
status = true;
}

drdrDB.Close();

connDB.Close();
classStatus = status;
}
catch(Exception ex)
{...
}
return arList;
}
-----------------------------------------------------

Then, I make the update, but it doesn't run :
-------------------------------------------------------
public bool updateTransmitStatus(object pa)
{
bool status = false;
String req = "UPDATE ENL SET TRANSMIT_DATA='S' WHERE ID="+pa;
SqlCeConnection connDB = new SqlCeConnection(strConn);
SqlCeCommand cmndDB = new SqlCeCommand(req, connDB);

try
{
connDB.Open();

if(cmndDB.ExecuteNonQuery() == 1) status = true;

connDB.Close();
}
catch(Exception ex)
{...
}
return status;
}

 

[Lonifasiko]

Could you try with this modified SQL sentence?

String req = "UPDATE ENL SET TRANSMIT_DATA='S' WHERE ID='" +
pa.ToString() + "'";

Hope it helps.

 

[Guest]

In fact, I found where the problem was.
i change the request by the following and it runs (where pa contains the
guid = "...-...-...-...-...") :

String req = "UPDATE ENL SET TRANSMIT_DATA='S' WHERE ID='{" +pa+ "}'";

Thanks.

 

[Alex Feinman [MVP]]

Wrong. You should use a SqlCeCommand with parameters. ADO.NET will take care
of the formatting

Hi,

On Pocket PC 2003 in C#, I have created a table which is using an
uniqueidentifier type of data :

------------------------------------------------------------------------
SqlCeConnection connDB = new SqlCeConnection();
SqlCeCommand cmndDB = new SqlCeCommand();
...
cmndDB.CommandText =
" CREATE TABLE ENL " +
" ( ID uniqueidentifier " +
" CONSTRAINT PKENL PRIMARY KEY " +
" , CAB_DISTRIB nchar(20) not null " +
" , CAB_REPRISE nchar(20) not null " +
" , DATE nchar(11) not null " + // HH:MM
" , HEURE nchar(6) not null " + // JJ/MM/AAAA
" , CODE_DPT nchar(3) not null " +
" , MOTIF nchar(51) not null " +
" , TRANSMIT_DATA nchar(1)" +
" , TRANSMIT_IMG nchar(1)" +
" )";
cmndDB.ExecuteNonQuery();
....
-------------------------------------------------------------------------

Then I insert rows in the table like that :

----------------------------------------------------------
SqlCeConnection connDB = new SqlCeConnection(strConn);
...
string req = "INSERT ENL
(ID,CAB_DISTRIB,CAB_REPRISE,DATE,HEURE,CODE_DPT,MOTIF,TRANSMIT_DATA)
VALUES
(NEWID(),'"+pa+"','"+rep+"','"+strDate+"','"+strHour+"','N','"+m+"','N')";
SqlCeCommand cmndDB = new SqlCeCommand();

try
{ connDB.Open();

cmndDB.Connection = connDB;
cmndDB.CommandText = req;
if(cmndDB.ExecuteNonQuery() == 1) status = true;

// Fermeture de la connexion
connDB.Close();
}
...
--------------------------------------------------

On that point, all is going OK.
Now I want to get only a list of my ID of the table in order to make an
update.

I get the id in a arraylist (is it well to do that ?) :

-----------------------------------------------------
public ArrayList getEnlTransmit()
{
SqlCeConnection connDB = new SqlCeConnection(strConn);
WHERE TRANSMIT_DATA='N'";
string req = "SELECT ID FROM ENL WHERE TRANSMIT_DATA='N'";
SqlCeCommand cmndDB = new SqlCeCommand(req, connDB);
SqlCeDataReader drdrDB;
bool status = false;
ArrayList arList = new ArrayList();

try
{
// Ouverture de la connexion
connDB.Open();

// Exécution de la requête
drdrDB = cmndDB.ExecuteReader();

while ( drdrDB.Read() )
{
arList.Add(drdrDB["ID"]);
status = true;
}

drdrDB.Close();

connDB.Close();
classStatus = status;
}
catch(Exception ex)
{...
}
return arList;
}
-----------------------------------------------------

Then, I make the update, but it doesn't run :
-------------------------------------------------------
public bool updateTransmitStatus(object pa)
{
bool status = false;
String req = "UPDATE ENL SET TRANSMIT_DATA='S' WHERE ID="+pa;
SqlCeConnection connDB = new SqlCeConnection(strConn);
SqlCeCommand cmndDB = new SqlCeCommand(req, connDB);

try
{
connDB.Open();

if(cmndDB.ExecuteNonQuery() == 1) status = true;

connDB.Close();
}
catch(Exception ex)
{...
}
return status;
}