[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

when i execute a SELECT into a database from VB it removes the first data :(

Posted on 2006-06-06
6
Medium Priority
?
309 Views
Last Modified: 2013-12-25
i have a an access database, a datagrid, a textbox and an adodc control in this form.

it displays the results, but it deletes the first data from de Access database, even when the datagrid is configured to be read-only (no update or write permissions).

well my code is the following:

Private Sub Form_Load()

  Dim sSQl As String
 
  'poblamos la fecha en nuestro formato
  txtFecha.Text = Format(Date, "Long Date")

   Adodc1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\SistemaOC.mdb"

   sSQl = "SELECT invoice_number, maturity_date, id, payment_form, total, status FROM purchase_invoice WHERE maturity_date = '" & txtFecha.Text & "' "
   Adodc1.CommandType = adCmdText
   Adodc1.RecordSource = sSQl
   Adodc1.Refresh
     
End Sub
0
Comment
Question by:José Perez
6 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 16842970
when working with access, dates are usually enclosed by #. not sure if that is the problem on your application
   sSQl = "SELECT invoice_number, maturity_date, id, payment_form, total, status FROM purchase_invoice WHERE maturity_date = #" & txtFecha.Text & "# "
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 16843010
Hi oscargarin,

try setting the CursorLocation to adUseClient

Cheers!
0
 
LVL 11

Expert Comment

by:leclairm
ID: 16846409
I would recommend not even using adodc.  It's a lot more trouble than it's worth.

Try something like this:

dim conn as new adodb.connection
dim rs as new adodb.recordset

conn.open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                          "Data Source=" & App.Path & "\SistemaOC.mdb"
 sSQl = "SELECT invoice_number, maturity_date, id, payment_form, total, status FROM purchase_invoice WHERE maturity_date = #" & txtFecha.Text & "# " 'as angelIII suggested....
rs.open ssql, conn
set yourgrid.datasource = rs

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 2

Author Comment

by:José Perez
ID: 16862870
emoreau : it is adUseClient

angelIII : it does not work because the string change to ...where maturity_date= #june 17... (it insert the # sign)

any other idea?

B!
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 375 total points
ID: 16863158
you have to format the date value into a specific format:
 sSQl = "SELECT invoice_number, maturity_date, id, payment_form, total, status FROM purchase_invoice WHERE maturity_date = #" & format(cdate(txtFecha.Text), "YYYY/MM/DD") & "# "
0
 
LVL 2

Author Comment

by:José Perez
ID: 16955331
let me try and i tell whats happening.

B!
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month18 days, 4 hours left to enroll

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question