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.
4 1371
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 9,735
Recognized Expert Moderator Expert
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.
"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
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!
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Michael Whittaker |
last post by:
Hello!
I have a problem with my php script.
The script's task is to search an IP-Database with ranges as entries
and find, in which range the entered IP is.
OK, I've queried the MySQL-Results of the ranges into an array,
containing the
"IP ==> internalid"
where internalid is the Primarykey of the table from which I can get
|
by: Ravi Shankar |
last post by:
Hi all,
I have a calendar application( like Microsoft Outlook) writtn in
Java.Whenever an event is created, we can set SMS/EMAIL notification. Hence
when an event is created, I am storing that event info and notification time
into a database.Now the notification can be ranging from 5 minutes to say
one day.Hence I need to query the databse every 5 minutes and fetch the data
and do send the notification directly. I understand that such a...
|
by: Keith |
last post by:
I am fairly new to SQL so sorry if this is a really dumb question.
I have a small (still) SQL database, which I am trying to query from an ASP
page.
The field I am querying is of DATETIME data type, and is populated
automatically using the GetDate() function as a default value.
When I try and search on this field, using a date/time in the format
dd/mm/yyyy hh:mm:ss as the search criteria, it fails with the following
|
by: valexena |
last post by:
After a few minutes of querying the database my session disconnects
abruptly. Can somebody help me and tell why can be happening?
--
Posted via http://dbforums.com
|
by: Ron L. |
last post by:
Hi,
Any suggestions for a good intorudction to OLAP querying with MDX?
Thanks,
Ron.
--
Performance Intelligence, Inc.
Spy 4 DB2 - http://www.pireporting.com/spy4db2.html
| |
by: Greg |
last post by:
I am working on a project that will have about 500,000 records in an XML
document. This document will need to be queried with XPath, and records
will need to be updated. I was thinking about splitting up the XML into
several XML documents (perhaps 50,000 per document) to be more efficient but
this will make things a lot more complex because the searching needs to go
accross all 500,000 records. Can anyone point me to some best practices...
|
by: Shane |
last post by:
I wonder if someone has any ideas about the following.
I am currently producing some reports for a manufacturing company who work
with metal.
A finished part can contain multiple sub-parts to make up the finished part.
The sub-parts can also be made up of sub-parts and those sub-parts can also
be made up of sub-parts etc etc.
All parts are contained within the same table and I have a seperate table
|
by: MDB |
last post by:
I'd normally Google for a question like this, and hope to snag a few
examples along with the answer, but this time I can't see to get the
keywords specific enough.
Or I'd ask coworkers, but they're just as new to ASP.NET as I am.
Is it possible to have a dataset filled with all the records in an SQL table
(on the small side, maybe three hundred records total), and then query that
table for subsets of data, e.q. a simple WHERE clause,...
|
by: RajSharma |
last post by:
Hi,
I am facing a problem regarding querying thru a large table having millions of rows.......
Its hanging in between while querying for all those rows
Can anybody suggest me a query regarding :
Querying the database everytime for next 100 records ( that means i need to set up a cursor) till the count of the table rows ends up(take 1 million rows e.g.)
The database is DB2
|
by: =?Utf-8?B?U3VoYXMgVmVuZ2lsYXQ=?= |
last post by:
Hello,
I am facing an issue while querying Active directory using C# code with
system.DirectoryServices namespace.
Here is the path for my LDAP -
"LDAP://CN=XY - C++/Unix and other, OU=Automatic,OU=DLs,DC=domain,DC=com";
I have replaced the / character with\/- but still is giving error while
querying. Please help me to resolve this.
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |