none
INSERT HTML INTO SQL SERVER NOT WORKING RRS feed

  • Question

  • i am trying to insert html into sql server , im not getting any error but no data is inserted...

    below is my complete code

            FolderBrowserDialog1.ShowDialog()
    
            Dim PPP As String = FolderBrowserDialog1.SelectedPath()
    
            Dim files() As String = System.IO.Directory.GetFiles(PPP, "*.html", searchOption:=IO.SearchOption.AllDirectories)
    
            Dim line As String
    
    
    
            For Each f As String In files
    
              
                Using reader As StreamReader = New StreamReader(f)
                
                    line = reader.ReadToEnd
    
    
                End Using
    
                TextBox1.Text = line
    
    
    
    
                Dim query As String = "INSERT INTO [tbl_ccm-demo] VALUES (@StudyName,@Study)"
                Using con As New SqlConnection("Data Source=.;Initial Catalog=CCM;Integrated Security=True")
                    Using cmd As New SqlCommand(query, con)
                        cmd.Parameters.Add("@StudyName", SqlDbType.VarChar).Value = f.ToString()
                        cmd.Parameters.AddWithValue("@Study", SqlDbType.VarChar).Value = TextBox1.Text
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
    
                    End Using
    
                End Using
    
    
                TextBox1.Text = ""
    
            Next
    
    
            MessageBox.Show("Done")

    I am able of inserting the file name into the table but NOT the HTML that im first populating in a textbox...

    Wednesday, December 27, 2017 10:34 AM

