不能用SQL检索ole对象
本文关键字:ole 对象 检索 SQL 不能 | 更新日期: 2023-09-27 18:04:40
我试图从我的数据库中检索图像,但不知何故,它似乎与SQL
出错这是代码:
public partial class producten : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
int artikelnummer = 1;
//Connect
string connectionstring = @"Provider=Microsoft.ACE.OLEDB.12.0;Data
Source=C:'Users'jeroen'Documents'Visual Studio 2010'Projects'producten
'producten'App_Data'Bimsports.accdb;Persist Security Info=True";
OleDbConnection conn = new OleDbConnection(connectionstring);
//Execute
string sql = "SELECT Merk, Maat, Omschrijving, Kleur, Prijs, BTW,
Categorie.Categorie, foto FROM Artikel INNER JOIN Categorie ON
Artikel.Categorie = Categorie.Categorienummer WHERE Artikelnummer =?";
OleDbCommand cmd = new OleDbCommand(sql, conn);
cmd.Parameters.AddWithValue("Artikelnummer", artikelnummer);
//Read
try
{
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
lbl_merk.Text = reader["Merk"].ToString();
lbl_maat.Text = reader["Maat"].ToString();
lbl_omschrijving.Text = reader["Omschrijving"].ToString();
lbl_kleur.Text = reader["Kleur"].ToString();
lbl_prijs.Text = reader["Prijs"].ToString();
lbl_btw.Text = reader["BTW"].ToString();
lbl_categorie.Text = reader["Categorie"].ToString();
byte[] image = (byte[])(reader["foto"]);
if (image == null)
img_nikeshirt.ImageUrl = null;
else
{
MemoryStream mstream = new MemoryStream(image);
img_nikeshirt.ImageUrl = "data:image/jpeg;base64," + Convert.ToBase64String(image);
}
}
}
catch (Exception ex)
{
lbl_merk.Text = "er ging iets fout bij de verbinding" + ex.Message;
}
finally
{
conn.Close();
}
foto
为原object(jpg)
。
如果我像这样执行它,它将不起作用。如果我删除foto
,没有什么问题。物品有问题吗?我可以在访问区打开。ole对象有什么特别之处吗?
首先,正如你所说的,图片是一个对象,你试图访问BLOB作为一个字符串,这就是为什么它崩溃。试着看看这里,去理解是怎么回事。我正在从MSDN中复制示例:
// Assumes that connection is a valid SqlConnection object.
SqlCommand command = new SqlCommand(
"SELECT pub_id, logo FROM pub_info", connection);
// Writes the BLOB to a file (*.bmp).
FileStream stream;
// Streams the BLOB to the FileStream object.
BinaryWriter writer;
// Size of the BLOB buffer.
int bufferSize = 100;
// The BLOB byte[] buffer to be filled by GetBytes.
byte[] outByte = new byte[bufferSize];
// The bytes returned from GetBytes.
long retval;
// The starting position in the BLOB output.
long startIndex = 0;
// The publisher id to use in the file name.
string pubID = "";
// Open the connection and read data into the DataReader.
connection.Open();
SqlDataReader reader = command.ExecuteReader(CommandBehavior.SequentialAccess);
while (reader.Read())
{
// Get the publisher id, which must occur before getting the logo.
pubID = reader.GetString(0);
// Create a file to hold the output.
stream = new FileStream(
"logo" + pubID + ".bmp", FileMode.OpenOrCreate, FileAccess.Write);
writer = new BinaryWriter(stream);
// Reset the starting byte for the new BLOB.
startIndex = 0;
// Read bytes into outByte[] and retain the number of bytes returned.
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
// Continue while there are bytes beyond the size of the buffer.
while (retval == bufferSize)
{
writer.Write(outByte);
writer.Flush();
// Reposition start index to end of last buffer and fill buffer.
startIndex += bufferSize;
retval = reader.GetBytes(1, startIndex, outByte, 0, bufferSize);
}
// Write the remaining buffer.
writer.Write(outByte, 0, (int)retval - 1);
writer.Flush();
// Close the output file.
writer.Close();
stream.Close();
}
// Close the reader and the connection.
reader.Close();
connection.Close();