fill datagrid with sql query

dpapala
04-17-2004, 03:27 AM
Hi,
i have a database with fields likename,address etc... i want to insert a datagrid to the form that shows some data that come from an sql query. But for the sql query to work i need a textbox value that is on the form. I inserted an adodc but if i put on the recordset,in adodc properties "...name LIKE '%text3%' it tries to find a name similar to text3 what should i do?

thanks

webbone
04-17-2004, 03:38 AM
I am assuming Text3 is one of your controls - if so you can construct your SQL this way:

" ... name LIKE '%" & Text3.Text & "%' ... "

This will concatenate the contents of Text3 into your line, i.e. if Text3.Text="George" then your SQL will end up as:

" ... name LIKE '%George%' ... "

dpapala
04-17-2004, 04:19 AM
I've allready tried that but i have no results on my datagrid

webbone
04-17-2004, 01:46 PM
Can you post your code so we can see how you are connecting to your database and what your exact SQL string is?

dpapala
04-19-2004, 03:58 AM
Dim Ind As Integer
Dim IndBut As Integer
Dim WhatTbl As String
Dim RefDone As Boolean
Dim DelCollection As New Collection
Dim rsRecordset As ADODB.Recordset
Dim connConnection As ADODB.Connection



Private Sub Command7_Click()
UpDateGrade Data1, ProgressBar1
End Sub



Private Sub Command1_Click()
temp = Text6.Text
Load frmEdit
frmEdit.Show
End Sub

Private Sub Combo1_Change()

End Sub

Private Sub Command9_Click()
Data1.Recordset.Move 0

Select Case WhatTbl
Case "Science"

Data1.Recordset.Move 0
CurrRecScience Data1, Data2, ProgressBar1
Data1.Recordset.Move 0
Case "Social Study"
Data1.Recordset.Move 0
CurrRecScience Data1, Data2, ProgressBar1
Data1.Recordset.Move 0
Case Else
CurrRec Data1, ProgressBar1
End Select
End Sub

Private Sub Data1_Reposition()
SSDBData1.Caption = "Record " & Data1.Recordset.AbsolutePosition + 1 & "/" & Data1.Recordset.RecordCount
Select Case WhatTbl
Dim FiltStr As String

Case "Science"

On Error Resume Next

FiltStr = "Select * From ScienceTstTbl where DtInd =" & Text11.Text
Label2.Caption = "Science Grades:"
Data2.RecordSource = FiltStr
Data2.Refresh



Case "Social Study"

On Error Resume Next
FiltStr = "Select * From SocialStudyTstTbl where DtInd =" & Text11.Text
Label2.Caption = "Science Grades:"
Data2.RecordSource = FiltStr
Data2.Refresh
End Select
'Text5 = Val(Text2(2)) + Val(Text9)
'Text6.Text = FormatNumber((Val(Text2(2)) + Val(Text9)) / 2, 2)
'Text7 = WhatGrade(Val(Text6.Text))
End Sub

Private Sub DataGrid1_Click()

End Sub

Private Sub Form_Initialize()


Me.StatusBar1.Panels(2).Text = "Μέγεθος Βάσης Δεδομένων " & Val(FileLen(App.Path & "\data1.mdb")) / 1024 & " KB"
'Call UpDateList
TreeView1.Refresh
End Sub


Private Sub Form_load()
Data1.DatabaseName = App.Path & "\data1.mdb"
Data1.RecordSource = "students"
StatusBar1.Panels(4).Text = "Reading"
ProgressBar1.Max = 100
Data2.RecordSource = ""

With DelCollection
.Add "Reading"
.Add "Language"
.Add "Science"
.Add "Spelling"
.Add "SocialStudy"
End With
Data1.Refresh
TreeView1.Refresh
Call UpDateList
End Sub

Private Sub mnubuttons_Click()
TabStrip1.Style = tabButtons
End Sub

Private Sub MnuFlat_Click()
TabStrip1.Style = tabFlatButtons
End Sub

