CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    34

    input info from excel to a database

    hi
    this is my code for pulling the info from the excel spreadsheet
    is seem to work so happy days
    Code:
    Dim xl As New Excel.Application  
      
        Dim xlsheet As Excel.Worksheet  
      
        Dim xlwbook As Excel.Workbook  
      
       
      
       ' Const SHEET_NAME As String = "CreditRatings"  
      
        Dim i As Integer  
      
        Dim j As Integer  
      
        Dim sBuffer As String  
      
        Dim LastRowColB As Integer  
      
        Dim fn As Integer  
      
      
        Set xlwbook = xl.Workbooks.Open("c:\CreditRatingsReport.xls")  
      
       Set xlsheet = xlwbook.Sheets.Item("CreditRatings")  
      
      
      
        LastRowColB = xl.Range("B65536").End(xlUp).row  
      
        fn = FreeFile  
      
        'Open sOUTPUT_PATH & "\Ivory_SecuritiesIssued" & ".csv" For Append As #fn  
      
        For i = 2 To LastRowColB  
      
            sBuffer = ""  
      
            For j = 1 To 24  
      
      
      
            If IsDate(xlsheet.Cells(i, j)) Then  
      
                sBuffer = sBuffer & Format(xlsheet.Cells(i, j)) & "|"  
      
            Else  
      
                sBuffer = sBuffer & Trim(xlsheet.Cells(i, j)) & "|"  
      
            End If  
      
                  
      
            Next j  
      
           ' MsgBox sBuffer  
      
        Next i
    now its the next part i cant get i need to put that into the table. but i have to stick to the structure in here so can any one help please.
    i need to get to to put the info into the table buy doing it something like this
    Code:
    DBCALL "SELECT", "ACRT", Cmd$ -- calls the table in the database  
      
         
     Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )  
          
          
        
          
         DBCALL "add", "ACRT", Cmd$ adds the info into tthe table  
              
        If result <> dbSUCCESS And result <> 100 Then  
                    MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running."  
                    Message 43  
                    result = 1  
                      
        End If
    its the middle part i can figure out the insert into table part anyone have any ideas

  2. #2
    Join Date
    Oct 2011
    Posts
    34

    Re: input info from excel to a database

    really stuck on this can anyone help

  3. #3
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: input info from excel to a database

    Well for one thing this statement is missing a lot of required info
    Code:
     Cmd$ = "INSERT INTO ACRT (CNO, MoodysRate, SNPRate, FITCHRate, MoodysWatch, snPWatch, FITCHWatch, MoodysIRB) Values )
    It should be
    Code:
    Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the first part)

    I have no idea what this line is actually doing
    Code:
    DBCALL "add", "ACRT", Cmd$ adds the info into tthe table
    And here you are checking result but you have not assigned result=anything so this is not goin to work either unless of course result is defined as a public variable and is assigned in the DBCall routine
    Code:
    If result <> dbSUCCESS And result <> 100 Then  
                    MsgBox "Data Base Unavailable or Unexpected error in Data Base or batch running."  
                    Message 43  
                    result = 1  
                      
        End If
    Always use [code][/code] tags when posting code.

  4. #4
    Join Date
    Oct 2011
    Posts
    34

    Re: input info from excel to a database

    hi the part im missing alot is really the part im stuck on.
    for this part
    Code:
    It should be 
    
    Cmd$ = "Insert Into TableName ( Your field names as above) Values ( The actual values for each field listed in the
    i want the values to be entered automatically. its a big long spreadsheet and this will be run on a daily basic and the spreadsheet values will change
    is an array the best way to get this done or is there a better way

  5. #5
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    Re: input info from excel to a database

    The values would be variables that you would read from the spread sheet then they would be inserted into the database. This would be done in a loop so it calls the insert for each new record. Keep in mind Insert is only for adding new records it is not for updating existing records.

    Of course if Your goal is to have lots of data entered into a database where people are entering new records all the time the best way would be to drop Excel from the equation and create a vb program for data entry/edited that talks directly to the database.
    Always use [code][/code] tags when posting code.

  6. #6
    Join Date
    Oct 2011
    Posts
    34

    Re: input info from excel to a database

    thanks will give it a go.
    info will be deleted from table each time we do an add.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  





Click Here to Expand Forum to Full Width

Featured