[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 211
  • Last Modified:

problem inserting data into excel from a sql server .net data set

Hi guys.  I am using excel object to insert data from a .net data set into an excel workbook from vb.net.  I am getting the following error when I try to insert a column that is over a certain limit:

"The field is too small to accept the amount of data you attempted to add"

How do I get around this?  I am creating the excel file on the fly, so is there a way to programatically increase the field size in excel?  Here is the code I am using to write to excel.  Any help is greatly appreciated.

Ryan

'************************************************************************
'Create path for satisfied
            Dim ExcelPath As String
            ExcelPath = "c:\Comerica Files\Comments\Service Delight - Date From " + Replace(Format(StartDate, "MM/dd/yy"), "/", "-") + " To " + Replace(Format(EndDate, "MM/dd/yy"), "/", "-") + ".xls"

            '*****************  Create the excel objects and Write column headers ***********************
            'Name the file and location
            Dim oExcel As Object
            Dim oBook As Object
            Dim oSheet As Object

            'Start a new workbook in Excel.
            oExcel = CreateObject("Excel.Application")
            oBook = oExcel.Workbooks.Add

            oSheet = oBook.Worksheets(1)
            oSheet.Range("A1").Value = "Branch"
            oSheet.Range("B1").Value = "BranchName"
            oSheet.Range("C1").Value = "TransNum"
            oSheet.Range("D1").Value = "Name1"
            oSheet.Range("E1").Value = "Name2"
            oSheet.Range("F1").Value = "Comment"
            oSheet.Range("G1").Value = "Date_1"
            oSheet.Range("H1").Value = "Region"
            oSheet.Range("I1").Value = "StudyID"

            oBook.SaveAs(ExcelPath)
            oSheet = Nothing
            oBook = Nothing
            oExcel.Quit()
            oExcel = Nothing
            GC.Collect()
            '***************************************************************

            '********************  Populate the new workbook  ************
            'This data must be pu into memory because the connection is to the excel workbook, not the database
            strqry = " SELECT Branch, BranchName, TransNum, Name1, Name2, " & _
                     " Comment, Date_1, Region, StudyID " & _
                     " FROM tbl_Comment " & _
                     " WHERE date_1 between '" & StartDate & "' and '" & EndDate & "' " & _
                     " and CommentType = 'SATISFIED'"
            dsData = Utils.getSurvDataSet(strqry)

            'Establish a connection to the Excel data source.
            Dim sConnectionString As String
            sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                "Data Source=" & ExcelPath & ";Extended Properties=Excel 8.0;"
            Dim objConn As New System.Data.OleDb.OleDbConnection(sConnectionString)
            objConn.Open()

            'Add records to the table.
            Dim objCmd As New System.Data.OleDb.OleDbCommand()
            objCmd.Connection = objConn

            Dim x As Integer = 0
            With dsData.Tables(0)
                For x = 0 To dsData.Tables(0).Rows.Count - 1
                    objCmd.CommandText = " Insert into [Sheet1$] (Branch, BranchName, TransNum, Name1, " & _
                                         " Name2, Comment, Date_1, Region, StudyID)" & _
                                         " values ('" & Replace(.Rows(x).Item("Branch").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("BranchName").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("TransNum").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("Name1").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("Name2").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("Comment").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("Date_1").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("Region").ToString, "'", "''") & "', " & _
                                         " '" & Replace(.Rows(x).Item("StudyID").ToString, "'", "''") & "') "
                    objCmd.ExecuteNonQuery()
                Next
            End With
            objConn.Close()

'************************************************************************
0
dchau12
Asked:
dchau12
  • 2
  • 2
1 Solution
 
Anthony PerkinsCommented:
Post the structure of your table tbl_Comment
0
 
dchau12Author Commented:
I have debugged it down to the field 'comment'.  It is a type VarChar of length 8,000.  If the length of the data in the field is less than 50 characters it works fine.  Over 50 characters bombs it out.  Here is the structure:

2      CommentID      int      4      0
0      TransNum      int      4      1
0      date_1      datetime      8      1
0      Comment      varchar      8000      1
0      CommentType      varchar      50      1
0      Branch      char      5      1
0      Name1      varchar      100      1
0      Name2      varchar      100      1
0      HomePhone      varchar      10      1
0      DayPhone      varchar      10      1
0      StudyID      int      4      1
0      BranchName      varchar      100      1
0      Region      varchar      100      1
1      Market      varchar      100      1
0
 
dchau12Author Commented:
At any rate, I have decided to go with DTS packages to get the job done.  Thank you so much for looking at this though.

Ryan
0
 
Anthony PerkinsCommented:
>>I have debugged it down to the field 'comment'.  It is a type VarChar of length 8,000. <<
That is what I expected.  Unfortunately using DTS may not give you much relief:  There is a limitation of 255 characters in a column using Excel.

If you are going down the DTS path the following article, should be helpful.
http://www.sqldts.com/default.aspx?254

Unrelated, but I am not sure if you realize, but your table exceeds the maximum width for a row supported in MS SQL Server.  Try this is SQL Query Analyzer:

Create Table #Temp (
      CommentID int,
      TransNum int,
      date_1 datetime,
      Comment varchar(8000),
      CommentType varchar(50),
      Branch char(5),
      Name1 varchar(100),
      Name2 varchar(100),
      HomePhone varchar(10),
      DayPhone varchar(10),
      StudyID int,
      BranchName varchar(100),
      Region varchar(100),
      Market varchar(100))

Drop Table #Temp

And you will get the following error message:
Warning: The table '#Temp' has been created but its maximum row size (8635) exceeds the maximum number of bytes per row (8060). INSERT or UPDATE of a row in this table will fail if the resulting row length exceeds 8060 bytes.

The problem will only occur when you attempt to INSERT or UPDATE more than the maximum (8060) allowed.  The operation will fail.
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now