Syntax to write a null value inside a sql Insert string?

Is there a way to represent the null value as a string (destination: varchar 
column) the way that the following string represents a null value for a 
date?

   Public Const cNullDate As Date = #12:00:00 AM#



Writing to an Access 2003, (conversion) table with VS2005 in VB.NET.

Within the table is a field named VSubType1 with a validation rule of: "In 
('P','O') Or Is Null"



The declare/move/insert statements look like this (shortened example):



      Dim intRecNbr As Integer = 0

      Dim VSubType1 As Object  (defined as Text 1 column in Access)

       Dim V1_Mom_Id As Object  (Long Integer)

      Dim V1_Dad_Id As Object  (Long Integer)



         intRecNbr += 1

         VSubType1 = System.DBNull.Value

         V1_Mom_Id = 0

         V1_Dad_Id = 0



         'insert new row

         sql = "INSERT INTO Convert_Case " & _

                  "(`RecNbr`, `VSubType1`, `V1_Mom_Id`, `V1_Dad_Id`) " & _

         "VALUES (" & intRecNbr & ", '" & _

                  VSubType1 & "', " & _

                  V1_Mom_Id & ", " & _

                  V1_Dad_Id & ")"



         'insert

         Try

            Dim lngRecordsAffected As Long = 0

            cnnJet.Execute(sql, lngRecordsAffected, adCmdText + 
adExecuteNoRecords)



Is there any way to code the "sql =" statement to pass a null value for 
VSubType1?  Like, maybe setting VSubType1 = Ctrl+0 (as in SQL Server) 
instead of System.DBNull.Value?  Or, perhaps System.DBNull.Value is not the 
right value for null within Access db?



Thanks in advance,

Dean S


0
Dean
4/27/2007 10:33:26 PM
access 16762 articles. 3 followers. Follow

1 Replies
774 Views

Similar Articles

[PageSpeed] 26

Hi Dean

A text field can have two sorts of "null" value.

If the Required property is not set, then its value can be Null.  This 
corresponds to a VarType of 1 in VBA.  In SQL you use the keyword Null:
    Update ... Set MyField = Null
    Select ... where MyField is Null
    Insert into MyTable (MyField) values (Null)
    ... etc

If the AllowZeroLength property is set, then its value can be "" (an empty 
string).  This corresponds to the constant vbNullString in VBA.  In SQL you 
use two adjacent single or double quotes ("" or ''):
    Update ... Set MyField = ""
    Select ... where MyField = ''
    Insert into MyTable (MyField) values ("")
    ... etc

Note that the rule is similar for dates.  If the Required property is not 
set, then the value of a date field can be Null.  This is different from a 
value of zero, which corresponds to #30-Dec-1899 12:00:00 AM#.  I would 
always use a Null in preference to a zero date.
-- 
Good Luck  :-)

Graham Mandeno [Access MVP]
Auckland, New Zealand

"Dean Slindee" <slindee@charter.net> wrote in message 
news:kjvYh.1180$eO4.698@newsfe12.lga...
> Is there a way to represent the null value as a string (destination: 
> varchar column) the way that the following string represents a null value 
> for a date?
>
>   Public Const cNullDate As Date = #12:00:00 AM#
>
>
>
> Writing to an Access 2003, (conversion) table with VS2005 in VB.NET.
>
> Within the table is a field named VSubType1 with a validation rule of: "In 
> ('P','O') Or Is Null"
>
>
>
> The declare/move/insert statements look like this (shortened example):
>
>
>
>      Dim intRecNbr As Integer = 0
>
>      Dim VSubType1 As Object  (defined as Text 1 column in Access)
>
>       Dim V1_Mom_Id As Object  (Long Integer)
>
>      Dim V1_Dad_Id As Object  (Long Integer)
>
>
>
>         intRecNbr += 1
>
>         VSubType1 = System.DBNull.Value
>
>         V1_Mom_Id = 0
>
>         V1_Dad_Id = 0
>
>
>
>         'insert new row
>
>         sql = "INSERT INTO Convert_Case " & _
>
>                  "(`RecNbr`, `VSubType1`, `V1_Mom_Id`, `V1_Dad_Id`) " & _
>
>         "VALUES (" & intRecNbr & ", '" & _
>
>                  VSubType1 & "', " & _
>
>                  V1_Mom_Id & ", " & _
>
>                  V1_Dad_Id & ")"
>
>
>
>         'insert
>
>         Try
>
>            Dim lngRecordsAffected As Long = 0
>
>            cnnJet.Execute(sql, lngRecordsAffected, adCmdText + 
> adExecuteNoRecords)
>
>
>
> Is there any way to code the "sql =" statement to pass a null value for 
> VSubType1?  Like, maybe setting VSubType1 = Ctrl+0 (as in SQL Server) 
> instead of System.DBNull.Value?  Or, perhaps System.DBNull.Value is not 
> the right value for null within Access db?
>
>
>
> Thanks in advance,
>
> Dean S
>
> 