Private Sub Mnutbs_Click()
TabStrip1.Style = tabTabs
End Sub

Private Sub ProgressBar2_MouseUp(Index As Integer, Button As Integer, Shift As Integer, x As Single, y As Single)
'ProgressBar2(0).ToolTipText = "Grade status for " & Text3 & "," & Text2(0).Text & " Avg " & ProgressBar2(0).Value
'ProgressBar2(1).ToolTipText = "Grade status on testes for " & Text3 & "," & Text2(0).Text & " Avg " & ProgressBar2(1).Value

End Sub

Private Sub TabStrip1_Click()
Select Case TabStrip1.SelectedItem.Index
Case 1
WhatTbl = "Reading"
' Label2.Caption = "Reading Grades:"
' Label3.Caption = "Reading Final Grade >>>"
' Frame2.Visible = False
Data1.RecordSource = "students"
Data2.RecordSource = ""
Data1.Refresh
' StatusBar1.Panels(4).Text = "Reading"

End Select
End Sub

Private Sub TabStrip1_MouseDown(Button As Integer, Shift As Integer, x As Single, y As Single)
If Button = 2 Then
PopupMenu MnuStyle
End If
End Sub

Private Sub Text2_Change(Index As Integer)
'ProgressBar2(0).Value = Val(Text2(2).Text)

End Sub





Private Sub Toolbar1_ButtonClick(ByVal Button As MSComctlLib.Button)
Dim v As Integer
On Error Resume Next
Select Case Button.Key
Case "New"
'ToDo: Add 'New' button code.
stud.Show
Case "econo"
temp = Text6.Text
Load frmEdit
frmEdit.Show
Case "Refresh"
Data1.Recordset.Update
TreeView1.Nodes.Clear
UpDateList
Data1.Refresh
Case "Print"
'ToDo: Add 'Print' button code.
Printer.Font = "Arial Greek"
Printer.FontSize = 14
If Text3.Text = "" Then
v = MsgBox("Δεν υπάρχει όνομα.Θέλεις να συνεχίσεις;", vbOKCancel, "Εκτύπωση")
End If
If v = vbOK Then
Printer.Print
Printer.Print
Printer.Print Label1(10).Caption; Tab(25); Text3.Text
Printer.Print Label1(11).Caption; Tab(25); Text2(0).Text
Printer.Print Label1(0).Caption; Tab(25); Text1(14).Text
Printer.Print Label1(21).Caption; Tab(25); Text1(15).Text
Printer.Print Label1(20).Caption; Tab(25); Text1(16).Text
Printer.Print Label1(19).Caption; Tab(25); Text1(17).Text
Printer.Print Label1(18).Caption; Tab(25); Text1(18).Text
Printer.Print Label1(17).Caption; Tab(25); Text1(25).Text

Printer.EndDoc
End If
If v = vbCancel Then
End If
If Text3.Text <> "" Then
Printer.Print
Printer.Print
Printer.Print Label1(10).Caption; Tab(25); Text3.Text
Printer.Print Label1(11).Caption; Tab(25); Text2(0).Text
Printer.Print Label1(0).Caption; Tab(25); Text1(14).Text
Printer.Print Label1(21).Caption; Tab(25); Text1(15).Text
Printer.Print Label1(20).Caption; Tab(25); Text1(16).Text
Printer.Print Label1(19).Caption; Tab(25); Text1(17).Text
Printer.Print Label1(18).Caption; Tab(25); Text1(18).Text
Printer.Print Label1(17).Caption; Tab(25); Text1(25).Text

Printer.EndDoc
End If
Case "Delete"
'ToDo: Add 'Delete' button code.
If MsgBox("Θέλεις να διαγράψεις το μαθητή/τρια " & Text2(0) & " " & Text3 & _
"", vbInformation + vbYesNo) = vbYes Then
Dim Here As String
Dim db As Database
Dim Rec As DAO.Recordset
Set db = OpenDatabase(App.Path & "\data1.mdb")
Set Rec = db.OpenRecordset("students")
Rec.Index = "PrimaryKey"

