|
|
Hi! I have a problem when I try to insert DateTime value in an access database.
I receive this exception: "Syntax error in INSERT INTO statement."
I try two different ways to add Datetime and no one works!
<br />
newDataRow["Timestamp"] = DateTime.Now.ToString();<br />
and
<br />
newDataRow["Timestamp"] = DateTime.Now;<br />
But if I remove all DateTime stuff in my database and my code, I am able to insert data whitout any problem.
Here my code:
DataSet dataSet = new DataSet();
string dbGenConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";
string dbConn = dbGenConn + "fileName.mdb" + "; Persist Security Info=False;";
OleDbConnection conn = new OleDbConnection(dbConn);
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(new OleDbCommand("SELECT * FROM Data", conn));
OleDbCommandBuilder dataCmdBuilder = new OleDbCommandBuilder(adapter);
adapter.Fill(dataSet, "Data");
for(int i = 0; i < items.Length; i++)
{
DataRow newDataRow = dataSet.Tables["Data"].NewRow();
newDataRow["DataID"] = i;
newDataRow["Timestamp"] = DateTime.Now.ToString();
newDataRow["Quality"] = 192
dataSet.Tables["Data"].Rows.Add(newDataRow);
}
adapter.Update(dataSet, "Data");
conn.Close();
|
|
|
|
|
i think this is the problem:
newDataRow["Timestamp"] = DateTime.Now.ToString();
if your database field is a text field use
newDataRow["Timestamp"] = Convert.ToString(DateTime.Now);
and if it's a datetime (much better) use it this way
newDataRow["Timestamp"] = DateTime.Now;
|
|
|
|
|
Hi !
When I create a new record in my table, an autoincremented field (which is the record's ID) is filled by the database. Often I need, as soon as I created the record, to know it's ID to use it.
How can I get this new record ID if the database is used in a multiuser environnement ? I guess creating the record and then getting the highest ID could be a problem if two records are simultaneously created.
Thank you for your help !
Jerome
|
|
|
|
|
I ran into the same situation. You have this solutions:
1. If your application uses only one server and all users connect to that server to write data in the database then you can use a critical section at the application level to lock the application when doing this:
a. Insert new value
b. Select MAx (ID)
2. Get rid of autoincrement values. Use a new table (say ID_Generator) that holds a table name and the max reached value. Then, to insert a unique id you would do this:
a. UPDATE ID_GENERATOR SET IDValue = IDValue + 1 WHERE TableName = 'MYTable'. This ensures that the page is locked during the update process
b. SELECT IDValue FROM ID_GENERATOR WHERE TableName = 'MyTable';
I prefer the second solution since it gives me complete control over IDs.
Best regards,
Alexandru Savescu
P.S. Interested in art? Visit this!
|
|
|
|
|
We use (2) and stored procedures to add data to tables which have an ID element, passing it back as an output parameter. That way it works with SQLServer and Oracle9, which we support - don't know about access, as that's not a supported product in this context.
Admittedly, we're writing COM+ components in C++, which makes this stuff easier 
Steve S
|
|
|
|
|
How about @@IDENTITY or SCOPE_IDENTITY()?
|
|
|
|
|
|
Hi !
Tom Archer wrote:
What database api are you using? ADO? ADO.NET? ...
I want to use only standard SQL with the ODBC driver, to be able to use several database types.
Jerome
|
|
|
|
|
APIs such as ADO.NET provide means of making this easier, but with straight ODBC, I believe you'll have to make a following read to get the value.
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
|
I am the datalayer of an enterprise application with .NET.
The database is now on SQL Server but we might port it for other systems like Oracle.
To make it more portable we are using OleDB. I would like to know how much the performance would imporve should I use Sql classes when dealing with SQL server.
Thanks.
Best regards,
Alexandru Savescu
P.S. Interested in art? Visit this!
|
|
|
|
|
Everything I've read says the same thing, but I've never seen it quantified.
A solution that gives you some flexibility might be to use the ADO.NET interfaces rather than the concrete classes. Use a factory class to create your connections, data readers, data adapters, etc. and then in your data layer use the interface types, rather than the concrete types to work with the objects.
To change to one of the other 'families' of classes, you only need to change the factory. You can have a factory for SQL Server and one for Oracle (with .NET 1.1). You could even instantiate the factory dynamically at runtime though you'll need a common base class or interface for your factories.
Here is some psuedo-code:
<br />
class SQLServerDataObjectFactory : DataObjectFactory<br />
{<br />
public IDbConnection CreateConnection(...)<br />
{<br />
return new SqlConnection(...);<br />
}<br />
}<br />
<br />
class OracleDataObjectFactory : DataObjectFactory<br />
{<br />
public IDbConnection CreateConnection(...)<br />
{<br />
return new OracleConnection(...);<br />
}<br />
}<br />
<br />
class MyDataLayerObject<br />
{<br />
public void MyDataLayerMethod()<br />
{<br />
<br />
IDbConnection connection = myDataObjectFactory.CreateConnection(...);<br />
<br />
}<br />
} <br />
<br />
Brad
|
|
|
|
|
I get this error message when I read data from MySQL database. It is Microsoft.Data.Odbc.OdbcException.
Anyone know what is this problem and how to solve it? TQ
|
|
|
|
|
OK, I was got the solution
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243
|
|
|
|
|
what is wrong here?
I'm using this function to add a new record, all is well, close the application, I get
The instruction at "0x044e2cc2" referenced memory at "0x00000020", memory could not be read.
If I add a second record right away will also error.
CDaoDatabase data;
CString SqlString;
CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString File; // = pApp->m_strDB;
File = "D:\\Apps\\MVS\\VC98\\MyProjects\\Homeplay\\Release\\ruthere.mdb";
char pszBuf[MAX_PATH];
GetDateFormat(LOCALE_USER_DEFAULT, NULL, NULL, "MMMM'.' dd',' yyyy", pszBuf, MAX_PATH);
// Build ODBC connection string
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,File);
TRY
{
// Open the database
data.Open(File);
// Allocate the recordset
CDaoRecordset recset( &data );
SqlString = "SELECT * "
"FROM Accounts"; // search all database
recset.Open(CRecordset::forwardOnly,SqlString, dbSeeChanges);
// make sure we can make changes and update
if (!recset.CanAppend() || !recset.CanUpdate( ))
{
AfxMessageBox("can't append to recordset");
return;
}
recset.AddNew();
recset.SetFieldValue("Nick", lpszUser);
recset.SetFieldValue("Pass", lpszPass);
recset.SetFieldValue("IP", lpszAddress);
recset.SetFieldValue("Authorize", "0");
recset.SetFieldValue("RegDate", pszBuf);
recset.Update();
recset.Close();
data.Close();
}
CATCH(CDBException, e)
{
// If a database exception occured, show error msg
AfxMessageBox("Database error: "+e->m_strError);
return;
}
END_CATCH;
any helps really appreciated.
shotgun
|
|
|
|
|
May sound silly, but you might try setting your recset and data to null before you exit. Everything looks fine to me.
Rocky Moore <><
|
|
|
|
|
tried that, on recset=NULL got the warning, "CDaoRecordset doesn't support =", same when I tried, delete recset;
thanks for the suggestions though.
shotgun
|
|
|
|
|
I have a sql trigger in that on each updated row I need to up the the schedule table using the scheduleid recorded in the checkinout table, i create a user defined function but I cant get it to work right. Yes I have tested the UDF and it works fine.
<br />
ALTER FUNCTION [Convert].IsOrderCompleted<br />
(<br />
<br />
@OrderNumber varchar(10)<br />
)<br />
RETURNS bit<br />
AS<br />
BEGIN<br />
DECLARE @FoundNull bit<br />
<br />
SET @FoundNull = 1<br />
<br />
SELECT @FoundNull = CASE WHEN CompletedDate IS NULL THEN 0 END FROM OrderDetail WHERE OrderNumber = @OrderNumber<br />
<br />
<br />
RETURN @FoundNull<br />
END<br />
the trigger
<br />
CREATE TRIGGER CheckInOut_ValidatedTrigger<br />
ON dbo.CheckInOut<br />
FOR UPDATE<br />
AS<br />
IF UPDATE(Validated)<br />
BEGIN<br />
IF ( Validated = 1 )<br />
BEGIN<br />
SET NOCOUNT ON<br />
<br />
UPDATE Schedule<br />
<br />
SET CompletedDate = GETDATE(),<br />
CompletedOrderMainStatus = ( CASE [Convert].IsOrderCompleted(SELECT TOP 1 inserted.OrderNumber FROM inserted) WHEN 0 THEN 3 ELSE 5 END )<br />
END<br />
END<br />
Please help someone.
I need this bad.
nick
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
Ista wrote:
i create a user defined function but I cant get it to work right.
What would be right?
You are aware that your trigger will run only after your UDF commits (ends) its' transaction?
|
|
|
|
|
Well actually the UDF runs fine but I want to use a select statement returning just 1 value and top 1 still confuses the compiler into think it will receive more than one row
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
A trigger runs once at the end of any statement it's involved in, NOT once per row. The inserted virtual table contains every row touched.
I suggest something like:
ALTER TRIGGER CheckInOut_ValidatedTrigger
ON dbo.CheckInOut
FOR UPDATE
AS
IF @@ROWCOUNT = 0 RETURN
IF NOT UPDATE( Validated ) RETURN
UPDATE Schedule
SET
CompletedDate = OD.CompletedDate,
CompletedOrderMainStatus =
CASE dbo.IsOrderCompleted( I.OrderNumber )
WHEN 0 THEN 3
ELSE 5
END
FROM
inserted I
INNER JOIN
OrderDetail OD
ON I.OrderNumber = OD.OrderNumber
WHERE
I.Validated = 1 AND
Schedule.OrderNumber = I.OrderNumberI've assumed that you have an OrderNumber column on your Schedule table.
|
|
|
|
|
heres how I solved it but I will change to use set method. A cursor was my meantime hack.
<small><br />
SET NOCOUNT ON<br />
<br />
<br />
DECLARE @OrderNumber varchar(10)<br />
DECLARE @ScheduleID bigint<br />
DECLARE @DispatcherID smallint<br />
DECLARE @ScheduleComplete bit<br />
<br />
DECLARE OrderCursor CURSOR FOR<br />
SELECT OrderNumber, ScheduleID, CheckOutDispatcherID, ScheduleComplete FROM inserted WHERE Validated = 1<br />
<br />
OPEN OrderCursor<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
<br />
WHILE @@FETCH_STATUS = 0<br />
BEGIN<br />
IF ( @ScheduleComplete IS NULL OR @ScheduleComplete = 0 )<br />
BEGIN<br />
UPDATE Schedule<br />
SET Attempts = Attempts + 1<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
ELSE<br />
BEGIN<br />
UPDATE Schedule<br />
SET CompletedDate = GETDATE(),<br />
CompletedOrderMainStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 5 END ),<br />
CompletedOrderStatus = ( CASE [Convert].IsOrderCompleted(@OrderNumber) WHEN 0 THEN 3 ELSE 8 END ),<br />
CompletedDispatcherID = (@DispatcherID)<br />
WHERE Schedule.ID = @ScheduleID<br />
END<br />
FETCH NEXT FROM OrderCursor INTO @OrderNumber, @ScheduleID, @DispatcherID, @ScheduleComplete<br />
END<br />
<br />
CLOSE OrderCursor<br />
DEALLOCATE OrderCursor<br />
<br />
SET NOCOUNT OFF<br />
I'm not an expert yet, but I play one at work. Yeah and here too.
|
|
|
|
|
 HELP!!!
