Solved

Problem with Inserting an image into a sql 2000 database

Posted on 2004-09-30
12
277 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
VB.Net How to Exit Sub - Exit Form??? 5 57
Close form "before" open 3 40
Question! 4 36
insert value of checklistbox checked 4 27
Extention Methods in C# 3.0 by Ivo Stoykov C# 3.0 offers extension methods. They allow extending existing classes without changing the class's source code or relying on inheritance. These are static methods invoked as instance method. This…
In order to hide the "ugly" records selectors (triangles) in the rowheaders, here are some suggestions. Microsoft doesn't have a direct method/property to do it. You can only hide the rowheader column. First solution, the easy way The first sol…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

810 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