0
Graham
4/27/2007 11:39:22 PM
Reply:

Similar Artilces:

Counting cells with a value in them
We are running an "If" equation on a column to determine if an event i taking too long to do. The resulting equation will yield an "X" if i is taking to long and a " " if not. I would like to have an equatio calculate the number of "X" in the column. Any ideas? Chris Nelso -- chris ----------------------------------------------------------------------- chrisn's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=650 View this thread: http://www.excelforum.com/showthread.php?threadid=31880 We had a brain cramp and one of th...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

"there has been an error reading or writing the file"
I have been having numerous problems lately with my Mny delux 2005. 1. portfolio does not show correct "Market Value" of most securities most of the time, 2. Lines listing securities on portfolio manager are duplicated and change upon being floated over by the mouse curser and 3. the program blows out after displaying the error message, "there has been an error reading or writing the file" after folowing the intructions at: http://help.msn.com/(ZmlsdGVyPUNEX0RMWCZwcm9qZWN0PW1vbmV5MDUmbWFya2V0PWVuLVVTJmN1PSZ0bXQ9dG1wOUYudG1wIC0gTWljcm9zb2Z0IE1vbmV5JmtjPSZmb3JtYXQ9)/help...

Help creating a script in SQL or Calculated field in Crystal
How do I take this script and manipulate it to give me one long string with static text as well as SQL data in specific positions within the string. Example result: Positions / Data: 1-3 / 173 (Static text) 4-6 / spaces 7-10 / "X_UPR30300"."YEAR1", (has to show up as 2007. Showing up as 2,007) 11 / 4 (Static text) 12-22 / "UPR00100"."SOCSCNUM" 23-57 / "UPR00100"."LASTNAME" 58-92 / "UPR00100"."FRSTNAME" 93 / "UPR00100"."MIDLNAME" 94-120 / "UPR00102"."ADDRESS1" 121-148 / &q...

Want Input boxes to accept unicode strings on Standard Windows XP
I have a MFC application that is currently built with MBCS mode. If I run the program on a Chinese OS (Windows XP), the input boxes (Edit Controls) can accept Chinese chars and display correctly. If I run it on a standard English XP, the input boxes won't accept Chinese chars (display as "????") -- please note that I have already installed CKJ on the system and IE and Outlook can display Chinese correctly. Is this just because of different MFC libraries used for the application? Can I force the application running on Standard XP to use the unicode libraries so ...

OWA from inside the LAN
Good morning all, I have an E2K3 server with an internal IP address, and do port forwarding from a firewall/router to it from the outside world. From outside the company, I can hit http://mail.xxxx.com/exchange (my A record public address) and get into OWA after entering name/password. When I am inside the company, if I try the same exercise, I cannot get past the login prompt! It keeps popping up and never lets me into the OWA. I have also tried entering <domain>/username and that did not work either. mail.xxx.com is resolving to the external address (verified by a ping). If I try fro...

ADO syntax problem
Hi! I am using the following code to query access table. the code works fine. My problem is how to name the field name ([Cell]) as a string, in order to use this query as a dynamic query. I tried many things but nothing succeeded. Thanks for your help! Dim cnn As New ADODB.Connection Dim rst As New ADODB.Recordset cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=C:\Documents and Settings\Eli\My Documents\ETV_Cells.mdb" rst.Open "SELECT * FROM CellTable ;", cnn, adOpenStatic rst.MoveFirst strCellCode = rst![Cell] ...

