I am REALLY very new to this so please excuse my ignorance, but I could really use some help! I've got two tables: BillsFiled and Authors On BillsFiled, there are the columns: ID, Symbol, Filedate, Type, Author, Title, Summary, among others This is populated from an imported Excel file which is filled in by an outside source which cares not for my needs of referential integrity which means that the "Author" field can contain more than one entry, such as: ID-Symbol-------Filedate------Type------------Author--------------------Title--------------------Summary 1 - s001 ---- 11/18/2014 --- Resolution -- Smith ; Stevens ----- A Resolution to... ------- yadayada 2 - h002 ---- 11/18/2014 --- Bill ----------- Diaz -------------------- A Bill to... ----------------- yadayada 3 - s002 ---- 11/18/2014 ---- Bill --------- Ryan ; Smith ; Harris-- A bill... -------------------- yadayada Because I need a record linking "Author" to "Symbol" I also have the table: Authors. This just contains the columns: Symbol and Author. It is my most fervent desire to run a query or some vba or a macro or something to split the multiple entries in "BillFiled" to append "Authors" such as: Symbol - Author s001 Smith s001 Stevens h002 Diaz s002 Ryan s002 Smith s002 Harris I found a string of code from about a year ago offered on this site in response to a very similar question from another user and have tried using it, but am getting strange results. The code (updated with my table and column names) was: Sub SplitIt() Dim rstBillFiled As Recordset Dim rstAuthors As Recordset Dim Items() As String ' use dbOpenSnapshot to open the source table READ-ONLY Set rstBillFiled = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenSnapshot) ' use dbOpenDynaset to open the destination table READ-WRITE Set rstAuthors = CurrentDb.OpenRecordset( _ "SELECT Symbol, Author FROM BillFiled;" _ , dbOpenDynaset) With rstBillFiled ' .BOF is Beginning of the table ' .EOF is End of the table ' Checking if both are false means there are records in the ' source table If Not (.BOF And .EOF) Then ' get the first record from the source table .MoveFirst Do ' if Author is NULL (empty) If Nz(!Author, "") = "" Then ' add a new record into the destination table ' with data from the source table for Symbol rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' set Author to NULL (empty) rstAuthors!Author = Null ' save the new record rstAuthors.Update Else ' if Author IS NOT NULL ' convert Author into an array of strings Items = Split(Nz(!Author, ""), ";") ' loop through the array of strings For a = LBound(Items) To UBound(Items) rstAuthors.AddNew rstAuthors!Symbol = rstBillFiled!Symbol ' Author is set to the current item in the array rstAuthors!Author = Items(a) rstAuthors.Update Next a End If ' load the next record from the source table .MoveNext ' repeat until the end of the source table is reached Loop Until .EOF End If ' close the source table rstBillFiled.Close End With ' close the destination table rstAuthors.Close End Sub With the instruction to then run SplitIt from the Immediate window. When I do that, it does collect and split the records but all of them are added into "BillFiled" rather than "Authors" Also it repeats the action with the first two records such that if there are 4 records in the source table attributable to 6 authors in total, it will add 8 new records to the source table and nothing to the destination table. As I said in the beginning, I really don't know what I'm doing and don't have any experience with code, but am trying to build this database to help a family member with their business who doesn't have time to learn how to. I have some time but sadly no knowledge so if anyone out there could take pity on me and lend your experienced eye to this, I would be so grateful! Thanks!

