I have a very frustrating and confusing problem here; I have a SQL query that functions perfectly well in Access however when I code it in VB.NET it does not yield the desired results;here is the code: - Dim oledbConnection As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & InstallLocation & "SDE.mdb;User Id=admin;Password=;")
-
oledbConnection.Open()
-
-
Dim oledbCommand As New OleDb.OleDbCommand("DELETE Sheet1.F4 FROM Sheet1 WHERE (((Sheet1.F4) Is Null)) OR (Sheet1.F4) LIKE '*E*';", oledbConnection)
-
oledbCommand.ExecuteNonQuery()
-
oledbConnection.Close()
The query runs and all nulls are dropped from the table, however none of rows that contain "E" get dropped.
Any advice would be greatly appreciated.
5 2163
To narrow down the problem a bit, this line executes but does not delete anything from the table, even though when run in Access, it works fine: - DELETE Sheet1.F4 FROM Sheet1 WHERE (Sheet1.F4) LIKE '*E*';
I suggest removing the ";", not used to access in sql we use %. However does seem like a syntax problem.
I suggest removing the ";", not used to access in sql we use %. However does seem like a syntax problem.
Thank you for your reply; I tried your suggestion, unfortunately removing the ";" has no appreciable effect.
What is particularly vexing about this problem is that the statement works perfectly in Access but not in VB.NET.
Well I was able to solve this problem by using the Access Interop Libraries and running the SQL Query internally in Access.
Somehow though I think there must be a better way to do this.
if you want to execute your query by using the oledbcommand (that would improve your app performance, I guess...) you should replace the star sign ( * ) with a percent sign ( % ) on your LIKE Operator.
That should solve the problem, I figure it out that , after several days.
Check your code again here, I made the correction:
Dim oledbConnection As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;Data Source=" & InstallLocation & "SDE.mdb;Us er Id=admin;Passwo rd=;")
oledbConnection .Open()
Dim oledbCommand As New OleDb.OleDbComm and("DELETE Sheet1.F4 FROM Sheet1 WHERE (((Sheet1.F4) Is Null)) OR (Sheet1.F4) LIKE '%E%';", oledbConnection )
oledbCommand.Ex ecuteNonQuery()
oledbConnection .Close()
PS. Remove the ";" anyway. =]
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Clive |
last post by:
Hello everyone,
This is my first time posting, up until now i've only used google to seach
:)
I'm creating a multiplayer minesweeper game in Windows, so I have been
reading up on CSockets. I am begining to have a grasp on them, but I have
this one query.
If a socket some data comes along that isnt part of my protocol, I want to
|
by: Dave Burt |
last post by:
Hi, Access officionados,
I'm new here, so please cut me slack/gently tell me off if I'm out of line
or in the wrong place.
OK, here's something that seems silly (and is also problematic to me).
I have a query which is updateable when viewed as a datasheet in Access (you
can insert, delete, update). Using queries, I can insert and update, but not
delete.
|
by: Tim ffitch |
last post by:
Hi
I have created a VB dll file that contains common functions I use
across various projects in VB, Access and Excel. Rather than have to
code the functions in each I decided to use the dll route.
The problem being that I can't call these functions from the query
designer in Access. I decided that I would try the route of declaring
the functions from the dll file the same way you would for the Windows
API. Access then complains that...
|
by: John Baker |
last post by:
Hi:
Access 2000 W98!
I have a table with numerous records in it, and am attempting to delete certain records
that have been selected from it. These are selected based on the ID number in a different
table. While I am using the tools in Access for query setup, its easier to show it on here
using the SQL for the query, which is as follows( the table is ):
DELETE .date, .,
|
by: nick_faye |
last post by:
Hi guys,
i'm still a newbie in using MS Access and in VB programming. I am
using DAO connection from my VB to access the entries on my MS Access
table. I am having trouble in editting and deleting entries from my
table because it becomes slower and slower when my table is getting
bigger and bigger. What I am doing right now is get the ID of the
entry to be editted/deleted and I have to loop from first entry until
I found the right ID...
| |
by: Shapper |
last post by:
Hello,
I have created 3 functions to insert, update and delete an Access
database record. The Insert and the Delete code are working fine.
The update is not. I checked and my database has all the necessary
records in it when testing it. I get the error "No value given for one
or more required parameters." when I try to update the database.
Can you tell me what am I doing wrong?
|
by: forbes |
last post by:
Hi,
I have a user that used the Query Wizard to create a query in Access.
Now she claims that her master table is missing all the data that was
excluded from the query. Can you create anything other than a select
query using the Wizard? What do you think happened to her data?
I am working remotely until Friday, so I can't get down to her office
and check out what she did.
|
by: Kevin M |
last post by:
I have one table and have created a form from that table. Also, I have
created a delete query. I want to but a button on the form which will delete
all records from the table; however, I cannot get anything to work. I know
this is probably simple for more experienced Access users. Any help would be
greatly appreciated.
Thanks
Kevin
|
by: pythonnoob |
last post by:
Hello everyone. New to python as well as this forum, but i must say ive learned a but already reading through some posts. Seems to be a pretty helpful community here.
Before i post a question ill give you a little background. I have done programming in the past in Basic, VB, and a little C. I am not much of a programmer, its more of a hobby/curiosity.
The following code is not mine, i am trying to modify a template of a mock database....
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |