code for saving data along with image in sql server

Question

mshravs 0 Junior Poster in Training

16 Years Ago

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.

.net:vb.net graphics:image microsoft:sql-server

3 Contributors
11 Replies
224 Views
3 Weeks Discussion Span
Latest Post 16 Years Ago Latest Post by dapsin999

Dani AI

Generated 7 Months Ago

The error "Conversion failed when converting character string to smalldatetime" is a type-conversion problem, not an image problem. In this thread already pointed toward parameterized SQL; the break happened after replaced parameters with string concatenation. That allows textbox text (or the literal string "null") to be sent directly to a smalldatetime column and causes SQL Server to try—and fail—to convert it. The reliable fix is: parse input into the correct CLR types in VB, pass those types as parameters with explicit SqlDbType, and use DBNull.Value for missing values.

A minimal pattern to follow (parse first, then add parameters) looks like this:

' build parameterized command text once
Dim sql = "INSERT INTO visitors_pass (..., date_of_visit, ..., time_in, time_out) VALUES (..., @dateVisit, ..., @tIn, @tOut)"
Using cmd As New SqlClient.SqlCommand(sql, conn)
  Dim dt As DateTime
  If DateTime.TryParse(dateOfVisitTextBox.Text, dt) Then
    cmd.Parameters.Add("@dateVisit", SqlDbType.SmallDateTime).Value = dt
  Else
    cmd.Parameters.Add("@dateVisit", SqlDbType.SmallDateTime).Value = DBNull.Value
  End If

  ' handle time/date-only entries the same way; convert integers with Integer.TryParse and use DBNull.Value for blanks
  ' add other parameters here, then execute the command
End Using

Additional practical notes tied to later posts: ’s code used MemoryStream.GetBuffer(), which can include unused bytes—use MemoryStream.ToArray() to get the exact image bytes. Prefer SQL Server varbinary(max) and SqlDbType.VarBinary instead of the deprecated image type for new designs. Never set a column to the literal string "null"; use DBNull.Value.

Quick troubleshooting checklist:

Confirm table column types in the database match the parameter types being sent.
Validate/parse every textbox value (dates, times, ints) before adding parameters.
Log parameter values (or use SQL Profiler) to see what the server actually receives.
Turn on Option Strict/compile-time checks to catch implicit conversions early.

Following these steps will stop the smalldatetime conversion errors and make the image/data upload code robust and secure.

All 11 Replies

sknake 1,622 Senior Poster

16 Years Ago

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

Edited 16 Years Ago by sknake because: n/a

sknake 1,622 Senior Poster

16 Years Ago

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

sknake 1,622 Senior Poster

16 Years Ago

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!

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

mshravs 0 Junior Poster in Training · Answer 1 · 2009-10-29T14:48:03+00:00

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

mshravs 0 Junior Poster in Training · Answer 2 · 2009-10-30T14:26:36+00:00

mshravs 0 Junior Poster in Training

16 Years Ago

thanks for the code its working now

mshravs 0 Junior Poster in Training · Answer 3 · 2009-10-31T17:11:34+00:00

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 1,622 Senior Poster Featured Poster · Answer 4 · 2009-10-31T19:39:06+00:00

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 0 Junior Poster in Training · Answer 5 · 2009-10-31T23:14:17+00:00

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 0 Junior Poster in Training · Answer 6 · 2009-11-02T14:24:22+00:00

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

sknake 1,622 Senior Poster Featured Poster · Answer 7 · 2009-11-03T05:12:28+00:00

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 0 Newbie Poster · Answer 8 · 2009-11-23T18:39:13+00:00

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

code for saving data along with image in sql server

Dani AI

Recommended Answers Collapse Answers

All 11 Replies

Recommended Answers