Jump to content
might_me

Erro de sintaxe na Instruccão Update

Recommended Posts

might_me

Ajudem-me a encontrar o erro neste codigo. Supostamente deveria de actualizar os dados na bd, mas em vez disso esta a dar um erro de sintaxe. Mas nao faço ideia porque. Aqui esta o codigo:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        sSQL = "UPDATE FROM GRP SET([iMAGE]=@IMAGE)"


        MsgBox(sSQL)
        PictureBox1.Image.Save(Application.StartupPath + "\temp1.dat")
        If conn.State = ConnectionState.Open Then conn.Close()

        'Imagens
        'A
        Dim fsa As New FileStream(Application.StartupPath + "\temp1.dat", FileMode.Open)
        Dim arbytia(fsa.Length) As Byte
        fsa.Read(arbytia, 0, arbytia.Length)
        fsa.Close()

        'insere 
        Dim cmd As New OleDbCommand(sSQL, conn)

        'A
        Dim mypara As New OleDbParameter("@IMAGE", OleDbType.LongVarBinary)
        mypara.Value = arbytia
        mypara.Size = arbytia.Length
        cmd.Parameters.Add(mypara)


        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()

        cmd.ExecuteNonQuery()

        File.Delete(Application.StartupPath + "\temp1.dat")

        conn.Close()

        MsgBox("dados guardados com sucesso")

        
    End Sub

Share this post


Link to post
Share on other sites
might_me
Imports System.IO
Imports System.Data
Imports System.Data.OleDb

Public Class Form1

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

    End Sub

    Private Sub PictureBox1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles PictureBox1.Click
        OpenFileDialog1.Filter = "JPG|*.jpg|GIF|*.gif|BMP|*.bmp"
        OpenFileDialog1().ShowDialog()
    End Sub

    Private Sub OpenFileDialog1_FileOk(ByVal sender As System.Object, ByVal e As System.ComponentModel.CancelEventArgs) Handles OpenFileDialog1.FileOk
        Try
            Img1 = Image.FromFile(OpenFileDialog1.FileName)
            PictureBox1.Image = Img1
            PictureBox1.SizeMode = PictureBoxSizeMode.Zoom
            PictureBox1.Image.Save(Application.StartupPath + "\temp0.dat", Imaging.ImageFormat.Jpeg)
            stringimagema = OpenFileDialog1.FileName

        Catch Toing As Exception
            MessageBox.Show(Toing.Message)
        End Try
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        sSQL = "UPDATE FROM GRP SET([iMAGE]=@IMAGE)"


        MsgBox(sSQL)
        PictureBox1.Image.Save(Application.StartupPath + "\temp1.dat")
        If conn.State = ConnectionState.Open Then conn.Close()

        'Imagens
        'A
        Dim fsa As New FileStream(Application.StartupPath + "\temp1.dat", FileMode.Open)
        Dim arbytia(fsa.Length) As Byte
        fsa.Read(arbytia, 0, arbytia.Length)
        fsa.Close()

        'insere 
        Dim cmd As New OleDbCommand(sSQL, conn)

        'A
        Dim mypara As New OleDbParameter("@IMAGE", OleDbType.LongVarBinary)
        mypara.Value = arbytia
        mypara.Size = arbytia.Length
        cmd.Parameters.Add(mypara)


        If conn.State = ConnectionState.Open Then conn.Close()
        conn.Open()

        cmd.ExecuteNonQuery()

        File.Delete(Application.StartupPath + "\temp1.dat")

        conn.Close()

        MsgBox("dados guardados com sucesso")

        
    End Sub

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click

        sSQL = "SELECT * FROM GRP where AUT=18"

        conn.Open()

        cmd = New OleDbCommand(sSQL, conn)
        dr = cmd.ExecuteReader()

        'consulta para textbox's
        Do While dr.Read()
            'escrever para uma textbox

            TextBox1.Text = dr.Item("AUT")
            TextBox2.Text = dr.Item("ID_GRP")
            TextBox3.Text = dr.Item("COD_FAM")
            TextBox4.Text = dr.Item("COD_SFM")
            TextBox5.Text = dr.Item("ALT")
            TextBox6.Text = dr.Item("PROF_ESTR")
            TextBox7.Text = dr.Item("CODIGO")
            TextBox8.Text = dr.Item("TIPO")
            TextBox9.Text = dr.Item("NOME_PT_GRP")
            Dim bit() As Byte
            Dim ms As MemoryStream
            If dr.Item("IMAGE") Is DBNull.Value Then
                PictureBox1.Image = PictureBox1.InitialImage
            Else
                bit = dr.Item("IMAGE")

                ms = New MemoryStream(bit)
                PictureBox1.Image = Image.FromStream(ms)
                PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            End If
        Loop

        dr.Close()
        conn.Close()
    End Sub

    Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
        Dim id As String

        id = TextBox1.Text + 1
        sSQL = "SELECT * FROM GRP where AUT=id"
        conn.Open()
        cmd = New OleDbCommand(sSQL, conn)
        dr = cmd.ExecuteReader()
        'consulta para textbox's
        'consulta para textbox's
        Do While dr.Read()
            'escrever para uma textbox

            TextBox1.Text = dr.Item("AUT")
            TextBox2.Text = dr.Item("ID_GRP")
            TextBox3.Text = dr.Item("COD_FAM")
            TextBox4.Text = dr.Item("COD_SFM")
            TextBox5.Text = dr.Item("ALT")
            TextBox6.Text = dr.Item("PROF_ESTR")
            TextBox7.Text = dr.Item("CODIGO")
            TextBox8.Text = dr.Item("TIPO")
            TextBox9.Text = dr.Item("NOME_PT_GRP")
            Dim bit() As Byte
            Dim ms As MemoryStream
            If dr.Item("IMAGE") Is DBNull.Value Then
                PictureBox1.Image = PictureBox1.InitialImage
            Else
                bit = dr.Item("IMAGE")

                ms = New MemoryStream(bit)
                PictureBox1.Image = Image.FromStream(ms)
                PictureBox1.SizeMode = PictureBoxSizeMode.StretchImage
            End If
        Loop

        dr.Close()
        conn.Close()
    End Sub
