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
Solved

Problem with Inserting an image into a sql 2000 database

Posted on 2004-09-30
12
282 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
  • 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

This article describes a simple method to resize a control at runtime.  It includes ready-to-use source code and a complete sample demonstration application.  We'll also talk about C# Extension Methods. Introduction In one of my applications…
Performance in games development is paramount: every microsecond counts to be able to do everything in less than 33ms (aiming at 16ms). C# foreach statement is one of the worst performance killers, and here I explain why.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

861 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