inserting null values

[whocracks]

hi...

can you tel me how to insert null values in Access table using Vb code....

actually i want to store record having some fields with null values...plz....... i mean error is in query statement... saying "Invalid use of null value.." ... :)

[Hemant Pathak]

Hi
try to use this Query of inserting the value of Access Table

insert into tablename values('" & null & "'" & "'")

[Mahesh_Bote]

why r u inserting NULLS? If u wants to insert NULL in a column, don't insert value for the said field. instead of this field, insert values for other columns.

i.e. u have table with A, B, C, D columns, and if u want to insert NULL into B, then use

Insert Into <Table Name> (A, C, D) Values (<Values for A>, <Values for C>, <Values for D>


Mahesh
:) :) :)

[sashi]

hi here,

the best practise will be defining a default value during the design time of your table.. let it take care the rest.. take care..

[gman_00ph]

why r u inserting NULLS? If u wants to insert NULL in a column, don't insert value for the said field. instead of this field, insert values for other columns.

i.e. u have table with A, B, C, D columns, and if u want to insert NULL into B, then use

Insert Into <Table Name> (A, C, D) Values (<Values for A>, <Values for C>, <Values for D>


Mahesh
:) :) :)

What if there is a value in B
and i want to put a null value in B

at this point i cant update it
not unless i use to insert this value=" " to B

[thisbetom]

If you are passing the variables to a dataset its "dbnull.value"
daTableAdapter.Insert(PK.tostring, Column2Data.2String, dbnull.Value)

You might be able to concatenate it into a string update query as well

[Befo]

hi...

can you tel me how to insert null values in Access table using Vb code....

actually i want to store record having some fields with null values...plz....... i mean error is in query statement... saying "Invalid use of null value.." ... :)

try passing the null value (access keyword) as a string in the sql query
i.e.
sql1="INSERT INTO tblMiTabla (Col1,Col2,Col3,Col4)
sql2="VALUES (" & var1 & "," & var2 & "," & IIf(IsNull(var3),"Null",var3) & ")"

where var1,var2,var3,var4 are variable names

[pureenhanoi]

What if there is a value in B
and i want to put a null value in B

at this point i cant update it
not unless i use to insert this value=" " to B

Becarefully using of Null value. GMan_OOph have right way for inserting Null value. But this happent if B field type are text, datetime, or several other types. If B field type are number, Yes/No, they have default values so that u cant let it Null by this way. U must use

INSERT INTO TableName(A,B,C,D) VALUES(" & a & ",Null," & c & "," & d & ")" 

When Updating Null value, if u replace old value by new value, so there is no problem. But if u plant add new value into old value, so u must check it first.

sql = "SELECT * FROM tableName"
if rs.state<> 0 Then rs.Close
rs.Open sql, cnxn, 1,3
      If isNull(rs.Fields("B") Then
             rs.Fields("B") = newValue
      Else
             rs.Fields("B") = rs.Fields("B") + newValue
      End If
rs.Update
 

Remember, in ur database, select Require = No, and AllowNull = Yes for this field before u can set Null value for it.

[hariharanmca]

hi...

can you tel me how to insert null values in Access table using Vb code....

actually i want to store record having some fields with null values...plz....... i mean error is in query statement... saying "Invalid use of null value.." ... :)

strSql=""
strSql="Insert Into" &  <Table Name> 
strSql=strSql & "(A"
strSql=strSql & iif(isNull(<Values for B>),  "",  ",B")
strSql=strSql & iif(isNull(<Values for C>),  "",  ",C")
strSql=strSql & iif(isNull(<Values for D>),  "",  ",D")
strSql=strSql & ") Values (" & <Values for A>
strSql=strSql & iif(isNull(<Values for B>),  "",  "," & <Values for B>)
strSql=strSql & iif(isNull(<Values for C>),  "",  "," & <Values for C>)
strSql=strSql & iif(isNull(<Values for D>),  "",  "," & <Values for D>)
strSql=strSql & ")"

I think this will help you