Filling DataGridView using Sql Statement

[steve_a4u]

hello there

i am steve, working an a project for my course, i actually got a problem...

i have got a table called "Appoitments" in oracle database, i have created a datagridview on the vb form trying to reterive data in the datagridview, i.e appointments of any perticular date selected by the user,
so i wrote a sub procedure to get the data and fill in the datagridview,

when i run the form its not loading any thing it just showing the attributes name thats it... i dont know what the problem is, then i changed my SQL statement to simple " Select * from Appointments" then its working fine, data is loaded in the datagridview.

but i actually want the user to select the data from the calender and click the button , so all the appointments for that perticular date will be displayed on the form, can you please help me out its urgent my time is running out


here is my code:-

Dim conn As New OleDbConnection()



Dim connectionString As String = "Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=scott;Password=steve;OLEDB.NET=true;" 'using OLEDB .Net Data Provider features

'Connection to datasource, using connection parameters given above

conn = New OleDbConnection(connectionString)

'Open database connection

conn.Open()

'Instantiate OleDbDataAdapter to create DataSet

Dim appointmentAdapter As OleDbDataAdapter = New OleDbDataAdapter()

'Fetch Product Detailsnn)

appointmentAdapter.SelectCommand = New OleDbCommand("SELECT APPOINTMENTTIME, REASONFORVISIT, PRIORITY, PATIENTID, DOCTORNUMBER FROM APPOINTMENTS WHERE APPOINTMENTDATE='16-JUL-07'", conn)

'In-Memory cache of data

Dim appointmentDataSet As DataSet = New DataSet("appointmentDataSet")


'Fill the dataset

appointmentAdapter.Fill(appointmentDataSet, "APPOINTMENTS")

DataGridView1.DataSource = appointmentDataSet

DataGridView1.DataMember = "APPOINTMENTS"



can any one help me out please its urgent...it will be greatfull

thank you
- steve

[GaryMazzone]

What is the actual data stored in the date field? Is it string or date type? If date type I would do a cast on the field to ensure that it is only checking date To_Char(APPOINTMENTDATE,'mm/dd/yy') = '7/16/07'

[jmcilhinney]

This is not related directly to your issue but I would suggest not using OleDb to connect to Oracle. The .NET Framework includes an Oracle-specific ADO.NET provider. In .NET 1.x it is part of the System.Data.dll assembly but in .NET 2.0 it is in its own System.Data.OracleClient.dll assembly, to which you must add a reference. Either way the types are members of the System.Data.OracleClient namespace. You can also download an ADO.NET provider from the Oracle Web site.

[steve_a4u]

hi man thanks for reply, well its a date type stored in actual database, here is the e.g: 16-JUL-07

[steve_a4u]

hi GaryMazzone, thanks very much for your help, it actually working, i appreciate that,thanks again

[steve_a4u]

hi there

i got another problem now

what you suggested actually working, but when i select the date from the month calender in vb its giving me in different date formate i.e "16/08/2007", i tried to formate the date i am not able to do it,

i have written a sub procedure to get the appointments which takes the selected data from the monthcalender as parameter,

when i select the date in calender i store it in a date variable and then call the appointment sub procedure passing the date variable as parameter, its actually not working can you please help

Public Sub GetAppointmentsData(ByVal appdate As Date)
Dim aa As String
aa = "'"


Dim conn As New OleDbConnection()



Dim connectionString As String = "Provider=OraOLEDB.Oracle;Data Source=orcl;User ID=scott;Password=steve;OLEDB.NET=true;" 'using OLEDB .Net Data Provider features

'Connection to datasource, using connection parameters given above

conn = New OleDbConnection(connectionString)

'Open database connection

conn.Open()

'Instantiate OleDbDataAdapter to create DataSet

Dim appointmentAdapter As OleDbDataAdapter = New OleDbDataAdapter()

'Fetch Product Detailsnn)

appointmentAdapter.SelectCommand = New OleDbCommand("Select APPOINTMENTTIME, REASONFORVISIT, PRIORITY, PATIENTID, DOCTORNUMBER FROM APPOINTMENTS WHERE To_Char(APPOINTMENTDATE,'DD/MM/YY') ='" & appdate & aa, conn)

'In-Memory cache of data

Dim appointmentDataSet As DataSet = New DataSet("appointmentDataSet")


'Fill the dataset

appointmentAdapter.Fill(appointmentDataSet, "APPOINTMENTS")

DataGridView1.DataSource = appointmentDataSet

DataGridView1.DataMember = "APPOINTMENTS"

End Sub

[GaryMazzone]

What is appdate? What is aa? Could you change the select to command and place it in and other var:

Dim strSQL As String = Select APPOINTMENTTIME, REASONFORVISIT, PRIORITY, PATIENTID, DOCTORNUMBER FROM APPOINTMENTS WHERE To_Char(APPOINTMENTDATE,'DD/MM/YY') ='" & appdate & aa

and the do a debug.print and post the resulting value back here so we can look at it?

[steve_a4u]

What is appdate? What is aa? Could you change the select to command and place it in and other var:

Dim strSQL As String = Select APPOINTMENTTIME, REASONFORVISIT, PRIORITY, PATIENTID, DOCTORNUMBER FROM APPOINTMENTS WHERE To_Char(APPOINTMENTDATE,'DD/MM/YY') ='" & appdate & aa

and the do a debug.print and post the resulting value back here so we can look at it?

well appDate is the date variable passed by the user , and in aa i stored just single inverted i.e " ' " which i need for the sql statement, well i could also use this symbol b" & " to join in the statement , but its giving me error,

i dont understand the last bit you said , can you please be clear change select to what ?

thank you
- stev