code for saving data along with image in sql server

[mshravs]

hey,
I'm working on a project, where I need to save data along with the image in Sql server, using Vb 2005. i just need the code for the above one.
please post asap need it urgently.

[sknake]

Here is code to save and retrieve an image with other columns:

Public Class frmImgUpload

  Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'Insert an image
    Using conn As New System.Data.SqlClient.SqlConnection("Data Source=apex2006sql;Initial Catalog=Scott;Integrated Security=True;")
      conn.Open()
      Using cmd As New SqlClient.SqlCommand("Insert Into Picture (Name, CreateDate, Picture) Values (@Name, @CreateDate, @Picture)", conn)
        cmd.Parameters.Add(New SqlClient.SqlParameter("@Name", SqlDbType.VarChar)).Value = "Picture 1"
        cmd.Parameters.Add(New SqlClient.SqlParameter("@CreateDate", SqlDbType.VarChar)).Value = DateTime.Today
        cmd.Parameters.Add(New SqlClient.SqlParameter("@Picture", SqlDbType.Image)).Value = IO.File.ReadAllBytes("C:\picture.bmp")
        cmd.ExecuteNonQuery()
      End Using
    End Using
  End Sub

  Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    'Read an image
    Using conn As New System.Data.SqlClient.SqlConnection("Data Source=apex2006sql;Initial Catalog=Scott;Integrated Security=True;")
      conn.Open()
      Using cmd As New SqlClient.SqlCommand("Select Top 1 Name, CreateDate, Picture From Picture", conn)
        Using dr As SqlClient.SqlDataReader = cmd.ExecuteReader()
          Using dt As New DataTable
            dt.Load(dr)
            Dim row As DataRow = dt.Rows(0)
            Dim sName As String = Convert.ToString(row("Name"))
            Dim dtCreateDate As DateTime = Convert.ToDateTime(row("CreateDate"))
            Using ms As New IO.MemoryStream(CType(row("Picture"), Byte()))
              Dim img As Image = Image.FromStream(ms)
              PictureBox1.Image = img
            End Using
            MessageBox.Show("Loaded image " + sName)
          End Using
        End Using
      End Using
    End Using
  End Sub
End Class

[mshravs]

i have used this code, but when i run this i'm getting an exceptional handling saying that system.data.sqlclient.sqlexceptional...............what is the problem...even after setting a right data configuration i'm getting this one.........plz help thanks

[sknake]

More than likely you forgot to change the connection string, query, column names, etc. The SQL code is specific to my table structure...

[mshravs]

thanks for the code its working now

[sknake]

I'm glad you got it working

Please mark this thread as solved if you have found an answer to your question and good luck!

[mshravs]

while running this program i'm getting this error
"Conversion failed when converting character string to smalldatetime data type."
the following is the code.........plz tell where i'm wrong

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click


        Dim ConnStr As String
        Dim visitors_name, officer_to_visit, purpose, date_of_visit, time_in, time_out As String
        Dim serial_no, year, floor As Integer
        ConnStr = ConfigurationManager.ConnectionStrings("gate").ConnectionString()

        Dim conn As New SqlConnection(ConnStr)

        conn.Open()
        serial_no = serialno.Text
        year = years.Text
        visitors_name = visitorsname.Text
        officer_to_visit = officertovisit.Text
        purpose = pur.Text
        date_of_visit = dateofvisit.Text
        floor = wing.Text
        time_in = timein.Text
        time_out = timeout.Text
        

        Dim adapter As New SqlClient.SqlDataAdapter("select serial_no,year,visitors_name,officer_to_visit,purpose,date_of_visit,floor,time_in,time_out from visitors_pass", conn)
        Dim insert As New SqlClient.SqlCommand("insert into visitors_pass (serial_no,year,visitors_name,officer_to_visit,purpose,date_of_visit,floor,time_in,time_out) Values('" & serial_no & "' ,'" & year & "', '" & visitors_name & "','" & officer_to_visit & "','" & purpose & "','" & floor & "','" & date_of_visit & "','" & time_in & "','" & time_out & "')", conn)

        'insert.Parameters.Add(New SqlClient.SqlParameter("serial_no", SqlDbType.SmallInt)).Value = 1
        'insert.Parameters.Add(New SqlClient.SqlParameter("year", SqlDbType.SmallInt)).Value = 2009
        'insert.Parameters.Add(New SqlClient.SqlParameter("visitors_name", SqlDbType.NChar)).Value = "null"
        'insert.Parameters.Add(New SqlClient.SqlParameter("officer_to_visit", SqlDbType.NChar)).Value = "null"
        'insert.Parameters.Add(New SqlClient.SqlParameter("purpose", SqlDbType.NChar)).Value = "null"
        'insert.Parameters.Add(New SqlClient.SqlParameter("date_of_visit", SqlDbType.SmallDateTime)).Value = DateTime.Today
        'insert.Parameters.Add(New SqlClient.SqlParameter("floor", SqlDbType.SmallInt)).Value = "null"
        'insert.Parameters.Add(New SqlClient.SqlParameter("time_in", SqlDbType.smalldatetime)).Value = "null"
        'insert.Parameters.Add(New SqlClient.SqlParameter("time_out", SqlDbType.smalldatetime)).Value = DateTime.Today

        insert.ExecuteNonQuery()
        conn.Close()
        MessageBox.Show("record saved")


        printb.Enabled = "true"
    End Sub