Comparing Values In 2 Columns
Hi, i have 2 list of students names both of which are not up to date with each other and because of this one list has more students than the other. I want to search for students names and see if there is a match, if a match is found i need to copy the email address and paste it into the cell by the other name in the other list. See Below My spreadsheet has columns titled, (A)Display Name, (B)Display Email, (C)Sims Name and (D)Sims Email. I basically want to write a script that takes each individual display name in column A and searches in column C for an identical match, if a match is found i...

Does Multi-Field Index Work For Date/Time Values Only
I created a MS-Access DB table with the following 3 columns: ColID - PRIMARY KEY Col1 - Number Col2 - Date/Time Next I created a multi-field index using Col1 & Col2. I entered the following row in the 1st row: 1 5/10/2007 Next when I tried to add the above row again, as expected, I wasn't allowed to enter the same row. Next I entered the following 2 rows: 1 5/11/2007 2 5/10/2007 Both the rows were accepted. After this I deleted the 3 records, went back to the design view & changed the data type of Col2 from Date/Time to Number keeping the multi-field index...

CRM 1.2
I am going to move the CRM SQL databases to a new server. We will also be moving Great Plains off the CRM server and on to the new SQL server. We are bouncing around the idea of naming the new SQL server to the name that the current CRM server uses and renaming the current CRM server. The thinking here is that we won't have to touch the Great Plains clients. What kinds of issues can I expect in this scenario? I believe that CRM "might" make us of the SID in which case I would need to use SysPrep to create the new one? I wouldn't think that this is that unusal of a proje...

"with <string> in sender" clause works on substring (matches on too many senders)
Outlook 2002 SP-3 I don't send myself e-mails but spammers would like to pretend that I do. They often must put something in the From header to be RFC compliant, so they throw in the same e-mail address as they shoved in the To header. Say I have an account called smith@domain.tld. I define the rule: Apply after receiving a message with smith@domain.tld in sender permanently delete stop processing more rules Looks good until you realize the match is on a substring rather than an exact match on the sender's full e-mail address. The messages do get deleted if I sen...

can not insert a mail model in a compaign mail activity ?
Hello, When I am trying to send a simple mail, I can insert an email template. but when I plan a compaign based on mailing I can't make an email based on template ... So what what should I do to enable this functionality knowing that it's very intersting ability in a CRM Thanks in advance ...

SQL select statement question
My dataset has 3 columns: customer id, store location, charges I'd like to write a single select statment that will show each customer id once, with the store location where they have the most charges, and the sum of the charges for that store location. This gives the max charges for each customer, but doesn't give the location: select a.custID, max(new.amt) from smallchg a inner join; (select custID, location, sum(charges) as amt from smallchg group by custID, location) as new ; on a.custID= new.CustID group by a.CustID thanks. joel Try this -- SELECT TOP 1 ...

value of value of a variable.
I tried searching, but no use! I have a Const NameA = "BLA BLA" I have a variable NameB Value of NameB is NameA. How do i get the text "BLA BLA" from NameB variable Is there anyway to do that? something like,, VALUE(NameB) Thanks & Regards Joe Hi Dim NameB as String NameB =3D NameA in a cell you could have range("A1").Value =3D NameB would now have content "BLA BLA" regards Paul On May 21, 12:35=A0pm, Joe <joe.varghese.j...@gmail.com> wrote: > I tried searching, but no use! > > I have a Const Name...

Table-like Outline and Collumm that sums prevous values up
Hello The report I would like to have should be in a tabular form just like the doc document I have attached. However, until now I have not found out how to set the report into a tabular outline. It is quite uncomfortable and time intensive to set up this report by using lines and the different text fields. In addition, it will not look any good at the end.. Also, I would need a columm that is able to sum up the values before. Just like the "total" under each semester. Are the issues solvable? Greets, Hubertholz attached: http://rapidshare.com/files/35708241...

