problem running insert statements with rda.submitSql [datetime]

[Thomas Peterson]

Hi,

I try to insert some date to my SQL Server 2000 database.

On the pocket pc I use the insert statements
from the rda.submitsql method to upload the data to my SQL server 2000.
My code works fine für integer, nvarchar but it does not work with
datetime. The colum on the sql server table is datetime.

(code vb.net 2003)

Dim rda As SqlCeRemoteDataAccess

rda = New SqlCeRemoteDataAccess

Dim remoteConnectString As String = "Provider=SQLOLEDB;" & _
"Data Source=" & SqlServerIP & ";" & _
"Initial Catalog=" & DatenbankSqlServer & ";" & "User Id=" &
SqlServerLogin & ";" & "Password=" & SqlServerPasswort

rda.InternetLogin = HTTPLogin
rda.InternetPassword = HTTPPasswort
rda.InternetUrl = ServerURL
rda.LocalConnectionString = "Data Source=\My Documents\test.sdf;" &
"SSCEatabase Password="

Dim uhrzeit As DateTime = DateTime.Now

Dim remoteSQL As String = "INSERT INTO " & TestTest & " (TUERZU)
VALUES(" & uhrzeit.ToString() & ")"

rda.SubmitSql(remoteSQL, remoteConnectString)

I also tried:

Dim remoteSQL As String = "INSERT INTO " & TestTest & " (TUERZU) VALUES(
' " & uhrzeit.ToString() & " ' )"
Dim remoteSQL As String = "INSERT INTO " & TestTest & " (TUERZU)
VALUES(" & uhrzeit & ")"
Dim remoteSQL As String = "INSERT INTO " & TestTest & " (TUERZU) VALUES(
' " & uhrzeit & " ' )"


with integer I used the following code which works fine:

Dim intTest As Integer = 4
Dim remoteSQL As String = "INSERT INTO " & TestTest & " (LINIE) VALUES("
& intTest & ")"
rda.SubmitSql(remoteSQL, remoteConnectString)


SqlServerIP, DatenbankSqlServer, SqlServerLogin, SqlServerPasswort, ....
are declared in a module.


This is the error message I get:
"The OLE DB Execute method failes. The SQL statement is not valid.
[,,,SQL statement,,,]

Does anybody know what is going wrong there?
Thanks.

Regards,

Thomas Peterson

 

[chris-s]

Try formatting your datetime as 'ToString("yyyy-M-d") ', works for us

Chris

 

[Thomas Peterson]

Hi,

Try formatting your datetime as 'ToString("yyyy-M-d") ', works for us

ok, thx for this tip. I used another formatting (I also need the date)
and now it works.

Thomas