[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Problem with Inserting an image into a sql 2000 database

Posted on 2004-09-30
12
Medium Priority
?
298 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 1600 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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

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…
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.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

656 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