不能用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对象有什么特别之处吗?

不能用SQL检索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();