Answered by:
INSERT HTML INTO SQL SERVER NOT WORKING

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
- Marked as answer by GE_NK Friday, December 29, 2017 6:04 AM
Friday, December 29, 2017 1:07 AM
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
- Edited by KareninstructorMVP Wednesday, December 27, 2017 12:27 PM
Wednesday, December 27, 2017 11:45 AM -
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.- Edited by Cherry BuMicrosoft contingent staff Thursday, December 28, 2017 2:33 AM
Thursday, December 28, 2017 2:32 AM -
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
Thursday, December 28, 2017 11:06 AM -
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 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"><!DOCTYPE html></div> <div class="Normal"><html></div> <div class="Normal"><body></div> <div class="Normal"><div id="demo"></div> <div class="Normal"><h2>The XMLHttpRequest Object</h2></div> <div class="Normal"><button type="button" onclick="loadDoc()">Change Content</button></div> <div class="Normal"></div></div> <div class="Normal"><script></div> <div class="Normal">function loadDoc() {</div> <div class="Normal"> var xhttp = new XMLHttpRequest();</div> <div class="Normal"> xhttp.onreadystatechange = function() {</div> <div class="Normal"> if (this.readyState == 4 && this.status == 200) {</div> <div class="Normal"> document.getElementById("demo").innerHTML =</div> <div class="Normal"> this.responseText;</div> <div class="Normal"> }</div> <div class="Normal"> };</div> <div class="Normal"> xhttp.open("GET", "ajax_info.txt", true);</div> <div class="Normal"> xhttp.send();</div> <div class="Normal">}</div> <div class="Normal"></script></div> <div class="Normal"></div> <div class="Normal"></body></div> <div class="Normal"></html></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 <div> section and a <button>.</span></div> <div class="Normal" style=' background-color: White; margin-top:12pt; margin-bottom:12pt;'><span style='font-family: Verdana; '>The <div> 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 <button> 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; '> loadDoc() {</span><br /><span style='font-family: Consolas; '>  </span><span style='font-family: Consolas; color: Blue; '>var</span><span style='font-family: Consolas; '> xhttp = </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> XMLHttpRequest();</span><br /><span style='font-family: Consolas; '>  xhttp.onreadystatechange = </span><span style='font-family: Consolas; color: Blue; '>function</span><span style='font-family: Consolas; '>() {</span><br /><span style='font-family: Consolas; '>    </span><span style='font-family: Consolas; color: Blue; '>if</span><span style='font-family: Consolas; '> (</span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.readyState == </span><span style='font-family: Consolas; color: Red; '>4</span><span style='font-family: Consolas; '> && </span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.status == </span><span style='font-family: Consolas; color: Red; '>200</span><span style='font-family: Consolas; '>) {</span><br /><span style='font-family: Consolas; '>     document.getElementById(</span><span style='font-family: Consolas; color: #f44336; '>"demo"</span><span style='font-family: Consolas; '>).innerHTML = </span><span style='font-family: Consolas; color: Blue; '>this</span><span style='font-family: Consolas; '>.responseText;</span><br /><span style='font-family: Consolas; '>    }</span><br /><span style='font-family: Consolas; '>  };</span><br /><span style='font-family: Consolas; '>  xhttp.open(</span><span style='font-family: Consolas; color: #f44336; '>"GET"</span><span style='font-family: Consolas; '>, </span><span style='font-family: Consolas; color: #f44336; '>"ajax_info.txt"</span><span style='font-family: Consolas; '>, </span><span style='font-family: Consolas; color: Blue; '>true</span><span style='font-family: Consolas; '>);</span><br /><span style='font-family: Consolas; '>  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 = </span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">A</span><span style='font-family: Verdana; font-size: 11pt; '>synchronous </span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">J</span><span style='font-family: Verdana; font-size: 11pt; '>avaScript </span><span style='font-family: Verdana; font-size: 11pt; ' class="Strong">A</span><span style='font-family: Verdana; font-size: 11pt; '>nd </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 </span><span style='font-family: Consolas; '>= </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> 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; '> xhttp = </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> XMLHttpRequest();</span></div> <div class="Normal"></div> <div class="Normal"><!DOCTYPE html></div> <div class="Normal"><html></div> <div class="Normal"><body></div> <div class="Normal"></div> <div class="Normal"><h2>The XMLHttpRequest Object</h2></div> <div class="Normal"></div> <div class="Normal"><p id="demo">Let AJAX change this text.</p></div> <div class="Normal"></div> <div class="Normal"><button type="button" onclick="loadDoc()">Change Content</button></div> <div class="Normal"></div> <div class="Normal"><script></div> <div class="Normal">function loadDoc() {</div> <div class="Normal"> var xhttp = new XMLHttpRequest();</div> <div class="Normal"> xhttp.onreadystatechange = function() {</div> <div class="Normal"> if (this.readyState == 4 && this.status == 200) {</div> <div class="Normal"> document.getElementById("demo").innerHTML = this.responseText;</div> <div class="Normal"> }</div> <div class="Normal"> };</div> <div class="Normal"> xhttp.open("GET", "ajax_info.txt", true);</div> <div class="Normal"> xhttp.send();</div> <div class="Normal">}</div> <div class="Normal"></script></div> <div class="Normal"></div> <div class="Normal"></body></div> <div class="Normal"></html></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 </span><span style='font-family: Consolas; '>= </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> 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; '> (window.XMLHttpRequest) {</span><br /><span style='font-family: Consolas; '>    </span><span style='font-family: Consolas; color: Green; '>// code for modern browsers</span><br /><span style='font-family: Consolas; '>    xmlhttp = </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> XMLHttpRequest();</span><br /><span style='font-family: Consolas; '> } </span><span style='font-family: Consolas; color: Blue; '>else</span><span style='font-family: Consolas; '> {</span><br /><span style='font-family: Consolas; '>    </span><span style='font-family: Consolas; color: Green; '>// code for old IE browsers</span><br /><span style='font-family: Consolas; '>    xmlhttp = </span><span style='font-family: Consolas; color: Blue; '>new</span><span style='font-family: Consolas; '> 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"><!DOCTYPE html></div> <div class="Normal"><html></div> <div class="Normal"><body></div> <div class="Normal"></div> <div class="Normal"><h2>The XMLHttpRequest Object</h2></div> <div class="Normal"></div> <div class="Normal"><p id="demo">Let AJAX change this text.</p></div> <div class="Normal"></div> <div class="Normal"><button type="button" onclick="loadDoc()">Change Content</button></div> <div class="Normal"></div> <div class="Normal"><script></div> <div class="Normal">function loadDoc() {</div> <div class="Normal"> var xhttp;</div> <div class="Normal"> if (window.XMLHttpRequest) {</div> <div class="Normal"> // code for modern browsers</div> <div class="Normal"> xhttp = new XMLHttpRequest();</div> <div class="Normal"> } else {</div> <div class="Normal"> // code for IE6, IE5</div> <div class="Normal"> xhttp = new ActiveXObject("Microsoft.XMLHTTP");</div> <div class="Normal"> }</div> <div class="Normal"> xhttp.onreadystatechange = function() {</div> <div class="Normal"> if (this.readyState == 4 && this.status == 200) {</div> <div class="Normal"> document.getElementById("demo").innerHTML = this.responseText;</div> <div class="Normal"> }</div> <div class="Normal"> };</div> <div class="Normal"> xhttp.open("GET", "ajax_info.txt", true);</div> <div class="Normal"> xhttp.send();</div> <div class="Normal">}</div> <div class="Normal"></script></div> <div class="Normal"></div> <div class="Normal"></body></div> <div class="Normal"></html></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 </span><br /><span style='font-family: Verdana; '>1: server connection established</span><br /><span style='font-family: Verdana; '>2: request received </span><br /><span style='font-family: Verdana; '>3: processing request </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
- Edited by KareninstructorMVP Thursday, December 28, 2017 1:01 PM
Thursday, December 28, 2017 12:38 PM -
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
- Marked as answer by GE_NK Friday, December 29, 2017 6:04 AM
Friday, December 29, 2017 1:07 AM