Help me to save image into mssql database

[manuaaa]

Please help me to save image to ms sql database. the database is available in web server. i am saving data through sql query like.

a.Query("INSERT INTO TAB_DAY_PLANNER (EMPID, Date_ID, DP_DATE, TIME_FROM, TIME_TO, PLANNED_PLACE, PLANNED_INSTITU, PLANNED_WITH, worktype, remark) VALUES("& Password.Text &", 101, '" & datetxt.Text & "', '" & frmbtn.Text & "', '" & tobtn.Text & "', '" & Spinner1.SelectedItem & "', '" & Spinner3.SelectedItem & "', '" & Spinner2.SelectedItem & "', '" & spLanguage.SelectedItem & "', '" & Remark.Text & "' )")

Pl help me to save image.

Thanks
manuaaa
Forward Message

 

[Erel]

Are you using the ASP .Net script?

 

[CARTHO]

To be able to store an image in MS SQL you'll have to specify the datafield-type as a BLOB (Binary Large OBject).

 

[wl]

Once you get the image uploaded to the server (eg: asp.net page) you can insert the uploaded blob (image) using openrowset.

See Working with Large Value Types

 

[manuaaa]

Dear Erel

Now I have changed my mind and instead of saving picture in data base I want to upload to my webserver. There is also facing problem which I have mentioned in my last thread.

Please help me.

thanks

 

[Erel]

Where is the other thread?

 

[Kwame Twum]

Guys, I'm using the ASP.Net script and still want to insert an image into the database. I hit F5 to begin debug and I end up with an error.
Here are the codes:
The code that does the Image conversion

Sub Read_Image(Dir As String, FileName As String) As Byte()
    Dim out As OutputStream
    out.InitializeToBytesArray(100)
    File.Copy2(File.OpenInput(Dir, FileName), out)
    Return out.ToBytesArray
End Sub

The code that sends the data to the server

Sub go_Click
    If tel.Text<>"" and u_name.Text<>"" Then
       ProgressDialogShow("Saving Your Details...")
       Dim byt() As Byte
       byt = Read_Image(File.DirRootExternal,"/Folder/me.jpg")
       'The error occurs on the next line
       new_mem.PostString(ServerUrl, "INSERT INTO MyTable (cellular,uname,img) VALUES ('" & tel.Text & "','" & u_name.Text & "','" & byt() & "')") 'new_mem has already been declared and initialized
    Else
        ToastMessageShow("Please fill required information",False)
    End If
End Sub

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
What can possibly be wrong? Seems to be a conversion issue
The error I get is attached below

 

[Erel]

Your code fails because you try to insert raw bytes into a string. This is not possible. You can convert the bytes to a base64 string (with StringUtils). However you will also need to modify the script to handle the encoded string.

 

[Kwame Twum]

The datatype of the database field is image. so the bytes would go into the database as an image... Maybe I don't get you. I'll however try using the StringUtils.. But which part of the script do I have to modify?

 

[Erel]

If you can use RDC instead of the script then it will be much simpler.

With the current script you should send the query as a string. You need to check with MS SQL documentation, how you can insert blobs as strings. It will not happen automatically.

 

[Kwame Twum]

With the current script you should send the query as a string. You need to check with MS SQL documentation, how you can insert blobs as strings. It will not happen automatically.

It does happen... I just can't figure a way to display the image after retrieving the blob as a json string when queried.

 

[Erel]

You should probably decode it from Base64 string to an array of bytes. It should be simple to convert an array of bytes to an image (InputStream.InitializeFromBytesArray).

 

[Kwame Twum]

Thanx Erel, I did exactly that and ended up with this.

 

[Erel]

Please post in a single thread.

You should take the List from the json string and convert it to an array of bytes.

 

[michals]

Hi, imm trying read image from mssql database with mssql libra.

Private Sub Image2

Private Sub Image2

    Dim imgstring As String
     Dim imgLogo As ImageView
        imgstring ="0xFFD8FFE000104A46494600010101012C012C0000FFE100DE45786966000049492A000800000009000001030001000000C40B00000101030001000000FE0D0000020103000100000008000........A069F12772DC4508B7B3B2B58D9CB2585ADB0658C31CB4971"
 
   Dim bytes(  ) As Byte
bytes=imgstring.GetBytes("UTF-8")

   Dim bmp As Bitmap
   Dim In As InputStream
   In.InitializeFromBytesArray(bytes , 0, bytes.Length)
   bmp.Initialize2(In)
   Activity.SetBackgroundImage(bmp)
    
End Sub

Code returns error on line bmp.Initialize2(In) : Error loading bitmap
Any ideas why ?

 

[Erel]

Assuming that this is indeed a valid image then remove the 0x at the beginning.

 

[michals]

Assuming that this is indeed a valid image then remove the 0x at the beginning.

still having same error afeter removing 0x
also when i select image from mssql
with code:

Dim l As List
Dim msqlcmd As String
Dim r As List
msqlcmd = "select top 1 img from images where id=5 "

l = msql.Query( msqlcmd)
If l.IsInitialized Then
     If  l.Size  > 0 Then
         r = l.Get(1)
       
       
    Dim sim As String
     Dim imgLogo As ImageView
         Dim su As StringUtils
      Dim buffer() As Byte
    Dim b As Bitmap
   buffer =   su.DecodeBase64(r.Get(0))
    Dim In As InputStream
    In.InitializeFromBytesArray(buffer, 0, buffer.Length)
    b.Initialize2(In)
    imgLogo.Bitmap = b
            
End If
End If

 

[michals]

 

[Erel]

Can you save this string to a file and upload the file?

 

[michals]

string you can download from here
Image was added with vb.net code

  If r = Windows.Forms.DialogResult.OK Then
                path = d.FileName
                Dim bfs As New FileStream(path, FileMode.Open)
                Dim bit(bfs.Length) As Byte
                bfs.Read(bit, 0, bfs.Length)
                bfs.Close()
                cn.Open()
                cmd.CommandText = "update images set img =@Image where id=" & prid
                cmd.Parameters.AddWithValue("@Image", bit)
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                cn.Close()
            End If
            RefreshImager()

on desktop app works fine,