Read and Write BLOB Data HELP!

[timtom1]

I am following this tutorial here http://support.microsoft.com/default.aspx?scid=kb;en-us;308042 the only thing I have changed it the field names in the tables.

but I get the following error message on this code when I debug it

Error: Column 'img_id' does not allow nulls

[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] fs [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] FileStream _[/SIZE]
[SIZE=2]([/SIZE][SIZE=2][COLOR=#800000]"C:\test_img.jpg"[/COLOR][/SIZE][SIZE=2], FileMode.OpenOrCreate, _[/SIZE]
[SIZE=2]FileAccess.Read)[/SIZE]
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] MyData(fs.Length) [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Byte[/COLOR][/SIZE]
[SIZE=2]fs.Read(MyData, 0, fs.Length)[/SIZE]
[SIZE=2]fs.Close()[/SIZE]
[SIZE=2]conn.Open()[/SIZE]
[SIZE=2]da.Fill(ds, [/SIZE][SIZE=2][COLOR=#800000]"db_img"[/COLOR][/SIZE][SIZE=2])[/SIZE]
 
 
[SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] myRow [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataRow = ds.Tables(0).NewRow[/SIZE]
 
[SIZE=2]myRow([/SIZE][SIZE=2][COLOR=#800000]"img_name"[/COLOR][/SIZE][SIZE=2]) = [/SIZE][SIZE=2][COLOR=#800000]"Name of Image"[/COLOR][/SIZE]
[SIZE=2]myRow([/SIZE][SIZE=2][COLOR=#800000]"img_type"[/COLOR][/SIZE][SIZE=2]) = MyData[/SIZE]
[SIZE=2]ds.Tables([/SIZE][SIZE=2][COLOR=#800000]"db_img"[/COLOR][/SIZE][SIZE=2]).Rows.Add(myRow)[/SIZE]
[SIZE=2]da.Update(ds, [/SIZE][SIZE=2][COLOR=#800000]"db_img"[/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2]fs = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]MyCB = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]ds = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]da = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]conn.Close()[/SIZE]
[SIZE=2]conn = [/SIZE][SIZE=2][COLOR=#0000ff]Nothing[/COLOR][/SIZE]
[SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#800000]"Image saved to database"[/COLOR][/SIZE][SIZE=2])[/SIZE]

 

[cjard]

Error: Column 'img_id' does not allow nulls

Curiously enough, the img_id column doesnt allow nulls (blank values), yet you never set it to a value, so it's going to be blank, so you can kinda guess how the error arises...

 

[timtom1]

with out sounding too thick? like this?

myRow([SIZE=2][COLOR=#800000]"img_id"[/COLOR][/SIZE][SIZE=2]) = [/SIZE][SIZE=2][COLOR=#800000]"WHAT GOES HERE? "[/COLOR][/SIZE]

 

[TechGnome]

no... by going into the design of the table in the database, and making sure that that field allows nulls.... currently it does not.

-tg

 

[cjard]

Well, that's an option.. Or put some valid data in the field.

I dunno, mang.. It's your database. You cant really ask us what to put in that ID field.. Ask whomever knows the database?!

 

[timtom1]

I created the database, its just a simple one it should auto increment but its not doing?

 

[TechGnome]

D'oh.... my bad, I mis read what col is the problem....

What dbms? Access? Oracle? SQL Server?

In Access, look at the table definition and see if the AutoIncrement flag is set.
In SQL Server, check to see if the col is set as an Identity.
Oracle.... I'm not sure about that.

-tg

 

[timtom1]

How do you add the reference to the mysql code library I've forgotten how. so I can use this again.

Imports MySql.Data.MySqlClient

I really am rubbish at vb

 

[cjard]

Its a lot to remember!

Go to Solution Explorer
Click second icon from Left - Show All Files
References folder appears, right click it
Choose add reference
Add a reference to the MySQL dll

 

[timtom1]

ok up to now I have it inserting rows into the database and giving the message "Image saved into Databse" yet the fields are still Null?

 Private Sub ButtonUpload_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ButtonUpload.Click
        conn = New MySqlConnection()
        ' connect to db
        conn.ConnectionString = "server=localhost;" & _
       "user id=*****;" & _
       "password=******;" & _
       "database=******"
        'open db connection
        conn.Open()
        Try
            Dim ms As New MemoryStream
            Me.PictureBoxDisplay.Image.Save(ms, Me.PictureBoxDisplay.Image.RawFormat)
            Dim arrayImage() As Byte = ms.GetBuffer
            ms.Close() ' Closes the Memory Stream
            'just take file name from path
            Dim nStr As String = Me.TextBoxPath.Text.Substring(Me.TextBoxPath.Text.LastIndexOf("\") + 1)
            Dim strQuery As String = "INSERT INTO image(Name, Picture) VALUES(@Name, @Picture)"
            Dim objcommand As New MySqlCommand(strQuery, Me.conn)
            With objcommand
                .Parameters.Add(New MySqlParameter("@Name", MySqlDbType.VarChar, 50)).Value = nStr
                .Parameters.Add(New MySqlParameter("@Picture", MySqlDbType.Blob)).Value = arrayImage
            End With
            objcommand.ExecuteNonQuery()
' Displayes the this message
            MessageBox.Show("Image Saved into the Database")
        Catch myerror As MySqlException
            MessageBox.Show("Error Connecting to Database: " & myerror.Message)
        Finally
            'close connection
            conn.Dispose()
        End Try
    End Sub

 

[cjard]

are you sure that the parameters are mapped correctly? If i wrote this sql for oracle:

INSERT INTO a VALUESp1, 2)

Then I would make the parameters like:

parameters.Add("p1", OracleDbType.Varchar)



Note the colon is gone from the name. Check on the MySQL documentation. Also does MySQL understand blobs? I dont know

 

[timtom1]

*********SOLVED Thanks for all your help***********

 

[cjard]

And the solution was... (in case others have that same issue) ...