Querying and VB

[mcmahon]

Hi,
I have a MS Access DB with a query that I need to display in vb but am not too sure on how to go about it. The query has some criteria - i.e you have to input a date to run the query. I think that's a parameter in vb......

Well I've been playing around with it for some time now but amn't really getting anywhere. Could ye recommend any good tutorial or link as to how to do it?

Here's the code that I've been working on:

 Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmTodaysAbsentees

    Inherits System.Windows.Forms.Form

    Dim objDataSet As New DataSet
    Dim dateToday = Now.Date

    Dim objConnection As New OleDb.OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FYP.mdb")
    Dim objAbsenteesDA As New OleDb.OleDbDataAdapter("Select * from TodaysAbsentees", objConnection)
    Dim objAbsenteesCB As New OleDb.OleDbCommandBuilder(objAbsenteesDA)
    Dim objRow As DataRow

    Private Sub frmTodaysAbsentees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet2.TodaysAbsentees' table. You can move, or remove it, as needed.
        'Me.TodaysAbsenteesTableAdapter.Fill(Me.DataSet2.TodaysAbsentees)

        'clear data set of any existing data
        objDataSet.Clear()

        'fill the data set with infro from the 'TodaysAbsentees' query
        objAbsenteesDA.FillSchema(objDataSet, SchemaType.Source, "TodaysAbsentees")
        objAbsenteesDA.Fill(objDataSet, "TodaysAbsentees")


        'setup the relationship
        objDataSet.Relations.Clear()
        objDataSet.Relations.Add("Class2Student", _
                                 objDataSet.Tables("tblClass").Columns("ClassID"), _
                                 objDataSet.Tables("tblStudent").Columns("ClassID"))
        objDataSet.Relations.Add("Student2Attendance", _
                                 objDataSet.Tables("tblStudent").Columns("StudentID"), _
                                 objDataSet.Tables("tblAttendance").Columns("StudentID"))

        lstAbsentStudents.Items.Clear()

        Dim I As Integer
        Dim strCurrent As String
        Dim strCurrent1 As String
        For I = 1 To objDataSet.Tables("TodaysAbsentees").Rows.Count
            strCurrent = objDataSet.Tables("TodaysAbsentees").Rows(I - 1).Item("SName")
            strCurrent1 = objDataSet.Tables("TodaysAbsentees").Rows(I - 1).Item("FName")
            lstAbsentStudents.Items.Add(strCurrent + ", " + strCurrent1)
        Next


    End Sub

I've been trying to reference the query as a dataset. But should I write the SQL code someplace??

Also, when I run it I get the error: "No value given for one or more required parameters"

Help would be greatly appreciated.

[mcmahon]

I've been working on it since posting this question earlier.

New Code::

Imports System
Imports System.Data
Imports System.Data.OleDb
Imports System.Data.SqlClient
Public Class frmTodaysAbsentees

    Inherits System.Windows.Forms.Form

    Dim objDataSet As New DataSet

    Dim objConnection As New OleDb.OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FYP.mdb")
    Dim objAbsenteesDA As New OleDb.OleDbDataAdapter("Select * from TodaysAbsentees", objConnection)
    Dim objAbsenteesCB As New OleDb.OleDbCommandBuilder(objAbsenteesDA)
    Dim objRow As DataRow
    Dim queryString As String = "SELECT * FROM TodaysAbsentees Where AttendanceDate LIKE ?"
    Dim AbsenteesCmd As OleDbCommand = New OleDbCommand(queryString, objConnection)
    Dim AbsenteesParam As New OleDb.OleDbParameter


    Private Sub frmTodaysAbsentees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet2.TodaysAbsentees' table. You can move, or remove it, as needed.
        'Me.TodaysAbsenteesTableAdapter.Fill(Me.DataSet2.TodaysAbsentees)
        AbsenteesCmd.Parameters.Add("AttendanceDate", OleDbType.Date).Value = Now.Date
        'Dim reader As OleDbDataReader = AbsenteesCmd.ExecuteReader()
        'clear data set of any existing data
        objDataSet.Clear()

        'fill the data set with infro from the 'TodaysAbsentees' query
        objAbsenteesDA.FillSchema(objDataSet, SchemaType.Source, "TodaysAbsentees")
        objAbsenteesDA.Fill(objDataSet, "TodaysAbsentees")

And the same error is occuring. How can I get this working? Also, I have a datagridview on the windows form for displaying the results

[Frinavale]

It could have something to do with not recognizing your table name.
Do you have a table "TodaysAbsentee s"? Is this spelled correctly?

It could have something to do with your Select statement also.
Try using

Dim queryString As String = "SELECT * FROM TodaysAbsentees Where AttendanceDate LIKE '@todaysDate'"

And change your code from:

AbsenteesCmd.Parameters.Add("AttendanceDate", OleDbType.Date).Value = Now.Date

To use the "todaysDate " parameter:

AbsenteesCmd.Parameters.Add("todaysDate", OleDbType.Date).Value = Now.Date

Let me know if this works out for you.

[mcmahon]

"TodaysAbsentee s" is the name of the query in my database and it is spelt correctly.

I made the changes to my code that you recommended but when I run it I still get that same error: "no value given for one or more required parameters".

I should mention, I have never used queries with vb before so don't really know what I'm doing. I've just been googling stuf to try and piece it together. But from looking at my code it doesnt look like much is going on

 Public Class frmTodaysAbsentees

    Inherits System.Windows.Forms.Form

    Dim objDataSet As New DataSet
    Dim dateToday = Now.Date

    Dim objConnection As New OleDb.OleDbConnection( _
    "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= FYP.mdb")
    Dim objAbsenteesDA As New OleDb.OleDbDataAdapter("Select * from TodaysAbsentees", objConnection)
    Dim objAbsenteesCB As New OleDb.OleDbCommandBuilder(objAbsenteesDA)
    Dim objRow As DataRow
    Dim queryString As String = "SELECT * FROM TodaysAbsentees Where AttendanceDate LIKE '@todaysDate'"
    Dim AbsenteesCmd As OleDbCommand = New OleDbCommand(queryString, objConnection)
    Dim AbsenteesParam As New OleDb.OleDbParameter


    Private Sub frmTodaysAbsentees_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'TODO: This line of code loads data into the 'DataSet2.TodaysAbsentees' table. You can move, or remove it, as needed.
        'Me.TodaysAbsenteesTableAdapter.Fill(Me.DataSet2.TodaysAbsentees)

        AbsenteesCmd.Parameters.Add("todaysDate", OleDbType.Date).Value = Now.Date

        objDataSet.Clear()

        'fill the data set with infro from the 'TodaysAbsentees' query
        objAbsenteesDA.FillSchema(objDataSet, SchemaType.Source, "TodaysAbsentees")
        objAbsenteesDA.Fill(objDataSet, "TodaysAbsentees")

        'DataGridView1.ClearSelection()


    End Sub

First off I need to sort out that parameters error. But I've got a dataset there, how will I actually get to show my query results in the datagridview that is on my form??

Thanks

[jg007]

for the datagrid try -

DataGridView1.DataSource = objDataSet.Tables("TodaysAbsentees")

You do not seem to be setting the dataadaptor to use the command that you are creating.

I just created a quick database but the query for me was -

  Dim queryString As String = "SELECT * FROM TodaysAbsentees Where AttendanceDate LIKE @todaysDate"

sorry, my code is a bit sloppy and I did not want to just post the whole code but hopefully you can work it out from there!