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

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
LVL 2
José PerezAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Éric MoreauSenior .Net ConsultantCommented:
Hi oscargarin,

try setting the CursorLocation to adUseClient

Cheers!
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
leclairmCommented:
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
 
José PerezAuthor Commented:
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
 
José PerezAuthor Commented:
let me try and i tell whats happening.

B!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.