Working with SQL server's Image data type

[Slade]

Hey guys next question :(. Now I have having troubles with the IMAGE data type, can anyone provide examples on how to retrieve and update the image? I have tried the following:

Dim imgdatastream As FileStream

Dim imgdatalen As Integer

Dim imgtype As String

Dim n As Integer

imgdatastream = Pic.PostedFile.InputStream

imgdatalen = Pic.PostedFile.ContentLength

imgtype = Pic.PostedFile.ContentType

Dim imgdata() As Byte = New [Byte](imgdatalen) {}

n = imgdatastream.Read(imgdata, 0, imgdatalen)
 
Dim paramAvatar As SqlParameter = myCommand.Parameters.Add("@Avatar", SqlDbType.Image)

paramAvatar.Value = n

But I recieved the lovely error "
Specified cast is not valid. "

help? :(

[Tekmaven]

Which line did you get the error on? I really haven't worked with the image data type before (i usually store an uploaded image on the server's file system), but I can play with it ;).

[Slade]

For this error it was this line

imgdatastream = Pic.PostedFile.InputStream

Cheers Tekmaven,

Slade

[Slade]

Hmm I think I found my mistake, but I can't test it as of yet... will let you know how I go when I get home :)

[Slade]

Hey guys :)

Well i've written some code to upload it :D but now I have to get some to download it and display it ehehe...

Ok stored procedure:

CREATE procedure sf_GetProfileImage
 @UserID nvarchar(20) 
  
AS
SELECT Avatar, Av_type
FROM sf_Members
WHERE UserID = @UserID
GO

and now the vb code...

Private Sub UpdateProfile(ByVal UserID As String)

Dim imgstream As Stream = Pic.PostedFile.InputStream

Dim imgdata(Pic.PostedFile.ContentLength) As Byte

imgstream.Read(imgdata, 0, Pic.PostedFile.ContentLength)



Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("strSqlConnectionString"))

Dim myCommand = New SqlCommand("sf_GetProfileImage", myConnection)

myCommand.CommandType = CommandType.StoredProcedure

Dim paramUserID As SqlParameter = myCommand2.Parameters.Add("@UserID", SqlDbType.NVarChar, 20)

paramUserID.Value = UserID

Dim myReader As SqlDataReader

myConnection.Open()

myReader = myCommand.ExecuteReader
 
Dim paramAvatar As SqlParameter = myCommand.Parameters.Add("@Avatar", SqlDbType.Image)
 
paramAv_type.Value = Pic.PostedFile.ContentType
Dim paramAv_type As SqlParameter = myCommand.Parameters.Add("@Av_type", SqlDbType.NVarChar, 100)

paramAvatar.Value = imgdata

myReader.Close()
myconnection.close
myconnection.dispose
mycommand.dispose
 
End Sub

And the html input button in the aspx page is:

< INPUT id="Pic" type="file" name="Pic" runat="server" >

and then of course you have to declare it in the server side code :|

Protected WithEvents Pic As System.Web.UI.HtmlControls.HtmlInputFile

PHEW! Finally done, now I need to know how the heck I can display these images on the page. Anyone got any ideas?

[Slade]

oh... I got it working but I must've forgot to hit the submit button when I typed it last night eheh.... I'll do it when I get home.

[rfidler]

paramAvatar.Value = n <----- this needs to be imgdata

[sangita32]

sing System;
using System.Data;
using System.Configuration;
using System.Collections;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using System.Data.SqlClient;


public partial class New_Registration : System.Web.UI.Page
{
    SqlConnection cn;
    SqlCommand cmd;
    SqlDataAdapter da;
    string file1;
    DataSet ds;

    void Day()
    {
        ddlday.Items.Add("Day");
        for (int i = 1; i <= 31; i++)
        {
            ddlday.Items.Add(i.ToString());
        }
    }
    void Year()
    {
        ddlyear.Items.Add("Year");
        for (int i = 1955; i <= 1992; i++)
        {
            ddlyear.Items.Add(i.ToString());
        }
    }
    void Height()
    {
        for (int i = 4; i <= 7; i++)
            ddlhft.Items.Add(i.ToString());
        for (int j = 1; j <= 11; j++)
            ddlhin.Items.Add(j.ToString());

        ddlhft.SelectedIndex = ddlhin.SelectedIndex = 0;
    }
    int id;
    int AutoNumber()
    {
        cn.Open();
        string str = "SELECT MAX(MemberId) FROM tbl_member";
        cmd = new SqlCommand(str, cn);
        try
        {
            id = (int)cmd.ExecuteScalar() + 1;
        }
        catch
        {
            id = 1;
        }
        cn.Close();
        return id;
    }