Insert a graphic in a cell
I'd like to be able to enbed a logo in a cell. Not possible. Graphic objects reside on the drawing layer "above" the cells. Cells can contain formulae or values only. Workaround. Insert your graphic, and size it so that it exactly covers the cell. Right-click it, choosing Format Picture. In the Properties tab of the Format Picture dialog, choose the Move and Size with cells radio button. In article <F253C07B-E71A-445E-B612-0189187A09D9@microsoft.com>, Pete_Escher fan <Pete_Escher fan@discussions.microsoft.com> wrote: > I'd like to be able to enbed a...

how can I show repeating values in a chart?
I would like to show modes in the form of a pie chart but am not sure how. For example the number 73 comes up 3 times in a column on my spreadsheet, how can I show that compared to the number 50 which come up 2 times in the sheet? Thanks for the help Hi, You will need to compute those values using formula or a pivot table and then chart the results. Cheers Andy Cindy wrote: > I would like to show modes in the form of a pie chart but am not sure how. > For example the number 73 comes up 3 times in a column on my spreadsheet, how > can I show that compared to the number 50 whi...

Automatically update Value for data label
Hello I am using Excel 2003 SP2, and have some graphs which have the value (data label) for the last month. Each month new data is entered and the data label has to be deleted for the previous month and the data label for the most recent month added (it still uses the same old data - new data is only entered for the most recent month). Is there any way where the data label can automatically update with the most recent months value (as the chart updates itself automatically currently). Any ideas appreciated. Thank you in advance. Regards, Nav ...

sessionclass.variable syntax?
What is the correct syntax for using the SessionClass.Variable property, specifically to set it to a particular value so I can then use this value in another add-in that fires later Thank you kevin ...

vbscript insert into access 2003 database with two different table
I am trying to insert data collected by WMI. Here is the script On Error Resume Next Const HKEY_CURRENT_USER = &H80000001 Const HKEY_LOCAL_MACHINE = &H80000002 Const ForReading = 1 'Create FSO Set objFSO = CreateObject("Scripting.FileSystemObject") 'Create an environment for the script to work Set wshshell = WScript.CreateObject("WScript.Shell") 'Connection to the database Set cnn = CreateObject("ADODB.Connection") 'Connection to a Recordset Set objRecordSet = CreateObject("ADODB.Recordset") 'Opens the Database ...

0.947694 Look Inside And Realize That Dream ... 0.2828604
1.178199E-02 Look Inside And Realize That Dream ... 9.059644E-02 0.947694 Now Visit http://nowclickhere.bravehost.com/index.html 0.2828604 ...

Thai collation in SQL for MS CRM 1.2
A number of our Partners have been requesting for double-byte characters such as Chinese, Japanese and Thai languages to be recognized by MS CRM 1.2. Most of their clients have this included in their business requirement. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane....

Auto insert of date
I'm trying to input some datas using preparedStatement and among those coiumns one column is of type "smalldatetime" i wish to input the current date and time at which i execute this query, how can i achieve this in JDBC or what is the sql query for this. I'm not sure if we're talking JET SQL or T-SQL or something else here? In JET SQL you want the Date() function, something like ... INSERT INTO Table1 ( TestDate ) SELECT Date() AS Expr1; If you're using T-SQL you want the GETDATE() function, something like ... INSERT INTO dbo.Table1 (Test...

Access 2007-Table not displaying the values from a combo box
I have built a database to schedule senior/disabled transportation appointments. I have created two tables and associated forms for data entry. I placed a combo box in the Schedule form so end users choose a client and fields complete on the form with client name, address, city, and home phone number. I did instruct Access to store the data in the SeniorIntakeID field in the Schedule table. I wrote a report that staff execute to see what is scheduled for a given date, for each driver. On the report I want to see the actual client's name, address, city, and home phone n...

Insert KB article in e-mail from outlook client
Hi there, I noticed I can send a KB article as an e-mail from the web client but not from within outlook? Is this correct (same goes for inserting e-mail templates) and if so, has anybody found a way to work around this?? Many thanks -- Aad van der Veld Senior CRM Consultant Capgemini Technology Services t: +31 (0)30 689 6047 m: +31 (0)6 150 30 749 e: aad.vandervelden@capgemini.com ...