Uploading images to SQL Express

Question

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 1

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 ?

Answer 2

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. 

Answer 3

I'll do my best.

Mat

Answer 4

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" />

<!-- <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Image1") %>'></asp:TextBox> -->

</EditItemTemplate>

<InsertItemTemplate>

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

<!-- <asp:TextBox ID="TextBox1" runat="server" Text='<%# Bind("Image1") %>'></asp:TextBox> -->

</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>

&nbsp;<br />

Answer 5

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!

Answer 6

I'm using VB & SQL express 2005...

Where do you add the SQL database name?

Answer 7

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
.
.
.
.

Answer 8

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?

Answer 9

Okay, I figured it out.... told you I was slow at this.

Answer 10

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.

Answer 11

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

Answer 12

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

Answer 13

Sorry Bart, I don't programm ASPX (yet). I programmed it as a Stand alone in VB.Net.

Answer 14

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.

Answer 15

There are numbers of Books, some good, some bad. But I didn't really use one. I have a few, but I look in this books just sometimes. I can't recomment one at this time, sorry.

As for your Problem with more than one result, I work on that and get back to you.

CU
Lars

Answer 16

Someone said VB Express is just an IDE for .Net framework and to interface with SQL express.  Does this mean VB Express can only be used with SQL express?

Answer 17

I'm not an elite programmer, but I was thinking....

Have the query reference a datagrid, obtain how many
records (or row) on the grid and view each record one by one using
the grid's row?

I don't know if this is sound programming, but I think it might be
really slow.