    protected void Page_Load(object sender, EventArgs e)
    {
        cn = new SqlConnection(ConfigurationManager.AppSettings["sikh"]);
        //cn.Open();

        if (!Page.IsPostBack)
        {
            Day();
            Year();
            Height();
        }
    }
    protected void Button1_Click(object sender, EventArgs e)
    {

        string gender = null;
        foreach (ListItem li in rblgen.Items)
            if (li.Selected)
                gender = li.Text;

        string bodytype = null;
        foreach (ListItem li in rblbody.Items)
            if (li.Selected)
                bodytype = li.Text;
        string emp = null;
        foreach (ListItem li in rblemp.Items)
            if (li.Selected)
                emp = li.Text;

       string DOB = ddlday.Text + "/" + ddlmonth.Text + "/" + ddlyear.Text;

        //Height
       string height = ddlhft.Text + "-" + ddlhin.Text;
        string Query = "insert into tbl_member(MemberId,MemberName,DisplayName,EmailId,Password,Pro_create_for,Gender,DateofBirth,Maritarial_Status,Complex,Height,BodyType,BodyWeight,Physical_Status,Appearance,Religion,Caste,Subcaste,Mother_tongue,Family_type,Family_value,Family_status,Education,EducationDetail,Occupation,OccupationDetail,FatherOccupation,MotherOccupation,Employedin,Annual_income,Country,Citizen,Residing_state,Residing_city,Phone_no,Mobile_no,Address,About_me,About_partner,ActiveTime) values('"+AutoNumber()+"','"+txtname.Text.Trim()+"','"+txtdname+",'"+txtemail.Text.Trim()+"','"+txtpwd.Text+"','"+ddlprofile.Text.Trim()+"','"+gender.ToString()+"','"+ DOB.ToString()+"','"+gender.ToString()+"','"+DOB.ToString()+"','"+ddlmatritalstatus.Text+"','"+ddcomplexion.Text+"','"+height.ToString()+"','"+ bodytype.ToString()+"','"+txtweight.Text.Trim()+"','"+ddlpstatus.Text+"','"+ddlappear.Text+"','"+txtreligion.Text.Trim()+"','"+ddlcaste.Text+"','"+txtcaste.Text.Trim()+"','"+ddlmother.Text+"','"+ddltype.SelectedItem.Text+"','"+ddlvalue.SelectedItem.ToString()+"','"+ddlstatus.SelectedItem.ToString()+"','"+ddledu.SelectedItem.ToString()+"','"+ txtedetail.Text.Trim()+"','"+ ddlocc.SelectedItem.ToString()+"','"+txtoccdetail.Text.Trim()+"','"+emp.ToString()+"','"+txtincome.Text.Trim()+"','"+ ddlcountry.SelectedItem.ToString()+"','"+ddlcity.SelectedItem.ToString()+"','"+ txtstate.Text.Trim()+"','"+ txtrcity.Text.Trim()+"','"+txtContactNo.Text.Trim()+"','"+txtpin.Text.Trim()+"','"+txtadd.Text.Trim()+"','"+txtme.Text.Trim()+"','"+txtpartner.Text.Trim()+"','"+DateTime.Now+"','0')";


        try
        {
            if (chkread.Checked)
            {
                cn.Open();
                cmd=new SqlCommand(Query,cn);   
                cmd.ExecuteNonQuery();
                Response.Write("<script language=jscript>alert('" + "Account Activation withing 48 hrs." + "')</script>");
                cn.Close();
            }
            else
            {
                Response.Write("<script language=jscript>alert('" + "Select Accpet Button." + "')</script>");
            }
        }
        catch (Exception ex) {
            //Response.Write(ex+"<br>"+Query);

        }
        //if (Page.IsValid)
        //{

        //    //String strquery = "insert into tbl_member(MemberName,DisplayName,Pro_create_for,Gender,EmailId,Password,DateofBirth,Age,Maritarial_Status,Height,BodyType,BodyWeight,Physical_Status,Appearance,Religion,Caste,Subcaste,Mother_tongue,Family_type,Family_value,Family_status,Education,EducationDetail,Occupation,OccupationDetail,FatherOccupation,MotherOccupation,Employedin,Annual_income,Country,Citizen,Residing_state,Residing_city,Phone_no,Pincode,Address,About_me,About_partner,ActiveTime,Status)values(@MemberName,@DisplayName,@Pro_create_for,@Gender,@EmailId,@Password,@DateofBirth,@Age,@Maritarial_Status,@Height,@BodyType,@BodyWeight,@Physical_Status,@Appearance,@Religion,@Caste,@Subcaste,@Mother_tongue,@Family_type,@Family_value,@Family_status,@Education,@EducationDetail,@Occupation,@OccupationDetail,@FatherOccupation,@MotherOccupation,@Employedin,@Annual_income,@Country,@Citizen,@Residing_state,@Residing_city,@Phone_no,@Pincode,@Address,@About_me,@About_partner,@ActiveTime,@Status)";
        //    cmd = new SqlCommand(strquery, cn);
        //    cmd.CommandType = CommandType.Text;


        //    cmd.Parameters.AddWithValue("@MemberName", txtname.Text);
        //    cmd.Parameters.AddWithValue("@DisplayName", txtdname.Text);
        //    cmd.Parameters.AddWithValue("@Pro_create_for", ddlprofile.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Gender", rblgen.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@EmailId", txtemail.Text);
        //    cmd.Parameters.AddWithValue("@Password", txtpwd.Text);
        //    cmd.Parameters.AddWithValue("@Age", txtage.Text);
        //    cmd.Parameters.AddWithValue("@DateofBirth", DateTime.Now);
        //    cmd.Parameters.AddWithValue("@Height", ddlft.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Maritarial_Status", ddlmatritalstatus.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@BodyType", rblbody.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@BodyWeight", txtweight.Text);
        //    cmd.Parameters.AddWithValue("@Complextion", ddcomplexion.SelectedItem.ToString());

        //    cmd.Parameters.AddWithValue("@Physical_Status", ddlpstatus.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Appearance", ddlappear.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Religion", txtreligion.Text);
        //    cmd.Parameters.AddWithValue("@Caste", ddlcaste.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Subcaste", txtcaste.Text);
        //    cmd.Parameters.AddWithValue("@Mother_tongue", ddlmother.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Family_type", ddltype.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Family_value", ddlvalue.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Family_status", ddlstatus.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Education", ddledu.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@EducationDetail", txtedetail.Text);
        //    cmd.Parameters.AddWithValue("@Occupation", ddlocc.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@OccupationDetail", txtoccdetail.Text);
        //    cmd.Parameters.AddWithValue("@FatherOccupation", txtfather.Text);
        //    cmd.Parameters.AddWithValue("@MotherOccupation", txtmother.Text);
        //    cmd.Parameters.AddWithValue("@Employedin",rblemp.SelectedItem.ToString());
        //    cmd.Parameters.AddWithValue("@Annual_income", txtincome.Text);
        //    //uploadfile();
        //    //cmd.Parameters.AddWithValue("@Photo1", SqlDbType.VarChar).Value = file1;
        //    cmd.Parameters.AddWithValue("@Country", ddlcountry.Text);
        //    cmd.Parameters.AddWithValue("@Citizen", ddlcity.Text);
        //    cmd.Parameters.AddWithValue("@Residing_state", txtstate.Text);
        //    cmd.Parameters.AddWithValue("@Residing_city", txtrcity.Text);
        //    cmd.Parameters.AddWithValue("@Phone_no",txtContactNo.Text);
        //    cmd.Parameters.AddWithValue("@Pincode", txtpin.Text);
        //    cmd.Parameters.AddWithValue("@Address", txtadd.Text);
        //    cmd.Parameters.AddWithValue("@About_me", txtme.Text);
        //    cmd.Parameters.AddWithValue("@About_partner", txtpartner.Text);
        //    cmd.Parameters.AddWithValue("ActiveTime", DateTime.Now);
        //    cmd.Parameters.AddWithValue("@Status", "Active");
        //    cmd.Parameters.AddWithValue("State", "add");
            try
            {
                if (chkread.Checked)
                {
                    cn.Open();
                    cmd.ExecuteNonQuery();
                    //Response.Write("<script language=jscript>alert('" + "Account Activation withing 48 hrs." + "')</script>");
                    cn.Close();

                    Response.Redirect("genregno.aspx");
                }
                else
                {
                    Response.Write("<script language=jscript>alert('" + "Select Accpet Button." + "')</script>");
                }
            }
            catch (Exception ex)
            {


            }
            //cn.Close();

            //txtname.Text = "";
            //txtdname.Text = "";
            //txtemail.Text = "";
            //txtpwd.Text = "";
            //txtpartner.Text = "";
            //txtpin.Text = "";
            //txtContactNo.Text = "";
            //txtoccdetail.Text = "";
            //txtrcity.Text = "";
            //txtreligion.Text = "";
            //txtstate.Text = "";
            //txtweight.Text = "";
            //txtadd.Text = "";
            //txtage.Text = "";
            //txtcaste.Text = "";
            //txtdateofbirth.Text = "";
            //txtincome.Text = "";
            //txtme.Text = "";
            //txtmother.Text = "";

            //txtfather.Text = "";
            //txtedetail.Text = "";

        }
    }