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 AsInteger

Dim imgtype AsString

Dim n AsInteger

imgdatastream = Pic.PostedFile.InputStream

imgdatalen = Pic.PostedFile.ContentLength

imgtype = Pic.PostedFile.ContentType

Dim imgdata() AsByte = 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...

PrivateSub UpdateProfile(ByVal UserID AsString)

Dim imgstream As Stream = Pic.PostedFile.InputStream

Dim imgdata(Pic.PostedFile.ContentLength) AsByte

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



Dim myConnection AsNew 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 = imgdatamyReader.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 :|

ProtectedWithEvents 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("");
cn.Close();
}
else
{
Response.Write("");
}
}
catch (Exception ex) {
//Response.Write(ex+"
"+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("");
cn.Close();

Response.Redirect("genregno.aspx");
}
else
{
Response.Write("");
}
}
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 = "";

}
}