(2005) data transfare

[GARAKWAZVO]

Guys im getting an err msg (dataset can't be null)from the following code can somebody help. I want to expot my data from oracle to sql using my code. most wellcome will be suggestions to the code

conn.Open()
            'collecting from a database
            gerry = "SELECT STTM_CUST_PERSONAL.FIRST_NAME, STTM_CUST_ACCOUNT.CUST_AC_NO, STTM_CUST_ACCOUNT.ADDRESS1, STTM_CUST_ACCOUNT.ADDRESS2, STTM_CUST_ACCOUNT.ADDRESS3, STTM_CUST_ACCOUNT.ADDRESS4, STTM_CUST_PERSONAL.TELEPHONE, STTM_CUST_PERSONAL.LAST_NAME, STTM_CUST_PERSONAL.FAX, STTM_CUSTOMER.COUNTRY, STTM_CUST_PERSONAL.E_MAIL FROM STTM_CUST_ACCOUNT, STTM_CUSTOMER, STTM_CUST_PERSONAL WHERE STTM_CUSTOMER.FROZEN='N' AND STTM_CUSTOMER.DECEASED = 'N' AND STTM_CUSTOMER.WHEREABOUTS_UNKOWN = 'N'AND STTM_CUST_ACOUNT.CUST_NO = STTM_CUSTOMER.CUSTOMER_NO AND STTM_CUSTOMER.CUSTOMER_NO = STTM_CUST_PERSONAL.CUSTOMER_NO"
            da = New OracleDataAdapter(gerry, conn)
            da.Fill(ds, "gerry1")
            'FILLING THE NEW DATABASE
            myConnection.Open()
            myCommand = New SqlCommand("INSERT INTO accounts (FIRST_NAME, LAST_NAME, CUST_AC_NO, ADDRESS1, ADDRESS2, ADDRESS3, ADDRESS4, TELEPHONE, FAX, COUNTRY, E_MAIL) VALUES (gerry1.FIRST_NAME, gerry1.LAST_NAME, gerry1.CUST_AC_NO, gerry1.ADDRESS1, gerry1.ADDRESS2, gerry1.ADDRESS3, gerry1.ADDRESS4, gerry1.TELEPHONE, gerry1.FAX, gerry1.COUNTRY, gerry1.E_MAIL)", myConnection)
            ra = myCommand.ExecuteNonQuery()
            MessageBox.Show("Records transfered" & ra)
myconnection.close()
conn.close()

[si_the_geek]

I would recommend checking that your SQL statement is valid by running it in the database tools - I found at least one obvious typo (in a table name), and more that looked dubious.

Here is an altered version (I'm not sure if the Join syntax is valid for Oracle, so you may need to change that back):

SELECT     P.FIRST_NAME,
           A.CUST_AC_NO,
           A.ADDRESS1,
           A.ADDRESS2,
           A.ADDRESS3,
           A.ADDRESS4,
           P.TELEPHONE,
           P.LAST_NAME,
           P.FAX,
           C.COUNTRY,
           P.E_MAIL

FROM       STTM_CUST_ACCOUNT A
INNER JOIN STTM_CUSTOMER C ON (A.CUST_NO = C.CUSTOMER_NO)
INNER JOIN STTM_CUST_PERSONAL P ON (C.CUSTOMER_NO = P.CUSTOMER_NO)

WHERE     C.FROZEN = 'N'
          AND C.DECEASED = 'N'
          AND C.WHEREABOUTS_UNKOWN = 'N'

edit: I also doubt that your INSERT is valid, but we'll come to that later.

[GARAKWAZVO]

that was excellent it works nomore errors and now can you help on the insert statement

[si_the_geek]

A major issue is your use of gerry1 for getting the values - you cannot simply put a variable name into a string and expect it to use the value, you need to append the value to the string instead, eg:

.. VALUES ('" & gerry1.FIRST_NAME & "', '" & gerry1.LAST_NAME ...

(note that as I don't use .Net, this may need minor changes)

This is assuming that the fields are text/string, if they are numeric data types then do not use the ' characters around the values.


Note that a simpler (and more efficient) method in cases like this is to merge the two SQL statements.. simply use the first half of the Insert (everything before '[i]Values[i]'), followed by the Select statement.