I can't get a simple update or insert to save special äöü characters. Instead they are being stored as äöü. The website displays äöü characters correctly if they are already stored as äöü in the database using phpMyAdmin (or some other tool like HeidiSQL), but for some reason I can't use my code/connection to the database to store them properly. This is where I stand: I added charset=utf8 to my connection string: MM_Connection_STRING = "PROVIDER=MSDASQL;DRIVER={MySQL ODBC 5.1 Driver};port=3306;SERVER=localhost;UID=xxx;PWD=xxx;database=xxx;Option=3;charset=utf8" I added accept-charset="UTF-8" to all my forms. I am running MariaDB and IIS 10. Server version: 10.1.13-MariaDB mariadb.org binary distribution Protocol version: 10 The page gets proxied using Apache 2.4. I am running asp (classic) code. Calling the MySQL server for character and collation, I get this: MariaDB> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%'; +--------------------------+--------------------+ | Variable_name | Value | +--------------------------+--------------------+ | character_set_client | utf8mb4 | | character_set_connection | utf8mb4 | | character_set_database | utf8mb4 | | character_set_filesystem | binary | | character_set_results | utf8mb4 | | character_set_server | utf8mb4 | | character_set_system | utf8 | | collation_connection | utf8mb4_general_ci | | collation_database | utf8mb4_general_ci | | collation_server | utf8mb4_general_ci | +--------------------------+--------------------+ The code worked when I was using iso-8859-1 as charset. Any help will be very much appropriated. Thank you.

I want to be able to run the 'compact and repair' process from within a VBA module in the database. I have a batch process that I run occasionally, it drops a few old tables, re-imports them from other databases, renames a couple of fields, does a few updates and makes a few other minor changes. The process isn't rocket science, but there are several steps so it really does needs to be automated. The problem is that a couple of the steps (the UPDATEs) temporarily increase the size of the database which can cause problems with subsequent imports. If I do the process manually (including compacting) then everything works fine and I end up with a 800MByte database. If I use my automated VBA script (without compacting) then it crashes halfway through when the database busts the 2Gbyte limit. I've found several threads on this subject, but they're all three of four years old (or more) and the methods they describe don't seem to work anymore. Are they're any solutions that work with Office 365 (version 1720)? The 'auto compact' causes the database to compact on closing, it does NOT allow the compaction of the database to be added between steps. I've tried this: Public Sub CompactDb2() Dim control As Office.CommandBarControl Set control = CommandBars.FindControl(Id:=2071) control.accDoDefaultAction End Sub And this: Public Sub CompactDb1() CommandBars("Menu Bar").Controls("Tools").Controls("Database utilities"). _ Controls("Compact and repair database...").accDoDefaultAction End Sub And this.... Public Sub CompactDb3() Application.SetOption "Auto compact", True End Sub Amongst other

I have added a trigger to perform a change tracking mechanism on a table that has a relatively high load. During testing, we encountered no issues but now as it is in production there are deadlocks happening from time to time. There is an old MS Access frontend accessing the database via OLEDB / ADODB that is not fault tolerant to the deadlock which leads to data integrity issues (and we cannot address this in short time as it is not our project). This is the deadlock graph:  The SQL statements (updates) on the left and right side of the deadlock graph are not within the trigger but of course triggering it. The deadlock occurs on the PK of the change tracking table. This is the trigger:

sql
ALTER TRIGGER [dbo].[TR_IND_ChangeTracking_OrderingItems_PickOrders]
ON [dbo].[OrderingItems]
FOR UPDATE, INSERT, DELETE
AS 
	DECLARE @InsertedOrderNumber int
	DECLARE @DeletedOrderNumber int

	SELECT @InsertedOrderNumber = OrderNumber FROM inserted
	SELECT @DeletedOrderNumber = OrderNumber FROM deleted

	-- Check if the order exists in IND_PickOrders, otherwise skip
	DECLARE @ExistsInPickOrders bit
	SET @ExistsInPickOrders = 0

	SELECT @ExistsInPickOrders = 1
		FROM [dbo].[IND_PickOrders]
		WHERE Auftragsnummer = @InsertedOrderNumber OR Auftragsnummer = @DeletedOrderNumber

	IF NOT @ExistsInPickOrders = 1
	BEGIN
		RETURN
	END

	DECLARE @Timestamp datetime
	DECLARE @ToUpdate bit
	DECLARE @State int

	SET @Timestamp = GETUTCDATE()

	-- Need to update an existing entity in queue?
	SELECT @ToUpdate = 1 
		FROM [dbo].[ChangeTracking_PickOrders] 
		WHERE OrderNumber = @InsertedOrderNumber OR OrderNumber = @DeletedOrderNumber

	-- There was an action happening, but no value has been inserted or deleted.
	-- I.e. trying to delete an entity that not exists.
	IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NULL
	BEGIN
		SET @State = 0
	END

	-- Entity has been inserted.
	IF (@InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NULL)
	BEGIN
		SET @State = 1
	END

	-- Entity has been updated.
	IF @InsertedOrderNumber IS NOT NULL AND @DeletedOrderNumber IS NOT NULL
	BEGIN
		SET @State = 2
	END

	-- Entity has been deleted.
	IF @InsertedOrderNumber IS NULL AND @DeletedOrderNumber IS NOT NULL
	BEGIN
		SET @State = 3
	END

	IF @State > 0
	BEGIN
		IF @ToUpdate = 1
		BEGIN
			-- Update the entity on change tracking table.
			UPDATE [dbo].[ChangeTracking_PickOrders]
				SET UpdateTimestamp = @Timestamp, State = @State
				WHERE OrderNumber = ISNULL(@InsertedOrderNumber, @DeletedOrderNumber)
		END
		ELSE
		BEGIN
			-- Insert if no entry exists.
			INSERT INTO [dbo].[ChangeTracking_PickOrders] (OrderNumber, UpdateTimestamp, State) 
				VALUES (ISNULL(@InsertedOrderNumber, @DeletedOrderNumber), @Timestamp, @State)
		END
	END