If TreeView1.SelectedItem.Key = "Root" Then Exit Sub
Here = Right(TreeView1.SelectedItem.Key, Len(TreeView1.SelectedItem.Key) - 1)
Rec.Seek "=", Here


Rec.Delete
Rec.Close
Data1.Refresh
End If

Case "save"
Data1.Recordset.Update
TreeView1.Nodes.Clear
UpDateList
Data1.Refresh
'Beep
Case "end"
Unload Me
Me.Hide
Case "Update"
Command9_Click
Case "Summary"
If Toolbar1.Buttons(10).Value = tbrPressed Then
Data1.RecordSource = "Summary"
Data1.Refresh
MSFlexGrid1.ColWidth(0) = 205
MSFlexGrid1.Visible = True
MSFlexGrid1.Width = 9630
MSFlexGrid1.Height = 5775
Else
MSFlexGrid1.Visible = False
MSFlexGrid1.Height = 1
MSFlexGrid1.Width = 1
Me.TabStrip1.Tabs.Item(1).Selected = True
End If
Case "CompDatabase"
Me.Data1.Database.Close
CompDatabase
Data1.DatabaseName = App.Path & "\data1.mdb"
Data1.RecordSource = "students"
StatusBar1.Panels(4).Text = "Reading"
Me.StatusBar1.Panels(2).Text = _
"Μέγεθος βάσης δεδομένων " & Val(FileLen(App.Path & "\data1.mdb")) / 1024 & " KB"

Data2.RecordSource = ""
Case "backup"
Load frmBackUp
frmBackUp.Show

End Select
End Sub






Private Sub TreeView1_Click()
Dim Here As String
If TreeView1.SelectedItem.Key = "Root" Then Exit Sub
If TreeView1.SelectedItem.Key = Null Then MsgBox "kjhkjsdhkfjhskdjfhkds"
Here = Right(TreeView1.SelectedItem.Key, Len(TreeView1.SelectedItem.Key) - 1)
Data1.Recordset.FindFirst "num=" & Here
temp = Text6.Text
End Sub

Private Sub UpDown1_DownClick()
Text1(Ind).Text = 0
End Sub

Private Sub UpDown1_UpClick()
Text1(Ind).Text = "X"
End Sub
Private Sub UpDateList()
Dim Rec As DAO.Recordset
Dim TempNode As Node
Dim CurNode As Node
Dim SqlStr As String
SqlStr = "Select distinct name,num,schools from students ORDER by schools"
Set Rec = Data1.Database.OpenRecordset(SqlStr)

Set TempNode = TreeView1.Nodes.Add(, , "Root", "Μαθητές", 8)
Do Until Rec.EOF
Set CurNode = TreeView1.Nodes.Add("Root", tvwChild, "R" & Rec.Fields("num") _
, Rec.Fields("schools") & "," & Rec.Fields("name"), 7)
Rec.MoveNext
Loop
End Sub

Private Sub CompDatabase()
Dim DelPath As String
On Error GoTo OpenData
DBEngine.CompactDatabase App.Path & "\data1.mdb", App.Path & "\schoolComp grades.mdb"

Kill (App.Path & "\data1.mdb")
FileCopy App.Path & "\schoolComp grades.mdb", App.Path & "\data1.mdb"
Kill (App.Path & "\schoolComp grades.mdb")

MsgBox "Η βάση δεδομένων συμπιέστηκε ", vbInformation, "Συμπίεση Βάσης"
Exit Sub
OpenData:
MsgBox Err.Description & vbNewLine & "Close all forms and retry", vbCritical, "Compact Error"

End Sub

-----------------------------------------------------------------

This is the code. I want on the form_Load() to put the sql query so it fills the datagrid. My database is in access and it is very simple with no relations. I want to use the table "students", and select the schools that a student is in. in the sql query i have to use text3.text which is the name of the student.

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum