0


SQL Server 2019 | nvarchar | Collation: Latin1_General_100_CI_AI

I ran a test with a simple insert statement to my database to make sure it would insert the data from a web form correctly.
That data is: Dvořák Hall
The data inserted as expected with the simple insert script. However, that is not the case with my MP3 Upload script.
Below is the main part of the code, to show what is being done to the metadata once it hits the scripts before being inserted into the SQL Server database.
There are multiple entries in the MP3 metadata, that are separated by forward slash mark and these entries have double curly brackets around them, which is where the REGEX comes into play within the code.
code is vb.net

postedFile.SaveAs(Convert.ToString(savepath & "\") + Convert.ToString(getfile & ".mp3"))
Dim mp3 As New ID3TagLibrary.MP3File(Server.MapPath("Files/" + getfile & ".mp3"))
Dim strWriters As String = Replace(mp3.Comment, "eng", "")
Dim Regex4 As Regex = New Regex("\{{.*?\}}")
Dim matches4 As MatchCollection = Regex4.Matches(strWriters)
Dim count4 As Integer = matches4.Count
Dim sV As String = matches4(0).Value.ToString 'Venue

Now the insert statement.

Dim Vparts As String() = sV.Split(New Char() {"/"c})
Dim Vpart As String
For Each Vpart In Vparts
strVenues = "INSERT INTO Venue(VName)VALUES(@VName)"
VenueCMD = New SqlCommand(strVenues, Artistcon)
VenueCMD.Parameters.Add(New SqlParameter("@VName", Vpart))
Try
VenueCMD.ExecuteNonQuery()
Catch ex As Exception
Finally
End Try

UPDATE I did a test when the file is first uploaded to the server when the metadata is captured. At that moment, the Dvořák Hall is changed to DvoYk Hall

It seems there is an issue with keeping format once it starts reading the file. This is what I did

Dim strWriters As String = Replace(mp3.Comment, "eng", "")
Response.Write(strWriters)
Response.End()

Output is: {{DvoYk Hall}}

CodingEE
  • 129
  • 2
  • 14
  • You have nothing in your `catch` block so you cannot see any errors and you have not shown where you assign a value to `theVpart` –  Jan 31 '20 at 06:44
  • Sorry, Justin. theVpart, was supposed to be changed in this example, to what is there, which is Vpart. I changed it in the code for here. – CodingEE Feb 01 '20 at 00:49
  • Can you use the ID3v2 tags instead? They should have the text encoded with UTF-16, which should be OK with text ilke "Dvořák". – Andrew Morton Feb 01 '20 at 17:15
  • Hello, Andrew. The ID3TagLibrary has the ID3v2 in it. It should be working? Do you know of a different component I can use? – CodingEE Feb 01 '20 at 20:26
  • I just downloaded the tool he made with the ID3TagLibrary and it does not format the name correctly either. So, Andrew, if you know of a better component, please, let me know. – CodingEE Feb 01 '20 at 20:32
  • @CodingEE I don't. It might be worth looking at the file in a hex editor, just in case the data is already wrong, in which case you have no hope of retrieving it in the form you expect. – Andrew Morton Feb 01 '20 at 20:44
  • I use a program called: MP3Tag Editor, and that program keeps all the text formatting. And when I look at the file through Windows Dialog Properties, the text format is correct as well.----- I am looking at some other components in hopes that one of them will do the job. I will post my findings when I have something. Until then, if you or anyone else comes across one, please post. Thanks. – CodingEE Feb 01 '20 at 22:20
  • There are a few libraries mentioned in [View/edit ID3 data for MP3 files](https://stackoverflow.com/q/68283/1115360). – Andrew Morton Feb 02 '20 at 14:53
  • It seems everything is in C#, why can't people do it both languages, with VB? – CodingEE Feb 02 '20 at 17:23

2 Answers2

0

Instead of adding a new parameter each time, you need to create one parameter and change its value, like this:

Dim sql = "INSERT INTO Venue(VName) VALUES (@VName)"

Using conn As New SqlConnection("your connection string here"),
        cmd As New SqlCommand(sql, conn)

    ' Create the parameter
    'TODO: set .Size parameter to the correct value.
    cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@VName", .SqlDbType = SqlDbType.NVarChar, .Size = 255})

    For Each vPart In Vparts
        ' Set the value of the parameter each time round the loop
        cmd.Parameters("@VName").Value = vPart
        cmd.ExecuteNonQuery()
    Next

End Using
Andrew Morton
  • 24,203
  • 9
  • 60
  • 84
  • Got the code working, but it does not insert properly. Instead, inserts the name as (DvoYk_Hall) with the Y taking over the accented characters. So, still, a no go, on getting the metadata from the file, to insert into the database column properly. – CodingEE Feb 01 '20 at 04:31
0

It all came down to the following.
The MP3 DLL file I was using, was the issue. I changed it and began using the TagLib-Sharp. This component keeps the formatting and is able to upload the data to the database without any issue.
What helped me to get to this, was an old post I made here on SO.

I looked back at the old example code I created while making the above-mentioned thread, and loaded it up with an mp3 file with the accented text. And wa-la worked like a charm.

All is good now.
If any lesson can be taken away from this, it is the following.
Never use something that is no longer in development, but stick with the ones that are still in development.

Community
  • 1
  • 1
CodingEE
  • 129
  • 2
  • 14