Post reply

Insert Command Query

  • February 21, 2006 at 7:38 am

    #67233

    INSERT INTO Name(Surname, FirstName) FROM SELECT Surname, Name

    From OtherNames WHERE Age < 30;

    Hi,

    Just new for this kind of programming. I am using VB for the code below. I have a recordset (rs) that I want to insert in TestCorpFile where I dont want to insert rs!EXP_CTrY = 'vn'. I tried to follow the syntax I researched in the above of my message but I didn't get it right. Please need someones expertise. Thanks in advance. 

    rs.Open "Pacrim", cnCurrent, adOpenDynamic, adLockOptimistic, adCmdTable

    cnCurrent.Execute ("insert into TestCorpFile(Ctry_code,Ship_ID,LCamount,USDamount,Weekending)" & _

         " values ('" & rs!EXP_CTrY & "','" & rs!SHIPMENT_ID & "'," & rs!CONVERTED_AMOUNT & "," & rs!USD_AMOUNT & ",'" & rs!TDATE & "')")

  • February 21, 2006 at 8:50 am

    #622315

    When you create a SQL statement like this in VB it becomes (as you've noticed) very difficult to work out whether it's correct. I suggest you do something like the following

    dim strSQL as string

    strSQL = "insert into TestCorpFile(Ctry_code,Ship_ID,LCamount,USDamount,Weekending)" & _

         " values ('" & rs!EXP_CTrY & "','" & rs!SHIPMENT_ID & "'," & rs!CONVERTED_AMOUNT & "," & rs!USD_AMOUNT & ",'" & rs!TDATE & "')"

    msgbox strSQL

    and you'll be able to work out what's going on.  Then just

    cnCurrent.Execute(strSQL)

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply