0

For a programming asignment, I am to program a prototype. Right now I'm only looking to code 1 method. The method is supposed to read a locale .csv file, and save it as a BLOB (Binary Large Object) in a database on an external server. However, I'm quite new at C#, and I am used to Java. I'm not exactly sure what a BLOB is, some sort of Byte Array or something?

So far my program can

  • Connect to the SQL server.
  • Read the .csv file.

The database table is called tblUsers.

The field I'm trying to insert it into is BlobFile, which is of datatype varbinary(8000).

I don't even know if the datatype is correct.

All I want is the .csv file saved into a table on a server.

"String or binary data would be truncated. The statement has been terminated." Is what I get unfortunately, which I can figure out means that my .csv file somewhat doesn't match with my datatype in the table. I don't know how to link you to the .csv but it looks like:

4.012 3.012 1.312 3.321 4.232

and so on. This is the C# code

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;


namespace WindowsFormsApplication8
{
public partial class Form1 : Form
{
    public Form1()
    {
        InitializeComponent();
    }


    private void button1_Click(object sender, EventArgs e)
    {
        string filePath = "C:/Users/Soeren/Desktop/epilepsi - semester/EpilepsiEKG/Patient1_Reciprocal_HFpower_x1.csv";
        FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read);
        string line = fs.ReadLine();
        string[] value = line.Split(',');
        BinaryReader reader = new BinaryReader(fs);


        byte[] BlobValue = reader.ReadBytes((int)fs.Length);
        fs.Close();
        reader.Close();
        //FILE READ!

        SqlConnection con = new SqlConnection(@"Data Source=webhotel10.iha.dk;Initial Catalog=F13ST2ITS2201270867;Persist Security Info=True;User ID=F13ST2ITS2201270867;Password=F13ST2ITS2201270867");
        SqlCommand com = new SqlCommand("insert into tblUsers(BlobFilename,BlobFile) values(@BlobFilename,@Blobfile)", con);
        SqlParameter BlobFileNameParam = new SqlParameter("@BlobFileName", SqlDbType.NChar);
        SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.Binary);
        com.Parameters.Add(BlobFileNameParam);
        com.Parameters.Add(BlobFileParam);

        BlobFileNameParam.Value = filePath.Substring(filePath.LastIndexOf("/") + 1);

        BlobFileParam.Value = BlobValue;

        try
        {

            com.Connection.Open();
            com.ExecuteNonQuery();
            MessageBox.Show(BlobFileNameParam.Value.ToString() + " saved to database.", "BLOB Saved", MessageBoxButtons.OK, MessageBoxIcon.Information);

        }


        catch (Exception ex)
        {

            MessageBox.Show(ex.Message, "Save Failed", MessageBoxButtons.OK, MessageBoxIcon.Error);

        }


        finally
        {

            com.Connection.Close();

        }
    }

}

}

1
  • That being said, the error message String or binary data would be truncated. The statement has been terminated. actually spells out what is wrong. You are trying to insert too much data into a field that has a specific dimension. Commented Oct 28, 2013 at 13:12

1 Answer 1

1

The way the BlobFileParam Parameter is declared, it is a fixed length binary Parameter with size 0 (as the size is not specified). You try to insert data with a length greater than the specified size (0). Try setting the size property of the parameter BlobFileParam to the correct length; I also think the SqlDbType.VarBinary might be a better choice for the type of the parameter.

Sign up to request clarification or add additional context in comments.

6 Comments

This is useful. My problem is that my .csv file changes size, it is variable. So the size attribute - what is that in this case? Is it the maximum size?
I think that changing the Parameter type to VarBinary along with a size of 8000 (as defined in the database) should work. The Parameter type and size then matches the database Definition.
SqlParameter BlobFileParam = new SqlParameter("@BlobFile", SqlDbType.VarBinary, 8000); It doesn't work. Same error. :(
I just checked the other Parameter also. It is of type NChar, it should also be NVarChar. This might also be the source of the error.
This works! Solved. I think I owe you some beers, holla at me if you come through Aarhus in Denmark lol
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.