-1

The current info needs to be handled:

Tid:            13.12.2014 01:48:48

what the script contains handling this is:

if isArray (arrString) then
  if uBound (arrString) > 0 then
    Select Case lcase (arrString(0))
      Case "tid": tid = trim (arrString (1)) & ":" & trim (arrString (2)) & ":" & trim (arrString (3))

When inserting with this code:

sql = "INSERT INTO LoginLogg VALUES ('" & tiden & "', '" & brukernavn & "', '" & maskinnavn & "', '" & operativsystem & "', '" & servicepack & "', '" & minne & "', '" & produsent & "', '" & modell & "', '" & bios & " / "  & bios2 & "', '"& serienummer & "', '"& printere & "', '"& ipadresse & "', '"& imagedato & "', '" & opplosninger & "')"

This into SQL it returns:

the conversion of a varchar data type to a datetime data type resulted in an out-of-range value errorcode: 80040e07

I cant find the reason why it wont work now. The database was moved from a 2003 to a 2008 R2.

If this would have been powershell i would have been able to solve this but VB isnt my strongest suit.

Updated the title hoping it is more suiting, along with the sql insertion code.

vya
  • 57
  • 1
  • 2
  • 7
  • Administer (and provide into sql) date and time in language / culture / locale indifferent [ISO format](http://www.cl.cam.ac.uk/~mgk25/iso-time.html) using next template: `YYYY-MM-DD HH:mm:ss` – JosefZ Dec 29 '14 at 15:09
  • Questions seeking debugging help (**"why isn't this code working?"**) must include the desired behavior, a *specific problem or error* and *the shortest code necessary* to reproduce it **in the question itself**. Questions without **a clear problem statement** are not useful to other readers. See: [How to create a Minimal, Complete, and Verifiable example](http://stackoverflow.com/help/mcve). – Ansgar Wiechers Dec 30 '14 at 00:42

2 Answers2

0
  1. Fix the discrepancy between tid and tiden
  2. See if feeding a m/d/y string - trim (arrString (2)) & "/" & trim (arrString (1)) & "/" & trim (arrString (3)) 'works'
  3. If (2) fails or you don't want a hack, try to concat a date literal "#m/d/y#" into your SQL statement
  4. If (3) fails or you want to do it correctly, use an ADODB.Command/prepared statement with a date parameter - obtained by DateSerial() on CInt(arrString(...))
Ekkehard.Horner
  • 38,498
  • 2
  • 45
  • 96
  • Hi I used tiden = Replace(tid,".","/") to fix the the / or - between the dates but still it fails on sql insert. I will see if i can google your advices – vya Jan 05 '15 at 08:35
  • @vya - did you change the order (m/d/y) in proposal (2)? – Ekkehard.Horner Jan 05 '15 at 08:39
  • Cdate doesnt convert it as here i norway the order of writing is as it is allready. The secondary solution you suggested returns: 48/13.12.2014 01/58 – vya Jan 05 '15 at 09:49
  • @vya - CDate isn't relevant here, because the conversion fails at the server. The result `48/13.12.2014 01/58` shows that your arrString doesn't contain what is to be expected, publish the array and the resulting tid[en]. Test if hardcoding `12/13/2014 01:48:48 AM` or `#12/13/2014 01:48:48 AM#` into your SQL statement works, if yes, adapt your string building accordingly. All this rigmarole could be avoided by approach (4). – Ekkehard.Horner Jan 05 '15 at 10:11
0

So i tried hardcoding the format that sql normally accepts: 2014/01/24 22:00:00 This failed.

then i tried to learn to split the strings with vb and joining them by doing: 

Dim ArrSpace, ArrDot

ArrSpace = Split(tid, " ")
ArrDot = Split(ArrSpace(0), ".")
tid = ArrDot(2) & "/" & ArrDot(1) & "/" & ArrDot(0) & " " & ArrSpace(1)

Then inserting tid. this worked now :) thank you for guiding me in the correct direction.

Thank you Ekkehard.Homer

vya
  • 57
  • 1
  • 2
  • 7