Access query from VB.NET - To insert data though they are NULL

Question

Hi

I need to insert data from DB to another DB. I run this query from VB.NET:

for example:

Insert into DBDestino.tablaDest (campo1,campo2)
select valor1,valor2 
from DBOrigen.tablaOrigen 

Field "campo1" is integer (in DBdestino)

But sometimes the value "valor1" (in DBOrigen) is NULL. If I run the previous query, it returns error and does not insert any data.

How I can insert data though "valor1" sometimes is NULL?

Answer 1

Check out this link http://articles.techrepublic.com.com/5100-10878_11-6125114.html

Answer 2

I doubt your problem is due to Null values in valor1, unless the campo1 field rejects Nulls.

You need an INSERT statement which Access' database engine will accept. For example, this statement executed in Access with DBDestino.mdb open:

INSERT INTO tablaDest ( campo1, campo2 )
SELECT valor1, valor2
FROM tablaOrigen IN 'C:\Access\DBOrigen.mdb';

I'm not proficient with VB.Net, but I think you can open a connection to DBDestino.mdb, and then execute the same INSERT statement which works in Access.

Imports System.Data.OleDb
Module Module1
    Dim cn As OleDbConnection
    Dim cmd As OleDbCommand
    Dim icount As Integer
    Dim strInsert As String
    Sub Main()
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" & _
                                 "Data Source=C:\Access\DBDestino.mdb;")
        cn.Open()
        strInsert = "INSERT INTO tablaDest ( campo1, campo2 ) " & _
            "SELECT valor1, valor2 " & _
            "FROM tablaOrigen IN 'C:\Access\DBOrigen.mdb';"
        cmd = New OleDbCommand(strInsert, cn)
        icount = cmd.ExecuteNonQuery
        Debug.Print(icount)
        cn.Close()
    End Sub
End Module

I tested that on my system, and the values from tablaOrigen were successfully inserted into tablaDest, Nulls included.