Solved

Problem with Inserting an image into a sql 2000 database

Posted on 2004-09-30
12
293 Views
Last Modified: 2010-04-15
Hello,

I am having a ton of trouble trying to get this piece of code to work correctly, can someone please tell me what's being done wrong here?

Thank you very much.
Ray


public void InsertImage(MemoryStream MemImg)
{

long ImgLen = MemImg.Length;
byte [] ByteImg;
ByteImage = new byte [ImgLen -1];
MemImg.Position = 0;
MemImg.Read(ByteImg, 0, (int)(ImgLen - 1));

//...sql objects declared here...

string strSql = "INSERT INTO Customers (FName, LName, Pic) VALUES ('" + txtFName.Text + "'," + "'" + txtLName.Text + "'," + ByteImg + ")";

mycmd = new SqlCommand(strSql, Conn);
mycmd.ExecuteNonQuery();
}

0
Comment
Question by:rfrankson
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
  • 2
  • +2
12 Comments
 
LVL 15

Expert Comment

by:praneetha
ID: 12192944
does it throw any exception...

if so what
0
 

Author Comment

by:rfrankson
ID: 12193040
Hey praneetha

I apoligize, I forgot to post that, the Exception occurs when it tries to execute the command  (mycmd.ExecuteNonQuery),  the exception that I get is:

Cannot use empty object or column names. Use a single space if necessary.\r\nThe name 'Byte' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.


Thank you,
Ray
0
 
LVL 15

Expert Comment

by:praneetha
ID: 12193379
http://www.aspfree.com/c/a/ASP.NET/Uploading-Images-to-a-Database--C---Part-I/

see ifi t helps you...

and could u insert any data thru aspx pages..need not be image...

0
Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

 
LVL 9

Expert Comment

by:s_sansanwal
ID: 12194359
0
 
LVL 11

Expert Comment

by:sachiek
ID: 12196829
From the exception thrown, I guess image is not properly been read or written into that bye array.
Did you able to debug and check the size of that byte array?

Regards,
Sachi
0
 
LVL 11

Expert Comment

by:sachiek
ID: 12196835
Here is the table structure.

