Uploading images to SQL Express

Hi,

Could someone help me on how I can store images into my SQL Table
I'm using "Image" as Column Name and I use VWD 2005 Express.
I saw a lot of C# examples, but not VB.NET examples.

Please help !!!!!

Bart

Answer this question

Uploading images to SQL Express

Raphael Londner
I'll do my best.

Mat
charliestrause
In VB6 I use

Dim F As Long, Data() As Byte, FileSize As Long

Open sFilename For Binary As #F
FileSize = LOF(F)
Data = InputB(FileSize, F)
Close #F

rs2.Open "select * from catalog where catalog.[catalog-no]='123'", cn, adOpenKeyset, adLockOptimistic

rs2!blob = Data
rs2.Update
rs2.Close

Might be of use
Vladimir Tchalkov MVP
Sorry Bart, I don't programm ASPX (yet). I programmed it as a Stand alone in VB.Net.
ComputerNut
If I wanted to add a description to identify each image, how would I do it

Assuming my tables are:

ID         index key
Photo    Image
Name    Description nchar(10)

How would I formulate the INSERT COMMAND

QuerySQL = "INSERT INTO Table (Image) VALUES (@Photo),(@Name)"

Dim comsql As New SqlClient.SqlCommand(QuerySQL, "c:\Image.mdf")