End Class

Share this post


Link to post
Share on other sites
might_me
Public Img1 As Image
    Public stringimagema As String
    Public conn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database9.accdb")
    Public ConnectionString, sSQL As String
    Public dr As System.Data.OleDb.OleDbDataReader
    Public cmd As New System.Data.OleDb.OleDbCommand(sSQL, conn)

Share this post


Link to post
Share on other sites
might_me

Alterei o codigo  mas agora diz que faltam dados para um ou mais parametros...

Dim ConnectionString As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Database9.accdb")
        Dim query As String = "UPDATE GRP SET [iMAGE] = @IMAGE WHERE AUT = @AUT"

        Dim command As New OleDbCommand(query, ConnectionString)

        MsgBox(query)

        command.Parameters.Add("@AUT", OleDbType.Integer).Value = TextBox1.Text
        PictureBox1.Image.Save(Application.StartupPath + "\temp1.dat")
        If conn.State = ConnectionState.Open Then conn.Close()

        'Imagens
        'A
        Dim fsa As New FileStream(Application.StartupPath + "\temp1.dat", FileMode.Open)
        Dim arbytia(fsa.Length) As Byte
        fsa.Read(arbytia, 0, arbytia.Length)
        fsa.Close()

        'insere 
        Dim cmd As New OleDbCommand(sSQL, conn)

        'A
        Dim mypara As New OleDbParameter("@IMAGE", OleDbType.LongVarBinary)
        mypara.Value = arbytia
        mypara.Size = arbytia.Length
        cmd.Parameters.Add("@IMAGE", OleDbType.VarChar).Value = PictureBox1

        ConnectionString.Open()

        Dim x As Integer = command.ExecuteNonQuery()

        If x < 1 Then
            MessageBox.Show("Erro ao actualizar", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("Actualizado com sucesso!", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        ConnectionString.Close()
        command = Nothing

Share this post


Link to post
Share on other sites
bioshock

O teu código está mal estruturado, comparando com o código do artigo que te mostrei. Faz como lá está.

E já agora começa a utilizar o GeSHi em conformidade com os tópicos. Se estás em VB.NET e estás a colocar código do mesmo, mete-o assim:

[code:=vbnet.]código[/code.] - sem todos os pontos.

Share this post


Link to post
Share on other sites
might_me

O teu código está mal estruturado, comparando com o código do artigo que te mostrei. Faz como lá está.

E já agora começa a utilizar o GeSHi em conformidade com os tópicos. Se estás em VB.NET e estás a colocar código do mesmo, mete-o assim:

[code:=vbnet.]código[/code.] - sem todos os pontos.
[/quote]

mas o problema é que eu necessíto de trabalhar as imagens e para isso preciso da minha parte do codigo.
Dim ConnectionString As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Database9.accdb")
        Dim query As String = "UPDATE GRP" & "SET [iMAGE] = @IMAGE" & " WHERE AUT = @AUT"



        MsgBox(query)


        PictureBox1.Image.Save(Application.StartupPath + "\temp1.dat")
        If conn.State = ConnectionState.Open Then conn.Close()

        'Imagens
        'A
        Dim fsa As New FileStream(Application.StartupPath + "\temp1.dat", FileMode.Open)
        Dim arbytia(fsa.Length) As Byte
        fsa.Close()

        'insere 
        ' Dim cmd As New OleDbCommand(sSQL, conn)
        Dim command As New OleDbCommand(query, ConnectionString)
        'A
        Dim mypara As New OleDbParameter("@IMAGE", OleDbType.LongVarBinary)
        mypara.Value = arbytia
        mypara.Size = arbytia.Length
        command.Parameters.Add("@AUT", OleDbType.Integer).Value = TextBox1.Text
        command.Parameters.Add(mypara)

        ConnectionString.Open()

        Dim x As Integer = command.ExecuteNonQuery()

        If x < 1 Then
            MessageBox.Show("Erro ao actualizar", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("Actualizado com sucesso!", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        ConnectionString.Close()
        command = Nothing

Share this post


Link to post
Share on other sites
might_me

quando coloco o codigo assim

Dim ConnectionString As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0; Data Source=Database9.accdb")
        Dim query As String = "UPDATE GRP" & "SET [iMAGE] = @IMAGE" & " WHERE AUT = @AUT"

        MsgBox(query)

Dim command As New OleDbCommand(query, ConnectionString)
command.Parameters.Add("@IMAGE", OleDbType.LongVarBinary).Value = PictureBox1.Image
ConnectionString.Open()

        Dim x As Integer = command.ExecuteNonQuery()

        If x < 1 Then
            MessageBox.Show("Erro ao actualizar", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
        Else
            MessageBox.Show("Actualizado com sucesso!", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
        End If

        ConnectionString.Close()
        command = Nothing


o erro que aparece diz que NAO FOI  POSSIVEL TRANSFORMAR PARAMETRO BITMAP EM BYTE []

Share this post


Link to post
Share on other sites
bioshock

Deves ter algo semelhante (corrige os possíveis erros, foi feito agora):

Dim fStream As New IO.FileStream("Caminho da imagem", IO.FileMode.Open, IO.FileAccess.Read)
Dim ImageData(fStream.Length() - 1) As Byte
fStream.Read(ImageData, 0, ImageData.Length)
fStream.Close()

Dim ConnectionString As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & Application.StartupPath & "\BD.mdb")
Dim query As String = "UPDATE GRP SET [iMAGE] = @image WHERE AUT = @aut"

Dim command As New OleDbCommand(query, ConnectionString)

command.Parameters.Add("@image", OleDbType.Binary).Value = ImageData
command.Parameters.Add("@aut", OleDbType.Binary).Value = ' Aqui fazes outro processo como o ImageData

ConnectionString.Open()

Dim x As Integer = command.ExecuteNonQuery()

If x < 1 Then
     MessageBox.Show("Erro ao actualizar", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Error)
Else
     MessageBox.Show("Actualizado com sucesso!", My.Application.Info.Title, MessageBoxButtons.OK, MessageBoxIcon.Information)
End If

ConnectionString.Close()
command = Nothing

Share this post


Link to post
Share on other sites
might_me

Ja consegui resolver o problema.Obrigada. :(

 sSQL = "UPDATE GRP SET [iMAGE]=@IMAGE WHERE AUTO LIKE '" & TextBox1.Text & "'"

        MsgBox(sSQL)
        PictureBox1.Image.Save(Application.StartupPath + "\temp1.dat")
        If conn.State = ConnectionState.Open Then conn.Close()

        'Imagens
        'A
        Dim fsa As New FileStream(Application.StartupPath + "\temp1.dat", FileMode.Open)
        Dim arbytia(fsa.Length) As Byte
        fsa.Read(arbytia, 0, arbytia.Length)
        fsa.Close()

        'insere
        Dim cmd As New OleDbCommand(sSQL, conn)

        'A
        Dim mypara As New OleDbParameter("@IMAGE", OleDbType.LongVarBinary)
        mypara.Value = arbytia
        mypara.Size = arbytia.Length
        cmd.Parameters.Add(mypara)



        conn.Open()

        cmd.ExecuteNonQuery()

        File.Delete(Application.StartupPath + "\temp1.dat")

        conn.Close()

        MsgBox("dados guardados com sucesso")

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...

Important Information

By using this site you accept our Terms of Use and Privacy Policy. We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.