0
Private Sub Save_Record()

    Dim cmd As New OleDbCommand
    Dim sSQL As String = String.Empty
    Dim arrImage() As Byte
    Dim myMs As New IO.MemoryStream
    Dim bSaveImage As Boolean = False
    Dim strImg As String = String.Empty

    Try
        If Not IsNothing(Me.pic1.Image) Then
            Me.pic1.Image.Save(myMs, Me.pic1.Image.RawFormat)
            arrImage = myMs.GetBuffer
        Else
            arrImage = Nothing
        End If
        'get connection string declared in the Module1.vb and assing it to conn variable
        con = New OleDbConnection(Get_Constring)
        con.Open()
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        'I just use the textbox tag property to idetify if the data is new or existing.
        If Me.txtFacultyName.Tag = 0 Then
            sSQL = "INSERT INTO faculty ( FacultyName, Course, Address, Image)"
            sSQL = sSQL & "  VALUES(@FacultyName, @Course, @Address, @Image)"
            cmd.CommandText = sSQL
        Else
            sSQL = "UPDATE faculty set FacultyName = @FacultyName, Course =  @Course, Address = @Address, Image =  @Image where FacultyID = @ID"
            cmd.CommandText = sSQL
        End If


        cmd.Parameters.Add("@FacultyName", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtFacultyName.Text)) > 0, Me.txtFacultyName.Text, DBNull.Value)
        cmd.Parameters.Add("@Course", OleDbType.VarChar).Value = IIf(Len(Trim(Me.cbocourse.Text)) > 0, Me.cbocourse.Text, DBNull.Value)
        cmd.Parameters.Add("@Address", OleDbType.VarChar).Value = IIf(Len(Trim(Me.txtaddress.Text)) > 0, Me.txtaddress.Text, DBNull.Value)
        cmd.Parameters.Add("@Image", OleDbType.Binary).Value = IIf(Not IsNothing(arrImage), arrImage, DBNull.Value)
        cmd.Parameters.Add("@ID", OleDbType.Numeric).Value = Me.txtFacultyName.Tag
        cmd.ExecuteNonQuery()

        'If the record is new then we have to get its ID so that we can edit it rightaway after the insertion.
        If Me.txtFacultyName.Tag = 0 Then
            cmd.CommandText = "Select @@Identity"
            'Set textbox tag property with the ID of new record
            Me.txtFacultyName.Tag = cmd.ExecuteScalar()
        End If
        MsgBox("Data has been save.")

    Catch ex As Exception
        MsgBox(ErrorToString)
    Finally
        con.Close()
    End Try

Whats Wrong with the Code

4
Contributors
4
Replies
31
Views
4 Years
Discussion Span
Last Post by Kishan Tilak
0

For parameterized queries using OleDb you have to use the syntax

sSQL = "INSERT INTO faculty (FacultyName, Course, Address, Image)" _
     & "  VALUES(?,?,?,?)

and you have to ensure that the parameters are added in the same order as they appear in the fields list. If you use SqlDb then you can use the @param format and add the parameters by name in any order.

0

When you insert a new record you don't have an existing FacultyID value. I suppose you've declared FacultyID as Identity/Autonumber value so you shouldn't try to insert in that field anything.

Move line 37 between lines 29 - 30. After that your code accesses FacultyID only when the record exists and therefore that field does have a value.

HTH

0

ssql="INSERT INTO faculty ( FacultyName, Course, Address, Image)VALUES(@FacultyName, @Course, @Address, @Image)"
try this Or if u want to new code to strore image in to database then i ill give u new code

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.