Answers

  • Hello,

    As promised, here is a project that works. I set it up to do one file insert at a time which when placed into a loop will work also. I modified the table as shown below (there are two script, one with data, one without data in the project).

    CREATE TABLE [dbo].[tbl_ccm-demo](
    	[studyID] [INT] IDENTITY(1,1) NOT NULL,
    	[StudyName] [VARCHAR](50) NULL,
    	[Study] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_tbl_ccm-demo] PRIMARY KEY CLUSTERED 
    (
    	[studyID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    I used the file you supplied, inserted it twice (once with a slight change) and extracted to prove it worked with a different file name.

    Data class

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class DataOperations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "CCM"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private SqlConnectionString As String = ""
        Public Exception As Exception
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            SqlConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        ''' <summary>
        ''' Insert one file
        ''' </summary>
        ''' <param name="pFilePath"></param>
        ''' <param name="pNewIdentifier"></param>
        ''' <param name="pStudyName"></param>
        ''' <returns></returns>
        Public Function FilePutSimple(ByVal pFilePath As String, ByRef pNewIdentifier As Integer, ByVal pStudyName As String) As Boolean
            Dim fileByes() As Byte
    
            Using stream = New FileStream(pFilePath, FileMode.Open, FileAccess.Read)
                Using reader = New BinaryReader(stream)
                    fileByes = reader.ReadBytes(CInt(stream.Length))
                End Using
            End Using
    
            Using cn As New SqlConnection() With {.ConnectionString = SqlConnectionString}
                Dim statement = "INSERT INTO dbo.[tbl_ccm-demo] (Study,StudyName) VALUES (@Study,@StudyName);SELECT CAST(scope_identity() AS int);"
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cmd.Parameters.Add("@Study", SqlDbType.VarBinary, fileByes.Length).Value = fileByes
                    cmd.Parameters.AddWithValue("@StudyName", pStudyName)
    
                    Try
                        cn.Open()
    
                        pNewIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
                        Return True
    
                    Catch ex As Exception
                        Exception = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
        ''' <summary>
        ''' Extract one file
        ''' </summary>
        ''' <param name="pIdentifier"></param>
        ''' <param name="pFileName"></param>
        ''' <returns></returns>
        Public Function FileGetSimple(ByVal pIdentifier As Integer, ByVal pFileName As String) As Boolean
            Using cn As New SqlConnection() With {.ConnectionString = SqlConnectionString}
    
                Dim statement = "SELECT Study FROM dbo.[tbl_ccm-demo]  WHERE studyid = @id;"
    
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cmd.Parameters.AddWithValue("@id", pIdentifier)
    
                    Try
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
    
                        If reader.HasRows Then
                            reader.Read()
    
                            Dim ndx As Integer = reader.GetOrdinal("Study")
    
                            Dim blob = New Byte((reader.GetBytes(ndx, 0, Nothing, 0, Integer.MaxValue)) - 1) {}
                            reader.GetBytes(ndx, 0, blob, 0, blob.Length)
    
                            Using fs = New FileStream(pFileName, FileMode.Create, FileAccess.Write)
                                fs.Write(blob, 0, blob.Length)
                            End Using
                        Else
                            '
                            ' Identifier not found
                            '
                            Exception = New Exception($"Identifier: {pIdentifier} not found: {pFileName}")
                            Return False
                        End If
                        Return True
    
    
                    Catch ex As Exception
                        Exception = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
    End Class
    

    Form code

    Imports BackEnd
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim newIdentifier As Integer = 0
            Dim ops As New DataOperations
            If ops.FilePutSimple(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "hasHtml.txt"), newIdentifier, "Some description") Then
                MessageBox.Show($"New id: {newIdentifier}")
            Else
                MessageBox.Show(ops.Exception.Message)
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim ops = New DataOperations()
    
            '
            ' I first added to files, that means we have id's 1 and 2 but not 3
            ' so when we hit three the back end code throws a trapped exception
            ' and we read it without blowing up :-)
            '
            Dim identifierList As New List(Of Integer) From {1, 2, 3}
    
            Dim fileName = ""
    
            For Each id As Integer In identifierList
                fileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"extracted{id}.html")
                If ops.FileGetSimple(id, fileName) Then
                    MessageBox.Show($"Success: {fileName}")
                Else
                    MessageBox.Show($"Failed: {ops.Exception.Message}")
                End If
    
            Next
        End Sub
    End Class
    

    Results


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by GE_NK Friday, December 29, 2017 6:04 AM
    Friday, December 29, 2017 1:07 AM
    Moderator

All replies

  • INSERT INTO tbl_ccm-demo (col1, col2) VALUES (@StudyName,@Study)

    col1 and col2 you have to name yourself of course


    Success Cor

    Wednesday, December 27, 2017 11:01 AM
  • Also try ‘cmd.Parameters.AddWithValue("@Study", TextBox1.Text)’ or ‘cmd.Parameters.Add("@Study", SqlDbType.VarChar).Value = TextBox1.Text’.

    Wednesday, December 27, 2017 11:21 AM
  • Along with an incorrect SQL INSERT statement I would look at using one connection, one command with parameters set before performing the inserts.

    The method below, you would pass in data read in from the files into parameter 1, the information in TextBox1 in parameter 2.

    Note I'm using a class rather than performing the inserts in a form, this is a best practice, decouple data operation from form operations. It's cleaner and reusable. The default creation of the class sets the connection string while the other constructor permits you to override the connection with another one.

    Imports System.Data.SqlClient
    
    Public Class Operations
        Private mConnectionString As String
        Public Property HasException As Boolean
        Public Property LastException As Exception
        Public Sub New()
            mConnectionString = "Data Source=.;Initial Catalog=CCM;Integrated Security=True"
        End Sub
        Public Sub New(ByVal pConnectionString As String)
            mConnectionString = pConnectionString
        End Sub
        ''' <summary>
        ''' If the method returns false then check HasException, if HasException
        ''' is true then LastException has the reason for failure
        ''' </summary>
        ''' <param name="pStudy">Array of lines from file</param>
        ''' <param name="pData">In your code this was TextBox1.Text</param>
        ''' <returns></returns>
        ''' <remarks>
        ''' Uses one connection, one command, setup parameters once.
        ''' </remarks>
        Public Function AddRecords(ByVal pStudy As String, ByVal pData As String()) As Boolean
    
            Using cn As New SqlConnection With {.ConnectionString = mConnectionString}
                Using cmd As New SqlCommand With {.Connection = cn}
    
                    cmd.CommandText = "INSERT INTO tbl_ccm-demo (StudyName, Study) VALUES (@StudyName,@Study)"
    
                    cmd.Parameters.Add("@StudyName", SqlDbType.VarChar)
                    cmd.Parameters.Add("@Study", SqlDbType.VarChar)
    
                    Try
    
                        cn.Open()
    
                        For Each item As String In pStudy
                            cmd.Parameters("@StudyName").Value = item
                            cmd.Parameters("@Study").Value = pData
                            cmd.ExecuteNonQuery()
                        Next
    
                    Catch ex As Exception
                        HasException = True
                        LastException = ex
                        Return False
                    End Try
    
                End Using
            End Using
    
            Return True
    
        End Function
    End Class


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Wednesday, December 27, 2017 11:45 AM
    Moderator
  • of course I have to write the column names of the table , sorry i missed typing them ... any way still stuck in the same problem.....
    Wednesday, December 27, 2017 3:08 PM
  • I tried both and still nothing.... only the studyname fields is filled ....
    Wednesday, December 27, 2017 3:09 PM
  • this is a winform , I am getting the names of the files after opening the folderbrowser dialog....

     im sorry i typed the sql statement igonring the columns... anyhow still im not able to write the html to the filed , only the study name . I even tried :

     Dim htmlEncoded As String = WebUtility.HtmlEncode(line)

    and still nothing.....

    Wednesday, December 27, 2017 3:12 PM
  • of course I have to write the column names of the table , sorry i missed typing them ... any way still stuck in the same problem.....

    Yea it was not the only problem

     cmd.Parameters.AddWithValue("@StudyName", f.ToString)
     cmd.Parameters.AddWithValue("@Study",  TextBox1.Text)


    Success Cor

    Wednesday, December 27, 2017 3:14 PM
  • Give some details about the column. Is it a text having corresponding size? Try writing some short text instead of HTML. Also show how did you determine that it does not contain any value.

    Wednesday, December 27, 2017 5:12 PM
  • Hi GE_NK,

    Because you want to insert html into sql server database, I can see there are two field from code, one is StudyName, store the html file path , another is Study, store the contents of html file. If the contents of html file is very large, you could get the following error using try.. catch..

    string or binary data would be truncated, the statement has been terminated

    So you could need to take care of the Study field data type size in sql server.

    I do one test at my side, you can take a look:

    The DataTable in SQL server:

    FolderBrowserDialog1.ShowDialog()
            Dim PPP As String = FolderBrowserDialog1.SelectedPath()
            Dim files() As String = System.IO.Directory.GetFiles(PPP, "*.html", searchOption:=IO.SearchOption.AllDirectories)
            Dim line As String
            For Each f As String In files
                Using reader As StreamReader = New StreamReader(f)
                    line = reader.ReadToEnd
                End Using
                TextBox1.Text = line
                Try
                    Dim query As String = "INSERT INTO Test3 VALUES (@StudyName,@Study)"
                    Using con As New SqlConnection("Data Source=(LOCALDB)\MSSQLLOCALDB;Integrated Security=True")
                        Using cmd As New SqlCommand(query, con)
                            cmd.Parameters.Add("@StudyName", SqlDbType.VarChar).Value = f.ToString()
                            cmd.Parameters.AddWithValue("@Study", SqlDbType.VarChar).Value = TextBox1.Text
                            con.Open()
                            cmd.ExecuteNonQuery()
                            'con.Close()
                        End Using
                    End Using
                    TextBox1.Text = ""
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
    
            Next
            MessageBox.Show("Done")

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.


    Thursday, December 28, 2017 2:32 AM
    Moderator
  • I am using nvarchar (MAX) and still nothing is written to the Study Field , i am re sending my complete code and the database table design.... :(

    Imports System.Data.SqlClient
    Imports System.IO
    Imports System.Net
    
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    
            Dim con As New SqlConnection("Data Source=.;Initial Catalog=CCM;Integrated Security=True")
    
    
            FolderBrowserDialog1.ShowDialog()
    
            Dim PPP As String = FolderBrowserDialog1.SelectedPath()
    
            Dim files() As String = System.IO.Directory.GetFiles(PPP, "*.html", searchOption:=IO.SearchOption.AllDirectories)
    
            Dim line As String
    
    
    
            For Each f As String In files
    
                
                Using reader As StreamReader = New StreamReader(f)
    
                    line = reader.ReadToEnd
    
    
                End Using
    
    
    
                Dim htmlEncoded As String = WebUtility.HtmlEncode(line)
    
    
                Try
    
                    Dim query As String = "INSERT INTO [tbl_ccm-demo] (StudyName,Study) VALUES (@StudyName,@Study)"
    
                    Using cmd As New SqlCommand(query, con)
                        cmd.Parameters.Add("@StudyName", SqlDbType.VarChar).Value = f.ToString()
                        cmd.Parameters.AddWithValue("@Study", SqlDbType.NVarChar).Value = htmlEncoded
                        con.Open()
                        cmd.ExecuteNonQuery()
                        con.Close()
    
                    End Using
    
    
    
                Catch ex As Exception
                    MessageBox.Show(ex.Message)
                End Try
    
    
            Next
    
    
            MessageBox.Show("Done")
    
    
        End Sub
    
    USE [CCM]
    GO
    
    
    DROP TABLE [dbo].[tbl_ccm-demo]
    GO
    
    
    SET ANSI_NULLS ON
    GO
    
    SET QUOTED_IDENTIFIER ON
    GO
    
    SET ANSI_PADDING ON
    GO
    
    CREATE TABLE [dbo].[tbl_ccm-demo](
    	[studyID] [int] IDENTITY(1,1) NOT NULL,
    	[StudyName] [varchar](50) NULL,
    	[Study] [nvarchar](max) NULL,
     CONSTRAINT [PK_tbl_ccm-demo] PRIMARY KEY CLUSTERED 
    (
    	[studyID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
    
    GO
    
    SET ANSI_PADDING OFF
    GO


    • Edited by GE_NK Thursday, December 28, 2017 6:45 AM
    Thursday, December 28, 2017 6:44 AM
  • Yes but there is an error in your code for which I showed a solution. 

    What you did with it. "Nothing" so why are you asking here?


    Success Cor

    Thursday, December 28, 2017 10:10 AM
  • Hello,

    Here is a solution to try, I did not run the solution as I don't have the files (html you have). If the FolderDialog had some setting set you will need to do that in the following.

    https://1drv.ms/u/s!AtGAgKKpqdWjiRZS1Oc9qCa_5xkz


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, December 28, 2017 11:06 AM
    Moderator
  • still nothing , the file name is only inserted but html of the file is not... I will share with you html file.

    and THANK YOU very much for your follow up.

    <?xml version="1.0"?><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
      "file:///C:/Program%20Files%20(x86)/Logictran/DTD/xhtml1/xhtml1-transitional.dtd" >
    <html  lang="en" xmlns="http://www.w3.org/1999/xhtml">
    <!--
    
    Document Comments:
    Version Comments:
    Comments:
    Keywords:
    Category:
    Page/Word/Char Count:2/4267/24323
    Translation Date:12/27/2017
    Translation Time:11:24:36
    Translation Platform:Win32
    Number of Output files:1
    This File:D:\demo\AJAX\AJAX.html
    PercentCallback=10
    createoutputfolder=1
    createimagefolder=0
    CSS=1
    XHTML=1
    HTML=4
    PreserveWhitespace=1
    NavLinks=Auto
    NavImagePath=
    SplitDepth=0
    GenIndex=1
    ImageExt=jpg
    copylinkedimages=1
    outfilename=D:\demo\AJAX.html
    trnfile=html.trn
    -->
    <head><meta http-equiv="Content-Type" content="text/html;" />
    <title>Function loadDoc()
    </title><base  />
    
    <style type="text/css">
    <!--
    .intro {  text-align: left; text-indent:0pt; margin-top:12pt; margin-bottom:12pt; font-family: Times New Roman; font-size: 12pt; }
    .Normal__Web_ {  text-align: left; text-indent:0pt; margin-top:12pt; margin-bottom:12pt; font-family: Times New Roman; font-size: 12pt; }
    .x__1601___1602___1585___1577_ {  text-align: right; text-indent:11pt; margin-top:6pt; margin-bottom:0pt; direction: rtl;unicode-bidi:embed; line-height:16pt;  font-family: Times New Roman; font-size: 10pt; direction: ltr;unicode-bidi:embed;}
    .heading_4 {  text-align: left; text-indent:0pt; margin-top:2pt; margin-bottom:0pt;  font-family: Calibri Light; color: Teal; font-size: 11pt;  font-style: italic;}
    .heading_3 {  text-align: left; text-indent:0pt; margin-top:2pt; margin-bottom:0pt;  font-family: Calibri Light; color: Teal; font-size: 12pt; }
    .heading_2 {  text-align: left; text-indent:0pt; margin-top:2pt; margin-bottom:0pt;  font-family: Calibri Light; color: Teal; font-size: 13pt; }
    .heading_1 {  text-align: left; text-indent:0pt; margin-top:12pt; margin-bottom:12pt; font-family: Times New Roman; font-size: 24pt; font-weight: bold; }
    .Normal {  text-align: left; text-indent:0pt; margin-top:0pt; margin-bottom:8pt;  font-family: Calibri; font-size: 11pt; }
    .text {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .html_tag {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Heading_4_Char {font-family: Calibri Light; color: Teal; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: italic; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Hyperlink {font-family: Times New Roman; color: Blue; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: underline;}
    .Emphasis {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: italic; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Strong {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: bold;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Heading_2_Char {font-family: Calibri Light; color: Teal; background-color: white ; font-size: 13pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Heading_3_Char {font-family: Calibri Light; color: Teal; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .color_h1 {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: normal;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Heading_1_Char {font-family: Times New Roman; color: Black; background-color: white ; font-size: 24pt; font-weight: bold;  font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    .Default_Paragraph_Font {font-family: Times New Roman; color: Black; background-color: white ; font-size: 12pt; font-weight: normal; direction: ltr;unicode-bidi:embed; font-style: normal; font-variant: normal; vertical-align: normal; text-decoration: none;}
    div.ltTOCtitle { font-family: Verdana; font-size: 8pt; font-weight: bold; text-align: center;}
    div.ltTOCl1 { font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:32pt; text-indent:-32pt;}
    div.ltTOCl2 { font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:40pt; text-indent:-32pt;}
    div.ltTOCl3 {  font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:48pt; text-indent:-32pt;}
    div.ltTOCl4 { font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:56pt; text-indent:-32pt;}
    div.ltTOCl5 { font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:64pt; text-indent:-32pt;}
    div.ltTOCl6 { font-family: Verdana; font-size: 8pt; font-weight: bold; margin-left:72pt; text-indent:-32pt;}
    -->
    </style>
    </head>
    
    <body>
    <div class="Normal" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'><span style='font-family: Segoe UI; font-size: 27pt; '>AJAX&#160;Introduction</span></div>
    <div class="Normal"></div>
    <div class="Normal" style=' margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; font-size: 12pt; '>AJAX
    is a developer's dream, because you can:</span></div>
    <ul style='margin-right:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:0pt;list-style-type=disc'>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:36pt; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; font-size: 12pt; '>Read
    data from a web server - after the page has loaded</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:36pt; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; font-size: 12pt; '>Update
    a web page without reloading the page</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:36pt; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; font-size: 12pt; '>Send
    data to a web server - in the
    background</span></li></ul><div class="Normal"></div>
    <div class="Normal">&lt;!DOCTYPE html&gt;</div>
    <div class="Normal">&lt;html&gt;</div>
    <div class="Normal">&lt;body&gt;</div>
    <div class="Normal">&lt;div id="demo"&gt;</div>
    <div class="Normal">&lt;h2&gt;The XMLHttpRequest Object&lt;/h2&gt;</div>
    <div class="Normal">&lt;button type="button" onclick="loadDoc()"&gt;Change
    Content&lt;/button&gt;</div>
    <div class="Normal">&lt;/div&gt;</div>
    <div class="Normal">&lt;script&gt;</div>
    <div class="Normal">function loadDoc() {</div>
    <div class="Normal"> &nbsp;var xhttp = new XMLHttpRequest();</div>
    <div class="Normal"> &nbsp;xhttp.onreadystatechange = function() {</div>
    <div class="Normal"> &nbsp; &nbsp;if (this.readyState == 4 &amp;&amp;
    this.status == 200) {</div>
    <div class="Normal"> &nbsp; &nbsp;
    &nbsp;document.getElementById("demo").innerHTML =</div>
    <div class="Normal"> &nbsp; &nbsp; &nbsp;this.responseText;</div>
    <div class="Normal"> &nbsp; &nbsp;}</div>
    <div class="Normal"> &nbsp;};</div>
    <div class="Normal"> &nbsp;xhttp.open("GET", "ajax_info.txt", true);</div>
    <div class="Normal"> &nbsp;xhttp.send();</div>
    <div class="Normal">}</div>
    <div class="Normal">&lt;/script&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;/body&gt;</div>
    <div class="Normal">&lt;/html&gt;</div>
    <div class="Normal"></div>
    <div class="Normal" style=' background-color: White; margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; '>The
    HTML page contains a &lt;div&gt; section and a &lt;button&gt;.</span></div>
    <div class="Normal" style=' background-color: White; margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; '>The
    &lt;div&gt; section is used to display information from a server.</span></div>
    <div class="Normal" style=' background-color: White; margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; '>The
    &lt;button&gt; calls a function (if it is clicked).</span></div>
    <div class="Normal" style=' background-color: White; margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; '>The
    function requests data from a web server and displays it:</span></div>
    <div class="Normal"></div>
    <h3 class="heading_3" style=' margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading37"></a><span style='font-family: Segoe UI; color: Black; font-size: 18pt; font-weight: bold; '>Function
    loadDoc()</span></h3>
    <div class="Normal" style=' background-color: White;'><span style='font-family: Consolas; color: Blue; '>function</span><span style='font-family: Consolas; '>&#160;loadDoc()
    {</span><br /><span style='font-family: Consolas; '>&#160;&#160;</span><span style='font-family: Consolas; color: Blue; '>var</span><span style='font-family: Consolas; '>&#160;xhttp
    =&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;XMLHttpRequest();</span><br /><span style='font-family: Consolas; '>&#160;
    xhttp.onreadystatechange&#160;=&#160;</span><span style='font-family: Consolas; color: Blue; '>function</span><span style='font-family: Consolas; '>()
    {</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;&#160;</span><span style='font-family: Consolas; color: Blue; '>if</span><span style='font-family: Consolas; '>&#160;(</span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.readyState&#160;==&#160;</span><span style='font-family: Consolas; color: Red; '>4</span><span style='font-family: Consolas; '>&#160;&amp;&amp;&#160;</span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.status&#160;==&#160;</span><span style='font-family: Consolas; color: Red; '>200</span><span style='font-family: Consolas; '>)
    {</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;&#160;
    document.getElementById(</span><span style='font-family: Consolas; color: #f44336; '>"demo"</span><span style='font-family: Consolas; '>).innerHTML&#160;=&#160;</span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.responseText;</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;&#160;}</span><br /><span style='font-family: Consolas; '>&#160;
    };</span><br /><span style='font-family: Consolas; '>&#160;
    xhttp.open(</span><span style='font-family: Consolas; color: #f44336; '>"GET"</span><span style='font-family: Consolas; '>,&#160;</span><span style='font-family: Consolas; color: #f44336; '>"ajax_info.txt"</span><span style='font-family: Consolas; '>,&#160;</span><span style='font-family: Consolas; color: Blue; '>true</span><span style='font-family: Consolas; '>);</span><br /><span style='font-family: Consolas; '>&#160;
    xhttp.send();</span><br /><span style='font-family: Consolas; '>}</span></div>
    <div class="Normal"></div>
    <div class="Normal"></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading41"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '>What
    is AJAX?</span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>AJAX
    =&#160;</span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">A</span><span style='font-family: Verdana; font-size: 11pt; '>synchronous&#160;</span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">J</span><span style='font-family: Verdana; font-size: 11pt; '>avaScript&#160;</span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">A</span><span style='font-family: Verdana; font-size: 11pt; '>nd&#160;</span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">X</span><span style='font-family: Verdana; font-size: 11pt; '>ML.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>AJAX
    is not a programming language.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>AJAX
    just uses a combination of:</span></div>
    <ul style='margin-right:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:0pt;list-style-type=disc'>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; margin-left:36pt; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>A
    browser built-in XMLHttpRequest object (to request data from a web
    server)</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; margin-left:36pt; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>JavaScript
    and HTML DOM (to display or use the
    data)</span></li></ul><div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>AJAX
    is a misleading name. AJAX applications might use XML to transport data, but it
    is equally common to transport data as plain text or JSON text.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>AJAX
    allows web pages to be updated asynchronously by exchanging data with a web
    server behind the scenes. This means that it is possible to update parts of a
    web page, without reloading the whole page.</span></div>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading50"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '><img
    height="3" width="3" src="AJAX00.jpg" 	alt="AJAX00.jpg" border="0" 
    /></span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><img height="32"
    width="32" src="AJAX01.jpg" 	alt="AJAX01.jpg" border="0"  /></div>
    <ul style='margin-right:0pt; margin-top:0pt; margin-bottom:0pt; margin-left:0pt;list-style-type=disc'>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>1.
    An event occurs in a web page (the page is loaded, a button is
    clicked)</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>2.
    An XMLHttpRequest object is created by JavaScript</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>3.
    The XMLHttpRequest object sends a request to a web server</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>4.
    The server processes the request</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>5.
    The server sends a response back to the web page</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>6.
    The response is read by JavaScript</span></li>
    <li class="Normal" style='text-indent:0pt; margin-top:0pt; margin-bottom:0pt; background-color: White; '><span style='font-size: 10pt; '></span><span style='font-family: Verdana; '>7.
    Proper action (like page update) is performed by
    JavaScript</span></li></ul><div class="Normal"></div>
    <div class="Normal"></div>
    <h1 class="heading_1" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading61"></a><span style='font-family: Segoe UI; font-size: 27pt; font-weight: normal; '>AJAX
    - The XMLHttpRequest Object</span></h1>
    <div class="Normal"></div>
    <div class="Normal"></div>
    <div class="intro" style=' background-color: White;'><span style='font-family: Verdana; '>he
    keystone of AJAX is the XMLHttpRequest object.</span></div>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading66"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '><img
    height="3" width="3" src="AJAX02.jpg" 	alt="AJAX02.jpg" border="0" 
    /></span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>All
    modern browsers support the XMLHttpRequest object.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>The
    XMLHttpRequest object can be used to exchange data with a web server behind the
    scenes. This means that it is possible to update parts of a web page, without
    reloading the whole page.</span></div>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading70"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '><img
    height="3" width="3" src="AJAX03.jpg" 	alt="AJAX03.jpg" border="0" 
    /></span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>All
    modern browsers (Chrome, Firefox, IE7+, Edge, Safari, Opera) have a built-in
    XMLHttpRequest object.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>Syntax
    for creating an XMLHttpRequest object:</span></div>
    <div class="Normal" style=' background-color: White;'><span style='font-family: Consolas;  font-style: italic;'>variable&#160;</span><span style='font-family: Consolas; '>=&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;XMLHttpRequest();</span></div>
    <h3 class="heading_3" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading74"></a><span style='font-family: Segoe UI; color: Black; font-size: 18pt; font-weight: bold; '>Example</span></h3>
    <div class="Normal" style=' background-color: White;'><span style='font-family: Consolas; color: Blue; '>var</span><span style='font-family: Consolas; '>&#160;xhttp
    =&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;XMLHttpRequest();</span></div>
    <div class="Normal"></div>
    <div class="Normal">&lt;!DOCTYPE html&gt;</div>
    <div class="Normal">&lt;html&gt;</div>
    <div class="Normal">&lt;body&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;h2&gt;The XMLHttpRequest Object&lt;/h2&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;p id="demo"&gt;Let AJAX change this
    text.&lt;/p&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;button type="button" onclick="loadDoc()"&gt;Change
    Content&lt;/button&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;script&gt;</div>
    <div class="Normal">function loadDoc() {</div>
    <div class="Normal"> &nbsp;var xhttp = new XMLHttpRequest();</div>
    <div class="Normal"> &nbsp;xhttp.onreadystatechange = function() {</div>
    <div class="Normal"> &nbsp; &nbsp;if (this.readyState == 4 &amp;&amp;
    this.status == 200) {</div>
    <div class="Normal"> &nbsp; &nbsp;
    &nbsp;document.getElementById("demo").innerHTML = this.responseText;</div>
    <div class="Normal"> &nbsp; &nbsp;}</div>
    <div class="Normal"> &nbsp;};</div>
    <div class="Normal"> &nbsp;xhttp.open("GET", "ajax_info.txt", true);</div>
    <div class="Normal"> &nbsp;xhttp.send();</div>
    <div class="Normal">}</div>
    <div class="Normal">&lt;/script&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;/body&gt;</div>
    <div class="Normal">&lt;/html&gt;</div>
    <div class="Normal"></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading103"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '>Access
    Across Domains</span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>For
    security reasons, modern browsers do not allow access across
    domains.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>This
    means that both the web page and the XML file it tries to load, must be located
    on the same server.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>The
    examples on W3Schools all open XML files located on the W3Schools
    domain.</span></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>If
    you want to use the example above on one of your own web pages, the XML files
    you load must be located on your own server.</span></div>
    <div class="Normal"></div>
    <div class="Normal"></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading110"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '>Older
    Browsers (IE5 and IE6)</span></h2>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>Old
    versions of Internet Explorer (5/6) use an ActiveX object instead of the
    XMLHttpRequest object:</span></div>
    <div class="Normal" style=' background-color: White;'><span style='font-family: Consolas;  font-style: italic;'>variable&#160;</span><span style='font-family: Consolas; '>=&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;ActiveXObject(</span><span style='font-family: Consolas; color: #f44336; '>"Microsoft.XMLHTTP"</span><span style='font-family: Consolas; '>);</span></div>
    <div class="Normal__Web_" style=' background-color: White;'></div>
    <div class="Normal__Web_" style=' background-color: White;'></div>
    <div class="Normal__Web_" style=' background-color: White;'></div>
    <div class="Normal__Web_" style=' background-color: White;'><span style='font-family: Verdana; font-size: 11pt; '>To
    handle IE5 and IE6, check if the browser supports the XMLHttpRequest object, or
    else create an ActiveX object:</span></div>
    <h3 class="heading_3" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading117"></a><span style='font-family: Segoe UI; color: Black; font-size: 18pt; font-weight: bold; '>Example</span></h3>
    <div class="Normal" style=' background-color: White;'><span style='font-family: Consolas; color: Blue; '>if</span><span style='font-family: Consolas; '>&#160;(window.XMLHttpRequest)
    {</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;&#160;</span><span style='font-family: Consolas; color: Green; '>//
    code for modern
    browsers</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;
    xmlhttp
    =&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;XMLHttpRequest();</span><br /><span style='font-family: Consolas; '>&#160;}&#160;</span><span style='font-family: Consolas; color: Blue; '>else</span><span style='font-family: Consolas; '>&#160;{</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;&#160;</span><span style='font-family: Consolas; color: Green; '>//
    code for old IE
    browsers</span><br /><span style='font-family: Consolas; '>&#160;&#160;&#160;
    xmlhttp
    =&#160;</span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '>&#160;ActiveXObject(</span><span style='font-family: Consolas; color: #f44336; '>"Microsoft.XMLHTTP"</span><span style='font-family: Consolas; '>);</span><br /><span style='font-family: Consolas; '>}</span></div>
    <div class="Normal"></div>
    <div class="Normal"></div>
    <div class="Normal">&lt;!DOCTYPE html&gt;</div>
    <div class="Normal">&lt;html&gt;</div>
    <div class="Normal">&lt;body&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;h2&gt;The XMLHttpRequest Object&lt;/h2&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;p id="demo"&gt;Let AJAX change this
    text.&lt;/p&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;button type="button" onclick="loadDoc()"&gt;Change
    Content&lt;/button&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;script&gt;</div>
    <div class="Normal">function loadDoc() {</div>
    <div class="Normal"> &nbsp;var xhttp;</div>
    <div class="Normal"> &nbsp;if (window.XMLHttpRequest) {</div>
    <div class="Normal"> &nbsp; &nbsp;// code for modern browsers</div>
    <div class="Normal"> &nbsp; &nbsp;xhttp = new XMLHttpRequest();</div>
    <div class="Normal"> &nbsp; &nbsp;} else {</div>
    <div class="Normal"> &nbsp; &nbsp;// code for IE6, IE5</div>
    <div class="Normal"> &nbsp; &nbsp;xhttp = new
    ActiveXObject("Microsoft.XMLHTTP");</div>
    <div class="Normal"> &nbsp;}</div>
    <div class="Normal"> &nbsp;xhttp.onreadystatechange = function() {</div>
    <div class="Normal"> &nbsp; &nbsp;if (this.readyState == 4 &amp;&amp;
    this.status == 200) {</div>
    <div class="Normal"> &nbsp; &nbsp;
    &nbsp;document.getElementById("demo").innerHTML = this.responseText;</div>
    <div class="Normal"> &nbsp; &nbsp;}</div>
    <div class="Normal"> &nbsp;};</div>
    <div class="Normal"> &nbsp;xhttp.open("GET", "ajax_info.txt", true);</div>
    <div class="Normal"> &nbsp;xhttp.send();</div>
    <div class="Normal">}</div>
    <div class="Normal">&lt;/script&gt;</div>
    <div class="Normal"></div>
    <div class="Normal">&lt;/body&gt;</div>
    <div class="Normal">&lt;/html&gt;</div>
    <div class="Normal"></div>
    <div class="Normal"></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading155"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '>XMLHttpRequest
    Object Methods</span></h2>
    <div style="; left: 0"><table rules="none" border="1" frame="box">
    <colgroup>
          <col width="217" />
          <col width="403" />
    </colgroup>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; font-weight: bold; '>Method</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; font-weight: bold; '>Description</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>new
    XMLHttpRequest()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Creates
    a new XMLHttpRequest object</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>abort()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Cancels
    the current request</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>getAllResponseHeaders()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Returns
    header information</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>getResponseHeader()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Returns
    specific header information</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>open(</span><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">method,
    url, async, user, psw</span><span style='font-family: Verdana; '>)</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Specifies
    the
    request</span><br /><span style='font-family: Verdana; '></span><br /><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">method</span><span style='font-family: Verdana; '>:
    the request type GET or
    POST</span><br /><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">url</span><span style='font-family: Verdana; '>:
    the file
    location</span><br /><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">async</span><span style='font-family: Verdana; '>:
    true (asynchronous) or false
    (synchronous)</span><br /><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">user</span><span style='font-family: Verdana; '>:
    optional user
    name</span><br /><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">psw</span><span style='font-family: Verdana; '>:
    optional password</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>send()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Sends
    the request to the server</span><br /><span style='font-family: Verdana; '>Used
    for GET requests</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>send(</span><span style='font-family: Verdana; font-size: 11pt; ' class="Emphasis">string</span><span style='font-family: Verdana; '>)</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Sends
    the request to the server.</span><br /><span style='font-family: Verdana; '>Used
    for POST requests</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>setRequestHeader()</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Adds
    a label/value pair to the header to be sent</span></div>
    </td>
    </tr>
    </table></div>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'></div>
    <h2 class="heading_2" style=' background-color: White; margin-top:7pt; margin-bottom:7pt;'>
    <a name="Heading175"></a><span style='font-family: Segoe UI; color: Black; font-size: 22pt; font-weight: bold; '><img
    height="3" width="3" src="AJAX04.jpg" 	alt="AJAX04.jpg" border="0" 
    /></span></h2>
    <div style="; left: 0"><table rules="none" border="1" frame="box">
    <colgroup>
          <col width="217" />
          <col width="403" />
    </colgroup>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; font-weight: bold; '>Property</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; font-weight: bold; '>Description</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>onreadystatechange</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Defines
    a function to be called when the readyState property changes</span></div>
    </td>
    </tr>
    <tr valign="top">
    <td width="217" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>readyState</span></div>
    </td>
    <td width="403" style='valign: top; background-color: white ; '>
    <div class="Normal" style=' margin-top:15pt; margin-bottom:15pt;'><span style='font-family: Verdana; '>Holds
    the status of the
    XMLHttpRequest.</span><br /><span style='font-family: Verdana; '>0: request not
    initialized&#160;</span><br /><span style='font-family: Verdana; '>1: server
    connection established</span><br /><span style='font-family: Verdana; '>2:
    request received&#160;</span><br /><span style='font-family: Verdana; '>3:
    processing request&#160;</span><br /><span style='font-family: Verdana; '>4:
    request finished and response is ready</span></div>
    </td>
    </tr>
    
    
    
    
    </body>
    
    </html>
    

    Thursday, December 28, 2017 12:11 PM
  • If what I provided did not work, try changing the field type for the file contents to varbinary as shown in the following MSDN code sample I wrote, it's in C# but it's still a viable option and if needed if you can wait when I get home tonight can provide a vb.net version.

    Referring to the code sample, look at Button1 click event, change fileName variable to one of your files then run the code, see if this works, if it does we know the issue was with the field type, if not something else is going on but my guess it's the field type.

    So if it works then you can adapt what I did to your project, should be just a little work to adapt to what I've done to place the code into a loop. I would be need to get ready for work.

    Button1 click event as per above

    Here is the insert method

    public bool FilePutSimple(string FilePath, ref int NewIdentifier,string FileName)
    {
        byte[] fileByes;
    
        using (var stream = new FileStream(FilePath, FileMode.Open, FileAccess.Read))
        {
            using (var reader = new BinaryReader(stream))
            {
                fileByes = reader.ReadBytes((int)stream.Length);
            }
        }
    
        using (SqlConnection cn = new SqlConnection() { ConnectionString = Properties.Settings.Default.ConnectionString })
        {
            var statement = "INSERT INTO Table1 (FileContents,FileName) VALUES (@FileContents,@FileName);SELECT CAST(scope_identity() AS int);";
            using (SqlCommand cmd = new SqlCommand() { Connection = cn, CommandText = statement })
            {
                cmd.Parameters.Add("@FileContents", SqlDbType.VarBinary, fileByes.Length).Value = fileByes;
                cmd.Parameters.AddWithValue("@FileName", FileName);
    
                try
                {
                    cn.Open();
    
                    NewIdentifier = Convert.ToInt32(cmd.ExecuteScalar());
                    return true;
    
                }
                catch (Exception ex)
                {
                    ExceptionMessage = ex.Message;
                    return false;
                }
            }
        }
    }
    public bool FileGetSimple(int Identifier, string fileName)
    {
        using (SqlConnection cn = new SqlConnection() { ConnectionString = Properties.Settings.Default.ConnectionString })
        {
    
            var statement = "SELECT id, [FileContents], FileName FROM Table1  WHERE id = @id;";
    
            using (SqlCommand cmd = new SqlCommand() { Connection = cn, CommandText = statement})
            {
                cmd.Parameters.AddWithValue("@id", Identifier);
    
                try
                {
                    cn.Open();
    
                    var reader = cmd.ExecuteReader();
    
                    if (reader.HasRows)
                    {
                        reader.Read();
    
                        // the blob field
                        int ndx = reader.GetOrdinal("FileContents");
    
                        var blob = new Byte[(reader.GetBytes(ndx, 0, null, 0, int.MaxValue))];
                        reader.GetBytes(ndx, 0, blob, 0, blob.Length);
    
                        using (var fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
                            fs.Write(blob, 0, blob.Length);
    
                    }
                    return true;
    
                }
    
                catch (Exception ex)
                {
                    ExceptionMessage = ex.Message;
                    return false;
                }
            }
        }
    }


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites


    Thursday, December 28, 2017 12:38 PM
    Moderator
  • It seems very difficult to follow what I wrote.

    I made your database and the code. Not any problem if you had changed what I wrote.

    Imports System.Data.SqlClient
    Public Class Form1
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
            Dim con As New SqlConnection("Data Source=TheServer;Initial Catalog=TestDataBase;Integrated Security=True")
            Try
                Dim query As String = "INSERT INTO [tbl_ccm-demo] (StudyName, Study) VALUES (@StudyName,@Study)"
                Dim x As String = "Dummies"
                Dim y = IO.File.ReadAllText("d:\test\testHtml.txt")
                Using cmd As New SqlCommand(query, con)
                    cmd.Parameters.AddWithValue("@StudyName", x)
                    cmd.Parameters.AddWithValue("@Study", y)
                    con.Open()
                    cmd.ExecuteNonQuery()
                End Using
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            End Try
        End Sub
    End Class


    Success Cor


    • Edited by Cor Ligthert Thursday, December 28, 2017 4:55 PM
    Thursday, December 28, 2017 4:53 PM
  • Hello,

    As promised, here is a project that works. I set it up to do one file insert at a time which when placed into a loop will work also. I modified the table as shown below (there are two script, one with data, one without data in the project).

    CREATE TABLE [dbo].[tbl_ccm-demo](
    	[studyID] [INT] IDENTITY(1,1) NOT NULL,
    	[StudyName] [VARCHAR](50) NULL,
    	[Study] [NVARCHAR](MAX) NULL,
     CONSTRAINT [PK_tbl_ccm-demo] PRIMARY KEY CLUSTERED 
    (
    	[studyID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    I used the file you supplied, inserted it twice (once with a slight change) and extracted to prove it worked with a different file name.

    Data class

    Imports System.Data.SqlClient
    Imports System.IO
    
    Public Class DataOperations
        ''' <summary>
        ''' Replace with your SQL Server name
        ''' </summary>
        Private Server As String = "KARENS-PC"
        ''' <summary>
        ''' Database in which data resides, see SQL_Script.sql
        ''' </summary>
        Private Catalog As String = "CCM"
        ''' <summary>
        ''' Connection string for connecting to the database
        ''' </summary>
        Private SqlConnectionString As String = ""
        Public Exception As Exception
        ''' <summary>
        ''' Setup the connection string
        ''' </summary>
        Public Sub New()
            SqlConnectionString = $"Data Source={Server};Initial Catalog={Catalog};Integrated Security=True"
        End Sub
        ''' <summary>
        ''' Insert one file
        ''' </summary>
        ''' <param name="pFilePath"></param>
        ''' <param name="pNewIdentifier"></param>
        ''' <param name="pStudyName"></param>
        ''' <returns></returns>
        Public Function FilePutSimple(ByVal pFilePath As String, ByRef pNewIdentifier As Integer, ByVal pStudyName As String) As Boolean
            Dim fileByes() As Byte
    
            Using stream = New FileStream(pFilePath, FileMode.Open, FileAccess.Read)
                Using reader = New BinaryReader(stream)
                    fileByes = reader.ReadBytes(CInt(stream.Length))
                End Using
            End Using
    
            Using cn As New SqlConnection() With {.ConnectionString = SqlConnectionString}
                Dim statement = "INSERT INTO dbo.[tbl_ccm-demo] (Study,StudyName) VALUES (@Study,@StudyName);SELECT CAST(scope_identity() AS int);"
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cmd.Parameters.Add("@Study", SqlDbType.VarBinary, fileByes.Length).Value = fileByes
                    cmd.Parameters.AddWithValue("@StudyName", pStudyName)
    
                    Try
                        cn.Open()
    
                        pNewIdentifier = Convert.ToInt32(cmd.ExecuteScalar())
                        Return True
    
                    Catch ex As Exception
                        Exception = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
        ''' <summary>
        ''' Extract one file
        ''' </summary>
        ''' <param name="pIdentifier"></param>
        ''' <param name="pFileName"></param>
        ''' <returns></returns>
        Public Function FileGetSimple(ByVal pIdentifier As Integer, ByVal pFileName As String) As Boolean
            Using cn As New SqlConnection() With {.ConnectionString = SqlConnectionString}
    
                Dim statement = "SELECT Study FROM dbo.[tbl_ccm-demo]  WHERE studyid = @id;"
    
                Using cmd As New SqlCommand() With {.Connection = cn, .CommandText = statement}
                    cmd.Parameters.AddWithValue("@id", pIdentifier)
    
                    Try
                        cn.Open()
    
                        Dim reader = cmd.ExecuteReader()
    
                        If reader.HasRows Then
                            reader.Read()
    
                            Dim ndx As Integer = reader.GetOrdinal("Study")
    
                            Dim blob = New Byte((reader.GetBytes(ndx, 0, Nothing, 0, Integer.MaxValue)) - 1) {}
                            reader.GetBytes(ndx, 0, blob, 0, blob.Length)
    
                            Using fs = New FileStream(pFileName, FileMode.Create, FileAccess.Write)
                                fs.Write(blob, 0, blob.Length)
                            End Using
                        Else
                            '
                            ' Identifier not found
                            '
                            Exception = New Exception($"Identifier: {pIdentifier} not found: {pFileName}")
                            Return False
                        End If
                        Return True
    
    
                    Catch ex As Exception
                        Exception = ex
                        Return False
                    End Try
                End Using
            End Using
        End Function
    End Class
    

    Form code

    Imports BackEnd
    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim newIdentifier As Integer = 0
            Dim ops As New DataOperations
            If ops.FilePutSimple(IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "hasHtml.txt"), newIdentifier, "Some description") Then
                MessageBox.Show($"New id: {newIdentifier}")
            Else
                MessageBox.Show(ops.Exception.Message)
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim ops = New DataOperations()
    
            '
            ' I first added to files, that means we have id's 1 and 2 but not 3
            ' so when we hit three the back end code throws a trapped exception
            ' and we read it without blowing up :-)
            '
            Dim identifierList As New List(Of Integer) From {1, 2, 3}
    
            Dim fileName = ""
    
            For Each id As Integer In identifierList
                fileName = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, $"extracted{id}.html")
                If ops.FileGetSimple(id, fileName) Then
                    MessageBox.Show($"Success: {fileName}")
                Else
                    MessageBox.Show($"Failed: {ops.Exception.Message}")
                End If
    
            Next
        End Sub
    End Class
    

    Results


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Marked as answer by GE_NK Friday, December 29, 2017 6:04 AM
    Friday, December 29, 2017 1:07 AM
    Moderator