MS SQL (online) - Access(offline) system

Hi everyone,

I 've built an application which runs on a ms sql Database, now my employer want a offline database system as well. I've already implemented Access, but having difficulties with something.

I was creating a form to transfer all data from the sql database to the access database, when I experienced a problem in my first (access) data accessor. Sql gave a syntax error, when I copied the sql string with some test data to Access it obviously gave the same error but afterwards focussed on the DataTime field of my sql string. So I'm assuming the problem lies with that.

Is there a difference between the DateTime in SQL Server and Access? If so is there a way to convert from one to the other?

My SQL test string:

Code Snippet

INSERT INTO dbo_tblPerson(ID,Name,Surname,Email,Website,Address,Country,Comments,DateTime,Telephone,Mobile,Active)
VALUES (1,To,Vdb,info@.,www.,str,BE,Test,Date.Now(),0474,0474,0)

My Data Accessor:

Code Snippet

public static int addPerson(clsPerson oPerson)

{

try

{

string sSQL = "INSERT INTO dbo_tblPerson(ID,Name,Surname,Email,Website,Address,Country,Comments,DateTime,Telephone,Mobile,Active) ";

sSQL += "VALUES (@.ID,@.Name,@.Surname,@.Email,@.Website,@.Address,@.Country,@.Comments,@.DateTime,@.Telephone,@.Mobile,@.Active)";

int i = 0;

int iHigh = getHighestID();

clsDatabase.ExecuteSQL(

sSQL,

new OleDbParameter("@.Name", oPerson.Name),

createNullableParameter("@.Surname", oPerson.Surname),

createNullableParameter("@.Email", oPerson.Email),

createNullableParameter("@.Website", oPerson.Website),

createNullableParameter("@.Address", oPerson.Address),

createNullableParameter("@.Country", oPerson.Country),

createNullableParameter("@.Comments", oPerson.Comments),

createNullableParameter("@.DateTime", oPerson.DateTime),

createNullableParameter("@.Telephone", oPerson.Telephone),

createNullableParameter("@.Mobile", oPerson.Mobile),

new OleDbParameter("@.Active", i)

);

return iHigh;

}

catch (Exception ex)

{

return 0;

//System.Reflection.MethodBase oExceptie = System.Reflection.MethodInfo.GetCurrentMethod();

//clsLogging.logException(1, "addInput: " + ex.Message, oExceptie);

}

}

private static int getHighestID()

{

int iID;

try

{

string sSQL = "SELECT MAX(ID) from dbo_tblPerson";

try

{

iID = Convert.ToInt32(clsDatabase.ExecuteScalar(sSQL, new OleDbParameter[0]));

}

catch (Exception ex)

{

iID = 0;

}

iID += 1;

}

catch (Exception ex)

{

throw ex;

}

return iID;

}

private static OleDbParameter createNullableParameter(string sName, object oProperty)

{

OleDbParameter oPar = new OleDbParameter() ;

if (oProperty == null)

{

oProperty = System.Convert.DBNull;

}

oPar.Value = oProperty;

oPar.ParameterName = sName;

return oPar;

}

Tobias

Apparently the databasefield name 'DateTime' caused problems, so I changed it to 'dt'.
Getting a whole new problem, being "Data type mismatch in criteria expression." Can it still be the datetime value thing?

Code Snippet

public static int addPerson(clsPerson oPerson)

{

try

{

string sSQL = "INSERT INTO dbo_tblPerson(Name,Surname,Email,Website,Address,Country,Comments,dt,Telephone,Mobile,Active) ";

sSQL += "VALUES (@.Name,@.Surname,@.Email,@.Website,@.Address,@.Country,@.Comments,@.dt,@.Telephone,@.Mobile,@.Active)";

int i = 0;

int iHigh = getHighestID();

clsDatabase.ExecuteSQL(

sSQL,

new OleDbParameter("@.Name", oPerson.Name),

createNullableParameter("@.Surname", oPerson.Surname),

createNullableParameter("@.Email", oPerson.Email),

createNullableParameter("@.Website", oPerson.Website),

createNullableParameter("@.Address", oPerson.Address),

createNullableParameter("@.Country", oPerson.Country),

createNullableParameter("@.Comments", oPerson.Comments),

createNullableParameter("@.dt", oPerson.DateTime),

createNullableParameter("@.Telephone", oPerson.Telephone),

createNullableParameter("@.Mobile", oPerson.Mobile),

new OleDbParameter("@.Active", i)

);

return iHigh;

}

catch (Exception ex)

{

return 0;

//System.Reflection.MethodBase oExceptie = System.Reflection.MethodInfo.GetCurrentMethod();

//clsLogging.logException(1, "addInput: " + ex.Message, oExceptie);

}

}

|||

This helped

oPerson.DateTime.ToString("MM-dd-yyyy hh:mms")

|||

I'm not sure what the definition is for "offline" system; but what about using SQL Server Express instead of Access?

|||

Then every client still has to run a server on their system .. don't they?

*edit: Anyway I 've succesfully implemented the access system already, just stuck now with how use it once distributed with Click Once. Don't know how to setup my Connectionstring for the local db.

|||

You mean an server operating system?

No, Express runs on XP(SP2) and Win2000 Pro (SP4)

|||But does the express edition act like a file or is it a running service? Because I can't ask for each client to install express on their system.|||

It runs as a service.

Sorry.

|||

That's what I thought, no worries.. glad I used the right way

You don't know a solution for my connectionstring problem? Or how to copy a file with Clickonce to c:\\DB\ ..?

|||

Not much help to you there...haven't worked with Access in a number of years.

|||

It doesn't really have to do with access.. I just need to put the access mdb file to a location I know on the users system using the ClickOnce Deployment, putting it between the application files works but then the location of the file would be something like

Code Snippet

C:\Documents and Settings\Tobias\Local Settings\Apps\2.0\2LQGDKTN.0KN\J3WQXOLX.BGB\netw...exe_75bfb84bc0f378ec_0000.0009_en-gb_fc79dfd558e684a4

So basically not usuable in a connectionstring for a database.