Results 1 to 5 of 5

Thread: SQL / VB help

  1. #1
    Join Date
    Mar 2009
    Posts
    2

    Unanswered: SQL / VB help

    hi there
    my very first posting so not sure if this is the exact thread i should be posting.apologies if not.



    question 1:

    what i am trying to do is to create a search button in a vb form that searches through records and displays the correct one. my vb project is linked to a database using ADODC control.

    i have used a combo box that populates one of the database fields (CustomerFirstName) from a table called (CustomerDetails).

    then, when i click on the combo box to select a name from the populated list, i make visible a label with the name selected.
    when i click on the label, it makes visible a datagrid that displays the correct record from the table , according to thename selected on the combobox.

    now, this final step doesnt work.

    what i did is : on the label1_click sub i ve made the recordsource equal to an sql statement but i get an error on the update statement of the adodc in my code.

    the code is the following:

    Private Sub Combo1_Click()

    Label1.Caption = "find" & Combo1.Text

    Label1.Visible = True

    End Sub


    Private Sub Form_Load()

    Label1.Visible = False
    DataGrid1.Visible = False


    Set cn = New ADODB.Connection

    cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & App.Path & "\IceCreamProject.mdb"

    cn.Open



    Set rs = New ADODB.Recordset
    'open the recordset
    With rs
    .Open "CustomerDetails", cn, adOpenKeyset, adLockPessimistic, adCmdTable
    'loop through the records until reaching the end or last record
    Do While Not .EOF
    Combo1.AddItem rs.Fields("CustomerFirstName")
    rs.MoveNext 'moves next record
    Loop
    If Not (.EOF And .BOF) Then
    rs.MoveFirst 'go to the first record if there are existing records
    'FillFields 'to reflect the current record in the controls
    End If
    End With
    End Sub

    Private Sub Label1_Click()


    DataGrid1.Visible = True

    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = "select CustomerDetails.CustomerFirstName From CustomerDetails WHERE CustomerDetails.CustomerFirstName=Combo1.value"
    Adodc1.Refresh

    End Sub




    when i run the code everything works fine until i click on the label which is supposed to bring up the datagrid with the correct sql results.
    the datagrid comes up but with the all the records of the table instead of the one i ve specified in the sql query.

    the error says:
    no value given for one or more required parameters

    and it highlights the following line:

    Adodc1.Refresh




    question 2:

    i have some problems with writing queries in vb.

    when i use a datagrid an ado and a textbox and when i run the program i type my sql query in the textbox the datagrid works fine. (i use the following line:Adodc1.RecordSource = Text1.Text)

    then i am able to create a query in access, copy paste it it in my text box when i run the program and it works fine.

    but when i try not to write the query on the textbox when i run the program, but i am trying to assign it in the code, it gives me errors. i guess it has to do with the syntax of the sql statement in vb.
    for example:

    text1.Text= "SELECT CustomerDetails.City1, CustomerDetails.PostCode1, CustomerDetails.Title From CustomerDetails WHERE (((CustomerDetails.City1)="london") AND ((CustomerDetails.Title)="Mrs."));"

    Adodc1.RecordSource = Text1.Text

    vbdoesnt like that.

    it particularly doesnt like that lets say for example from the above statement CustomerDetails.City1)="london")

    so how do i assign specific criteria in an sql vb query?

    thanks very much and i hope what i am talking about makes sense!

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    this is not a mysql problem

    moving thread to vb forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2009
    Posts
    29
    "SELECT City1, PostCode1, Title From CustomerDetails WHERE City1 = 'london' AND Title = 'Mrs.'"

    try that on your query, Use Single Quote ' ' on string. Doesnt have to write the tablename.Fieldname, beacuse u use only one table.

    also, what is/are the error message/s?

    you just type your code and doesnt tell us the error message on your question #2.

    wasnt able to understand question #1
    Last edited by jerome.mercado26; 03-17-09 at 02:55.

  4. #4
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Q1

    Code:
    Private Sub Label1_Click()
    
    
    DataGrid1.Visible = True
    
    Adodc1.CommandType = adCmdText
    Adodc1.RecordSource = "select CustomerDetails.CustomerFirstName From CustomerDetails WHERE CustomerDetails.CustomerFirstName=Combo1.value"
    Adodc1.Refresh
    
    End Sub
    Your executing the sql with : ".....where CustomerDetails.CustomerFirstName = Combo1.value"

    What you want is: "...WHERE CustomerDetails.CustomerFirstName = " & Combo1.value

    See the differance
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

  5. #5
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Q2

    SQL on text if you say ie : where city = london
    it will only look for london and not for London
    on text better use the LIKE operator

    and as jerome points out
    Your problem is the quotes use single quotes
    vb will filter them out cause of the nature of vb is to see text as string
    Code:
    so if you write :  "He said and i quote "ill sew you"  end quote "
                  start^                stop^      start^         stop^
    the string starts at "He... and ends at : ....quote "....
    as you see the ill sew you seems to be floating in mid air
    thats why vb will ommit the double quotes out of a textbox
    Greetz Marvels -^.^-
    Developments : VB4 Through .Net; Basic; DOS ; CNC ; Sinclair
    Databases : SQL Server Through 2005; Access 3 Through 2003 ; Oracle 8 & 9.i ;
    OS : Win 3.11 Through XP ; NortonComander ; DOS

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •