0

i have an image along with a form, to be saved in the sql database. but how should i do it. here is the code that i used to save the form in the database.

Protected Sub save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles save.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 String
        Dim photo As Byte
        ConnStr = ConfigurationManager.ConnectionStrings("gate").ConnectionString()
        Dim conn As New SqlConnection(ConnStr)
        Dim dt As Date
        dt = Date.Today
        conn.Open()
        serial_no = serialno.Text
        years.Text = DateTime.Now.Year
        visitors_name = visitorsname.Text
        officer_to_visit = officertovisit.Text
        purpose = pur.Text
        date_of_visit = dateofvisit.Text
        floor = wing.Text
        time_in = inout.Text
        time_out = timeout.Text
        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,photo) Values('" & serial_no & "' ,'" & dt.Year.ToString & "', '" & visitors_name & "','" & officer_to_visit & "','" & purpose & "',CONVERT(datetime ,'" & date_of_visit & "', 103),'" & floor & "','" & time_in & "','" & time_out & "','" & photo & "')", conn)
        insert.ExecuteNonQuery()
        conn.Close()
        MsgBox("record saved")
    End Sub

is it enough to add the coding to this code or need to do some alterations........please help me...............
thank you.

3
Contributors
4
Replies
5
Views
7 Years
Discussion Span
Last Post by pauldani
0

You need to use parameterized SQL. This question was asked in other threads:
http://www.daniweb.com/forums/thread214619.html
http://www.daniweb.com/forums/thread209172.html

I believe those are both C# posts but the VB.NET code would look like:

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
0

i actually don't want to use parameters anywhere for saving, is it possible in that way

0

>>i actually don't want to use parameters anywhere for saving, is it possible in that way

Uh.... why? I don't know if you can or not but i wouldn't waste my time looking for another solution because you have the answer.

0

Hi

Try this code

Dim intImageSize As Int64
    Dim strImageType As String
    Dim ImageStream As Stream

    ' Gets the Size of the Image
    intImageSize = PersonImage.PostedFile.ContentLength

    ' Gets the Image Type
    strImageType = PersonImage.PostedFile.ContentType

    ' Reads the Image
    ImageStream = PersonImage.PostedFile.InputStream

    Dim ImageContent(intImageSize) As Byte
    Dim intStatus As Integer
    intStatus = ImageStream.Read(ImageContent, 0, intImageSize)

    ' Create Instance of Connection and Command Object
    Dim myConnection As New SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
    Dim myCommand As New SqlCommand("sp_person_isp", myConnection)

    ' Mark the Command as a SPROC
    myCommand.CommandType = CommandType.StoredProcedure

    ' Add Parameters to SPROC
    Dim prmPersonImage As New SqlParameter("@PersonImage", SqlDbType.Image)
    prmPersonImage.Value = ImageContent
    myCommand.Parameters.Add(prmPersonImage)

    Dim prmPersonImageType As New SqlParameter("@PersonImageType", SqlDbType.VarChar, 255)
    prmPersonImageType.Value = strImageType
    myCommand.Parameters.Add(prmPersonImageType)

    Try
        myConnection.Open()
        myCommand.ExecuteNonQuery()
        myConnection.Close()
        Response.Write("New person successfully added!")
    Catch SQLexc As SqlException
        Response.Write("Insert Failed. Error Details are: " & SQLexc.ToString())
    End Try
This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.