INSERT HTML INTO SQL SERVER NOT WORKING
Question
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 ClassForm 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 ClassResults
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
All replies
-
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, Moderator Wednesday, December 27, 2017 12:27 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, Moderator Thursday, December 28, 2017 2:33 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
-
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, Moderator Thursday, December 28, 2017 1:01 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
-
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 ClassForm 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 ClassResults
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
