plz help in visual basic data fetch from the database with count function

The result is not show when we put 0 in "strSQL = adoRS.Fields(0)" then show zero in the textfeild otherwise not show any thing.
Plz Help
Thanx in advance.

Code:

---

Private Sub cmdExit_Click()
    Dim opt
    opt = MsgBox("Do You Want to Close This Form", vbYesNo, "Client Registration details")
    If opt = 6 Then
        Unload Me
    End If
End Sub

Private Sub cmdReset_Click()
cboMonth.ListIndex = -1
cboYear.ListIndex = -1
cboMonth1.ListIndex = -1
cboYear1.ListIndex = -1
txtRegisDetails = ""
txtdetails = ""
End Sub

Private Sub cmdShow_Click()
 
 Dim adoconn As New ADODB.Connection
 Dim adoCD As New ADODB.Command

Dim strSQL As String
 
    Dim m1, m2, y1, y2, FY1, FY2
    Dim Desc As String, AllOK As Boolean
    AllOK = True
    If cboMonth.Text = "" Then AllOK = False
    If cboYear.Text = "" Then AllOK = False
    If cboMonth1.Text = "" Then AllOK = False
    If cboYear1.Text = "" Then AllOK = False
    If Not (AllOK) Then
  MsgBox "All combo boxes require an entry.", vbOKOnly + vbInformation, "Information Missing"
 
  If cboYear1.Text = "" Then cboYear1.SetFocus
  If cboMonth1.Text = "" Then cboMonth1.SetFocus
  If cboYear.Text = "" Then cboYear.SetFocus
  If cboMonth.Text = "" Then cboMonth.SetFocus
 
 End If
   
    m1 = Trim(cboMonth.Text)
    y1 = Trim(cboYear.Text)
    m2 = Trim(cboMonth1.Text)
    y2 = Trim(cboYear1.Text)
   
    FY1 = y1 + "-" + m1 + "-1"
    FY2 = y2 + "-" + m2 + "-31"
 
    If FY1 > FY2 Then
    MsgBox "Starting Search Date not be greater then the Ending Search Date.", vbOKOnly + vbInformation, "Date Selection is Wrong"
    cboYear.SetFocus
    Else
  txtRegisDetails.Text = FY1 & " to " & FY2
    End If

adoconn.ConnectionString = "Provider=MSDASQL.1;driver={SQL Server};server=LocalDB;uid=fnsuser;pwd=1234;database=firstandsecond"

'open the connection
adoconn.Open
adoCD.ActiveConnection = adoconn  'Connection

'instatiate new recordSet
Dim adoRS As New ADODB.Recordset
adoCD.CommandText = "SELECT count(registerdate) FROM customer where registerdate between " & FY1 & " and " & FY2
Set adoRS = adoCD.Execute

strSQL = adoRS.Fields()    // error rise      (argument not optional)

txtdetails.Text = strSQL  'adoRS.Fields(CustomerID)    ("CustomerID")

End Sub

Private Function validateAll() As Boolean
 
    Dim mandatoryFields As String
    Dim focusControl As Control
    mandatoryFields = ""
    validateAll = False
   
   
    If Trim(cboMonth.Text) = "" Then
        mandatoryFields = mandatoryFields & IIf(mandatoryFields = "", "Month", ", Month")
        Set focusControl = IIf(focusControl Is Nothing, cboMonth, focusControl)
    End If

    If Trim(cboYear.Text) = "" Then
        mandatoryFields = mandatoryFields & IIf(mandatoryFields = "", "Year", ", Year")
        Set focusControl = IIf(focusControl Is Nothing, cboYear, focusControl)
    End If
   
    If Trim(cboMonth1.Text) = "" Then
        mandatoryFields = mandatoryFields & IIf(mandatoryFields = "", "Month1", ", Months")
        Set focusControl = IIf(focusControl Is Nothing, cboMonth1, focusControl)
    End If
   
    If Trim(cboYear1.Text) = "" Then
        mandatoryFields = mandatoryFields & IIf(mandatoryFields = "", "Distributor", ", Years")
        Set focusControl = IIf(focusControl Is Nothing, cboYear1, focusControl)
    End If
    If mandatoryFields <> "" Then
        FNSAlert "Following Field/s Are mandatory: " & vbCrLf & mandatoryFields, 2
        focusControl.SetFocus
        Exit Function
    End If
       
    validateAll = True
End Function

---

Welcome to the forums. :wave:

You need to have a field name listed in your code.

Code:

---

'this needs to change
from
strSQL = adoRS.Fields()
'to
strSQL = adoRS.Fields("thenameofthefieldinyourrecordset")

---

Also, you have a couple of other things that need to be addressed.

Code:

---

'this is wrong
Dim adoRS As New ADODB.Recordset
Set adoRS = adoCD.Execute
'it should be
Dim adoRS As ADODB.Recordset
Set adoRS = New ADODB.Recordset

---

SELECT queries do not use the .Execute method. That is used with action queries like UPDATE, INSERT, DELETE, etc.

SELECT queries create a recordset and needs to be OPENED. The syntax for that is

Code:

---

adors.Open yourquerystring, yourconnectionobject

---

Quote:

---

SELECT queries do not use the .Execute method.

---

I normally use the Open method because it gives you more control however there is nothing wrong with using the Execute method to run a Select statement.

The Execute method, by default, returns a ForwardOnly, ReadOnly recordset.

is registerDate a date field or a text field? Is the backend DB Access or someething else. When you use date in a query you must enclose them in the proper diliminators for the database type being used (most us single qoute ' Access uses pould sings # and Oracle must format the date in specific form). If the field is a text value the the values need to enclosed in single qoutes.

And MySql encloses dates in single quotes, but also needs dates in the Euro format - yyyy/mm/dd.
Although for some reason it can display them in the US style - mm/dd/yyyy. But you have to use Euro style to run a query.

Thanx friends
Hack, brucevde, GaryMazzone, salvelinus for ur suggestion :wave:

take care n bye-2 :wave: