Access object query statement. Query all rows which have data

I need to write a query and pass it to my command that will in turn execute Access table object in vb and come up with all the records that have data in certain columns. With another words if field x=null avoid and don't select.

 

Here is the code I've got and I am trying to tune it up so I can achieve the above stated effect:

        Dim strConnectionString As String
        Dim objConnection As New OleDbConnection
        Dim objCommand As New OleDbCommand
        Dim objDataAdapter As OleDbDataAdapter

        Dim objDataTable As DataTable = Nothing

        Dim strSQL As String
        Dim strTimeTicksSQLExp As String
        Dim strTimeDeltaSQLExp As String

        Dim intCount As Integer

        strSQL = "SELECT `Exch Date`, Time, `Time Sent`, Exch, Status, Action, Qty, Product, MMMYY, IP, `TT Order Key`, `Exchange Order ID`, Msg, 'Exch Mbr ID', 'Exch Grp ID', 'Exch Trd ID', 'Trd ID', Acct  FROM " & strTable & " WHERE Status IN ('ACCEPT', 'OK') AND Action IN ('Add','Change','Delete') ORDER BY Exch, `Time Sent`, `TT Order Key`, Status"

        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";"
        objConnection.ConnectionString = strConnectionString
        If objConnection.State <> ConnectionState.Open Then
            Try
                objConnection.Open()
            Catch ex As Exception
                'Exception
            End Try
        End If
        If objConnection.State <> ConnectionState.Open Then
            CalculateTradeTimes = Nothing
            Exit Function
        End If

        objCommand.CommandType = CommandType.Text
        objCommand.Connection = objConnection

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        If Not (objDataTable Is Nothing) Then
            objDataTable.Dispose()
            objDataTable = Nothing
        End If

        objDataTable = New DataTable()

        objDataAdapter.Fill(objDataTable)

 

Technically I need to check if the "IP"  columns has no data don't select it, else select record.

 

Thanks in advance. 

0
kirilminev
6/10/2008 4:15:24 PM
asp.net.access-datasource 4679 articles. 0 followers. Follow

2 Replies
802 Views

Similar Articles

[PageSpeed] 51

Guys that query worked in my access, but via the code it doesn't seem to be selecting the right data. It just selects all the rows.

 

 

Any ideas here is the code:

 Dim strConnectionString As String
        Dim objConnection As New OleDbConnection
        Dim objCommand As New OleDbCommand
        Dim objDataAdapter As OleDbDataAdapter

        Dim objDataTable As DataTable = Nothing

        Dim strSQL As String
        Dim strTimeTicksSQLExp As String
        Dim strTimeDeltaSQLExp As String

        Dim intCount As Integer

        strSQL = "SELECT `Exch Date`, Time, `Time Sent`, Exch, Status, Action, Qty, Product, MMMYY, IP, `TT Order Key`, `Exchange Order ID`, Msg, 'Exch Mbr ID', 'Exch Grp ID', 'Exch Trd ID', 'Trd ID', Acct  FROM " & strTable & " WHERE Status IN ('ACCEPT', 'OK') AND Action IN ('Add','Change','Delete') AND IP Is Not Null ORDER BY Exch, `Time Sent`, `TT Order Key`, Status"

        strConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strDatabase & ";"
        objConnection.ConnectionString = strConnectionString
        If objConnection.State <> ConnectionState.Open Then
            Try
                objConnection.Open()
            Catch ex As Exception
                'Exception
            End Try
        End If
        If objConnection.State <> ConnectionState.Open Then
            CalculateTradeTimes = Nothing
            Exit Function
        End If

        objCommand.CommandType = CommandType.Text
        objCommand.Connection = objConnection

        objCommand.CommandText = strSQL

        objDataAdapter = New OleDbDataAdapter(objCommand)

        If Not (objDataTable Is Nothing) Then
            objDataTable.Dispose()
            objDataTable = Nothing
        End If

        objDataTable = New DataTable()

        objDataAdapter.Fill(objDataTable)


        strTimeTicksSQLExp = "CONVERT(SUBSTRING(Time, 1, 2), 'System.Int32') * 3600000 + CONVERT(SUBSTRING(Time, 4, 2), 'System.Int32') * 60000 + CONVERT(SUBSTRING(Time, 7, 2), 'System.Int32') * 1000 + CONVERT(SUBSTRING(Time, 10, 3), 'System.Int32')"
        strTimeDeltaSQLExp = "(CONVERT(SUBSTRING(Time, 1, 2), 'System.Int32') - CONVERT(SUBSTRING(`Time Sent`, 1, 2), 'System.Int32')) * 3600000 + (CONVERT(SUBSTRING(Time, 4, 2), 'System.Int32') - CONVERT(SUBSTRING(`Time Sent`, 4, 2), 'System.Int32')) * 60000 + (CONVERT(SUBSTRING(Time, 7, 2), 'System.Int32') - CONVERT(SUBSTRING(`Time Sent`, 7, 2), 'System.Int32')) * 1000 + (CONVERT(SUBSTRING(Time, 10, 3), 'System.Int32') - CONVERT(SUBSTRING(`Time Sent`, 10, 3), 'System.Int32'))"

        objDataTable.Columns.Add("Time Ticks", System.Type.GetType("System.Int32"), strTimeTicksSQLExp)
        objDataTable.Columns.Add("Time Delta", System.Type.GetType("System.Int32"), strTimeDeltaSQLExp)

        Dim objDataColumn As New DataColumn("Time Delta G2E")

        objDataColumn.DataType = System.Type.GetType("System.Int32")

        objDataTable.Columns.Add(objDataColumn)


        For intCount = 0 To objDataTable.Rows.Count - 2

            Try
                If objDataTable.Rows(intCount).Item("TT Order Key") = objDataTable.Rows(intCount + 1).Item("TT Order Key") Then
                    If objDataTable.Rows(intCount).Item("Status").ToString = "ACCEPT" And objDataTable.Rows(intCount + 1).Item("Status").ToString = "OK" Then
                        objDataTable.Rows(intCount + 1).Item("Time Delta G2E") = objDataTable.Rows(intCount + 1).Item("Time Delta") - objDataTable.Rows(intCount).Item("Time Delta")
                        If objDataTable.Rows(intCount + 1).Item("Time Delta G2E") < 0 Then
                            objDataTable.Rows(intCount + 1).Item("Time Delta G2E") = 0
                        End If
                        Debug.Print(objDataTable.Rows(intCount + 1).Item("Time Delta G2E").ToString)
                        intCount = intCount + 1
                    End If
                End If
            Catch ex As Exception
                Continue For
            End Try

        Next intCount


        objConnection.Close()

        If objConnection.State <> ConnectionState.Closed Then
            Try
                objConnection.Close()
            Catch ex As Exception
                'Exception
            End Try
        End If

        objCommand.Dispose()
        objDataAdapter.Dispose()
        objConnection.Dispose()

        objCommand = Nothing
        objDataAdapter = Nothing
        objConnection = Nothing

 

thank you 

0
kirilminev
6/10/2008 4:57:21 PM

Never mind I got it wasn't looking at the right source it is all good now.

 

Thank you problem solved. 

0
kirilminev
6/10/2008 5:02:44 PM
Reply:

Similar Artilces:

OleDb
I have a very complex query with 6 table Joins. I created the query in Access because I thought that the query would be compiled or prepared. I did some tests to compare the time with which I could access 100 results from the Access Query and then the same 100 results from just pasting the query string into the OleDb Command object. I noticed that pasting the query into OleDB command object was slightly faster then just selecting from the Query in Access. Can anyone Concur? A query is a view, and I don't think there's much difference in performance when you add a where clau...

Need help in a query
create table #Temps ( num int, OP_Bal int, quantity int ) insert into #Temps values(1, 234, 12) insert into #Temps values(2, 234, 08) insert into #Temps values(3, 234, 9) insert into #Temps values(4, 234, 11)select * from #Temps I need a result something like this num   OP_Bal   quantity   Closing_Bal ----------------------------------------------------- 1       234          12           246 2    &nb...

Query Analyzer / Query in MS Access
Hiiiiiiiiiiiiiiiiii, How can create query analyzer  / Query in MS Access using Vb.NEt 2003 (web base)... Can give me sample program for this... For Database i using MS Access 2003... Thanks...

Anyone know of query statement for access to get rows older than certain date?
I have a date/time field and only want to pull those records where that field has a date later than one I specify. The intent is to retrieve a list of client deadliens, but only those for today and later - ignoring past deadlines. Anyone have any ideas? I can do this with MS SQL (using the CONVERT function), but can't seem to get avalid date comparison going in my query. All help much welcome.Stephen You can use date function in Ms-Access as follows: SELECT * FROM polls WHERE startdate > #01/04/2004#; SELECT * FROM polls WHERE startdate > date(); Check out ...

Query in Access not working in VS Query Builder !!!!
hello guys i have a query in my MS access DB running & showing results , but when i try to use it by query builder in VS 2005, the VS see it as a function !!. and it doesnt even work when i copy the sql to the query builder here is the code :SELECT DISTINCT PositionTbl.PositionCode, EmpPosition.PafNo, EmpNames. FN, EmpNames.LN, TblEmployee.ActMobDt, TblEmployee.ActDeMobDt, (SELECT SUM(TimeSheetTbl.SatST + TimeSheetTbl.SatOT + TimeSheetTbl.SunST + TimeSheetTbl.SunOT + TimeSheetTbl.MonST + TimeSheetTbl.MonOT ...

Using the AS in a SQL query accessing an Access DB
Hello, I'm using a Asp.Net GridView for the first time and am VERY pleased with how easy it is to set up. However, I'm running into a couple of simple problems. First, my query looks like this: sql.Append("SELECT DISTINCT a As 'something somthing' ...") My problem is that when I view the grid, the single quotes actually show up. When I eliminate them, no results are found (because of the spaces). How do I get around this? Also, I selected the features Edit and Delete in the GridView (using VWD Express). How do I go about actually implementing those? I am g...

Query of Queried data
I am new to asp.net after working with coldfusion.  I have created a datagrid from a query of a sql database.  I am interested to know how can I create another datagrid which will process a query of the data in the original grid.  So essentually it is a query of a query. Thank you   You want to filter on the result of the first query? The easiest way I can think of, is to put the data in a datatable, then use the select and dataview features of the datatable. Hi,uusquintsYou can filter the datatable in the code-behind and bind the result to the 2nd datagird:...

access data... access...
Hi, I'm trying to handle a simple structured access db in c#... but I'm a bit confused on how to do it! How can I select, insert, edit and remove records from an access database? should I use datasets? Thanks I suggest reading microsoft's Documentation on ADO.Net. OleDb is the DataProvider you want to use if you're going against an Access DB. another thing: how can I get, having an opened oledbconnection, all the tables, and the fields in those tables, from the database?...

Access GridView Row Data without cell access
Hello,I have a gridview that I modify before rendering to add custom separators between different entities. I compare the value in a particular cell with the previous value, and take action accordingly. Everything works great. However, I no longer want to display the 'entity' column in the actual gridview rows - only as a separator. So, if I remove the column from the gridview, how can I access a particular value on each row without calling a cell? Keep in mind, this takes place during the render stage and not gridRowBound. My render code is as follows: 1 Protected Overri...

Problem accessing query in the design mode MS access
My problem is opening up a query in design mode, not working with forms. I'm not even trying to make a change - it's just opening up the query.This problem is only seems to happen when other people are using the datafile that the query is also looking at. The query is using the linked table but I didnt find any access permision  problem on the table beacuse the query actually do open finally but just after a long delay.   I really apricate any help on this issue Thanks Database might be locked when other people are using it . Remember to close each connection when ...

OLEDB / Access 2002
I have the database connection working fine, and simple queries like "SELECT * FROM News" work fine... but when I attempt to do a simple Inner Join I get the following Error: Syntax error in FROM clause when I call the .Execute method of the Command object. Now the Query itself works just fine if I run it in Access... which is why I'm so lost now. Here is the code, any help would be appreciated! (btw the provider i'm using is Microsoft.Jet.OLEDB.4.0) private void do_GetNews() { // At this point in the code, the connection is open and works fine... I just ...

Access Database Connection/Query Problem: "Object reference not set to an instance of an object"
I have been working on a web site in Visual Web Developer for several months.  For no apparent reason, I can no longer create, modify, or connect to my Microsoft Access database source.  The database is still where it has always been, still opens, etc.  Below are a few of the symptoms.  Does anyone know what would cause this and more importantly how I can fix it? (1) I can no longer use the "Query Builder" with existing or new aspx pages; when I try I get this message: "Object reference not set to an instance of an object" (2) I tried using the "Design Custom Statement...

Access access
========== Hi, Everyone ... I'm going to lock this thread for now. If you have a question about working with Access in Web Matrix, please start a new thread in this forum. Otherwise, this thread will get to be unwieldy. Thanks! ========== Updated 6/29/03 Hello, everyone. I've been working with the new version of Web Matrix (version .6, build 812, available right now!). In particular, I've been playing with support for Microsoft Access, which is one of the great new features in the new release. I wrote up some notes on what I've found to share some of my experiences and to a...

Direct Data Access or Disconnected Data Access
For which one should i opt ? Direct Data Access or Disconnected Data Access ? I'm currently reading a book on ASP.NET 2.0 and both access methods are explained , but i would like to know what the general tendency is ? Do you opt for Direct or Disconneted access ? Thx Tom Neither one is really better than the other if you're speaking generally.  However, certain situations make one preferred over the other.  Connected access (data readers) is generally faster and uses less memory.  This is the preferred method when you have an application that demands the highest perfor...

Web resources about - Access object query statement. Query all rows which have data - asp.net.access-datasource

User:Jimbo Wales/Statement of principles - Wikipedia, the free encyclopedia
As we move forward with software and social changes, I think it is imperative that I state clearly and forcefully my views on openness and the ...

Category:Articles containing potentially dated statements from June 2006 - Wikipedia, the free encyclopedia ...
This is an administration category . It is used for administration of the Wikipedia project and is not part of the encyclopedia. It contains ...

Malcolm Turnbull's statement to UN climate conference in Paris
Malcolm Turnbull joined other world leaders in Paris for the United Nations climate talks on Monday. This is his statement.

Obama releases emotional statement on Planned Parenthood shooting - Business Insider Deutschland
... Parenthood shooting in Colorado Springs, Colorado. REUTERS/Jonathan Ernst President Barack Obama. President Barack Obama released a statement ...

Election 2016 News Today Dominated by Media Furor over These Trump Statements
Here is today’s top election 2016 news: The media attacked GOP presidential front-runner Donald Trump this week for saying thousands of New Jersey ...

President Obama delivers a statement on national security
x YouTube Video Wednesday, Nov 25, 2015 · 5:08:06 PM +00:00 · Laura Clawson We’ll have a more in-depth account coming soon, but the summary ...

Why Chancellor George Osborne's Autumn Statement Is Good News For Entrepreneurs
Why the UK Chancellor's statement is mostly good news for Britain's business owners.

Trump Tweets Lengthy Statement About Reporter Serge Kovaleski, Demands Apology from New York Times
Donald Trump responded to critics who accused him of imitating the mannerisms of New York Times reporter Serge Kovaleski in a lengthy statement ...

WWE News: Update On Why WWE Sent Out A Statement After The Controversial Paige And Charlotte RAW Promo ...
When WWE gets the wrestling world talking, that is usually a very good thing. However, that does not seem to be the case when it comes to Paige ...

Planned Parenthood shooting suspect's statements could suggest motive
Los Angeles Times Planned Parenthood shooting suspect's statements could suggest motive Los Angeles Times Scott Dontanville, a co-pastor at ...

Resources last updated: 12/3/2015 2:07:56 AM