comSQL.Parameters.Add(New SqlClient.SqlParameter("@Image", SqlDbType.Image).Value = bNachrichtimage, @Name,

Thank you.
minsim
Thanks Lars.... can you believe it, I actually figured it out... but I didn't know that there was an auto increment with the ID Key... so I guess I'll go back and take a look at it.

I really hate to keep asking questions, but is there a BOOK that helped you learn all this stuff I did VB6 (Novice - had to use VB4 manuals - VB6 didn't ship with any). I want to be an expert and not a dummy.

As for books, I'm interested in VB express with SQL.

Anyway, I have several pictures in the DB, Desc: Flowers, Birds, Trees, etc.
When I do a Query on say... Flowers (4 items in the DB), how do I select one item at a time for viewing in a picture box

For instance, if I am doing a query on flowers, 4 results will be returned. What code do I write to select each result for individual display

Thanks again.
FST
Hi,

Can someone give me a complete example of his environment
I'm rather new to VB.NET.

What I mean is (if possible) the complete code of the ASPX page and the code behind file .vb

Thanks to all....
Bart
Tuhin
Thanks Mat,

I'm using VB.NET and I'm rather new to ASP.NET and VB.NET.
If I put my code here in the forum, could you help me

Thanks already!!!!
B.
hemaral
You have to add for each Value a SQLParameter like this

dim id as integer = 1
dim desc as string = "Some description"
QuerySQL = "Insert Into Table (ID, Image, Name) VALUE (@ID, @Photo, @Name)"

comsql.Parameter.add(New SqlClient.SqlParameter("@ID", SqlDbType.Int).Value = id
comsql.Parameter.add(New SqlClient.SqlParameter("@Photo", SqlDbType.Image).Value = Photo
comsql.Parameter.add(New SqlClient.SqlParameter("@Name", SqlDbType.nchar).Value = desc

If your key (id) is set to Autoincrement in your database, you must NOT add the first Parameter!!!

After you have add all your Parameters, just execute the query

Regards
Lars Breiter
mrcs
I'm using VB & SQL express 2005...

Where do you add the SQL database name
akegalj
Thanks... I'm really slow at this...

I was looking at the (SQL) Data Type such as nchar(10)... does that mean
it holds 10 characters

What if I wanted more or less than 10, how do I set the amount
Jin12551
Okay, I figured it out.... told you I was slow at this.
Dhanya
I do it this way (in VB.net and SQL-Server 2000)
=======================

Dim f As New IO.FileInfo(File)

Dim fs As IO.FileStream

Dim bNachrichtimage() As Byte

ReDim bNachrichtimage(f.Length)

fs = f.OpenRead

fs.Read(bNachrichtimage, 0, f.Length)

fs.Close()

QuerySQL = "INSERT INTO Table (Image) VALUES (@Image)"

Dim comSQL As New SqlClient.SqlCommand(QuerySQL, cnSQL)

comSQL.Parameters.Add(New SqlClient.SqlParameter("@Image", SqlDbType.Image)).Value = bNachrichtimage

comSQL.ExecuteNonQuery()
===================================

This is just a example from my Programm. But it works great!
BrandyR
Hi Mat,

Thanks in advance for helping me...

Here is my code :

System.InvalidCastException: Unable to cast object of type 'System.Web.UI.WebControls.FileUpload' to type 'System.Web.UI.HtmlControls.HtmlInputFile'.

This my code behind file :

Protected Sub DetailsView1_ItemUpdating(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.DetailsViewUpdateEventArgs) Handles DetailsView1.ItemUpdating

'copy file data into record

Dim upload As HtmlInputFile = CType(Me.DetailsView1.FindControl("FileUpload1"), HtmlInputFile)

If Not upload Is Nothing Then

If upload.PostedFile.FileName <> "" Then

'copy file to a byte array in memory

Dim data(upload.PostedFile.ContentLength) As Byte

upload.PostedFile.InputStream.Read(data, 0, upload.PostedFile.ContentLength)

'get filename part only

Dim filename As String

Dim tmp As Integer = upload.PostedFile.FileName.LastIndexOf("\")

If tmp >= 0 Then

'strip directory part

filename = upload.PostedFile.FileName.Substring(tmp + 1, _

upload.PostedFile.FileName.Length - tmp - 1)

Else

filename = upload.PostedFile.FileName

End If

'save this to the fields

e.NewValues("image1") = data

'e.NewValues("filename") = filename

'e.NewValues("contenttype") = upload.PostedFile.ContentType

End If

End If

End Sub

End Class

Could you tell me what I did wrong

Here is my DetailsView page with the SQLDataSource :

<asp:SqlDataSource ID="SqlDataSource1"

runat="server" ConnectionString="<%$ ConnectionStrings:TESTDBConnectionString %>"

DeleteCommand="DELETE FROM aspnet_BuyGroundImages WHERE (BuyGroundID = @Original_BuyGroundID)"

InsertCommand="INSERT INTO aspnet_BuyGroundImages(BuyGroundDescription, Image1, Image2, Image3, Image4, Image5, OptionOn, Sold, Price) VALUES (@BuyGroundDescription, @Image1, @Image2, @Image3, @Image4, @Image5, @OptionOn, @Sold, @Price)" SelectCommand="SELECT BuyGroundID, BuyGroundDescription, Image1, Image2, Image3, Image4, Image5, OptionOn, Sold, Price FROM aspnet_BuyGroundImages" UpdateCommand="UPDATE aspnet_BuyGroundImages SET BuyGroundDescription = @BuyGroundDescription, Image1 = @Image1, Image2 = @Image2, Image3 = @Image3, Image4 = @Image4, Image5 = @Image5, OptionOn = @OptionOn, Sold = @Sold, Price = @Price WHERE (BuyGroundID = @original_BuyGroundID)">

<DeleteParameters>

<asp:Parameter Name="Original_BuyGroundID" />

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="BuyGroundDescription" />

<asp:Parameter Name="Image1" />

<asp:Parameter Name="Image2" />

<asp:Parameter Name="Image3" />

<asp:Parameter Name="Image4" />

<asp:Parameter Name="Image5" />

<asp:Parameter Name="OptionOn" />

<asp:Parameter Name="Sold" />

<asp:Parameter Name="Price" />

<asp:Parameter Name="original_BuyGroundID" />

</UpdateParameters>

<InsertParameters>

<asp:Parameter Name="BuyGroundDescription" />

<asp:Parameter Name="Image1" />

<asp:Parameter Name="Image2" />

<asp:Parameter Name="Image3" />

<asp:Parameter Name="Image4" />

<asp:Parameter Name="Image5" />

<asp:Parameter Name="OptionOn" />

<asp:Parameter Name="Sold" />

<asp:Parameter Name="Price" />

</InsertParameters>

</asp:SqlDataSource>

<asp:DetailsView ID="DetailsView1" runat="server" AllowPaging="True" AutoGenerateRows="False"

DataKeyNames="BuyGroundID" DataSourceID="SqlDataSource2" Height="50px" Width="125px">

<Fields>

<asp:BoundField DataField="BuyGroundID" HeaderText="BuyGroundID" InsertVisible="False"

ReadOnly="True" SortExpression="BuyGroundID" />

<asp:BoundField DataField="BuyGroundDescription" HeaderText="BuyGroundDescription"

SortExpression="BuyGroundDescription" />

<asp:TemplateField HeaderText="Image1" SortExpression="Image1">

<ItemTemplate>

<asp:Label ID="Label1" runat="server" Text='<%# Bind("Image1") %>'></asp:Label>

</ItemTemplate>

<EditItemTemplate>

<asp:FileUpload ID="FileUpload1" runat="server" />



</EditItemTemplate>

<InsertItemTemplate>

<asp:FileUpload ID="FileUpload2" runat="server" />



</InsertItemTemplate>

</asp:TemplateField>

<asp:BoundField DataField="Image2" HeaderText="Image2" SortExpression="Image2" />

<asp:BoundField DataField="Image3" HeaderText="Image3" SortExpression="Image3" />

<asp:BoundField DataField="Image4" HeaderText="Image4" SortExpression="Image4" />

<asp:BoundField DataField="Image5" HeaderText="Image5" SortExpression="Image5" />

<asp:CheckBoxField DataField="OptionOn" HeaderText="OptionOn" SortExpression="OptionOn" />

<asp:CheckBoxField DataField="Sold" HeaderText="Sold" SortExpression="Sold" />

<asp:BoundField DataField="Price" HeaderText="Price" SortExpression="Price" />

<asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowInsertButton="True" />

</Fields>

</asp:DetailsView>

 <br />
Ravirudra
OK, in this Code:

Dim comSQL As New SqlClient.SqlCommand(QuerySQL, cnSQL)

is the Objekt "cnSQL" my connection to a Database like this

dim constr as String
constr = 'put here your connection string, it must contain all data like databasename, server, user etc.

after that you can define a connection to a database like this:

dim cnSQL as new SQLClient.SQLconnection(constr)
cnSQL.open
.
.
.
.
Uploading images to SQL Express

Answer this question