Problems filling image field in MSSQL server

[Andreas van Bergen]

Hi,

I am filling a byte[] with a string (about 1700 characters) which i want to
insert into an image field in MSSQL server. This all goes well.....accept
when i want to retrieve that particullar field, my result is a string with
16 characters...where are my other 1684 characters ??

Here is some code that i use to perform this action :

//****************************************************************************************
// This is the filling part....
//****************************************************************************************
ASCIIEncoding ascii = new ASCIIEncoding();
byte[] oByteArray = ascii.GetBytes(aStringToWrite);

StringBuilder oBuilder = new StringBuilder();
oBuilder.Append("INSERT INTO Tabel (");
oBuilder.Append("MessageContent");
oBuilder.Append(") VALUES (@MessageContent)");

SqlCommand oCommand = new SqlCommand(oBuilder.ToString());

SqlParameter pMessageContent = new SqlParameter("@MessageContent",
SqlDbType.Image, 16);
pMessageContent.Value = oByteArray;
oCommand.Parameters.Add(pMessageContent);

ExcecuteQuery(oCommand);

//****************************************************************************************
// This is the retrieving part....
//****************************************************************************************
string sSQL = String.Format("SELECT {0} FROM Tabel WHERE MessageID = {1}",
aColumnName, aMessageID);
DataTable oTable = ResultQuery(sSQL);
DataRow oRow = (DataRow)oTable.Rows[0];

byte[] oByteArray = (byte[])oRow[aColumnName];

ASCIIEncoding ascii = new ASCIIEncoding();
char[] oCharArray = ascii.GetChars(oByteArray);

string Anwser = new string(oCharArray);
//****************************************************************************************

Does anybody have a suggestion how to solve this problem?

Thanks in advance.

Andreas

 

