Solved

Problem with Inserting an image into a sql 2000 database

Posted on 2004-09-30
12
260 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
 
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
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

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
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.
This video discusses moving either the default database or any database to a new volume.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

758 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now