I am trying to insert a row into an Access table using ADO.Net & C#. But it always give me an exception of "Syntax Error". But I don't understand, I am using the OleDbCommandBuilder to build the sql, why it has syntax error?
Please help me, I am almost crazy!!!!
Here is the table I want to insert records:
table name : Table1
fields:
ID: int, (AutoNumber, primary key)
SaveDate: Date/Time, default value = CDate() // this field is used to save the date&time when this row insert, so I don't want to write it in my code
field1: text(20)
field2: text(20)
field3: text(30)
Here is my code:
public bool Insert()<br />
{<br />
try<br />
{<br />
OleDbDataAdapter da = new OleDbDataAdapter(<br />
"select top 1 * from table1", <br />
"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=.\\test.mdb;User Id=;Password=;Mode=ReadWrite");<br />
<br />
DataSet ds = new DataSet();<br />
da.Fill(ds, "table1");<br />
OleDbCommandBuilder bld = new OleDbCommandBuilder(da);<br />
<br />
DataRow r = ds.Tables[0].NewRow();<br />
for ( int i = 0; Table1[i].Field != null; i++ ) <br />
{<br />
if ( string.Compare(Table1[i].Field, "ID", true) != 0 &&<br />
string.Compare(Table1[i].Field, "SaveDate", true) != 0 ) <br />
{ <br />
r[Table1[i].Field] = Table1[i].Value;<br />
}<br />
}<br />
ds.Tables[0].Rows.Add(r);<br />
int naffected = da.Update(ds, "table1");<br />
da.Dispose();<br />
da = null;<br />
if ( naffected > 0 )<br />
return true;<br />
} <br />
catch ( Exception e ) <br />
{<br />
ShowMessage(e.Message);<br />
}<br />
return false;<br />
}
|
|
|
|
|
I know why now. Because in my table, I use a reserved word as my field name. Thank you for reading.
|
|
|
|
|
I think you can get away with that, but only if you enclose the column name in square brackets when referring to it in code.
Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
