|
 |
asp_databases thread: RE: SQL Insert Statement with Null Date
Values
Message #1 by Martin Lee <access@o...> on Sat, 20 Apr 2002 19:23:16 +0800
|
|
Thanks for the info Kim, will start from there.
Martin
At 10:09 4/19/2002 +0200, you wrote:
>You can do that using adox - Here are a couple of links...
>
>http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
>admscadoapireference.asp
>http://www.4guysfromrolla.com/webtech/013101-1.shtml
>
>-Kim
>
>-----Original Message-----
>From: Martin Lee [mailto:access@o...]
>Sent: 19. april 2002 11:27
>To: ASP Databases
>Subject: [asp_databases] RE: SQL Insert Statement with Null Date Values
>
>
>Kim,
>
>thanks, this does the trick.
>
>Now.....just out of interest, how would you detect the field types
>automatically? :)
>
>Martin
>
>At 07:07 4/19/2002 +0200, you wrote:
> >The question is if you're allowed to do that - ## isn't a date, neither is
> ># #
> >
> >I'm not sure if there are any better ways to get around it than building a
> >prefixSQL and a suffixSQL string and then check every single date, numeric
> >and boolean field as well as all text fields that can't contain zero-length
> >strings for null values something like this:
> >
> >
> >'initiate the prefix- and suffixSQL strings..
> >prefixSQL = "INSERT INTO " & strTableName & " ("
> >suffixSQL = ") VALUES (
> >
> >'start building the strings
> >if not isnull(arrUpdateData(1,i)) then
> > prefixSQL = prefixSQL & "fieldname1,"
> > suffixSQL = suffixSQL & arrUpdateData(1,i) & ","
> >end if
> >if not isnull(arrUpdateData(2,i)) then
> > prefixSQL = prefixSQL & "fieldname2,"
> > suffixSQL = suffixSQL & "#" & arrUpdateData(2,i) & "#,"
> >end if
> >
> >'getting rid of the last comma in each of the strings..
> >prefixSQL = left(prefixSQL,len(prefixSQL)-1)
> >suffixSQL = left(suffixSQL,len(suffixSQL)-1)
> >
> >'and eventually combining the strings and adding the closing bracket..
> >strSQL = prefixSQL & suffixSQL & ");"
> >
> >You can create some more versatile functions too, of course, to detect the
> >types of fields automatically, plus more.
> >
> >-Kim
>
>
Message #2 by "Kim Iwan Hansen" <kimiwan@k...> on Fri, 19 Apr 2002 22:09:33 +0200
|
|
You can do that using adox - Here are a couple of links...
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/
admscadoapireference.asp
http://www.4guysfromrolla.com/webtech/013101-1.shtml
-Kim
-----Original Message-----
From: Martin Lee [mailto:access@o...]
Sent: 19. april 2002 11:27
To: ASP Databases
Subject: [asp_databases] RE: SQL Insert Statement with Null Date Values
Kim,
thanks, this does the trick.
Now.....just out of interest, how would you detect the field types
automatically? :)
Martin
At 07:07 4/19/2002 +0200, you wrote:
>The question is if you're allowed to do that - ## isn't a date, neither is
># #
>
>I'm not sure if there are any better ways to get around it than building a
>prefixSQL and a suffixSQL string and then check every single date, numeric
>and boolean field as well as all text fields that can't contain zero-length
>strings for null values something like this:
>
>
>'initiate the prefix- and suffixSQL strings..
>prefixSQL = "INSERT INTO " & strTableName & " ("
>suffixSQL = ") VALUES (
>
>'start building the strings
>if not isnull(arrUpdateData(1,i)) then
> prefixSQL = prefixSQL & "fieldname1,"
> suffixSQL = suffixSQL & arrUpdateData(1,i) & ","
>end if
>if not isnull(arrUpdateData(2,i)) then
> prefixSQL = prefixSQL & "fieldname2,"
> suffixSQL = suffixSQL & "#" & arrUpdateData(2,i) & "#,"
>end if
>
>'getting rid of the last comma in each of the strings..
>prefixSQL = left(prefixSQL,len(prefixSQL)-1)
>suffixSQL = left(suffixSQL,len(suffixSQL)-1)
>
>'and eventually combining the strings and adding the closing bracket..
>strSQL = prefixSQL & suffixSQL & ");"
>
>You can create some more versatile functions too, of course, to detect the
>types of fields automatically, plus more.
>
>-Kim
Message #3 by Martin Lee <access@o...> on Fri, 19 Apr 2002 17:26:40 +0800
|
|
Kim,
thanks, this does the trick.
Now.....just out of interest, how would you detect the field types
automatically? :)
Martin
At 07:07 4/19/2002 +0200, you wrote:
>The question is if you're allowed to do that - ## isn't a date, neither is
># #
>
>I'm not sure if there are any better ways to get around it than building a
>prefixSQL and a suffixSQL string and then check every single date, numeric
>and boolean field as well as all text fields that can't contain zero-length
>strings for null values something like this:
>
>
>'initiate the prefix- and suffixSQL strings..
>prefixSQL = "INSERT INTO " & strTableName & " ("
>suffixSQL = ") VALUES (
>
>'start building the strings
>if not isnull(arrUpdateData(1,i)) then
> prefixSQL = prefixSQL & "fieldname1,"
> suffixSQL = suffixSQL & arrUpdateData(1,i) & ","
>end if
>if not isnull(arrUpdateData(2,i)) then
> prefixSQL = prefixSQL & "fieldname2,"
> suffixSQL = suffixSQL & "#" & arrUpdateData(2,i) & "#,"
>end if
>
>'getting rid of the last comma in each of the strings..
>prefixSQL = left(prefixSQL,len(prefixSQL)-1)
>suffixSQL = left(suffixSQL,len(suffixSQL)-1)
>
>'and eventually combining the strings and adding the closing bracket..
>strSQL = prefixSQL & suffixSQL & ");"
>
>You can create some more versatile functions too, of course, to detect the
>types of fields automatically, plus more.
>
>-Kim
>
>
>
>
>-----Original Message-----
>From: Drew, Ron [mailto:RDrew@B...]
>Sent: 19. april 2002 04:50
>To: ASP Databases
>Subject: [asp_databases] RE: SQL Insert Statement with Null Date Values
>
>
>
>Use IsNull function - syntax:
>If IsNull(objField.Value) then
> Response.Write(" ")
>End If
>
> > How to write the codes to insert a [ ] if the field is [null].
>
>-----Original Message-----
>From: Martin Lee [mailto:access@o...]
>Sent: Thursday, April 18, 2002 9:42 PM
>To: ASP Databases
>Subject: [asp_databases] SQL Insert Statement with Null Date Values
>
>
>
>Have a function which updates an Access 2000 table from another Access
>2000
>database.
>
>The below works fine, until PostDate contains a Null Value (which is
>frequently the case) and we get the error:
>
>Microsoft JET Database Engine error '80040e07'
>Syntax error in date in query expression '##'.
>
>For i = 0 to intRowCount
>
>SQL = "Insert Into " & strTableName
>SQL = SQL & " (MailId, AccountNo, DateRcv, PostDate, MailType, Origin,
>Comments, Status, ForwardingID) Values ("
>SQL = SQL & arrUpdateData(0,i) & ", "
>SQL = SQL & "'" & arrUpdateData(1,i) & "', "
>SQL = SQL & "#" & arrUpdateData(2,i) & "#, "
>SQL = SQL & "#" & arrUpdateData(3,i) & "#, " ' Problem with Null Values
>here SQL = SQL & "'" & arrUpdateData(4,i) & "', " SQL = SQL & "'" &
>arrUpdateData(5,i) & "', " SQL = SQL & "'" & arrUpdateData(6,i) & "', "
>SQL = SQL & "'" & arrUpdateData(7,i) & "', " SQL = SQL &
>arrUpdateData(8,i) & ")"
>
>objconn.Execute (SQL)
>
>Next
>
>
>
>
>
Message #4 by Martin Lee <access@o...> on Fri, 19 Apr 2002 14:31:32 +0800
|
|
Ron,
do you mean using:
If Is NotNull(arrUpdateData(3,i)) Then SQL = SQL & "#" &
arrUpdateData(3,i) & "#, " '
Or if is null, inserting a space?
Would prefer, if possible, to maintain the original format. So when
querying the table a null value is returned if no date.
Martin
At 10:49 4/18/2002 -0400, you wrote:
>Use IsNull function - syntax:
>If IsNull(objField.Value) then
> Response.Write(" ")
>End If
>
> > How to write the codes to insert a [ ] if the field is [null].
>
>-----Original Message-----
>From: Martin Lee [mailto:access@o...]
>Sent: Thursday, April 18, 2002 9:42 PM
>To: ASP Databases
>Subject: [asp_databases] SQL Insert Statement with Null Date Values
>
>
>
>Have a function which updates an Access 2000 table from another Access
>2000
>database.
>
>The below works fine, until PostDate contains a Null Value (which is
>frequently the case) and we get the error:
>
>Microsoft JET Database Engine error '80040e07'
>Syntax error in date in query expression '##'.
>
>For i = 0 to intRowCount
>
>SQL = "Insert Into " & strTableName
>SQL = SQL & " (MailId, AccountNo, DateRcv, PostDate, MailType, Origin,
>Comments, Status, ForwardingID) Values ("
>SQL = SQL & arrUpdateData(0,i) & ", "
>SQL = SQL & "'" & arrUpdateData(1,i) & "', "
>SQL = SQL & "#" & arrUpdateData(2,i) & "#, "
>SQL = SQL & "#" & arrUpdateData(3,i) & "#, " ' Problem with Null Values
>here SQL = SQL & "'" & arrUpdateData(4,i) & "', " SQL = SQL & "'" &
>arrUpdateData(5,i) & "', " SQL = SQL & "'" & arrUpdateData(6,i) & "', "
>SQL = SQL & "'" & arrUpdateData(7,i) & "', " SQL = SQL &
>arrUpdateData(8,i) & ")"
>
>objconn.Execute (SQL)
>
>Next
>
>
>
Message #5 by "Kim Iwan Hansen" <kimiwan@k...> on Fri, 19 Apr 2002 07:07:28 +0200
|
|
The question is if you're allowed to do that - ## isn't a date, neither is
# #
I'm not sure if there are any better ways to get around it than building a
prefixSQL and a suffixSQL string and then check every single date, numeric
and boolean field as well as all text fields that can't contain zero-length
strings for null values something like this:
'initiate the prefix- and suffixSQL strings..
prefixSQL = "INSERT INTO " & strTableName & " ("
suffixSQL = ") VALUES (
'start building the strings
if not isnull(arrUpdateData(1,i)) then
prefixSQL = prefixSQL & "fieldname1,"
suffixSQL = suffixSQL & arrUpdateData(1,i) & ","
end if
if not isnull(arrUpdateData(2,i)) then
prefixSQL = prefixSQL & "fieldname2,"
suffixSQL = suffixSQL & "#" & arrUpdateData(2,i) & "#,"
end if
'getting rid of the last comma in each of the strings..
prefixSQL = left(prefixSQL,len(prefixSQL)-1)
suffixSQL = left(suffixSQL,len(suffixSQL)-1)
'and eventually combining the strings and adding the closing bracket..
strSQL = prefixSQL & suffixSQL & ");"
You can create some more versatile functions too, of course, to detect the
types of fields automatically, plus more.
-Kim
-----Original Message-----
From: Drew, Ron [mailto:RDrew@B...]
Sent: 19. april 2002 04:50
To: ASP Databases
Subject: [asp_databases] RE: SQL Insert Statement with Null Date Values
Use IsNull function - syntax:
If IsNull(objField.Value) then
Response.Write(" ")
End If
> How to write the codes to insert a [ ] if the field is [null].
-----Original Message-----
From: Martin Lee [mailto:access@o...]
Sent: Thursday, April 18, 2002 9:42 PM
To: ASP Databases
Subject: [asp_databases] SQL Insert Statement with Null Date Values
Have a function which updates an Access 2000 table from another Access
2000
database.
The below works fine, until PostDate contains a Null Value (which is
frequently the case) and we get the error:
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '##'.
For i = 0 to intRowCount
SQL = "Insert Into " & strTableName
SQL = SQL & " (MailId, AccountNo, DateRcv, PostDate, MailType, Origin,
Comments, Status, ForwardingID) Values ("
SQL = SQL & arrUpdateData(0,i) & ", "
SQL = SQL & "'" & arrUpdateData(1,i) & "', "
SQL = SQL & "#" & arrUpdateData(2,i) & "#, "
SQL = SQL & "#" & arrUpdateData(3,i) & "#, " ' Problem with Null Values
here SQL = SQL & "'" & arrUpdateData(4,i) & "', " SQL = SQL & "'" &
arrUpdateData(5,i) & "', " SQL = SQL & "'" & arrUpdateData(6,i) & "', "
SQL = SQL & "'" & arrUpdateData(7,i) & "', " SQL = SQL &
arrUpdateData(8,i) & ")"
objconn.Execute (SQL)
Next
Message #6 by Martin Lee <access@o...> on Fri, 19 Apr 2002 09:42:00 +0800
|
|
Have a function which updates an Access 2000 table from another Access 2000
database.
The below works fine, until PostDate contains a Null Value (which is
frequently the case) and we get the error:
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '##'.
For i = 0 to intRowCount
SQL = "Insert Into " & strTableName
SQL = SQL & " (MailId, AccountNo, DateRcv, PostDate, MailType, Origin,
Comments, Status, ForwardingID) Values ("
SQL = SQL & arrUpdateData(0,i) & ", "
SQL = SQL & "'" & arrUpdateData(1,i) & "', "
SQL = SQL & "#" & arrUpdateData(2,i) & "#, "
SQL = SQL & "#" & arrUpdateData(3,i) & "#, " ' Problem with Null Values here
SQL = SQL & "'" & arrUpdateData(4,i) & "', "
SQL = SQL & "'" & arrUpdateData(5,i) & "', "
SQL = SQL & "'" & arrUpdateData(6,i) & "', "
SQL = SQL & "'" & arrUpdateData(7,i) & "', "
SQL = SQL & arrUpdateData(8,i) & ")"
objconn.Execute (SQL)
Next
Message #7 by "Drew, Ron" <RDrew@B...> on Thu, 18 Apr 2002 22:49:45 -0400
|
|
Use IsNull function - syntax:
If IsNull(objField.Value) then
Response.Write(" ")
End If
> How to write the codes to insert a [ ] if the field is [null].
-----Original Message-----
From: Martin Lee [mailto:access@o...]
Sent: Thursday, April 18, 2002 9:42 PM
To: ASP Databases
Subject: [asp_databases] SQL Insert Statement with Null Date Values
Have a function which updates an Access 2000 table from another Access
2000
database.
The below works fine, until PostDate contains a Null Value (which is
frequently the case) and we get the error:
Microsoft JET Database Engine error '80040e07'
Syntax error in date in query expression '##'.
For i =3D 0 to intRowCount
SQL =3D "Insert Into " & strTableName
SQL =3D SQL & " (MailId, AccountNo, DateRcv, PostDate, MailType, Origin,
Comments, Status, ForwardingID) Values ("
SQL =3D SQL & arrUpdateData(0,i) & ", "
SQL =3D SQL & "'" & arrUpdateData(1,i) & "', "
SQL =3D SQL & "#" & arrUpdateData(2,i) & "#, "
SQL =3D SQL & "#" & arrUpdateData(3,i) & "#, " ' Problem with Null
Values
here SQL =3D SQL & "'" & arrUpdateData(4,i) & "', " SQL =3D SQL & "'" &
arrUpdateData(5,i) & "', " SQL =3D SQL & "'" & arrUpdateData(6,i) & "',
"
SQL =3D SQL & "'" & arrUpdateData(7,i) & "', " SQL =3D SQL &
arrUpdateData(8,i) & ")"
objconn.Execute (SQL)
Next
|
|
|
View Cart