the config is in the appconfig

[sknake]

Please use code tags when posting code on daniweb:

[code]

...code here...

[/code]

From what I can tell you effectively remove using parameters from the query which will lead to all kinds of errors in your application. Please restore the parameterized SQL and post the code back in code tags.

One hint: you don't set a column value to a string "null" if you want to use a database null. You should use parameter.Value = DBNull.Value

[mshravs]

while running this program i'm getting this error
"Conversion failed when converting character string to smalldatetime data type."
the following is the code.........plz tell where i'm wrong

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click


Dim ConnStr As String
Dim visitors_name, officer_to_visit, purpose, date_of_visit, time_in, time_out As String
Dim serial_no, year, floor As Integer
ConnStr = ConfigurationManager.ConnectionStrings("gate").ConnectionString()

Dim conn As New SqlConnection(ConnStr)

conn.Open()
serial_no = serialno.Text
year = years.Text
visitors_name = visitorsname.Text
officer_to_visit = officertovisit.Text
purpose = pur.Text
date_of_visit = dateofvisit.Text
floor = wing.Text
time_in = timein.Text
time_out = timeout.Text


Dim adapter As New SqlClient.SqlDataAdapter("select serial_no,year,visitors_name,officer_to_visit,purpose,date_of_visit,floor,
time_in,time_out from visitors_pass", conn)
Dim insert As New SqlClient.SqlCommand("insert into visitors_pass (serial_no,year,visitors_name,officer_to_visit,purpose,date_of_visit,floor,
time_in,time_out) Values('" & serial_no & "' ,'" & year & "', '" & visitors_name & "','" & officer_to_visit & "','" & purpose & "','" & floor & "','" & date_of_visit & "','" & time_in & "','" & time_out & "')", conn)

'insert.Parameters.Add(New SqlClient.SqlParameter("serial_no", SqlDbType.SmallInt)).Value = 1
'insert.Parameters.Add(New SqlClient.SqlParameter("year", SqlDbType.SmallInt)).Value = 2009
'insert.Parameters.Add(New SqlClient.SqlParameter("visitors_name", SqlDbType.NChar)).Value = "null"
'insert.Parameters.Add(New SqlClient.SqlParameter("officer_to_visit", SqlDbType.NChar)).Value = "null"
'insert.Parameters.Add(New SqlClient.SqlParameter("purpose", SqlDbType.NChar)).Value = "null"
'insert.Parameters.Add(New SqlClient.SqlParameter("date_of_visit", SqlDbType.SmallDateTime)).Value = DateTime.Today
'insert.Parameters.Add(New SqlClient.SqlParameter("floor", SqlDbType.SmallInt)).Value = "null"
'insert.Parameters.Add(New SqlClient.SqlParameter("time_in", SqlDbType.smalldatetime)).Value = "null"
'insert.Parameters.Add(New SqlClient.SqlParameter("time_out", SqlDbType.smalldatetime)).Value = DateTime.Today

insert.ExecuteNonQuery()
conn.Close()
MessageBox.Show("record saved")


printb.Enabled = "true"
End Sub

the database configuration is done in the appconfig
help me plz

[mshravs]

plz anyone tell what's wrong in code
i delibertly needed it

[sknake]

You undid the parameterized SQL and inserted the value members directly in to the command text. Did you expect for a result other than it breaking?

I gave you an example of parameterized SQL. Use it. If you're still having problems I will help you from there. Inserting values in the command text decreases server performance and introduces significant security problems with SQL-Injection.

[dapsin999]

Can anybody please help me with this code:

Private Sub SavePicture()

        Dim PictureId() As String = Split(txtPic.Text, "\")
        Array.Reverse(PictureId)
        Dim ms1 As New MemoryStream()
        [B]PicBox.Image.Save(ms1, PicBox.Image.RawFormat)[/B]
        Dim Picture() As Byte = ms1.GetBuffer
        ms1.Close()


        Dim SignatureId() As String = Split(txtSign.Text, "\")
        Array.Reverse(SignatureId)
        Dim ms2 As New MemoryStream()
       [B] SignBox.Image.Save(ms2, SignBox.Image.RawFormat)[/B]
        Dim Signature() As Byte = ms2.GetBuffer
        ms2.Close()


        Dim FingerPrintId() As String = Split(txtFinger.Text, "\")
        Array.Reverse(FingerPrintId)
        Dim ms3 As New MemoryStream()
   [B]     FingerBox.Image.Save(ms3, FingerBox.Image.RawFormat)[/B]
        Dim FingerPrint() As Byte = ms3.GetBuffer
        ms3.Close()

        cn.Open()
        Try
            Dim strSQL As String = _
                "INSERT INTO Images (RECRUITMENTNO, PictureId, Picture,SignatureId,Signature,FingerPrintId,FingerPrint)" & _
                "VALUES ('" & txtRecruitmentno.Text & "', @PictureId, @Picture, @SignatureId, @Signature, @FingerPrintId, @FingerPrint)"

            Dim cmd As New SqlCommand(strSQL, cn)
            With cmd
                .Parameters.Add(New SqlParameter("@PictureId", SqlDbType.NVarChar, 50)).Value = PictureId(0)
                .Parameters.Add(New SqlParameter("@Picture", SqlDbType.Image)).Value = Picture
                .Parameters.Add(New SqlParameter("@SignatureId", SqlDbType.NVarChar, 50)).Value = SignatureId(0)
                .Parameters.Add(New SqlParameter("@Signature", SqlDbType.Image)).Value = Signature
                .Parameters.Add(New SqlParameter("@FingerPrintId", SqlDbType.NVarChar, 50)).Value = FingerPrintId(0)
                .Parameters.Add(New SqlParameter("@FingerPrint", SqlDbType.Image)).Value = FingerPrint


            End With

            cmd.ExecuteNonQuery()


            MessageBox.Show(PictureId(0) & " saved to the database.", "Image Save Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
            MessageBox.Show(SignatureId(0) & " saved to the database.", "Image Save Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
            MessageBox.Show(FingerPrintId(0) & " saved to the database.", "Image Save Status", MessageBoxButtons.OK, MessageBoxIcon.Information)
        Catch sqlExc As SqlException
            MessageBox.Show(sqlExc.ToString, "SQL Exception Error!", _
                MessageBoxButtons.OK, MessageBoxIcon.Error)
        Catch exc As Exception
            MessageBox.Show(exc.Message)
        End Try
        cn.Close()

    End Sub
    Private Sub DisplayPicture()
        Dim Picture As String
        Dim Signature As String
        Dim FingerPrint As String
        Dim insd As New SqlCommand
        Dim selectdata As String
        cn.Open()


        selectdata = " select PICTUREID,PICTURE,SIGNATUREID,SIGNATURE,FINGERPRINTID,FINGERPRINT FROM Images where RECRUITMENTNO = '" & txtRecruitmentno.Text & "'"
        insd.Connection = cn
        insd.CommandType = CommandType.Text
        insd.CommandText = selectdata

        Try
            Dim dr As SqlDataReader = insd.ExecuteReader
            dr.Read()
            Dim arrPicture() As Byte = CType(dr.Item(1), Byte())
            Dim arrSignature() As Byte = CType(dr.Item(3), Byte())
            Dim arrFingerPrint() As Byte = CType(dr.Item(5), Byte())
            Dim ms1 As New MemoryStream(arrPicture)
            If arrPicture.GetUpperBound(0) > 0 Then
                ms1 = New MemoryStream(arrPicture)
                txtPic.Text = dr.Item(0).ToString
                'lblFileName.Text = dr.Item(1).ToString
                With PicBox
                    .Image = Image.FromStream(ms1)
                    .SizeMode = PictureBoxSizeMode.Zoom
                    .BorderStyle = BorderStyle.FixedSingle
                End With
                ms1.Flush()
                ms1.Close()
            End If
            Dim ms2 As New MemoryStream(arrSignature)
            If arrSignature.GetUpperBound(0) > 0 Then
                ms2 = New MemoryStream(arrSignature)
                txtSign.Text = dr.Item(2).ToString
                'lblFileName.Text = dr.Item(1).ToString
                With SignBox
                    .Image = Image.FromStream(ms2)
                    .SizeMode = PictureBoxSizeMode.Zoom
                    .BorderStyle = BorderStyle.FixedSingle
                End With
                ms2.Flush()
                ms2.Close()
            End If

            Dim ms3 As New MemoryStream(arrFingerPrint)
            If arrFingerPrint.GetUpperBound(0) > 0 Then
                ms3 = New MemoryStream(arrFingerPrint)
                txtFinger.Text = dr.Item(4).ToString
                'lblFileName.Text = dr.Item(1).ToString
                With FingerBox
                    .Image = Image.FromStream(ms3)
                    .SizeMode = PictureBoxSizeMode.Zoom
                    .BorderStyle = BorderStyle.FixedSingle
                End With
                ms3.Flush()
                ms3.Close()


                dr.Close()
            Else
                Picture = Nothing
                Signature = Nothing
                FingerPrint = Nothing
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message)
        End Try
        cn.Close()


    End Sub

When i run it, it saves and displays image but its throws an exception(the part highlighted) when i try to update. That is if, i am saving a data that has one or all of the retrieved pictures.
Please i need help asap.
Thank you very much
Dapsin