[Miha Markic [MVP C#]]

Hi Andreas,

Because you are sending value 16 as length in parameter declaration.
You might set it with 2,147,483,647 instead as this is the max length of an
image field on sql server 2000.

 

[Andreas van Bergen]

Thanks Miha, it works fine! I thought that 16 was the maximum for SQL type
image, because if you create a new column with type image in the Enterprice
Manager you can't even alter the size.......

Thanks again !!

Greetz,

Andreas

Hi Andreas,

Because you are sending value 16 as length in parameter declaration.
You might set it with 2,147,483,647 instead as this is the max length of
an image field on sql server 2000.
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I am filling a byte[] with a string (about 1700 characters) which i want
to insert into an image field in MSSQL server. This all goes
well.....accept when i want to retrieve that particullar field, my result
is a string with 16 characters...where are my other 1684 characters ??

Here is some code that i use to perform this action :

//****************************************************************************************
// This is the filling part....
//****************************************************************************************
ASCIIEncoding ascii = new ASCIIEncoding();
byte[] oByteArray = ascii.GetBytes(aStringToWrite);

StringBuilder oBuilder = new StringBuilder();
oBuilder.Append("INSERT INTO Tabel (");
oBuilder.Append("MessageContent");
oBuilder.Append(") VALUES (@MessageContent)");

SqlCommand oCommand = new SqlCommand(oBuilder.ToString());

SqlParameter pMessageContent = new SqlParameter("@MessageContent",
SqlDbType.Image, 16);
pMessageContent.Value = oByteArray;
oCommand.Parameters.Add(pMessageContent);

ExcecuteQuery(oCommand);

//****************************************************************************************
// This is the retrieving part....
//****************************************************************************************
string sSQL = String.Format("SELECT {0} FROM Tabel WHERE MessageID =
{1}", aColumnName, aMessageID);
DataTable oTable = ResultQuery(sSQL);
DataRow oRow = (DataRow)oTable.Rows[0];

byte[] oByteArray = (byte[])oRow[aColumnName];

ASCIIEncoding ascii = new ASCIIEncoding();
char[] oCharArray = ascii.GetChars(oByteArray);

string Anwser = new string(oCharArray);
//****************************************************************************************

Does anybody have a suggestion how to solve this problem?

Thanks in advance.

Andreas

 

[Miha Markic [MVP C#]]

Hi there,

The value of 16 in EM means that the byte array instance (without the actual
data) uses 16 bytes.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Thanks Miha, it works fine! I thought that 16 was the maximum for SQL type
image, because if you create a new column with type image in the
Enterprice Manager you can't even alter the size.......

Thanks again !!

Greetz,

Andreas

Hi Andreas,

Because you are sending value 16 as length in parameter declaration.
You might set it with 2,147,483,647 instead as this is the max length of
an image field on sql server 2000.
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I am filling a byte[] with a string (about 1700 characters) which i want
to insert into an image field in MSSQL server. This all goes
well.....accept when i want to retrieve that particullar field, my
result is a string with 16 characters...where are my other 1684
characters ??

Here is some code that i use to perform this action :

//****************************************************************************************
// This is the filling part....
//****************************************************************************************
ASCIIEncoding ascii = new ASCIIEncoding();
byte[] oByteArray = ascii.GetBytes(aStringToWrite);

StringBuilder oBuilder = new StringBuilder();
oBuilder.Append("INSERT INTO Tabel (");
oBuilder.Append("MessageContent");
oBuilder.Append(") VALUES (@MessageContent)");

SqlCommand oCommand = new SqlCommand(oBuilder.ToString());

SqlParameter pMessageContent = new SqlParameter("@MessageContent",
SqlDbType.Image, 16);
pMessageContent.Value = oByteArray;
oCommand.Parameters.Add(pMessageContent);

ExcecuteQuery(oCommand);

//****************************************************************************************
// This is the retrieving part....
//****************************************************************************************
string sSQL = String.Format("SELECT {0} FROM Tabel WHERE MessageID =
{1}", aColumnName, aMessageID);
DataTable oTable = ResultQuery(sSQL);
DataRow oRow = (DataRow)oTable.Rows[0];

byte[] oByteArray = (byte[])oRow[aColumnName];

ASCIIEncoding ascii = new ASCIIEncoding();
char[] oCharArray = ascii.GetChars(oByteArray);

string Anwser = new string(oCharArray);
//****************************************************************************************

Does anybody have a suggestion how to solve this problem?

Thanks in advance.

Andreas

 

[Cor Ligthert [MVP]]

Andreas,

In addition to Miha

Afaik is the image not stored in that place, it is only the adress where it
is stored.

Cor

Thanks Miha, it works fine! I thought that 16 was the maximum for SQL type
image, because if you create a new column with type image in the
Enterprice Manager you can't even alter the size.......

Thanks again !!

Greetz,

Andreas

Hi Andreas,

Because you are sending value 16 as length in parameter declaration.
You might set it with 2,147,483,647 instead as this is the max length of
an image field on sql server 2000.
--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Hi,

I am filling a byte[] with a string (about 1700 characters) which i want
to insert into an image field in MSSQL server. This all goes
well.....accept when i want to retrieve that particullar field, my
result is a string with 16 characters...where are my other 1684
characters ??

Here is some code that i use to perform this action :

//****************************************************************************************
// This is the filling part....
//****************************************************************************************
ASCIIEncoding ascii = new ASCIIEncoding();
byte[] oByteArray = ascii.GetBytes(aStringToWrite);

StringBuilder oBuilder = new StringBuilder();
oBuilder.Append("INSERT INTO Tabel (");
oBuilder.Append("MessageContent");
oBuilder.Append(") VALUES (@MessageContent)");

SqlCommand oCommand = new SqlCommand(oBuilder.ToString());

SqlParameter pMessageContent = new SqlParameter("@MessageContent",
SqlDbType.Image, 16);
pMessageContent.Value = oByteArray;
oCommand.Parameters.Add(pMessageContent);

ExcecuteQuery(oCommand);

//****************************************************************************************
// This is the retrieving part....
//****************************************************************************************
string sSQL = String.Format("SELECT {0} FROM Tabel WHERE MessageID =
{1}", aColumnName, aMessageID);
DataTable oTable = ResultQuery(sSQL);
DataRow oRow = (DataRow)oTable.Rows[0];

byte[] oByteArray = (byte[])oRow[aColumnName];

ASCIIEncoding ascii = new ASCIIEncoding();
char[] oCharArray = ascii.GetChars(oByteArray);

string Anwser = new string(oCharArray);
//****************************************************************************************

Does anybody have a suggestion how to solve this problem?

Thanks in advance.

Andreas