Datagrid question from query statement

dakota97 · December 15, 2003

Ok, I have a connection and query working so that I can populate a litbox that I have, but what I'd like to do is populate a datagrid instead. Thee working code that I have is as follows, but hwo I can incorporate this into creating a dataset and datagrid instead?

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

Dim strdate As String = DateTimePicker1.Value.ToShortDateString

Dim enddate As String = DateTimePicker2.Value.ToShortDateString

'Create the database connection

Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb")

MyConnection.Open()

'Query the database to find matching record

Dim MyCommand As New OleDbCommand("SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID='" & TextBox1.Text & "'", MyConnection)

Thanks in advance,

Chris

Bucky · December 15, 2003

Read up on the [mshelp=ms-help://MS.MSDNQTR.2003FEB.1033/cpref/html/frlrfsystemdataoledboledbdataadapterclasstopic.htm]OleDbDataAdapter class[/mshelp]; it does just what you're

looking for.

millenniasp · December 15, 2003

here is my code for a datagrid

       Dim myDa As OleDbDataAdapter
       Dim strSQL As String
       Dim myCnn As OleDbConnection
       Dim myTable As String

       myTable = AddLeads.strTable.ToString
       btnBack.Text = "Back to " & myTable
       strSQL = "SELECT " & myTable & ".* "
       strSQL = strSQL & "FROM " & myTable & ";"

       myCnn = New OleDbConnection(AddLeads.strCnnAccess)
       myCnn.Open()
       myDa = New OleDbDataAdapter(strSQL, myCnn)

       Dim myDs As New DataSet


       myDa.Fill(myDs, "ViewRecords")

       dgViewData.DataSource = myDs
       dgViewData.DataMember = "ViewRecords"

       myCnn.Close()
       myCnn.Dispose()
       myDa.Dispose()
       strSQL = ""

dakota97 · December 15, 2003

thanks for the replies

millenniasp....what I'm looking to do is have my form show a textbox and 2 datepickers...I want to have the user click a button and have the information displayed in a datagrid based on my query...I understand your code, but how could I implement this into what I'm trying to accomplish?

Thanks in advance,

Chris

millenniasp · December 15, 2003

try this

'first put a label on your form
       Try

           Dim strdate As String = DateTimePicker1.Value.ToShortDateString
           Dim enddate As String = DateTimePicker2.Value.ToShortDateString
           Dim myEmployee As String = TextBox1.Text
           Dim strSQL As String

           strSQL = "SELECT * FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID = '" & myEmployee & "'"
           'Create the database connection
           Dim MyConnection As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source= timeclock.mdb")
           MyConnection.Open()

           Dim myDA As New OleDbDataAdapter(strSQL, MyConnection)
           Dim myDs As New DataSet


           myDA.Fill(myDs, "TimeIn")

           'this is your datagrid control 
           myDatagrid.DataSouce = myDs
           myDatagrid.DataMember = "TimeIn"

           MyConnection.Close()
           MyConnection.Dispose()
           myDA.Dispose()
           myDs.Dispose()
           strSQL = ""

       Catch ex As Exception

           Label1.Text = ex.Message.ToString

       End Try

let me know if there is an error

Edited December 16, 2003 by millenniasp

millenniasp · December 15, 2003

another thing you might want to try is builing your query in Access first and then copy it over to your code. for example here is my query from a database in access:

SELECT dbo_addleads.*

FROM dbo_addleads

WHERE (((dbo_addleads.transferdate) Between #1/1/2003# And #12/31/2003#));

so I would change strSQL to

strSQL = "SELECT dbo_addleads.* FROM dbo_addleads WHERE (((dbo_addleads.transferdate) Between #" & strstart & "# And #" & strend "#));"

try your query string as

strSQL = "SELECT Timein.* FROM Timein WHERE DateIn between #" & strdate & "# and #" & enddate & "# AND EmpID = '" & myEmployee & "';"

dakota97 · December 15, 2003

Ok, I'm lost on this one. I've zipped the form that I'm working with in hopes that you may be so kind as to let me look at the code and see what you're doing with it. If you would, please guide me in the right direction.

Thanks,

Chris

[edit]Please don't post binaries in zips you attach[/edit]

Edited December 15, 2003 by Volte

millenniasp · December 15, 2003

Do you have the database? I can write the code if you give me the database (you don't have to have any data in it, but I need the table structure to write the query.)

It'll take me about 15 minutes to complete :)

millenniasp · December 15, 2003

Okay,

Changed textbox1 = txtEmployee

changed datetimepicker1 = dtStart

changed datetimepicker2 = dtEnd

changed datagrid1 = dgTimeView

here is the code


       Dim strEmployee As String = txtEmployee.Text.Trim.ToString
       Dim strStart As String = dtStart.Value.ToShortDateString
       Dim strEnd As String = dtEnd.Value.ToShortDateString

       Dim strSQL As String
       Dim strConnection As String

       strSQL = "SELECT Timein.* FROM Timein WHERE  (((Timein.DateIn) between #" & strStart & "# and #" & strEnd & "# AND EmpID =" & _
       strSQL = strSQL & "'" strEmployee & "'));"

       'I noticed your path was wrong
       strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\timeclock.mdb" 'make sure the path on your computer
       'is correct!!!!


       Dim myCnn As New OleDbConnection(strConnection)
       Dim myDA As New OleDbDataAdapter(strSQL, strConnection)
       Dim ds As New DataSet

       myDA.Fill(ds, "TimeIn")

       dgTimeView.DataSource = ds
       dgTimeView.DataMember = "TimeIn"

       myCnn.Close()
       myCnn.Dispose()
       myDA.Dispose()
       ds.Dispose()
       strSQL = ""

Edited December 15, 2003 by millenniasp

dakota97 · December 16, 2003

ok...now where do I put that code in the form?

millenniasp · December 16, 2003

double click on the button that you want this event to happen from. It's the one on the left. put the code in there.

Make sure the path to the database is correct! it should be something like c:\timeclock.mdb==make sure you put in the correct path!

dakota97 · December 16, 2003

Ok, thank you. One problem though. I'm getting an error during runtime on the following line:

myDA.Fill(ds, "TimeIn")

I made sure my DB was in the right location

millenniasp · December 16, 2003

hmmm okay that means that there is a problem with either the query or the connection,

put this in your connection string,

"User Id=admin;" & _

"Password=;"

so your connection string should look like this

strConnection ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\timeclock.mdb; User Id=admin; Password="

millenniasp · December 16, 2003

If you are still having problems here replace strSQL with this

strSQL = "SELECT TimeIn.* "

strSQL = strSQL & "FROM TimeIn "

strSQL = strSQL & "WHERE (((TimeIn.DateIn) Between #" & strStart & "# AND " & "#" & strEnd & "# ) AND ((TimeIn.EmpID)="" & strEmployee & ""));"

Now, if you EmpID is a numerical field, take out the double quotes so it would be

((TimeIn.EmpID)=" & strEmployee & "));"

dakota97 · December 16, 2003

I am still getting the error. I've doublechecked everything, and still cannot find the problem. There is no DB password, the DB is in the right directory, and it still won't work. This is getting very frustrating, but I guess that comes with the teritory. Any other suggestions?

millenniasp · December 16, 2003

I am on my way to work. If you can create a new empty Access Database,import the table in that you are using into the new database, delete the data from the table, post the new database here, I will have a look at it.

If you need help I willl write out detailed instructions when I get to work! We'll get this resolved :)

dakota97 · December 16, 2003

Ok, here is a new DB with the two tables that I have and fields. Thank you for your diligence and help. I will remember this in the future.

Chris

newdb.zipFetching info...

millenniasp · December 16, 2003

Private Sub Button1_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles Button1.Click

       Dim strEmployee As String = txtEmployee.Text.Trim.ToString
       Dim strStart As String = dtStart.Value.ToShortDateString
       Dim strEnd As String = dtEnd.Value.ToShortDateString

       Dim strSQL As String
       Dim strConnection As String
       
       Try
           strSQL = "SELECT TimeIn.* "
           strSQL = strSQL & "FROM TimeIn "
           strSQL = strSQL & "WHERE (((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd
           strSQL = strSQL & "#) AND ((TimeIn.EmpID)=" & "'" & strEmployee & "'" & "));"


           'I noticed your path was wrong
           strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\TimeClock.mdb; " & _
           strConnection = strConnection & "User ID=admin; Password=;" 


           Dim myCnn As New OleDbConnection(strConnection)
           Dim myDA As New OleDbDataAdapter(strSQL, myCnn)
           myCnn.Open()
           Dim ds As New DataSet

           myDA.Fill(ds, "TimeIn")

           dgTimeView.DataSource = ds
           dgTimeView.DataMember = "TimeIn"


           myCnn.Close()
           myCnn.Dispose()
           myDA.Dispose()
           ds.Dispose()
           strSQL = ""


       Catch ex As Exception
           'Dim strMessage As String
           'strMessage = ex.Message.ToString
           'strMessage = strMessage & "  " & ex.ToString

           'Label1.Text = strMessage
       End Try

   End Sub

this works on my machine

millenniasp · December 16, 2003

I have access 2000 on my computer, your version is a higher one than mine.

What are the table names and the fields with their values?

example

TimeIn

Auto autonumber (whatever your primary key is)

EmpID text

DateIn datetime

if you give me the format of the tables I can recreate them and post the query.

the only thing that will change is the query.

dakota97 · December 17, 2003

Ok, here's the DB in 2000 format

newdb2000.zipFetching info...

millenniasp · December 17, 2003

Okay, Now I see the problem, I will be at work in about an hour. Your database table is totally different than mine. I will post the query after testing it on my laptop in about an hour.

dakota97 · December 17, 2003

Again, thank you very much for your help. I'll be waiting for your response

Chris

millenniasp · December 17, 2003

Is this a class or a work project? I only ask because there is some database changes that I would like to recommend.

dakota97 · December 17, 2003

It's a work project. I'm the General Manager of a local computer store, and I'm trying to come up with a simple program to keep track of our employee hours. Any suggestions would be greatly appreciated, so feel free to make any changes you would like, and I can work arouind them.

millenniasp · December 17, 2003

Private Sub Button1_Click(ByVal sender As System.Object, _
               ByVal e As System.EventArgs) Handles Button1.Click

       Dim strEmployee As String = txtEmployee.Text.Trim.ToString
       Dim strStart As String = dtStart.Value.ToShortDateString
       Dim strEnd As String = dtEnd.Value.ToShortDateString

       Dim strSQL As String
       Dim strConnection As String
       
       Try
           strSQL = "SELECT TimeIn.* "
           strSQL = strSQL & "FROM TimeIn "
           strSQL = strSQL & "WHERE (((TimeIn.DateIn) Between #" & strStart & "# And #" & strEnd
           strSQL = strSQL & "#) AND ((TimeIn.EmpID)=" & "'" & strEmployee & "'" & "));"



           strConnection = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\TimeClock.mdb; " 

           Dim myCnn As New OleDbConnection(strConnection)
           Dim myDA As New OleDbDataAdapter(strSQL, myCnn)
           myCnn.Open()
           Dim ds As New DataSet

           myDA.Fill(ds, "TimeIn")

           dgTimeView.DataSource = ds
           dgTimeView.DataMember = "TimeIn"


           myCnn.Close()
           myCnn.Dispose()
           myDA.Dispose()
           ds.Dispose()
           strSQL = ""


       Catch ex As Exception
           Dim strMessage As String
           strMessage = ex.Message.ToString
           strMessage = strMessage & "  " & ex.ToString

           Label1.Text = strMessage
       End Try

   End Sub

this works on my machine!

Sign In

Datagrid question from query statement

Recommended Posts

dakota97

Bucky

millenniasp

dakota97

millenniasp

millenniasp

dakota97

millenniasp

millenniasp

dakota97

millenniasp

dakota97

millenniasp

millenniasp

dakota97

millenniasp

dakota97

millenniasp

millenniasp

dakota97

millenniasp

dakota97

millenniasp

dakota97

millenniasp

Join the conversation

Browse

Activity