Click here to Skip to main content
15,881,172 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I have a code which inserting all information from textboxes to excel file. What I trying to do to using this code also add a picture from picturebox. So the picture will be inserted with data from textboxes in the same row.



VB
Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=DATA\DATA.xlsm;Extended Properties = ""Excel 12.0 Xml;HDR=YES"""

        'Create an INSERT INTO SQL statement
        Dim insertStatement As String = "INSERT INTO [DATA$] ([WEEK], [DATE], [PART NAME], [PART NUMBER], [REASON FOR SCRAP], [LINE], [MACHINE], [TOOLING], [MTO], [QTY], [WHO SCRAP IT]) VALUES ('" + WEEK_TEXT.Text + "','" + DATEE_TEXT.Text + "','" + PARTNAME_TEXT.Text + "','" + PARTNUMBER_TEXT.Text + "','" + SCRAP_REASON_TEXT.Text + "','" + LINE_text.Text + "','" + MACHINE_TEXT.Text + "','" + SIDE_TEXT.Text + "','" + MTO_TEXT.Text + "','" + QUANTITY_TEXT.Text + "','" + TL_TS_TEXT.Text + "')"
        Dim insertStatement1 As String = "INSERT INTO [QAD DATA$] ([WEEK], [DATE], [PART NAME], [QAD NUMBER], [REASON FOR SCRAP], [LINE], [MACHINE], [TOOLING], [MTO], [QTY], [WHO SCRAP IT]) VALUES ('" + WEEK_TEXT.Text + "','" + DATEE_TEXT.Text + "','" + PictureBox1 + "','" + QAD_NUMBER_TEXT.Text + "','" + SCRAP_REASON_TEXT.Text + "','" + LINE_text.Text + "','" + MACHINE_TEXT.Text + "','" + SIDE_TEXT.Text + "','" + MTO_TEXT.Text + "','" + QUANTITY_TEXT.Text + "','" + TL_TS_TEXT.Text + "')"

        'Create a connection object to connect to the Excel Workbook
        Dim connection As New OleDbConnection(connectionString)
        Dim connection1 As New OleDbConnection(connectionString)

        'Create a command object that will execute the insert statement
        Dim command As New OleDbCommand(insertStatement, connection)
        Dim command1 As New OleDbCommand(insertStatement1, connection1)


        'Open the connection, execute the statement and close the connection
        connection.Open()
        connection1.Open()

        command.ExecuteNonQuery()
        command1.ExecuteNonQuery()


        connection.Close()
        connection1.Close()
        'Dispose of the connection and command objects
        connection.Dispose()
        command.Dispose()
        command1.Dispose()


What I have tried:

I check many codes from the internet, but when I try to transfer data to excel in the cell where should be picture its say "
System.Drawing.Bitmap
"
Posted
Updated 17-Apr-18 8:08am

1 solution

VB
Dim insertStatement As String = "INSERT INTO [DATA$] ([WEEK], [DATE], [PART NAME], [PART NUMBER], [REASON FOR SCRAP], [LINE], [MACHINE], [TOOLING], [MTO], [QTY], [WHO SCRAP IT]) VALUES ('" + WEEK_TEXT.Text + "','" + DATEE_TEXT.Text + "','" + PARTNAME_TEXT.Text + "','" + PARTNUMBER_TEXT.Text + "','" + SCRAP_REASON_TEXT.Text + "','" + LINE_text.Text + "','" + MACHINE_TEXT.Text + "','" + SIDE_TEXT.Text + "','" + MTO_TEXT.Text + "','" + QUANTITY_TEXT.Text + "','" + TL_TS_TEXT.Text + "')"
Dim insertStatement1 As String = "INSERT INTO [QAD DATA$] ([WEEK], [DATE], [PART NAME], [QAD NUMBER], [REASON FOR SCRAP], [LINE], [MACHINE], [TOOLING], [MTO], [QTY], [WHO SCRAP IT]) VALUES ('" + WEEK_TEXT.Text + "','" + DATEE_TEXT.Text + "','" + PictureBox1 + "','" + QAD_NUMBER_TEXT.Text + "','" + SCRAP_REASON_TEXT.Text + "','" + LINE_text.Text + "','" + MACHINE_TEXT.Text + "','" + SIDE_TEXT.Text + "','" + MTO_TEXT.Text + "','" + QUANTITY_TEXT.Text + "','" + TL_TS_TEXT.Text + "')"

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
 
Share this answer
 
Comments
Member 13659167 17-Apr-18 14:09pm    
This application is only to store information about scraps ( all will be store in excel file )

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900