CREATE TABLE [dbo].[image] (
    [img_pk] [int] IDENTITY (1, 1) NOT NULL ,
    [img_name] [varchar] (50) NULL ,
    [img_data] [image] NULL ,
    [img_contenttype] [varchar] (50) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[image] WITH NOCHECK ADD
CONSTRAINT [PK_image] PRIMARY KEY NONCLUSTERED
(
    [img_pk]
) ON [PRIMARY] GO

0
 
LVL 11

Expert Comment

by:sachiek
ID: 12196838
File to upload : UploadImage.aspx

<%@ Page language="c#" Src="UploadImage.aspx.cs" Inherits="DBImages.UploadImage" %>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN" >
<HTML>
<HEAD>
</HEAD>
<body bgcolor=#ffffff>
<form enctype="multipart/form-data" runat=server id=form1 name=form1>
<h3>The ASPFree Friendly Image Uploader</h3>
Enter A Friendly Name<input type=text id=txtImgName runat="server" > 
<asp:RequiredFieldValidator id=RequiredFieldValidator1 runat="server" ErrorMessage="Required" ControlToValidate="txtImgName"></asp:RequiredFieldValidator>
<br>Select File To Upload:
<input id="UploadFile" type=file runat=server>
<asp:button id=UploadBtn Text="Upload Me!" OnClick="UploadBtn_Click" runat="server"></asp:button>
</form>

</body>
</HTML>
0
 
LVL 11

Accepted Solution

by:
sachiek earned 400 total points
ID: 12196840
UploadImage.aspx.cs (codebehind file)

using System;
using System.Configuration;
using System.Collections;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Web;
using System.IO;
using System.Web.SessionState;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
namespace DBImages
{
    public class UploadImage : System.Web.UI.Page
    {
        protected System.Web.UI.WebControls.Button UploadBtn;
        protected System.Web.UI.WebControls.RequiredFieldValidator RequiredFieldValidator1;
        protected System.Web.UI.HtmlControls.HtmlInputText txtImgName;
        protected System.Web.UI.HtmlControls.HtmlInputFile UploadFile;
        public UploadImage() { }
        private void Page_Load(object sender, System.EventArgs e){ }
        public void UploadBtn_Click(object sender, System.EventArgs e)
        {
            if (Page.IsValid) //save the image
            {
                Stream imgStream = UploadFile.PostedFile.InputStream;
                int imgLen = UploadFile.PostedFile.ContentLength;
                string imgContentType = UploadFile.PostedFile.ContentType;
                string imgName = txtImgName.Value;
                byte[] imgBinaryData = new byte[imgLen];
                int n = imgStream.Read(imgBinaryData,0,imgLen);
                int RowsAffected = SaveToDB( imgName, imgBinaryData,imgContentType);
                if ( RowsAffected>0 )
                {
                    Response.Write("<BR>The Image was saved");
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }
        }

        private int SaveToDB(string imgName, byte[] imgbin, string imgcontenttype)
        {
            //use the web.config to store the connection string
            SqlConnection connection = new SqlConnection(ConfigurationSettings.AppSettings["DSN"]);
            SqlCommand command = new SqlCommand( "INSERT INTO Image (img_name,img_data,img_contenttype) VALUES ( @img_name, @img_data,@img_contenttype )", connection );
            SqlParameter param0 = new SqlParameter( "@img_name", SqlDbType.VarChar,50 );
            param0.Value = imgName;
            command.Parameters.Add( param0 );
            SqlParameter param1 = new SqlParameter( "@img_data", SqlDbType.Image );
            param1.Value = imgbin;
            command.Parameters.Add( param1 );
            SqlParameter param2 = new SqlParameter( "@img_contenttype", SqlDbType.VarChar,50 );
            param2.Value = imgcontenttype;
            command.Parameters.Add( param2 );
            connection.Open();
            int numRowsAffected = command.ExecuteNonQuery();
            connection.Close();
            return numRowsAffected;
        }
    }
}
0
 
LVL 11

Expert Comment

by:sachiek
ID: 12196843
Web.config

<configuration>
    <appSettings>
            <add key="DSN" value="server=localhost;uid=sa;pwd=;Database=dbname"/>
    </appSettings>
    <system.web>
            <customErrors mode="Off" />
    </system.web>
</configuration>
0
 
LVL 11

Expert Comment

by:sachiek
ID: 12196848
Hope you got it right this time..

cheers!
Sachi
0
 
LVL 8

Expert Comment

by:vinhnl
ID: 12198434
Try this code:

using System;
using System.IO;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using System.Data.SqlClient;

public class Image2DB
{
      private static byte[] ConvertImage2Bytes(Image image)
      {
            byte[] result = null;
            if(image != null)
            {
                  MemoryStream ms = new MemoryStream();
                  image.Save(ms,System.Drawing.Imaging.ImageFormat.Bmp);
                  result = ms.GetBuffer();
                  ms.Close();
            }
            return result;
      }

      private static Image ConvertBytes2Image(byte[] bytes)
      {
            Image result = null;
            if(bytes != null)
            {
                  MemoryStream ms = new MemoryStream( bytes );
                  result = Image.FromStream(ms);
                  ms.Close();
            }
            return result;
      }

      public static void Create(string KeyID, Image image)
      {
            try
            {
                  string INSERT_DATA_RECORD = "INSERT INTO T_IMAGE (KeyID,Image) Values(@KeyID,@Image)";

                  SqlConnection Connection = new SqlConnection("Server = VINHNL; Database = MyDB; UID = sa; Pwd = ;");
                  SqlCommand Command = new SqlCommand( INSERT_DATA_RECORD , Connection );

                  SqlParameter [] arParms = new SqlParameter[2];
                  arParms[0] = new SqlParameter("@KeyID", KeyID);
                  if(image == null)
                        arParms[1] = new SqlParameter("@Image", DBNull.Value);
                  else
                  {
                        arParms[1] = new SqlParameter("@Image", ConvertImage2Bytes(image) );
                  }

                  Command.Parameters.Add( arParms[0] );
                  Command.Parameters.Add( arParms[1] );
                  Connection.Open();
                  Command.ExecuteNonQuery();
                  Connection.Close();
            }
            catch(SqlException ex)
            {
                  MessageBox.Show(ex.Message);
            }
      }

      public static Image Get(string KeyID)
      {
            Image result = null;
            try
            {
                  string LOAD_DATA_RECORD = "SELECT Image FROM T_IMAGE WHERE KeyID = @KeyID";

                  SqlConnection Connection = new SqlConnection("Server = VINHNL; Database = MyDB; UID = sa; Pwd = ;");
                  SqlCommand Command = new SqlCommand( LOAD_DATA_RECORD , Connection );

                  SqlParameter arParms = new SqlParameter("@KeyID", KeyID);

                  Command.Parameters.Add( arParms);
                  Connection.Open();
                  System.Data.SqlClient.SqlDataReader reader = Command.ExecuteReader();
                  if( reader.Read() )
                        result =  ConvertBytes2Image( (byte[])reader["Image"] );
                  reader.Close();
                  Connection.Close();
            }
            catch(SqlException ex)
            {
                  MessageBox.Show(ex.Message);
            }
            return result;
      }
}

// use Image2DB

private void btnSave_Click(object sender, System.EventArgs e)
{
      Image2DB.Create( "TestImage", pbxImage.Image );
}

private void btnLoad_Click(object sender, System.EventArgs e)
{
      pbxImage.Image = Image2DB.Get( "TestImage" );
}

// create T_TABLE

CREATE TABLE [dbo].[T_IMAGE] (
      [KeyID] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
      [Image] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Good Luck,
VINHNL
0
 

Author Comment

by:rfrankson
ID: 12213107
Hey sachiek your solution worked great for me,

Thank you all for your replies!

Ray
0

Featured Post

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction Although it is an old technology, serial ports are still being used by many hardware manufacturers. If you develop applications in C#, Microsoft .NET framework has SerialPort class to communicate with the serial ports.  I needed to…
Exception Handling is in the core of any application that is able to dignify its name. In this article, I'll guide you through the process of writing a DRY (Don't Repeat Yourself) Exception Handling mechanism, using Aspect Oriented Programming.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question