The trigger first checks if the order number exists in another table and if not, returns. Then I check if the order number exists in the change tracking table and insert or update the specific row in the change tracking table. The logic of the Access frontend iterates over a set of ordering items of an order, calculates some fields and updates the order row by row by using an ADORecordSet. I suspect the Access frontend to not wait until the update and the trigger have completed (releasing the locks) and update the next row from a different connection of the ConnectionPool (thus leading to a differnt server process?). As me not being a DBA, is there anything I may have overseen? Thank you for any tips in advance! Edit: The schema of [dbo].[ChangeTracking_PickOrders]

CREATE TABLE [dbo].[ChangeTracking_PickOrders](
	[OrderNumber] [int] NOT NULL,
	[State] [int] NOT NULL,
	[UpdateTimestamp] [datetime] NOT NULL,
 CONSTRAINT [PK_OrderNumber] PRIMARY KEY CLUSTERED 
(
	[OrderNumber] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

I’m having trouble trying to find a way to send a ms access application to someone I have built one for. They say you cannot open it from OneDrive or problems occur. But is the person able to copy it from OneDrive to there desktop? Email works but has a 25 mb limit. How can I simply send an accdb file?

I recently swapped database servers (restored databases from old to new and then swapped IPs)... Old: Sql Server 2005, Windows Server 2003 New: Sql Server 2008, Windows Server 2012 All processes that connect to the server (dot net website, PHP, vbscript, mssql) from a geographically nearby VPN work fine on the new server. But we have a VPN between the server (UK) and a satelite office (KY USA) and a database application running on Microsoft Access, and this application is throwing an error when performing queries on the new server. The error is.... > Error: ODBC--call failed. > > [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (recv()). > (#10054) [Microsoft][ODBC SQL Server Driver][DBNETLIB]General network > error. Check your network documentation. (#11) If I temporarily have this application query the old server (by changing the local hosts file) the error doesn't occur and everything works. To help with troubleshooting this I built a small script... 'ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _ ' "OLE DB Services=-2;Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*" ConString="Provider=SQLOLEDB;Data Source=172.17.201.11,1433;" & _ "Initial Catalog=Perf;Network=DBMSSOCN;User Id=*;Password=*" set conn = CreateObject("ADODB.Connection") Conn.connectiontimeout=500 Conn.commandtimeout=500 conn.open ConString SCRIPTTIMEOUT = 1000 'sqlstring="SELECT top 100 ClientID FROM Clients" sqlstring="SELECT top 1000 ClientID FROM Clients" 'sqlstring="SELECT ClientID FROM Clients" 'succeeds with top 100 'fails with top 1000 or no limit set Rcount = Conn.Execute(sqlstring) msgbox Rcount(0) Rcount.Close set Rcount = Nothing set sqlstring = Nothing Conn.Close set Conn = Nothing The script works if I query for 100 rows. If I try 1000 it fails every time (I haven't yey tried intermediate amounts but I don't see much point narrowing it down) The script works if I switch the IP address to the old server (Everything else is the same - same vpn, same client, same script) The script and the access application work fine in every way over a different VPN (Also UK) - I have trawled through every setting I can find on each server (in MS SQL Studio, Configuration Manager, Network Settings) to try to match, and try different settings. - I have stopped and started services - I have googled this and tried every suggestion I could find (I've lost track, but I have tried adding OLE DB Services=-2. I have tried Encrypt=on (and off). I have tried Trusted_connection=on (and off). I have tried Pooling=on (and off). I have tried specifying the instance name after the IP. Both servers are in the same physical rack, on the same network/subnet, connected to the same physical switch. Before I go down the route of messing with VPN settings, swapping wires (Requiring access to the DC) is there something I can/should do/look at on the new server to help? **Edit:** If I add Packet Size=1024; to my connection string it works! Thing is, both servers have that set to 4096 by default, and the old server works just fine with that. **Edit 2** The above worked for my test script, but not for the access app. I am hoping to find a workaround that doesn't require a connection string change because that is going to be a hassle (involving the developer of the access app)

I am developing a frontend application in VBA, which is linked to an Oracle database. In Oracle there are the two tables Table1and Table2, which are related to each other by expTypeID and measID:

Table1
expTypeID | measID| val
12		    20001   1.1
13		    20002	200
13		    21043	250
12		    29321	0.9
13		    29322	150
15		    23450	23000

Table2
expTypeID |	measID | productID | productName
12		    20001	   100023	Apple
13		    20002	   100023	Apple
13		    21043	   129842	Pear
12		    29321 	   198372	Orange
13		    29322	   198372	Orange

I would like to get a new table, that for all products contains the values for specific expTypeIDs in different columns, e.g expTypeID = 12 -> ""Density"" and expTypeID = 13 -> ""EModulus"":

GoalTable
productName | productID |	Density |	EModulus
Apple		  100023	      1.1		  200
Pear          129842	       -          250
Orange	      198372	      0.9		  150

I know that it is possible when all the necessary data is available in one table: show-one-column-data-as-two-columns-in-sql (I found the query proposed by mat particularly useful). Is it still possible when the data is present in two tables? Thanks in advance

I'm a complete beginner, just trying out/looking into Databases as more of a hobby, and think Microsoft Access would be an easy way to start with the whole front-end forms and back-end combination. I just have a form with a combo box [UserNameEntry] that you can select a value from a table 'Users' in a 'Alias' field and then just press a button [Command27], and another text box will have its text replaced by that string. This is more of a test/precursor before me wanting to set it to get the record matching the 'Alias' value selected in the combo box, then checking if another field in that same record matches a particular value. I don't mind if any help is in the expression, macro or VBA builder, all is good to learn. I tried the following expression in the On Click event of the button [Command27] (and many other alternatives) but haven't had much luck:

=[Command27].[Caption]="[Users]![Alias]=" & "[UserNameEntry]"

Any help would be appreciated!

Simple question, but I am new to databases. I have a field called 'ActorRetired?', and in some VBA code I need to set the value of it to -1. But When I try:

Me.ActorRetired?.Value = -1

I get the error 'Compile Error: Expected: Expression' with the question mark highlighted. How do I refer to the field name without this error? So far I have tried

Me.[ActorRetired?].Value = -1
Me."ActorRetired?".Value = -1

But neither has worked. Please help!

I am using below VBA script to download attachments from email and save it to my windows directory. I need help to modify this script - 1) to save rename the attachment before saving it 2) Overwrite the file if it already exists. Kindly help. Public Sub SaveAttachmentsToDisk(MItem As Outlook.MailItem) Dim oAttachment As Outlook.Attachment Dim sSaveFolder As String sSaveFolder = "F:\RBL Attachments\" For Each oAttachment In MItem.Attachments oAttachment.SaveAsFile sSaveFolder & oAttachment.DisplayName Next End Sub

Hi I Inserted A Select MaxID+1 Data and want to retrieve what was that ID I inserted the Following is the codes Im trying to Insert data from Excel to Ms Access and Automatically Retrieve that Data. For MultiUser Purpose I need to retrieve specifically the data I inserted.

Sub PostData()
Dim cnn As ADODB.Connection 'dim the ADO collection class
Dim rst As ADODB.Recordset 'dim the ADO recordset classe here
Dim dbPath
Dim x As Long, i As Long

'add error handling
On Error GoTo errHandler:

dbPath = Sheets("Sheet3").Range("h1").Value

Set cnn = New ADODB.Connection

cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

Set rst = New ADODB.Recordset 'assign memory to the recordset
Sql = "INSERT INTO DvID(DVnumber)SELECT Max(DVNumber)+1 FROM DvID "
rst.Open Sql, cnn
Sheet3.Range("A2").CopyFromRecordset rst
rst.Close
cnn.Close

Set rst = Nothing
Set cnn = Nothing

On Error GoTo 0
Exit Sub
errHandler:

Set rst = Nothing
Set cnn = Nothing

MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure Export_Data"
End Sub

Below is some VBA code to authorize users to login into an MS Access database. I am trying to fix this code and I don’t understand what is the issue in this code snippet.

`
Private Sub cmdLogin_Click()
Dim lngUserID As Long
    lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)
    If lngUserID = 0 Then
        MsgBox "Username and/or password are incorrect."
        Me!txtPassword.SetFocus
    Else
        MsgBox "Registration successful!"
        OptionSetting "CurrentUserID", CStr(lngUserID)
        DoCmd.Close acForm, Me.Name
        DoCmd.OpenForm "Homepage"
    End If
      SetWebControlAsIE9
    ChangeWebControlFeature
END SUB

` The debugger in the VBE shows the following code highlighed in yellow. Can someone help me to understand what the issue is?

`
lngUserID = Nz(DLookup("UserID", _
    "tblUser", "username = '" _
    & Me!cbousername _
    & "' AND Password = '" _
    & Me!txtPassword & "'"), 0)

`

Access will be the front end but I don't want to use ODBC on everyone's PC. Do you have any suggestions on how Access can communicate to SQL without using ODBC?

I have an Excel macro-enabled file (.xlsm) with VBA that builds various SELECT statements and also executes a stored procedure on a SQL Server DB in order to populate a table. It is from a legacy system and uses an old deprecated driver (MSOLEDB.1), and I am trying to update it to read from a new database, while updating the driver as well. I've updated the new connection string in the VBA code from using Provider=MSOLEDB.1 to the new MSOLEDBSQL driver (which is installed on new SQL Server). I can step through the code and all executes perfectly. I can run the macro from the refresh button on the sheet, all good. *But when I save and close the file, and then attempt to re-open it - upon hitting Enable for the macro warning, it hangs excel.* When I change it back to the old string, all works fine again- I can close and reopen the file, and re-run the code with no issues. I can include more detail about both old and new environment, but I wanted to see if anyone even has any suggestions of how to troubleshoot this. The key difference is that old string authenticated via SQL login and the new one via Windows. (I don't want to authenticate via SQL login moving forward.) Old (sensitive data replaced): **"PROVIDER=SQLOLEDB.1;DATA SOURCE=#.#.#.#\SQLEXPRESS;INITIAL CATALOG=Databasename;NETWORK=DBMSSOCN;USER ID=SQLUser;PASSWORD=password;"** New: **"Provider=MSOLEDBSQL;Server=servername;Database=Databasename;Trusted_Connection=yes"** Running Debug->Compile VBA Project has sorted out buggy things like this before, but not this time. I've tried recompiling every sheet with code (even added comments to sheets with no codes so I could recompile them) but no effect. I also tried adding the ForceVBALoadFromSource from which had no effect. I believe it has either something to do with the new driver with Windows authentication, but I am really hoping for some hints.

I have an Excel file that, to keep it simple here, has 3 columns: ActivityID, ActivityName and TotalHours. TotalHours column is basically all the hours allotted to that specific ActivityID and the file can have about ~1500 of these activityID equating to 1500 rows. Now the thing is that there are additional columns in the file which break down the TotalHours by week dates. These hours are split into different week periods but adding them all up equals TotalHours. I don't want to end up dynamically creating table columns in my DB as I could end up with 100+ fields. One solution is that I could read the Excel just like it is and do calculations on the fly but this would then require the file to be strictly templatized meaning the data needs to be consistent and be in specified cells for me to read them. This is something that I am trying to avoid. Is there any better way that you know I can approach this problem? Thanks

I am working on an MS Access database that is intended to help keep track of a large scale implementation of many component parts connected together. I have created a working implementation based on my self-taught database design skills, but it lacks features that I'd like to have. I'm wondering if there would be a way to more effectively model the actual implementation in a database than I have created. The design consists of a collection of varied implementations of similar component parts. There are 7 types of components, A through G. As you can see in the image below, there are two complexities that make modelling it difficult. The image does not show every possible connection, but essentially any component D through F can connect to any other component D through F. I've substituted some clumsy made-up example components for clarity. First, Component B may be connected to component C directly, or it may be connected through any number of components D through F. Components D through F may also be connected to other instances of components D through F. For example, it may simply be B->C, or it could be B->D->F->F->D->E->C or any other configuration. Finally, each instance of components D through F might be connected to more than one other component D through F, creating branching paths. Second, instances of component C and component G are inherently related, usually (but not necessarily) existing one for one in a pair. However, instances of component G is actually physically connected to a component B. This creates a circular relationship. As far as I have been able to tell this system is not possible to implement fully in a relational database. Instead I have implemented the below design. I grouped components D through F together as they have similar attributes, and the user will simply register their existence, but must reference other materials to determine the configuration. I also removed the link from component B and G, as a component G cannot exist without a component C. My main issue with this design is that when, for example, two component C instances and a number of components D through F are related to a component B, there is no way to tell which components D through F are associated with a specific component C. In an ideal world, I'd like to be able to match the primary key of any component and pull up the information each item in the specific chain of components that the matching component exists within. Sorry for the vagueness, but I can't speak on the specifics. Thank you.

I'm relatively new to Microsoft Access, and just a beginner with SQL programming. I have a table in MS Access. It is an import from a .txt file. In the first column There is a String with a Number or Code in it. This number always Comes after a string "Arbeitslpatz "(The first character starts at string Position 17) and always changes every 76 rows. (Total rows 29790). What I want to achieve is to copy this number (for example 101100) and paste it in a new column in the first 76 rows. Then comes the next number for the next 76 rows, and so on. (I later want to to a lookup function with SQL WHERE functions and return some desired values, and for that I need to have these "Arbeitsplatz numbers" in a new row. I already assigned an ID to the file, but I didn't manage to get farther. (I experimented with VBA Loops, but didn' really succeed) Either SQL or VBA Code would be appreciated. I don't know how well the screenshots can be seen &/ if I provided enough Information... Thank you all for your help This is an Image from the orignial text file which I linkt to the database in Access:

I have a portable PostgreSQL database that runs perfectly on my computer as a database server. This is a work computer that I use for a lot of work in Excel with non-sensitive data (that is, data that is visible to anyone on the LAN). Having access to a fast database server would make my life much easier, however, I need to be able to write to it using either VBA/VBS/Powershell or .NET. I can't install the ODBC driver for the database as that requires administrator privileges and I don't have a great relationship with the IT staff, so they would reject any request without even reading it. Is there a way of inserting data quickly into PostgreSQL (or any other fast database server) without installing the driver? I'm thinking of something that functions as a portable driver. Since the database is run with only my privileges and I can insert into it directly form the command line, I am hoping there might some way of doing this from VBA.

I am trying to extract the values from an mdf database to excel, but i can't display the header texts(title fields). I can only view the values. In order to make this work i need a query like this below, but it seems that select with cte gives me an error.

I created an sql table on visual studio for visual basic, where i can put dynamic data every time i click on a button. I get the table results from textboxes and one datagridview with values(quantity,price,discount). I use the "command insert values into" inside a for loop to be able to get all the datagridview results. The problem is that the table has rows with duplicate values. I want to remove the duplicate values and keep only the values from datagridview e.g I have a table with the following format: